Thursday 27 December 2007

SQL Server 2008 Geography: STExteriorRing, STInteriorRingN

MSDN describes STExteriorRing() with this query
DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 3 0, 3 3, 0 3, 0 0),(2 2, 2 1, 1 1, 1 2, 2 2))', 0);
SELECT @g.STExteriorRing().ToString();

which returns
LINESTRING (0 0, 3 0, 3 3, 0 0, 0 0)

Here it is visually -- the Green line is the original geometry (one square inside another), the thicker Blue line shows that STExteriorRing and the Orange line shows STInteriorRing(1):
DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 3 0, 3 3, 0 3, 0 0),(2 2, 2 1, 1 1, 1 2, 2 2))', 0);
SELECT @g.STExteriorRing(), 0.3 as thickness, 'Blue' as color
UNION ALL
SELECT @g.STInteriorRingN(1), 0.2 as thickness, 'Orange' as color
UNION ALL
SELECT @g, 0.1 as thickness, 'Green' as color



Interestingly, this query exposed a bug in Geoquery where the Exterior and Interior rings were being 'joined' (ie there was no "pen up" occuring as the shapes were generated) -- causing the dodgy looking lines on the world map in the last post. Here's the "fixed" map drawing...

SQL Server 2008 Geometry: STUnion Aggregate

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) -- Can only STUnion two Geometries
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))
SELECT dbo.UnionAggregate(Shape) FROM TestShapes



DOWNLOAD the source (8k) to try out the two aggregates posted on MSDN, or follow these steps:

1. Download the code from MSDN, or my copy. NOTE: You must do all these steps on a PC with SQL Server 2008 (Katmai) installed to get the Microsoft.SqlServer.Types.dll assembly. I copied this assembly into C:\assemblies\.

2. Compile the C# SQLCLR functions in C:\assemblies\ via the Command Prompt
c:\windows\microsoft.net\framework\v3.5\csc /t:library /r:Microsoft.SqlServer.Types.dll C:\assemblies\aggregate.cs

3. Execute T-SQL to register the aggregate functions in SQL Server 2008
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


Here's another example of using the STUnion Aggregate, this time to outline continents on a GEOMETRY map. Note the thickness and color columns are a feature of Geoquery.

select continent, dbo.UnionAggregate(Location), 0.5 as thickness, 'Brown' as color
from countries2 where continent is not null and Location.STArea() > 40
group by continent
union all
select name, Location, 0.1 as thickness, 'Green' as color
from countries2


(Not sure why weird lines appear through some of the STUnion-ed geometries... something to investigate another day)

Wednesday 19 December 2007

SQL Server 2008 Geometry: Reduce

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.


A few additional examples - some countries missing purely because the KML import had some errors...




