locked
Analysis/Visualisation tools that natively support sql server spatial data types RRS feed

  • Question

  • Does anyone know of any analytical tools that will allow me to visualise the spatial data (polygons) held in sql server geometry fields?

    Something that'll let me add layers based on an sql query.. Just need to visualise and overlay the layers at the moment but any client side interaction/collision detection between the layers is a bonus.

    I know arcgis supports sql server as a backend but it's too complex a solution for what I'm after. i want to use my own tsql queries that perform spatial functions on the database side. My understanding is that arcgis simply uses the db as a data store and does all the computation in the application.

    Obviously powermaps/ssrs/powerview aren't viable as they only support a lat/long when it comes to spatial

    I've tried the SpatialWpf viewer demo app off codeproject but it's too buggy.


    Jakub @ Adelaide, Australia

    Wednesday, September 17, 2014 12:02 AM

Answers

  • Not an exhaustive list, but hopefully this will give you some idea starting points.

    Based on your description, you might have best luck using Bing map or Google map support. Not exactly sure what you want built-in, but SQL Server queries emit WKT, which both/all map APIs support. There's a lot of Bing map examples floating around; start with some of the Bing map forums http://blogs.bing.com/maps/2012/03/16/the-bing-maps-forums/ or their blog and ask specifically about things you want. If you want KML to pass to Google maps, I wrote a little library once (http://sqlservertokml.codeplex.com/) that does conversion to KML.  

    You're correct about PowerMap only supporting points, but SSRS supports ALL SQL spatial types directly, with the exception of the SQL Server 2012 curve types. Since it's a reporting system, the interactivity is limited to what you can do with URL redirection triggered by SSRS actions to a different report that shows other layers or other views of the map (e.g. no panning or scrolling).

    Some of the open source tools support SQL Server as well, such as MapServer (http://mapserver.org/), which is part of the OSGEO project. it may not require as much custom programming on your part. Here's the link to the SQL Server plug-in and instructions http://www.mapserver.org/input/vector/mssql.html.

    IIRC, in addition to ESRI's main products which do support SQL Server as a data store, there are/were also some free or inexpensive libraries (e.g. ARCGIS API for Silverlight, MapIt offerings) that expose the ESRI APIs and may do what you want with minimal programming. I worked with these a few years ago, not sure what the current status is. As you said there is a LOT more to ARCGIS than that, but the entire GIS package may be more than you need, but there may be a more affordable desktop version that does what you want.

    There's a SQL Server spatial integration piece for MapPoint, released a number of years ago. I see there is still a MapPoint 2013 version, not sure how well the integration works with the newer versions of MapPoint

    Hope this helps get you started, Bob




    Wednesday, September 17, 2014 8:29 AM

All replies

  • Not an exhaustive list, but hopefully this will give you some idea starting points.

    Based on your description, you might have best luck using Bing map or Google map support. Not exactly sure what you want built-in, but SQL Server queries emit WKT, which both/all map APIs support. There's a lot of Bing map examples floating around; start with some of the Bing map forums http://blogs.bing.com/maps/2012/03/16/the-bing-maps-forums/ or their blog and ask specifically about things you want. If you want KML to pass to Google maps, I wrote a little library once (http://sqlservertokml.codeplex.com/) that does conversion to KML.  

    You're correct about PowerMap only supporting points, but SSRS supports ALL SQL spatial types directly, with the exception of the SQL Server 2012 curve types. Since it's a reporting system, the interactivity is limited to what you can do with URL redirection triggered by SSRS actions to a different report that shows other layers or other views of the map (e.g. no panning or scrolling).

    Some of the open source tools support SQL Server as well, such as MapServer (http://mapserver.org/), which is part of the OSGEO project. it may not require as much custom programming on your part. Here's the link to the SQL Server plug-in and instructions http://www.mapserver.org/input/vector/mssql.html.

    IIRC, in addition to ESRI's main products which do support SQL Server as a data store, there are/were also some free or inexpensive libraries (e.g. ARCGIS API for Silverlight, MapIt offerings) that expose the ESRI APIs and may do what you want with minimal programming. I worked with these a few years ago, not sure what the current status is. As you said there is a LOT more to ARCGIS than that, but the entire GIS package may be more than you need, but there may be a more affordable desktop version that does what you want.

    There's a SQL Server spatial integration piece for MapPoint, released a number of years ago. I see there is still a MapPoint 2013 version, not sure how well the integration works with the newer versions of MapPoint

    Hope this helps get you started, Bob




    Wednesday, September 17, 2014 8:29 AM
  • oops forgot to click 'alert me on replies'

    This isn't for any kind of external facing webapp that i'm developing.

    It's purely internal analysis over some spatial data that we have.

    You're right regarding SSRS. I saw that it could do polygons and gave it a shot. Unfortunately it couldn't handle the 40-50k polygons I had and even when I jacked up the max geometry it couldn't render them all.

    I suspect the web APIs of google and bing will similarly struggle with getting passed tens of thousands of polygons

    unfortunately my data is from Australia, and MapPoint looks to be US and EU only with only some web service for Australia. Again I don't think it could handle what I have to work with.

    I was hoping to stay away from web apps due to the volume of data, but it looks like i'll mapserver is the only remaining contender


    Jakub @ Adelaide, Australia

    Friday, September 26, 2014 5:32 AM
  • Or, if you've eliminated all of the others because of your sheer number of polygons, aversion to web-based rendering, and requirement of keeping track of that many polygons and layers materialized for rendering all at once, you might reconsider ARCGIS.... or equivalent if there is one.

    Cheers, Bob

    Friday, September 26, 2014 8:11 AM