More

Find location details within circle buffer

Find location details within circle buffer


Wondering how would i go about getting this to work.

I have a table with location data (lat, long, homevalue) in sql server 2008. Given a lat long say (32.113, -81.3225), i want to draw a 50 mile radius circle and get the number of locations and total home value within the circle buffer.

Thanks vic


Mapoholic,

Thanks for your reply. If i understand you correctly, you are using the "shape" as an attribute to perform stintersect and stbuffer on the location table. My limitations are that, i have the location table which has just the lat, long, homevalue in dollars. I need to independently draw a cirlce based on the given lat long, which if i am doing it right would be like this

DECLARE @Result geography SELECT @Result = geography::Point(32.113, -81.3225,4326); select @Result.STBuffer(10000);

and then i need to JOIN or use some method to check if the lat longs in the location table are within the @Result.STBuffer(10000). Any ideas?

Thanks again

vic


STBuffer and STIntersects are the functions you want I think. This is an example of getting the location records within a 10m buffer around the point:

select * from locations where shape.STIntersects(geography::STPointFromText('POINT(32.113, -81.3225)', 4326).STBuffer(10))=1

Where 4326 is the SRID (assuming the data is in WGS84) and 'shape' is the name of the spatial column


You can use a Great Circle distance calculation. To use km instead of miles use 6371 instead of 3959 below.

declare @lat1 as float, @lon1 as float, @DistanceFilter as float set @lat1 = 32.113 set @lon1 = -81.3225 set @DistanceFilter = 50 select count(*) count_properties, sum(homevalue) sum_homevalue from [table] where ACOS(COS(RADIANS([email protected]))*COS(RADIANS(90-latitude)) +SIN(RADIANS([email protected])) *SIN(RADIANS(90-latitude))*COS(RADIANS(@lon1-longitude)))*3959 <= @DistanceFilter