NOTE 1: Geoquery Analyzer is currently in PRE alpha development status (ie. it's pretty dodgy). If/when the code gets to a fairly stable/usable point, it'll be uploaded here.

NOTE 2: if you looked really closely at the coordinates, you'll see I've changed the latitude values to be positive... the reason is NOTE 1.

NOTE 3: on the world maps, the 0,0 point is top/left which makes it appear upsidedown

Saturday 15 December 2007

Battleship 2008 - GEOMETRY datatype sample

Here's a quick example of the new coordinate datatypes in SQL Server 2008, in the form of a game!

Battleship 2008 - GEOMETRY datatype and spatial functions in SQL Server (and on CodeProject) uses the basics - POINT and LINESTRING - to build a simple, single-player version of the game Battleship

This is what a game "looks like" (13 is the GameId, 4 is my PlayerId... the other numbers are the co-ordinates I'm shooting at)

it's played in Management Studio, like a 'command line', so you'll need to keep your own copy of the board on paper (like this?)


Enjoy!

Thursday 13 December 2007

SQL Server 2008 Geography vs Geometry

Really basic comparison of GEOGRAPHY and GEOMETRY data types in SQL Server 2008.

First, some queries:
DECLARE @ll geography;
DECLARE @g geometry;

PRINT 'LatLong polygon length'
SET @ll = geography::STGeomFromText(
'POLYGON((1 1,1 5,5 5,5 1,1 1))', 4326);
SELECT @ll.STLength() -- 1773426.60585491

PRINT 'Geometry polygon length'
SET @g = geometry::STGeomFromText(
'POLYGON((1 1,1 5,5 5,5 1,1 1))', 4326);
SELECT @g.STLength() -- 16

PRINT 'LatLong polygon area'
SET @ll = geography::STGeomFromText(
'POLYGON((3 3,6 4,6 2,3 3))', 4326);
SELECT @ll.STArea() -- 36749145501.1338

PRINT 'Geometry polygon area'
SET @g = geometry::STGeomFromText(
'POLYGON((3 3,6 4,6 2,3 3))', 4326);
SELECT @g.STArea() -- 3


You might want to read this excellent explanation of the 'Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon has the wrong ring orientation.' requirement before looking at the next query.

PRINT 'Ring-orientation: counterclockwise'
DECLARE @ll geography;
SET @ll = geography::STGeomFromText('POLYGON((3 3,6 4,6 2,3 3))', 4326);
-- Command(s) completed successfully.
SET @ll = geography::STGeomFromText('POLYGON((3 3,6 2,6 4,3 3))', 4326);
-- The specified input does not represent a valid geography instance because it exceeds a single hemisphere.


Make sense? Maybe this diagram will help (green, anti-clockwise arrows good; red, clockwise arrows bad)


The first attempt at MUTLIPOLYGON didn't work, and at first I couldn't understand why. I got this example WKT from Wikipedia:
DECLARE @ll geography;
SET @ll = geography::STGeomFromText(
'MULTIPOLYGON(((1 1,5 1,5 5,1 5,1 1),(2 2, 3 2, 3 3, 2 3,2 2)),((3 3,6 2,6 4,3 3)))'
, 4326);
-- The specified input does not represent a valid geography instance.

Here it is 'on paper'

you'll notice they overlap and are all described clockwise... no problem, right? After all, the example came from Wikipedia! Hmmm, the Reduce doco just happens to mention for example, an invalid MultiPolygon instance is created if Reduce() is applied to simplify each ring in the instance and the resulting rings overlap..

Ah ha! So SQL Server doesn't like overlapping elements within a MULTIPOLYGON? Let's try removing the 'overlaps' (making the green polygon smaller) like this:
DECLARE @l1 geography;
SET @l1 = geography::STGeomFromText(
'MULTIPOLYGON(((2 2, 2 3, 3 3, 3 2, 2 2)
,(3 3,6 4,6 2,3 3))
,((1 1,2 1,2 2,1 2,1 1)))'
, 4326);
-- The specified input does not represent a valid geography instance



Still not working - hmm, maybe the ring-orientation is also a problem?

DECLARE @l1 geography;
SET @l1 = geography::STGeomFromText(
'MULTIPOLYGON(((2 2, 2 3, 3 3, 3 2, 2 2)
,(3 3,6 4,6 2,3 3))
,((1 1,1 2,2 2,2 1,1 1)))'

, 4326); -- IT WORKS!



Interestingly, as long one of the POLYGONs is anti-clockwise, the query seems to work! Also interestingly, the previous query works using the GEOMETRY type (just to prove that the 'hemisphere problem' only applies to GEOGRAPHY!).

DECLARE @l1 geometry;
SET @l1 = geometry::STGeomFromText(
'MULTIPOLYGON(((2 2, 2 3, 3 3, 3 2, 2 2)
,(3 3,6 4,6 2,3 3))
,((1 1,2 1,2 2,1 2,1 1)))'
, 4326);
-- WORKS FOR GEOMETRY


Finally, here's another MULITPOLYGON example
DECLARE @ll geography;
SET @ll = geography::STGeomFromText(
'MULTIPOLYGON ((
(35.003075386599093 136.49999931072208,
35.004100499758216 136.00000001975212,
35.000000005283141 136.00000001974058,
35.003075386599093 136.49999931072208)),
((34.999999973649494 138.00000000826108,
36.5 136,
35.761206832024406 136.00000002190484,
35.004100371491738 136.99469190018391,
34.999999973649494 138.00000000826108)))'

, 4326);

SELECT @ll.STLength() -- 634673.083368519
SELECT @ll.STArea() -- 11370068204.2658
SELECT @ll.STIntersects(geography::STGeomFromText('POINT(-30.041 153.166)',4326)) -- 0
SELECT @ll.STIntersects(geography::STGeomFromText('POINT(36.5 136)',4326)) -- 1


