![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjfAU3ZmNwkwcwurjLMZoAkM-YcB7StG2VIxCYygZAi0wYe4vNCNPppoZhdhwN6u0vEmUuRHzFbp8wx_TTH3AcXVN1oNrdAfQMrlT9JZCw1GjIF0zMqcmK6CA0P9JzNVLIJiuOD/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:
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgwdoenGZev18VfvYuPBA9rE-xFcJzaVyEDt7Cc97OUw8RuyvmQTH5gIfeXOWSvNnSceB9Sf2eEo2zb4A8ak35qzi827tKPlLCWeV7O3ysvWX3o6kHXeHatM9QCghvINCvnYDM6/s400/Sql2008schema.png)
and the data inserted. The KMZ file was parsed with the XSD generator and C# (similar to John's example);
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjyRl481l7v_hFhbi9kuxmZX165-3lpn86WS50J6273TcZPaCdtIWsOOXWa80m_qyc2kZVKzA_S1ZjIciqoQ16oz_cDnaEB-Fr49NYHQN7yudl9aKuTPXLOTtpiHuKBZW5lDqel/s400/Sql2008kmz.png)
the CSV files were opened in Excel
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4QWLNbVqo1TNcs3mZQW_Wn8vJO0evMkGd4wEyaIciDstqGngbtENl5UfEzAvU1aVPMVIhKUCMXBy9X5Q2to4H03KXxldzsuROW7Ar1uhQTK4wTryd-wIJTYKbdunJWfqGCERv/s400/Sql2008excel.png)
and INSERT statements generated.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgtEf2VGf96KHPLCgl3NUbVCGWRp-IbDhG-p8uGK2ZbYTmW5mlkIAXq3GcpbVQqssHiFb4JIZtLvGZSdNr16UcGVtubEzQukkU7gFnoaZoBzDb01cLWuNh4bWqnwbLA3I4pdrQs/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
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbrWBmPMYgEXE2S922Ngt8_vfCElHH_xqNvUv8b8fMQYziiFJdkrkFh94s7mg-pn83GVgVHQSPAegWHx-3gIVfqRYbdC0PuFYPbUzxMHx0NRfe05DSXlznFDW_yQODCOG1DlLu/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
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9S3EIIyQAiqLsDJawnG5_boqBOHB8ErXMp0m9BB-4nfq7rJGUJ4gLKUyxUX2jZGZPagonUc577bTAV7x3whsOn1GknU8gLoaxTCf41mgaxZNzqxncu41UJfIbJcfr_rcocMmp/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
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhJvumHtNfbbdfNq6aFZ5SYjC87TPWuAtUNMqB8MPZO10uWzJKq_ri1o0fKjMfZHrv7IF-Q0Vxa9LgSZ0P9We5mGEPdp_2cio0Hw-58wjUKcJhmyA6t-mUJuPcOXyVOapR_Hc5L/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()
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRJLN7BjODXFIagS1t6oDQRQFgZDA3YpcAbXVijMgX_f6hv-9yjVTdj2DaUKa9w5a6Dp1TJnVQGdDozH2eDkPUDMMZq3DWYp_xVuH_aH5Fv5Ut1msBRmZxIPzueR2tEHU7ZWXO/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
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgipxgeNtiZ3ZNYtxGgrGIaD5mDwWAXuuAS1pKHj4eH7HNwj5BXlMGW78Gn6_RTIcU0MoIkH67EbKQYluYMgNHIT7ibMaX6m9JRVC9VexdaqP2LhFwNoVX3JIuLGF4bsYEDUsJw/s400/Sql2008_polygon.png)
No comments:
Post a Comment
Note: only a member of this blog may post a comment.