In my previous article, I covered creating geospatial SQL Server tables using the freely available Natural Earth resources. 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 which can be used as an alternative to ESRI Shapefiles for geospatial data. In this article, we’ll be creating a simple SQL Server Reporting Services report which utilizes this spatial data in lieu of the more commonly used shapefiles to plot data on a custom map.
To get started, we’ll first need to create some assets. Follow the steps in the previous article to setup the Natural Earth tables. Next, we’ll create some views to simplify queries, then we’ll create a report using these assets as well as some sample data.
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.
After installing SQL Server Reporting Services (SSRS), are you receiving an Error 404, Error 400, “Invalid Request” error, or “Bad Connection” error on first visiting the SSRS web portal (the error message seems to vary based on version, browser, and whether accessing via http/https or /reports vs /reportserver) ?
I’ve run into this a few times so I’m listing the steps I’ve used to fix it. For me, the root cause of this error has been the SSRS Configuration Wizard automatically configuring SSRS to use HTTPS, but assigning an invalid machine SSL Certificate. The fix is to self-generate a new and valid SSL certificate for the SSRS website to use. The below steps are done on the machine running the SSRS web portal: