compare longitude and latitude values of two tables

advance thanks to all
i want to do one report based on long and lat values.can anybody help me the logic behind this????

one eg

i have one table in my sql db which contain lon and lat values of vehicle movement.
eg:
lon is 58.549315555555559 and lat is 23.619708888888891
this table does not contain any location name.
...........................
i have another table which contain location name and corres lang and lat values.
In this table exact value of vehicle long and lat is not there

but i have nearly 10 records that long and lat values are near to above values(first table)

from this how i will select one location?
using which formula i want to do this?
nearly 20 meters differ is there then i can select.
..........

You could calculate the distance between the two locations you are comparing. MapPoint has a method to do that, but it sounds like you are working with raw numbers.

The calculation can be found on the Aviation Formulary:

Aviation Formulary V1.43 (http://williams.best.vwh.net/avform.htm)

This covers mathematically sound ways of calculating the trigonometry (ie. without errors blowing up).

Depending on how accurate you want things and what assumptions you want to make (eg. poles, international date line, etc), you could do a simple Pythagorean or Manhattan distance calculation. These are much quicker (especially Manhattan), and much easier to code without making mistakes, but they are less accurate.

Pythagorean distance = sqrt(x_diff*x_diff + y*diff*y_diff).
Technically only valid for Euclidean space - not the curved Earth. If you are near the equator and distances are small, it is actually pretty close. (higher latitudes can be improved my using a correction factor for longitude)

Manhattan distance = abs(x_diff) + abs(y_diff)
Much less accurate - even for Euclidean space, but still valid for some applications.
Might be applicable with corrections for a simple "is this a match?" question.


Richard

dear friend thank u somuch 4 ur reply...
dont think based on map point...i am doing reports...based on sql tables...
i looked formula.......can u tell me one simple example...based on

irst table
---------
lon is-> 58.549315555555559 and lat is-> 23.619708888888891

second table
------------
lon is-> 58.550315555555559 and lat is-> 23.619708888887791 location1
lon is-> 58.549415555555559 and lat is-> 23.619708888888891 location2

based on first table value which is the nearest point in second table...using formula can u explain me???

The maths to do an accurate comparison is on the Aviation Formulary (see link above). This includes a discussion of how to do an accurate calculation - the order of calculation can affect the accuracy.

Richard

dear friend
thank u so much...
i done distance calculation using

acos(sin(@lat1)*sin(@lat2)+cos(@lat1)*cos(@lat2)*c os(@Long1-@long2)) this formula....

again can u give me a suggestion....

first table i have one longitude1 and latitude1...
in my second table i have 4000 records of long2 and lat2 with location name......
here i want to check each longitude1 and latitude1 with these 4000 records and which is less distance that location name i am taking...
this will take lot of time...

so i dont want to check all 4000 records..i want to check some records which is most nearer to long1 and lat1 ....how i can find out that points...

expecting ur suggestion...
regards

The trigonometry might be relatively complex, but I would have thought it would take longer to fetch the coordinates from your database than to perform the calculation. I would have thought modern cpus then you should be looking at 4000 calculations in fractions of a second.

Do you want the closest, or just a matching pin?

If the latter, you could do a quick pass by comparing longitudes and latitudes (apply Wilfried's cos correction for longitudes when latitude is high). Then only do the full calculation on points that are within a certain fraction (eg. 1minute of arc).

If you are trying to find the closest, then you would need to be a bit clever, because the closest might be 100s of km away.

Simple comparison (pseudo code, I don't know which language you're using):

if (abs(lat1-lat2) <0.01 and abs( lng2/cos(lat1) - lng2/cos(lat2)) < 0.01 then
// do full check
endif

Note: "0.01" would depend on your units and how close the check should be.
If degrees, then 0.01 is about 2/3rds of a minute of arc.
ALSO: The above will give a maths error if lat1 or lat2 is 90deg (=north pole)- you MUST check for this.


Richard

dear friend
thank u very much for ur reply...
based on ur suggestion only i am moving...

i want a location name within 20 meter difference..........
eg
lon is-> 58.549315555555559 and lat is-> 23.619708888888891

using acos(sin(@lat1)*sin(@lat2)+cos(@lat1)*cos(@lat2)*c os(@Long1-@long2)) ...formula i can get location name....

in my report if i have 25 records...each records i want to compare with 4000 records....
basedon distance i am taking location name....

but this is very slow....

from 4000 records first i want to findout certain points which is most near to lon is-> 58.549315555555559 and lat is-> 23.619708888888891
if nearly 20 meters this is easy....

is (abs(lat1-lat2) <0.01 and abs( lng2/cos(lat1) - lng2/cos(lat2)) < 0.01
formula will give correct calculation???

now based on ur suggestion i am using the query is as follows

SELECT distinct [Longitude],[Latitude],[INFO_ID],location FROM [TRTY_ioo].[dbo].[POI]
where (abs(@lat1-[Latitude]) <0.01 and abs( [Longitude]/cos(@lat1) - [Longitude]/cos([Latitude])) < 0.01 )


is this query correct???assume @lat1 is 23.619708888888891 ie from first table...the query is based on second table...shall i move with this....

if lat1 or lat2 is 90deg ... then which value i want to pass there 90.1 is ok????
i am not expert in formulas...so pls help me...


regards

Hi,

To speed up I should not do the calculation inside the select statement. Then you can speed up a lot if you work with integers instead of floating point. You can work in 1/100000 degree for example. Also a copy of the table in memory could speed up a lot. And very important you need of course create the right indexes in the database.

dear friend
i could nt understand anything from ur last reply...anyway u done a gr8 job ...thank u so much....

Hi,

ok I try again:

You do this in a select statement: "...where (abs(@lat1-[Latitude]) <0.01 and abs( [Longitude]/cos(@lat1) - [Longitude]/cos([Latitude])) < 0.01 )". This means you let the database server do the calculations. You don't know how much it is optimized to do such calculations, so you better do it yourself and give the calculated values in the select statement.

Latitude and Longitude are floating point numbers. If you add 2 extra fields in the table holding integer numbers that represent the original value multiply by 100000 or so you also gain speed because integers are much faster than float.

If you want fast 'select' statements with 'where' clauses, then you have to make sure you build indexes in the database to speed this up. For example if you have a table with 16384 records then a search can take maximum 16384 compares for the CPU. If you index the table then the search is maximum 15 compares (both in worst case).

You can build a table in memory instead of on disk in the database server with the added fields and indexes. Searching in a memory table is also faster than searching on disk.

dear friend
I got ur point...thank u so much 4 spending ur valuable time 4 me...
i will try based on ur suggestion...

i have i more doubt...
i am using aviation formula for calculating the distance..

eg
long1 is-> 58.549315555555559 and lat1 is-> 23.619708888888891
long1 is-> 59.549315555 and lat1 is-> 23.6298


distance= acos(sin(@lat1)*sin(@lat2)+cos(@lat1)*cos(@lat2)*c os(@Long1-@long2))

this distance variable is float...i want to know this 'distance is in meter or not????

regards

Hi,

The calculated distance is in nautical miles. 1 nautical mile is 1.852 Km.

thank u so much dear friend...
i completed my job based on ur suggestions only...

if anything again i will come back....
u done a g8 job
god bless u
regards