SQL Server Spatial data; adding a radius around a geometry

Posted on June 24, 2010

0


I have been using the new spatial datatypes of late for a project at work and came up against an interesting issue.

For accuracy by far the best datatype to store geograhic information is geography, if loaded correctly you can get very neat distance calculations between points, shapes etc based on the curvature of the earth. Nice.

However,  performance-wise Рits a dog. Even with spatial indexing applied; over thousands of loaded shapes and having to use STIntersects, STDistance to get the information we need it really wasnt performing.

So, we converted all the shapes, points to geometry – much like this:


DECLARE @geo geography -- This is our geography, imagine its got a shape in

-- Convert it to a geometry

SELECT GEOMETRY::STGeomFromWKB(@geo.STAsBinary(),4326)

All is good in the land of performance, sacrificing slight accuracy (in the UK, this is not significant). We now gain some nifty geometry only methods like STWithin, STContains plus many more. We can do all sort of funky stuff like show me all the postcodes in (STWithin) London. Neato.

Then of course we wanted to apply a radius around our searching, e.g. show me all the Towns within a 3000 metre distance from a postcode… and you cant, at least not as you think.

If the data was still a geography, you’d just use STBuffer(@radius) to apply a X metre radius around your starting point. Now the data is a geometry, STBuffer still exists but its now on a different scale – you cant just (easily) convert your metres to a value that STBuffer will give you an accurate result.

So, can it be done? Well, I think I found an easy answer. By flipping the shape from a geometry to a geography and back we can apply a STBuffer in metres around our shape.

This can be done like this:

DECLARE @radius INT -- e.g. 3000 metres
DECLARE @geo geometry -- my starting shape
SET @geo = GEOMETRY::STGeomFromWKB(GEOGRAPHY::STGeomFromWKB(
@geo.STAsBinary(),4326).STBuffer(@radius).STAsBinary(),4326)
SELECT Geo FROM GeometryTable
WHERE Geo.STWithin (@geo) = 1

What this little snippet would do is return you all shapes from your geometry table where they are within (inside) your starting shape plus a 3000 metre radius. The calculation is simple and quick. Assuming you have got a spatial index (in our case, we have a bounding box of the UK) the performance is very nippy indeed. Next up; showing distance in metres...

Edit: After this article was published , we ended up tranforming the data via a different project (Tranverse Mercator) which meant we didnt need to flip the geometry to get a buffer.
Advertisements