5

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

|improve this question
  • So are your lat/long values in a geometry column? If not, you'll have to do that first to get any use out of spatial queries. Mapoholic assumes that the column containing the geometry is called shape, and so the query is testing the intersection of a buffered point and each row of the locations table. Beware that doing such queries on large areas that haven't been projected will give you incorrect results. This would be ok at city level, but would be very poor at state or country size. PostGIS has a geography type that alleviates some of these problems, but I don't know if SQL-server does. – MerseyViking Jul 27 '11 at 13:31
  • SQL server does as well- it defines two types, similar to PostGIS, geometry (cartesian) and geography (spherical). And I think it would be ok even on large areas since you HAVE to define the projection- in this case 4326 – mapoholic Jul 28 '11 at 12:34
  • Also- about not having an actual geography column: You could create the geography on the fly. Try this: select * from pois_wgs84 where geography::STPointFromText('POINT(' + CAST(x_col as varchar) + ' ' + CAST(y_col as varchar) +')', 4326) .STIntersects(geography::STPointFromText('POINT(32.113 -81.3225)', 4326).STBuffer(10))=1 (assuming your lat/lon columns are called X_COL, Y_COL – mapoholic Jul 28 '11 at 13:46
4

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

|improve this answer
  • I was able to get lat long into geography data type and perform the STIntersect and STBUffer on my dataset. thanks – vic Jul 27 '11 at 18:08
  • is SQL_Server smart enough to understand the 10 as meters and not degrees? – Ian Turton♦ Jul 28 '11 at 10:31
  • thats a very good question! I think so. In sqlserver you can visualize the results of any query and it seems to calculate it as meters rather than degrees. And even the example that is given for STBuffer talks about meters – mapoholic Jul 28 '11 at 12:36
  • In order for a query like this to work, the comma between the latitude and longitude must be removed. Also, longitude must come first in the POINT function. – Nate S. Apr 15 '15 at 19:12
1

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
|improve this answer
  • For improved (and numerically more stable) formulas search our site for Haversine. – whuber♦ Nov 13 '12 at 6:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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