Here's the query that confirms they don't overlap and therefore make a valid MULTIPOLYGON:
DECLARE @l1 geography;
DECLARE @l2 geography;
SET @l1 = geography::STGeomFromText('POLYGON ((
35.003075386599093 136.49999931072208,
35.004100499758216 136.00000001975212,
35.000000005283141 136.00000001974058,
35.003075386599093 136.49999931072208))'
, 4326);
SET @l2 = geography::STGeomFromText('POLYGON ((
34.999999973649494 138.00000000826108,
36.5 136,
35.761206832024406 136.00000002190484,
35.004100371491738 136.99469190018391,
34.999999973649494 138.00000000826108))'

, 4326);
SELECT @l1.STIntersects(@l2) -- 0 -- doesn't intersect!



That last example is from this post (in Japanese) on these Microsoft.SqlServer.Types. There is also a couple of excellent images that demonstrate the difference between GEOGRAPHY and GEOMETRY (remember to imagine the 2nd image is on a sphere!).



Wednesday 12 December 2007

SQL Server 2008 Geography: STIntersects, STArea


Having read (and tried out) John's first impression of SQL 2008 Spatial, I went about extending the dataset to try some of the other capabilities of SQL Server 2008's geography datatypes and functions.

The following tables were added:

and the data inserted. The KMZ file was parsed with the XSD generator and C# (similar to John's example);



the CSV files were opened in Excel



and INSERT statements generated.



It's worthwhile noting that the data import didn't go 100% according to plan. Some electorates consist of more than one polygon (to be unioned together, I suppose) - I've only imported the first Polygon for each electorate in the KMZ file. Other electorates failed with SQL errors - still to determine why the coordinates listed cause SQL Server to fail (no, it's not the ring-orientation...).


The following queries were then possible...

select state
, electorate
, ROUND(Location.STArea()/1000000,0) As [AreaSqKm]
from electorates



select state
, electorate
, COUNT(p.Title) AS [NumberOfPostcodes]
, ROUND(e.Location.STArea()/1000000,0) AS [AreaSqKms]
, ROUND(e.Location.STArea()/1000000 /COUNT(p.Title),0) AS [AreaPerPostcodeSqKm]
from electorates e
inner join Postcodes p on (p.Location.STIntersects(e.Location) = 1)
group by State, electorate, e.Location.STArea()/1000000



select state
, e.electorate
, p.Title as [Suburb]
, COUNT(pp.PollingPlaceID) AS NumberPollingPlaces
from electorates e
inner join Postcodes p on (p.Location.STIntersects(e.Location) = 1)
inner join PollingPlaces pp on (pp.Electorate = e.Electorate AND pp.Postcode = p.Postcode)
group by state, e.electorate, p.Title



select state
, e.electorate
, ROUND(e.Location.STArea(),0) AS [AreaSqMs]
, SUM(ppv.TotalVotes) AS [TotalVotes]
, ROUND(e.Location.STArea() /(1+SUM(ppv.TotalVotes)),0) AS [AreaPerVoterM]
from electorates e
inner join Postcodes p
on (p.Location.STIntersects(e.Location) = 1)
inner join PollingPlaces pp
inner join PollingPlaceVotes ppv on ppv.PollingPlaceId = pp.PollingPlaceId
on (pp.Electorate = e.Electorate AND pp.Postcode = p.Postcode)
group by State, e.electorate, e.Location.STArea()



It is this query I've attempted to map at the top of the post - hopefully demonstrating finding points inside a polygon to group postcode data together for couting and summation; and using the area function to determine 'density'.

The next step is to get the POLYGON data into a map (as well as the POINTS)...

select electorate, Location.STAsText() as PolygonWKT
from electorates order by electorate


Tuesday 11 December 2007

SQL Server 2008 (Katmai) Diagram Tool

The Database Diagram Tool in SQL Server 2008 (Katmai) Management Studio has (once again) not changed much from the previous version.

Some notes:

Scripted Diagram export still works
This script to 'export' diagrams to SQL file (designed for SQL Server 2005) works in 2008. Implies there hasn't been any change to the storage format, and probably diagrams scripted in 2005 can be 'rehydrated' in 2008.

Diagram context menu unchanged
Nothing new in this menu - although I had forgotten the Copy to Clipboard item existed...


Table context menu adds Spatial Indexes... item
New spatial types can have their indexes setup just like other index types


Scroll down to Spatial Indexes on this post and these explanations of spatial indexes for a brief explanation of 'how they work'

(and no, this dialog doesn't actually resize in Management Studio - it's been Photoshopped for clarity)

SQL Server 2008 (Katmai) Spatial/Geography Functions

Decided to take the 'night off' from SilverlightEarth.com to play with the new Geography types in the latest SQL Server 2008 CTP.

