SQL Server Reporting Services (SSRS) has excellent geospatial support for displaying data on a map.  Maps are typically created using ESRI Shapefiles (.shp files).  These Shapefiles are typically created with complex GIS software and made available for download (sometimes free and sometimes not) to be used.  Additionally, SSRS has an excellent default set of Shapefiles built in for the US which can show the country, states, and individual counties.

Example of SSRS Shape File showing Georgia and its 159 counties.

But what about when you need more flexibility in your geographic display?  Some examples of this may be wanting to display something that you can’t find a shape file for (maybe all the states and provinces in North America) or maybe you want to dynamically draw the geography based on some property of the dataset.  Geospatial data queries to the rescue!  Using SQL Server’s native geospatial support, a geospatial query can be created to return something as simple as a point or rectangle, or complex as the geography of an entire country and all of its rivers.

Getting all of the latitude and longitude coordinates to create a useful geospatial query could potentially be an enormous amount of work.  Fortunately, that work has already been done in a freely available resource, thanks to Natural Earth and Laurent Dupuis.  SQL Server 2012 or greater is recommended for this process.

Example of a geospatial query, shown in the SSMS results pane, based on the imported Natural Earth data.

What is it?

Natural Earth is an extensive public domain map dataset available at 1:10m, 1:50m, and 1:110 million scales in  vector and raster data formats.  Unfortunately, neither SQL Server or SSRS support these formats.  Fortunately, Laurent Dupuis created a .NET utility to convert these downloaded maps into geospatial data tables which can then be queries to create geospatial queries usable by SSRS or other geospatial-aware applications.

 

Download Necessary Files

1.  First, download Laurent’s Natural Earth Import Utility, available from his blog, here.  (alternate mirror link should the primary link become unavailable, here)

2.  Next, download the Natural Earth maps of interest.  In your web browser, go to http://www.naturalearthdata.com/downloads/.  In the text description, just above the “Large / Medium / Small” icons, you’ll see a sentence which says “Download all vector themes as SHP/GeoDB (279 mb).  Click that link (or click here) to perform that download of natural_earth_vector.zip.

Choose a resolution from the Natural Earth download page.

Run the Importer

1.  Once the downloads complete, unzip the NaturalEarth.zip Importer utility which was downloaded in step one of the downloads from Laurent’s site.  Do NOT unzip the large natural_earth_vector.zip file which was downloaded from the Natural Earth website in step 2 above — we’ll use that file in its zipped format.

2.  From the directory which Laurent’s Natural Earth Import Utility unzips into, go to NaturalEarth\NaturalEarthImport\bin\Release and double-click the NaturalEarthImport.exe file to run the application.

3.  Upon running the utility, in the Source File section, locate the previously downloaded natural_earth_vector.zip file.  In the Destination section, choose a SQL Server for the Server Name field and enter a database name in the Database section (make sure to the database exists — create a new blank one in SSMS if necessary.)  Press the Proceed button and the process will begin.  The import process will take 15-30 mins and create around 169 new tables.

Run the Natural Earth Import utility and point it to the natural_earth_vector.zip file previously downloaded.

 

The Natural Earth Importer in progress.

4.  After waiting for the import process to complete, you now have a full set of spatial data available for use.

A sampling of the tables created by the Natural Earth Importer.

Understanding the Data

The Natural Earth Downloads page provides a great reference for exploring the data.  The data is broken up as follows:

Resolution – The Natural Earth zip file contains a large amount of data, all of which is imported into SQL Server tables.  The data is divided into different resolutions, 10m (Large), 50m (Medium), and 110m (Small) based on the level of detail required.  All features may not be available at all resolutions.  10m is the most detailed (and contains the most points per shape) and 110m is the least detailed.  I typically recommend using the 50m dataset for most purposes.  The table filenames each contain the resolution for each table.

Categories – Each resolution is divided into up to three categories:  Cultural, Physical, Raster based on the type of data contained within the dataset.  Clicking on the link will provide a more detailed breakdown of the subcategories within each category.  (Note, there is no need to actually download anything from the following download buttons — these were all included in the original download).

  • Cultural – Country / state boundaries, cities, and other POIs.  The Admin subcategory of this category will typically be the most useful and frequently used data.
  • Physical – Water and geographic lines (equator, etc)
  • Raster – Topographic coloring data

Clicking into each category will bring up additional detail on the breakdown of subcategories within the main category.  Hovering over a green download button will give the filename of the download, which should match a table name in the imported SQL data tables.

Natural Earth Category details page.

 

Additional detail from the About page, clicked on from the Natural Earth subcategory page.

 

Data Table Organization

While there are a number of data tables in the Natural Earth dataset, the organization is very simple.  The table named layer_objects contains the geospatial shape data for the entire collection.  Each table with a “ne” prefix contains the metadata describing the collection of objects (such as states) and can be joined to the layer_objects table via the GlobalObjectID column.

 

Introductory Queries

A few basic queries to help get started — these make handy views if you use them frequently.  The results returned include both data which can be filtered and spatial results which can be displayed in the spatial results tab, or via an application such as SSRS.  These queries are just a starting point of things that can be done.

Spatial results for Canadian provinces and US states, with Hawaii filtered out.

 

Return all of the Canadian Provinces and US States, except Hawaii:

 

Show all of the major US and Canadian cities:

 

Show all of the major population centers in the US and Canada:

 

Conclusion

Following the above steps, you should now have a very rich source of spatial data in an easily filterable format, readily available in a SQL Server database.  This spatial data can be viewed in the SSMS results pane, or in any application which supports the display of spatial data via a spatial query, such as SSRS.  In the next part, I’ll go more in depth into using this data in an SSRS report.