CODEWITHSUNDEEP
sql-server-2008coordinates
Ivan Fazaniuk
Ivan Fazaniuk

Reputation: 1070

Select data based on calculated distance between coordinates

Database stores 4 points with coordinates like:

Name | Lat | Long

Point 1 | 11.111 | 22.222

Point 2 | 22.222 | 33.333

Point 3 | 44.444 | 55.555

Point 4 | 66.666 | 77.777

Technology:

MS SQL Server

Web application gets current user lat and long via HTML5, then it should calculate what points of those 4 are nearer then 0.5 km. How?

It should display Point 1 and Point 2 based on this illustration:

enter image description here

Upvotes: 2

Views: 323

Answers (2)

Durai Amuthan.H
Durai Amuthan.H

Reputation: 32270

Using Sql Server:

You can find distance between two coordinates in KiloMetres using the below function

CREATE FUNCTION dbo.fnCalcDistanceKM(@lat1 FLOAT, @lat2 FLOAT, @lon1 FLOAT, @lon2 FLOAT)
    RETURNS FLOAT 
    AS
    BEGIN

        RETURN ACOS(SIN(PI()*@lat1/180.0)*SIN(PI()*@lat2/180.0)+COS(PI()*@lat1/180.0)*COS(PI()*@lat2/180.0)*COS(PI()*@lon2/180.0-PI()*@lon1/180.0))*6371
    END

Sample Usage:

select [dbo].[fnCalcDistanceKM](13.077085,80.262675,13.065701,80.258916)

Reference

Using Entity Framework(dot net):

Entity framework 5.0 allows you to write LINQ expression like this

private Facility GetNearestFacilityToJobsite(DbGeography jobsite)
{   
    var q1 = from f in context.Facilities            
             let distance = f.Geocode.Distance(jobsite)
             where distance < 500 * 1609.344     
             orderby distance 
             select f;   
    return q1.FirstOrDefault();
}

Reference

I hope this is enough to get you started.

Upvotes: 2

Karl Kieninger
Karl Kieninger

Reputation: 9129

You need to convert your point to the geography data type. Then you can do a WHERE @here.STDistance(testPoint) < 500.

The basics of using the geogrpahy point to calculate distance can be found in this question.

Upvotes: 0

Related Questions