Finding locations within distance SQLServer Azure

Managed to work out how to search within a circular buffer, and return the distance, as well as all features sorted by distance. The performance is impressive in Azure.

First I needed to create the geography column on the Azure table.

update <table> add SP_Geography geography;
update <table> set SP_Geography = GEOGRAPHY::Point(Y, X,4326);
create spatial index SIndx_SpatialTable_geography_col1 ON <table>(SP_Geography);

Will roll this into a stored procedure, but for now here is the raw SQL.

SqlCommand comm = new SqlCommand();
comm.CommandText = “declare @po geography  = geography::Point(@x, @y, 4326);” +
“declare @bu geography ;” +
“set rowcount @rows;” +
 ”set @bu = (select @po.STBuffer(@radius));” +
“select ID, Name, “+
“SP_Geography.STDistance(@po) Distance, “ +
“SP_Geography.STAsText() Obj “ +
“from <table> where SP_Geography.STIntersects(@bu)=1 order by Distance;” +
“set rowcount 0″;
comm.Parameters.Add(new SqlParameter(“rows”, rows));
comm.Parameters.Add(new SqlParameter(“y”, lon));
comm.Parameters.Add(new SqlParameter(“x”, lat));
comm.Parameters.Add(new SqlParameter(“radius”, radius));

First we create the comment, we then declare a variable of type geography and create a point with EPSG/SRS 4326, in this case the centre of the Bing map I am also rendering. We then create a buffer which we create around the point at a given radius. The rowcount is also dynamic letting us limit the number of returns. We then use an STIntersect on all features in the table against the buffer, ordering all results by distance (so they render form the centre out).

I rolled this into a Web Service with the WebMethod of WithinDistance.  I have also added integrated security between the application and the services, the services require a key which is the hash of a whole load of stuff, including a time dimension and the users id. Resulting in a really nice integration between the web services and the application.

The prototype looks like this, and the points render in realtime as the map is dragged.

Advertisement