Spatial data in the UK
I am just loving the fact that the Ordance Survey has now released a huge amount of data that can be used freely.
I’ve downloaded the Panorama (tm) data http://www.ordnancesurvey.co.uk/oswebsite/products/land-form-panorama-contours/index.html. which is all the contours for the UK
This I’ve loaded into SQL Server using Safe Computing’s FME (http://www.safe.com/). This is because the data is a Autocad DXF file and translating that to SQL Server spatial data is not easy. The FME workbench is not too intuitive straight off but once you get the nack of how it works its very easy.
Almost 800,000 Contours take up ~ 1GB of space, and an index on top uses 200MB.
Its very cool.
This is the output of a query to get all the contours near where I grew up. I chose this because the is a very distinctive limestone ridge called the Wenlock Edge that runs to one side of the village. You can see a picture of it here http://www.bbc.co.uk/england/sevenwonders/midlands/wenlock-edge/index.shtml
I wondered if I would be able to figure it out from just the contours and you can. You can also see the hill I used to go sledging down.
So what use is this, well you can use a find nearest type query to get the nearest contour and therefore a rough approximation of your elevation.
To do a nearest the best solution is to find those within a specific distance and then order by distance. Currently the optimiser can’t optimise the straight order by STDistance which would be the simplest. If you don’t find a nearest then you need to look a little wider. This is the best route as it can efficiently use an index, the choice of your filter distance is the only thing you need to decide on.
select top 1 CAST(Elevation as varchar(100)) elevation
where Shape.STDistance(@point) < 500
order by Shape.STDistance(@point)
To display the results above I’ve also used a trick I use a lot to display the point I’m looking for. You will see that it is a circle (my house). To achieve this, union all the results of your query with query that returns the additional point i.e.
select CAST(Elevation as varchar(100)) elevation
where Shape.STDistance(@point) < 3000
select 'Home', @point.STBuffer(50),0
You might ask why you have to do a nearest neighbour query. This is because the contours are not always closed lines. I am contemplating working out have to close the lines so I can get polygons. Thats an interesting challenge, as is finding the two contours either side of a point so that you can interpolate the elevation more accurately. Those will be another blog post.
You could of course use an API to get the elevation, like the google maps api http://maps.google.com/maps/api/elevation/xml?locations=lat,long&sensor=false I couldn’t find a bing maps equivalent.