There is surprisingly little around in terms of demos and samples so far, John O'Brien's First Impression being the best by far (and uses Aussie data too!). Only took a few minute to get his postcode-data demo going in C# & WebDev Express 2008.

His code covers:
  • [geography] SQL Data Type
  • geography::STPointFromText(@WKT, 4326)
  • POLYGON (Well Known Text syntax)
  • geography::STPolyFromText(@WKTBounds, 4326)
  • STIntersects 'function'
  • SPATIAL INDEX on a column of type [geography]
and discusses the interesting 'side effect' of the datatype enforcing polygons not spanning greater than a hemisphere of area AND "Must be supplied in counter-clockwise order"! (the 4326 SRID is explained here)

This query over John's data:
SELECT Id, Postcode, Title, Location.STAsText()
FROM postcodes
returns the list of all the postcodes with 'human readable' co-ordinates, but that's about the only "interesting" thing you can do without adding other data...

So I visited the Google Election '07 site which has a link to http://voteaustralia.googlepages.com/aus.kmz that contains a 'collection' of polygons defining the electorates in Australia.

It was trivial to extract the data for just one electorate (Cook) using Excel to re-order (see counter-clockwise note above, and error below) and reformat the <coordinates> to fit in the following query:
DECLARE @g geography;
SET @g = geography::STGeomFromText('POLYGON((
-34.041 151.166,-34.036 151.178,-34.035 151.187,-34.037 151.195,
-- coordinates removed for clarity
-34.052 151.156,-34.044 151.162,-34.041 151.166))', 4326);

SELECT [Postcode] as Title
, [Title] as Description
, [Location].STAsText()
FROM [dbo].[Postcodes]
WHERE [Location].STIntersects(@g) = 1
Which returns the 31 postcodes in the Cook electorate, and
SELECT @g.STArea() -- square metres
which returns 195631.754900467 (square metres).

It shouldn't be hard to programmatically import the electorial boundaries to do more spatial queries; could also mash it up with aec polling place and voting population data (by postcode). Using the area and population we could do some density visualisation, and maybe some other useless calculations...

This is the error I got when I DIDN'T reverse the order of the coordinates from the KML file: localhost(MYPC\Administrator): Msg 6522, Level 16, State 1, Line 2
A .NET Framework error occurred during execution of user defined routine or aggregate 'geography':
Microsoft.SqlServer.Types.GLArgumentException: 24205: The specified input does not represent a valid geography instance because it exceeds a single hemisphere. Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon has the wrong ring orientation.
Microsoft.SqlServer.Types.GLArgumentException:
at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode)
at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeometryData g)
- as explained above, you must ensure the POLYGON is described in counter-clockwise order!


Link drop:
geography Data Type Reference (Books Online)

Demystifying Spatial Support in SQL Server 2008 is an EXCELLENT 'real world' example.

SQL Server 2008 Katmai will Include Spatial Support is also good reading

And finally, this is a rather old (May) 'introduction' but still worth a look.


WOT: TODO in SilverlightEarth.com - FlashEarth mash-up

Saturday 1 December 2007

Introducing 'Gaml' - Geographic Application Markup Language

Adding a few geographic tags to existing Xaml provides infinite flexibility for map "markup". This is a simple example of a flickr photo 'balloon' on Silverlight Earth



<Canvas xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:g="http://silverlightearth.com/2007/gaml"
g:Latitude="40.712459"
g:Longitude="-73.9497466875"
g:Zoom="13"
>
<Path Stroke="Black" Fill="Gray"
Data="m 30,20 v -20 h 200 v 85 h -200 v -45 l -30,-10 l 30,-10" />
<Image Canvas.Top="5" Canvas.Left="35" Width="75" Height="75"
Source="http://....flickr.com/..._dcdc289280_s.jpg" />
<TextBlock Canvas.Left="114" Canvas.Top="8"
Text="New York - East River"
FontSize="10" FontFamily="Verdana" Foreground="#FFFFFFFF" />
</Canvas>


The sample currently works, but due to a hosting issue (not serving .gaml files), the other demo's are not currently online. When this is resolved, the above example will be available on http://www.silverlightearth.com/?q=http://www.silverlightearth.com/gaml/Samples/flickr_nyc1.gaml

There's more information on Gaml here, but there's a little more work to be done on the implementation... if you're wondering why some of the samples are flag-markers, they were the first examples of Xaml clipart that I could find!