Sql-server – Visualiser for spatial query results

spatialsql serversql-server-2008

This question Whats the easiest way to get sql 08 spatial data onto a map? (and many other articles) recommends Craig Dunn's Geoquery, but all the download links appear to be kaput.

SQL Server Managament Studio's spatial results tab will show a single dataset, and this MSDN article, Spatial Data Support In SQL Server 2008 mentions a workaround using UNION ALL to display point data overlayed on a map:

SELECT geog, name FROM Mondial.dbo.city WHERE geog IS NOT NULL 
UNION ALL 
SELECT geog, cntry_name FROM SpatialSamples.dbo.cntry00

Whenever I try something similar, I only ever get a single resultset displayed.

Is there something built into either SQL Server 2008 or 2012 to do this?

Best Answer

I would use Excel 2013 - it has fairly easy Map integration which can take Lat+Lon, eg:

http://www.sqljason.com/2013/03/geospatial-analytics-microsoft-bi-john.html

I would pull a SQL view through PowerPivot for easy derivation of .Lat and .Long, and for refreshing the data.

Alternatively SQL Server Reporting Services 2008 R2 or 2012 has similar Map integration, but the end user experience is static.

http://msdn.microsoft.com/en-AU/library/ee240845.aspx

Related Solutions

Sql-server – Where is the information about what files each database stored for SQL Server 2008 R2

SQL Server maintains this in sys.master_files, after you have restored the master database
When SQL Server starts up, you'll see a lot of suspect databases.

You can attach the MDFs and LDFs using the info in sys.master_files

Sql-server – SQL Server 2008 – Return records in chronological order, given a separation factor

Prior to SQL 2008, the most common solution was to use a UDF to calculate the great-circle distance between two points on a sphere. The Haversine formula is probably the most commonly used method.

Of course the Earth is not actually a perfect sphere, but this was considered "good enough" for most uses.

In SQL 2008, as you anticipated, such calculations are simplified and made more accurate by the introduction of the Geography and Geometry data types. Here's a brief sample of how you can use them to simplify distance calculations.

DECLARE @locations TABLE(locname VARCHAR(100), coord geography)
DECLARE @loc1 geography
DECLARE @loc2 geography


INSERT INTO @locations 
VALUES('HOME', geography::Point(-81.810194, 41.478156, 4326))   --Note: Lat, Long, SRID
                                                                --The 4326 is the SRID (spatial reference id) used by SQL as 
                                                                --a reference to the WGS 84 Standard. This is the same reference
                                                                --used by the GPS system
INSERT INTO @locations 
VALUES('WORK', geography::Point(-81.687771, 41.498227, 4326))

SELECT * FROM @locations

SELECT @loc1 = coord FROM @locations WHERE locname = 'HOME'
SELECT @loc2 = coord FROM @locations WHERE locname = 'WORK'

SELECT @loc1.STDistance(@loc2) * 3.2808399  --STDistance is in meters so we multiply to convert to feet 

SELECT @loc1.STIntersects(@loc2.STBuffer(300 / 3.2808399)) as isWithin300Ft  --This formula returns True when the point @loc1 intersects with the 300ft buffer zone around @loc2

The SRID is the key to the improved accuracy. The WGS 84 specification to which it refers includes a standardized coordinate system and a reference ellipsoid. In other words, it accounts for the non-spherical nature of the Earth, giving better results than a pure spherical Great Circle calculation.

If GIS accuracy is important to your work, this is the simplest way to implement it in SQL 2008.

Best Answer

Related Solutions

Sql-server – Where is the information about what files each database stored for SQL Server 2008 R2

Sql-server – SQL Server 2008 – Return records in chronological order, given a separation factor

Related Question