![](http://1.bp.blogspot.com/_Ba76y6K7kvs/R1_T-Es3WnI/AAAAAAAAAJQ/X5liInIrUG0/s320/Sql2008_colorgraph.png)
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:
![](http://3.bp.blogspot.com/_Ba76y6K7kvs/R1-zHks3WeI/AAAAAAAAAII/6ZZ4P_IMX6E/s400/Sql2008schema.png)
and the data inserted. The KMZ file was parsed with the XSD generator and C# (similar to John's example);
![](http://1.bp.blogspot.com/_Ba76y6K7kvs/R1-0mEs3WfI/AAAAAAAAAIQ/l5QYTpSAWmo/s400/Sql2008kmz.png)
the CSV files were opened in Excel
![](http://1.bp.blogspot.com/_Ba76y6K7kvs/R1-0rEs3WgI/AAAAAAAAAIY/8Y-0xirIk-Y/s400/Sql2008excel.png)
and INSERT statements generated.
![](http://4.bp.blogspot.com/_Ba76y6K7kvs/R1-0v0s3WhI/AAAAAAAAAIg/ctW_YKnF3_w/s400/Sql2008sql.png)
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
![](http://3.bp.blogspot.com/_Ba76y6K7kvs/R1-1jks3WiI/AAAAAAAAAIo/yl-PQo3HgC0/s400/Sql2008_area.png)
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
![](http://2.bp.blogspot.com/_Ba76y6K7kvs/R1-2CUs3WjI/AAAAAAAAAIw/8FhT19fuulg/s400/Sql2008_postcodearea.png)
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
![](http://2.bp.blogspot.com/_Ba76y6K7kvs/R1-4OUs3WkI/AAAAAAAAAI4/UW0lC9fncqQ/s400/Sql2008_suburbs.png)
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()
![](http://1.bp.blogspot.com/_Ba76y6K7kvs/R1-8uEs3WlI/AAAAAAAAAJA/toVF_TF16Pg/s400/Sql2008_density.png)
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
![](http://3.bp.blogspot.com/_Ba76y6K7kvs/R1--Aks3WmI/AAAAAAAAAJI/C-_vuP6JwXc/s400/Sql2008_polygon.png)
No comments:
Post a Comment
Note: only a member of this blog may post a comment.