Wednesday 9 January 2008

Geoquery: Geography MkII

Just displaying a series of points doesn't look like much



but it can be the foundation of some neat spatial reporting - this query (attempts to) show the density of votes by postcode.

select p.postcode, p.location,
CASE
WHEN p.Postcode < 2000 THEN '#44ff0000'
WHEN p.Postcode < 3000 THEN '#4400ff00'
WHEN p.Postcode < 4000 THEN '#440000ff'
WHEN p.Postcode < 5000 THEN '#44880088'
WHEN p.Postcode < 7000 THEN '#44FFFF00'
END AS [Color]
, CASE WHEN Totalvotes < 1000 THEN 1
WHEN Totalvotes < 2000 THEN 2
WHEN Totalvotes < 3000 THEN 3
WHEN Totalvotes < 4000 THEN 4
WHEN Totalvotes < 5000 THEN 5
ELSE 6 END AS [Thickness]
from Postcodes P
INNER JOIN PollingPlaces pp ON pp.Postcode = p.Postcode
INNER JOIN PollingPlaceVotes ppv ON ppv.PollingPlaceID = pp.PollingPlaceID




Note the status bar - that's 56,951 rows!! Try loading those points into Virtual Earth! :-)

No comments:

Post a Comment

Note: only a member of this blog may post a comment.