Get all the points (circles with radius) that overlap a given point

Question

I need to find a better way for a "circle overlay with given points" system.

I have many points, for example. restaurants, and each item has a point of place and radius, for example. "food export". The radius is different, some have 3 km, others 10 km. I am looking from the point. for example "My position" latitude / longitude.

I need to find the best way to find all the restaurants that overlap my point. (Bring me food). (There is no point in the circle, but which circles overlap my point.)

I am thinking of storing lat / lng as a geography type in SQLServer 2008. Is this a way to do this?

Is it possible to query directly over sqlserver? Or do I need to do this in code? And what is the way to do this?

Answer 1

Yes, it is precisely that geography and spatial methods are good. Here is a quick example:

 DECLARE @Restaurant TABLE ( Name nvarchar(50), Location geography, DeliveryRadiusMetres int ); INSERT @Restaurant VALUES -- long lat ('Dominos','POINT(-0.109339 51.532835)',2000 ), ('Pizza Hut','POINT(-0.102961 51.541157)',2000 ); 

Note that here, to build geography values, I use an implicit string conversion that invokes geography::Parse behind the scenes.

 DECLARE @MyLocation geography = 'POINT(-0.115063 51.550231)'; SELECT Name FROM @Restaurant R WHERE R.Location.STDistance(@MyLocation) <= R.DeliveryRadiusMetres ; 

Answer 2

Yes, you can draw circles as points in the form of a geography / geometry type. You can then write SQL queries directly against this data with features such as STWithin and STTouches.

Set up the location of your restaurant as a point with a radius. Then you can write a query like this:

 DECLARE @RestaurantCoverage Geometry SET @RestaurantCoverage = 'Point(10 5)' --Location SET @RestaurantCoverage = @RestaurantCoverage.STBuffer(5) --Cover radius DECLARE @YourLocation Geometry SET @YourLocation = 'Point(13 5)' --Your location SELECT @YourLocation.STWithin(@RestaurantCoverage) 

http://technet.microsoft.com/en-us/library/bb933991.aspx

Also works directly with table data with the where statement.