Geoquery Analyzer

December 2007
The Geo in Geoquery is for GEOmetry and GEOgraphy - the two new types in SQL Server 2008 (code-name 'Katmai'). Geoquery Analyzer is not currently available for download, but you can see some screenshots of it 'working' below:

Playing SQL Battleship 2008

Aggregate Spatial Functions

There doesn't seem to be a lot of information about Geometry Aggregates, except for these posts on MSDN Forums, including How to calculate table all geometry extent?

Why might we want aggregate functions on the GEOMETRY spatial datatype? Here's an example of 'how to use' the STUnion() from MSDN:

DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 0);
SET @h = geometry::STGeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0);
SELECT @g.STUnion(@h)

On the left are the two GEOMETRYs, on the right is the STUnion() result.

The problem with these simple examples is they don't scale well to 'real' spatial database queries where you need to use these functions across result-sets. For example, it would be much nicer if we could insert data as follows

INSERT INTO TestShapes VALUES (geometry::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 0))
INSERT INTO TestShapes VALUES (geometry::STGeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0))
-- Now 'Union' all the shapes
SELECT dbo.UnionAggregate(Shape) FROM TestShapes

On the left is the result of this new UnionAggregate query, on the left is the result after added another shape;

INSERT INTO TestShapes VALUES (geometry::STGeomFromText('POLYGON((3 3, 5 3, 5 5, 3 5, 3 3))', 0))

DOWNLOAD the source (8k) to try out the two aggregates posted on MSDN (you'll use the CMD and SQL commands below).

c:\windows\microsoft.net\framework\v3.5\csc /t:library /r:Microsoft.SqlServer.Types.dll aggregate.cs

CREATE ASSEMBLY SpatialAggregate FROM 'c:\assemblies\Aggregate.dll'
go
CREATE AGGREGATE EnvelopeAggregate(@input GEOMETRY) RETURNS GEOMETRY 
external name [SpatialAggregate].[SilverlightEarth.Geoquery.SqlClr.EnvelopeAggregate]
go
CREATE AGGREGATE UnionAggregate(@input GEOMETRY) RETURNS GEOMETRY 
external name [SpatialAggregate].[SilverlightEarth.Geoquery.SqlClr.UnionAggregate]
go

sp_configure 'clr enabled', 1;
go
RECONFIGURE;
go

Visualizing Map POLYGONs

Geoquery Analyzer doesn't yet handle GEOGRAPHY datatypes, so I uploaded the World Countries as (GEOMETRY) polygons. This means the STArea() calculation is not correct, but it still enables the POLYGONs to be 'mapped' using Geoquery Analyzer.
The two queries are below; the first returns the POLYGONs 'as is'

SELECT * FROM Countries2

while the second query uses the STArea() function to determine what Reduce(tolerance) to use. The STNumPoints()

SELECT Location.STNumPoints(), Location.STArea(), name
, CASE 
WHEN Location.STArea() > 500 THEN Location.Reduce(1)
WHEN Location.STArea() > 200 THEN Location.Reduce(0.5)
WHEN Location.STArea() > 100 THEN Location.Reduce(0.1)
ELSE Location END
FROM countries2
ORDER BY  Location.STArea() DESC

select *
from countries2 
where population > (40 * 1000000) -- 40 million
order by population desc

Visualizing POINT, LINESTRING and POLYGON

Visualizing the results from this post on using STUnion and STConvexHull.

Visualizing the Reduce() function

I once had a go at using the Douglas-Peucker Algorithm in C# (for RaceReplay.net), so it was interesting to see it "pop-up" in SQL Server 2008 as the Reduce() function.

To understand what it does, you could try these queries (using country polygon data from the Google Earth community):

-- Tasmania from kmz
DECLARE @a GEOMETRY
SET @a = geometry::STGeomFromText('POLYGON((146.5861 41.18666, 147.9718 40.74479, 148.2733 40.90111, 148.3638 42.22243, 148.1953 41.94545, 148.0791 42.11722, 147.8429 42.87292, 147.9997 42.90708, 147.9955 43.22759, 147.7897 43.24695, 147.6316 43.06555, 147.7065 42.93833, 147.8994 43.02687, 147.8258 42.93195, 147.5575 42.83056, 147.4271 43.04174, 147.3175 42.84666, 147.2475 43.26917, 146.9913 43.11243, 147.0953 43.28872, 146.8336 43.64806, 146.0383 43.49805, 145.9327 43.37632, 146.2345 43.32514, 145.8369 43.29723, 145.4597 42.90444, 145.2052 42.25695, 145.4694 42.52306, 145.552 42.35111, 144.8586 41.54445, 144.6372 41.03194, 144.7014 40.75917000000001, 146.5861 41.18666))',0)
SELECT @a.STNumPoints() AS [PointsInRawPolygon]
, @a.Reduce(0.2).STNumPoints() AS [PointsReduce1]
, @a.Reduce(1).STNumPoints() AS [PointsReduce5]



But it would be even better if we could visualize the effects that Reduce() has on the polygon data... introducing Geoquery Analyzer

Polygon data without Reduce()



Polygon data with moderate Reduce() tolerance



Polygon data with agressive Reduce() tolerance



Polygon data with overly agressive Reduce() tolerance!



And of course, if you raise the tolerance too high,
System.AgumentException: 24127: Using Reduce with the specified tolerance will yield an instance that is not valid. Using a smaller tolerance may achieve a valid result.