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.

If I understand @Mapoholic correctly, they 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).


2 Answers 2


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(90-@lat1))*COS(RADIANS(90-latitude)) +SIN(RADIANS(90-@lat1)) *SIN(RADIANS(90-latitude))*COS(RADIANS(@lon1-longitude)))*3959
<= @DistanceFilter

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.