SQL Server & BI blog by Andreas Wolter (Microsoft Certified Master): Database Engine - Reporting - Integration - Analysis Services

Andreas Wolter - SQL Server & BI blog (Database Engine, Reporting Services, Integration Services, Analysis Services)

Addresses for geographical Data, ESRI-Shapefiles and other SQL Server geographical related stuff

SQL Server 2008 R2 Reporting Services supports the visualization of geographical data in 3 ways.

  • Datasets, based on the built-in .Net System-datatypes “geography” and “geometry”, which have been around since SQL Server 2008
  • via built-in maps – but only for the United States
  • via ESRI-Shapefiles, which one has to provide on his own

Now, where can you get those Shape-files?

Other, than in the USA where gathered data belongs to the people, with no cost (Census Bureau Geography), in other countries, such as in Germany, it is by far not easy, to even get such datat all.

In the following, I am collecting internet-sites, where one can find geographical data. I'd be happy to include more URLs, if you found a good one, to share.

 

Spatial Data at MIT
libraries.mit.edu/gis/data/index.html

several links to other map-data, even outside US

MapCruzin.com
www.mapcruzin.com/...

free maps from around the world. political as well as physical, some even down to street-/building-level

Natural Earth
www.naturalearthdata.com/downloads/

several maps of the world with borders and physical structure

Bundeswahlleiter
www.bundeswahlleiter.de/...

maps of German "Wahlkreise", nice to play around with. They even include votes per area

www.datendieter.de

various kinds of  datasets, also geographical, mainly German – mostly for a fee, but some is also free

drupal.org/node/19983

Cities of the world database donated by MaxMind.com

dev.maxmind.com/geoip/legacy/geolite/

GeoLite Free Downloadable Databases

efele.net/maps/tz/world/

A shapefile of the TZ timezones of the world

www.earthtools.org/webservices.htm#timezone

Time Zone and Local Time - This service responds with the time zone and local time at the given latitude and longitude.

  

Miscellaneous:

Other:

MsSQLSpatial http://mssqlspatial.codeplex.com/ spatial datatype-extension for SQL Server 2005
Spatial Tools with Shapefile Uploader

http://www.sharpgis.net/page/SQL-Server-2008-Spatial-Tools.aspx

Tools for importing ESRI-Shapefiles into SQL Server as well as for looking at the data

 


Normal 0 21 false false false DE X-NONE X-NONE

Geocoding

The other option to show geospatial data in Reports, is to just use plain geometry/geography data. Here are some resources that help in getting access to such and/or converting/”geocoding” data:

The Google Geocoding API

developers.google.com/maps/documentation/geocoding/

Rob Farley shows how you can geocode you address-data using bing-maps and powershell:

sqlblog.com/blogs/rob_farley/archive/2010/05/23/fetching-latitude-and-longitude-co-ordinates-for-addresses-using-powershell.aspx

A nice walk-through in geocoding using SSIS and Google Geocoding API by Jeffrey Verheul:

devjef.wordpress.com/2013/02/05/enriching-your-dataset-the-ssis-way/

Discussion on Mapping From City Name to Latitude and Longitude & Mapping From Latitude and Longitude to City with further links: stackoverflow.com/questions/23572/latitude-longitude-database

 

 

Further good articles concerning geographical data and SQL Server:

Creating your own SSRS map using Visio

http://blog.oraylis.de/2010/07/creating-your-own-ssrs-map-using-visio/

Using Visio and SSRS Map Reports for Store Layouts etc.

http://blog.oraylis.de/2010/06/using-visio-and-ssrs-map-reports-for-store-layouts-etc/

Using ESRI ShapeFiles with SSI

http://ssis-components.net/post/2010/04/11/ESRI-ShapeFiles-verarbeiten.aspx

For importing shape-files into SQL Server, see this blog-post at MSDN: http://blogs.msdn.com/seanboon/archive/2009/11/17/sql-server-2008-r2-map-tips-how-to-import-shapefiles-into-sql-server-and-aggregate-spatial-data.aspx

Achieve Spatial Data Support in SSIS:
http://www.sql-server-performance.com/articles/biz/spatial_data_support_ssis_p1.aspx

Bridge The Gap between Bing and Google Maps using SSIS
http://www.sql-server-performance.com/articles/biz/bing_google_maps_p1.aspx 

Heat Maps as Reports

http://prologika.com/CS/blogs/blog/archive/2009/08/30/heat-maps-as-reports.aspx

Stacia Misner shows in her blog, how to include your maps in the Map-Gallery for the Map-Builder-Wizard:

http://blog.datainspirations.com/2010/08/31/do-it-yourself-map-gallery-in-sql-server-2008-r2-reporting-services/

Rob Farley shows, how you can geocode you address-data using bing-maps and powershell:

http://sqlblog.com/blogs/rob_farley/archive/2010/05/23/fetching-latitude-and-longitude-co-ordinates-for-addresses-using-powershell.aspx

 

Have fun with Reporting Services 2008 R2

Andreas


Sarpedon Quality Lab

Kommentare

Keine Kommentare