SELECT top 35 Lat Lan records with reference to a particular Lat Lan from MS SQL Table

Question

I am working with a 'NearBy Properties' Module in C#,Asp.net,.net ver 2.0 and MSSQL 2005.My requirement is like ,

I pass a specific Lat and Lan value to DB,It should fetch me 35 nearest properties by comparing the passed Lat Lan with the Lat Lan values already saved in DB.Can this be done in the SQL part itself ?

I can do this in Asp.net part by

looping through all the rows in a DataTable,then

Creating an extra field called 'Miles' in DataTable and inserting Km values in to it,like this

public decimal calcDistance(decimal latA, decimal longA, decimal latB, decimal longB)
{
        double theDistance = (Math.Sin(DegreesToRadians(latA)) *
        Math.Sin(DegreesToRadians(latB)) +
        Math.Cos(DegreesToRadians(latA)) *
        Math.Cos(DegreesToRadians(latB)) *
        Math.Cos(DegreesToRadians(longA - longB)));

return Convert.ToDecimal((RadiansToDegrees(Math.Acos(theDistance)))) * 69.09M * 1.6093M;
}

Then Sorting the DataTable with 'Miles' field,and fetching top 35 rows from DataTable.

The Problem is,

I have to fetch all the DB rows in to asp part and then do a for loop and then do a sort,this takes up lot of time and resources,Is there any way I can execute this in the SQL part itself ?

SOLUTION

I tweaked @Bacon Bits answer below as

SELECT PM.Id AS PropertyId,PM.Address,PM.PropertyImage,  ROUND((DEGREES(ACOS(SIN(RADIANS('9.98')) * SIN(RADIANS(PM.Latitude)) + COS(RADIANS('9.98')) * COS(RADIANS(PM.Latitude)) * COS(RADIANS(76.27999999999997 - PM.Longitude)))) * 69.09 * 1.6093),0) AS Miles 
FROM Tbl_PropertyMaster PM 
INNER JOIN dbo.Tbl_PropertyApproval AS PA  
ON PA.PropertyId = PM.Id 
WHERE PM.IsDeleted='False' AND PM.Enabled='True' and PA.Action='Approved' AND ROUND((DEGREES(ACOS(SIN(RADIANS('9.98')) * SIN(RADIANS(PM.Latitude)) + COS(RADIANS('9.98')) * COS(RADIANS(PM.Latitude)) * COS(RADIANS(76.27999999999997 - PM.Longitude)))) * 69.09 * 1.6093),0) < 1000 
ORDER BY Miles DESC

Have you looked at http://stackoverflow.com/questions/6548940? — DocMax, Jan 25 '13 at 06:51
[SQL Server supports trig](http://msdn.microsoft.com/en-us/library/ms191457.aspx) so it should be possible... You can even precompute most of the trig results if you're happy to sacrifice space for speed. — Rawling, Jan 25 '13 at 06:58

score 0 · Answer 1 · edited May 23 '17 at 12:03

Create a Scalar Function that calculate the distance between given two set of lan-lat parameters (like you did in asp.net side). Example:

SELECT TOP 35 * FROM Table_1 ORDER BY dbo.calcDistance(field_latitude,field_longitude, param_lat,param_long) DESC;

I dont know how to calculate the distance from given two set of lang,lat. It is up to you write the function implementation. You can use here and here

Bacon Bits · Accepted Answer · 2013-01-25T14:38:12.180

0

Try the following user defined function, which is a conversion of the function you supplied to TSQL. This should be as accurate as SQL Server can be. The queries should work on SQL Server 2005 and later. Note that you should probably evaluate the product of the two static values. I'm just lazy.

CREATE FUNCTION dbo.calcDistance (
    @latA AS NUMERIC(38, 35),
    @longA AS NUMERIC(38, 35),
    @latB AS NUMERIC(38, 35),
    @longB AS NUMERIC(38, 35)
    )
RETURNS NUMERIC(38, 35)
AS
BEGIN
    RETURN (DEGREES(ACOS(SIN(RADIANS(@latA)) * SIN(RADIANS(@latB)) + COS(RADIANS(@latA)) * COS(RADIANS(@latB)) * COS(RADIANS(@longA - @longB)))) * 69.09 * 1.6093)
END

SQLFiddle. My example uses the location in your test and the Wikipedia coordinates of Mangalore.

edited Jan 25 '13 at 14:38

answered Jan 25 '13 at 07:54

Bacon Bits

30,782
5
59
66

I tried this SELECT Id,Address,(DEGREES(ACOS(SIN(RADIANS(8.4874949) * SIN(RADIANS(Latitude)) + COS(RADIANS(8.4874949)) * COS(RADIANS(Latitude)) * COS(RADIANS(76.948623 - Longitude))))) * 69.09 * 1.6093) AS distanta FROM Tbl_PropertyMaster ORDER BY distanta ASC, Gives distance with only decimal changes,I dont think that is right – sajanyamaha Jan 25 '13 at 09:27
It may be a parenthetical or order of operations problem, but as I said I was converting your function. The main point is that the math functions are available. – Bacon Bits Jan 25 '13 at 12:51
yes, there is a paranthetical issue at the first sin function. It should be ..SIN(RADIANS(@latA)).. – Fredrick Gauss Jan 25 '13 at 14:22
but some rows are returning a value '9.49281351085778E-05' and other rows are returning correctly and are sorted correctly,but these rows are not taking part in sort and are listed on top of result set.Why is it so @BaconBits ? – sajanyamaha Jan 28 '13 at 04:35
9.49...E-05 is 0.0000949..... Assuming the function result is in km, that's, what, about 9.5 cm? Make sure to round the result to the precision of the input values. If you enter, for example, 45.98 that's only four significant digits. Remember that pi is irrational, so you're likely to get as many digits returned as the numeric field has because you're converting to degrees and radians. Are you trying to find the distance between the same point? What values aren't working for you? – Bacon Bits Jan 28 '13 at 05:15
Ok it worked,regarding 9.49...E-05,i was looking in to very close by locations.I updated the question above with your 'SOLUTION',Thanks a lot man. – sajanyamaha Jan 29 '13 at 04:24

SELECT top 35 Lat Lan records with reference to a particular Lat Lan from MS SQL Table

2 Answers2