Point on Circle (Longitude/Latitude) in .NET/SQL

Question

Hello ;)

I've got the following problem: I have a table with long/lat values and another table with a project-id and long/lat/radius (circle).

Now I need to find out which project matches the long/lat values from the first table.

Is there an easy way to do that?

I'm using VB.net and a SQL Server.

Thank you very much for your help.

Answer 1

There are multiple algorithms to calculate distance on sphere, but we use following:

create function GetDistance(
    @latitudeFrom decimal(30,10), 
    @longitudeFrom decimal(30,10),
    @latitudeTo decimal(30,10), 
    @longitudeTo decimal(30,10)
)
RETURNS float
AS
BEGIN

DECLARE @distance float

    SET @distance = ROUND(6378.137 * ACOS(
            convert(decimal(30,10), 
            (SIN(RADIANS(@latitudeFrom)) * SIN(RADIANS(@latitudeTo))) +
            (COS(RADIANS(@latitudeFrom)) * COS(RADIANS(@latitudeTo)) *
             COS(RADIANS(@longitudeTo) - RADIANS(@longitudeFrom))))), 15)

 RETURN @distance
 END
 go

(where 6378.137 - is Earth's radius)

So now when you can calculate distance between 2 points on Earth you can build query

 select * 
     from Table1, Project 
     where dbo.GetDistance(
           Table1.lat, Table1.lon, 
           Project.lat, Project.lon) < @YouRadius

where @YouRadius - parameterized radius of your circle

Answer 2

Hi

Relatively easy: compute the great-circle distance between the point and the centre of the circle. If the distance is less than the radius, the point is in the circle, if not not. If it's on the circumference of the circle, you decide. There have been several SO discussions on computation of great-circle distances, have a look at them.

To speed things up you might consider storing, with the definition of each circle, its minimum bounding box in lat/long 'squares'. This would provide you with the info necessary to do a quick check of whether a point is possibly inside a circle, only computing distances when the point is inside the mbb.

Regards

Mark