Importing and using GIS Spatial Data
April 16, 2012 5 Comments
For a project I’m working on right now, we (the team I’m currently a part of) decided to research the use of GIS data. The GIS data is available for free at DIVA-GIS. If you want to download the GIS data, choose the country and in the Subject drop-down, choose “Administrative areas”.
To import the data, I’ve used an easy to use .NET Tool: Shape2SQL. This tool is created by Morten Nielsen (Blog | @dotMorten), and allows the user to import Shapefiles (.SHP) into SQL Server without problems. If you want to download this tool-set, I advise you to download the “SqlSpatialTools”, which also contains “SQLSpatial.exe”, which allows you to query and visualize the data.
After downloading the GIS data and tools, run the “Sharp2Sql.exe”. You will see the following screen pop up (at first run only):
Fill in the server and database information. In my case, I imported the data on a local SQL Server:
After that, you will see a start screen like this:
Now you need to select a SHP file. If you press the button, the following window shows:
As you can see, I picked the GIS data of The Netherlands as an example. After selecting a source file, you need to chance the settings of the import:
I also changed the “Geometry Name” on the right from “geom” to “geog”, just to remind myself that the content of the column is Geography- and not Geometry-data.
Once you decided about the options and naming conventions, press “Upload to Database”, and wait for the file to be processed:
Once the processing is completed, you can start using the GIS data. You can do this straight from SQL Server, but you could also use the “SqlSpatial.exe” that you downloaded as part of the “SqlSpatialTools”. If you choose to use this tool, it would look something like this:
You can run the same query in SQL Server Management Studio (SSMS), and you will get an extra tab in the resultset:
There’s only one more thing to remember: In SQL Server 2008 and 2008 R2 you can only select 1 hemisphere at a time. SQL Server 2012 has a new version of the Geography assembly, and supports querying multiple hemispheres at the same time. For more information about this, read the MSDN article about Spatial Data Types.