Creating a Shapefile Using Latitude and Longitude Coordinates
by Forrest Dermid , Senior Solutions Engineer
Let’s walk through how to create a shapefile using latitude and longitude coordinates. We’ll use QGis for the shapefile and Pyramid Analytics’ BI Office for the presentation layer.
Before you get started, it is important to review this entire post to understand the process and ensure your file has the necessary information for a hierarchy (if appropriate) so shapefiles are presented as desired. Also, ensure your data is clean before you start—no duplicates or bad data like a zip code that does not exist.
To begin, create or obtain a file that has latitude and longitude coordinates, in our case we are plotting oil and gas wells. Each well also has field, state, county, country and other relevant information that will be needed later for the drill down through the available shapefiles. We’ll need to create a shapefile. We can do that using QGis which is free and can be downloaded here. This is not the only option for creating shapefiles, but it is what we’ll use for today’s discussion.
Here are the steps you’ll need to follow to create a shapefile using latitude and longitude coordinates.
- After installing QGIS, Open QGIS
- Select Add Delimited Text Layer from the Layer menu tab as shown below.
- Browse to your scv file and import the file with latitude and longitude coordinates.
- Generate Point Coordinates: X Field = Longitude Field Name, Y Field = Latitude Field Name
After checking to ensure your data is aligned to the column name, select OK. Tip: If you are exporting from SQL Server and having trouble with the fields losing their column alignment when importing in QGis, try exporting to Excel from SQL Server, then open and save the Excel file as csv.
You may be prompted to select the Coordinate Reference System. We used WGS84. Select OK.
You will now see the wells via their coordinates as referenced below.
Save the QGis project into a folder.
To create a shapefile, right click on the file imported in Layer Panel (tblWellNames was ours) and select Save As referenced below.
Select format (ESRI Shapefile), name the file, and browse to the location you want to save the shapefile. Select Save, then select OK.
- You now have two files in your project as referenced below, shapefile and the project. Select Save from the ribbon.
Browse to the folder where you saved your shapefile and confirm you have the following files: .dbf, .prj, .qpj, .shp, .shx. Tip: If you saved the files in a folder with other files, create a new folder and move the files into it. Then the zip file will be named the folder name as described in the next step.
Select the following three files individually (.dbf, .shp, .shx), right click to create a zip file. This file will be used to upload to BI Office. The three files are added to the zip file individually.
Open the BI Office Admin Console.
Select the Settings tab.
Select Geo Spatial.
Enter the descriptive information for the new file being uploaded.
For File, select the eclipse button and browse to the zip file created previously using the .dbf, .shp and .shx files from QGis.
- Select Next.
- For this step, we need to create a reference lookup (Pyramid Analytics ID - PAID) in the newly created table tblWellCoordinateNames with the existing table(s) in the Pyramid GIS Database for the shapefile. The field wellname is the lowest level in tblWellCoordinateNames and will be represented by the shapefile from the zip file created with QGis. The next highest shape level will be State which is already in the PA GIS database and the field for state in tblWellCoordinateNames is stateprov. The next highest shapefile will be Country which is already in the PA GIS database and the field for country in tblWellCoordinateNames is country.
- Select Process. When complete, you will have shapefiles in the PA GIS database to reference in your models via the table tblWellCoordinateNames. Hang in there, almost done.
- There are three methods for the Pyramid GIS lookup: PAID, Text, and Latitude/Longitude. See the documentation for the pros/cons of each. We are going to use PAID with multidimensional models.
- For the Well dimension, we need to create a view/table (dimFieldFinalPAID) that includes the PAID reference from the Pyramid GIS tables for the corresponding WellName, State, and Country. It will be important that the DB table fields you will be joining are compatible both in data type and label with the Pyramid GIS table fields.
Open your SQL Server Analysis Services project.
Add the new view/tables dimFieldFinalPAID to the data source view.
- Either create a dimension with wells or edit an existing dimension so that each level has the corresponding PAID attribute.
- For each PAID attribute, change the attribute properties type to Geography>GeoBoundaryPolygon and change the AttributeHierarchyEnabled property to False.
- Process the model.
- Open BI Office.
- Select New Data Discovery and select the OilGasWells model.
- Position the dimension Wells on rows and Measures on columns.
- Select the vertical split screen Report Layout and select the grid and map.
- If the Map report type is not enabled, check to ensure you have the Dimension levels labeled in the Well dimension as referenced below. Note the WellCoordinateName shape level you created and should be mapped to WellNames.
- Drill down on country USA to go to States.
- Drill down on Wyoming and see the Wells.
In this example the size of the pie chart represents the measure footage. Adding other measures will create a pie chart with slices, e.g., Oil and Water.
You did it! That’s how you create shapefiles using latitude and longitude coordinates. We hope you found this helpful.
NOTE: Within the BI Office user interface, you can use the geospatial upload tool to load shapefiles (WGS 84) and addresses into the Pyramid GIS database. This is an advanced function that should only be used by analysts with solid knowledge of the shape data. Go here for details.