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.

image

 

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

         ,Shape

         ,Shape.STDistance(@point)  distance

     from Contour

    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

        ,Shape

        ,Shape.STDistance(@point)  distance

    from Contour

   where Shape.STDistance(@point) < 3000

union  all

  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.

Published 05 April 2010 14:01 by simonsabin
Filed under: ,

Comments

# Twitter Trackbacks for Spatial data in the UK - Simons SQL Blog [sqlblogcasts.com] on Topsy.com

Pingback from  Twitter Trackbacks for                 Spatial data in the UK - Simons SQL Blog         [sqlblogcasts.com]        on Topsy.com

05 April 2010 15:11 by SimonS Blog on SQL Server Stuff

# 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