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