Get the shortest distance from a point

Question

I have this table in sqlite

Locations
ID
Lat ( latitude)
Lon ( longitude) 
Type
Name
City

I have for example 100 records what I need is to get (using my own coordinates) the nearest point in my table.

What I did is to get the shortest distance between my current point and each one in the table, and return the shortest one, but I am searching for a better solution

Thanks

Answer 1

A possible solution is to use a grid for the whole map your are interested in and pre-assign points to a particular row/column. Then:

1. Calculate the grid location of your new point - add a column to the database for this.
2. Calculate the distance of all coordinates in the current grid - if one exists
3. You still need to calculate all the distances in the next grid out (you are unlikely to be perfectly centered in your current square, you always need to check one grid distance out from the one your best match was in.)

Should cut down a lot on the number of calculations you need to do.

If you expect to always find a location within X distance you could query for x/y coords that would fall within that range your coords +/- x KM (a square), calculate if they then fall within the xKM circle from your point, and then choose the shortest.

UPDATE - Grid option

I am assuming you already are doing the distance between two points calculation and will not describe that.

If you have an atlas handy you can see an example by looking a place up in the index. It will give you a page and a grid location like M5. If you go to that page it will have rows and columns labeled with numbers and letters and if you look in the square where row M and column 5 intersect you will find the city there. To do it for your system you need to:

1. determine how big your grid should be (how dense are your points - would be no good to have a big grid and all your points land in one square).
2. For each point calculate which grid it is in. If your polygons are complex there is tons of point in polygon code out there to copy. If (as my example is) you just use squares, you just need to determine which row/column each point is between.
3. See map for user location and closest points example:

So if the user is the green marker, he would be in C4. You would search all other points in C4 and determine that the closest is #2. Then you would also have to check one grid out all the way around to make sure there wasn't a closer item than the one you found, so this includes squares: B3,B4,B5,C3,C5,D3,D4,D5. When you do you will pick #3 from C3 and you are finished.

If the user had been in square D2 where there are no other points your would have found your first match in say C2. When checking C1,C2,C3,D1,D3,E1,E2,E3. Once found you would then again need to check another radius out, which would have be: B0-4, C0,C4,D0,D4,E0,E4,F0-4. etc. You can see that grid selection will be important to make this as efficient as possible.

Also Note this assumes your grids are equal unlike my hand drawn example.

Option 2:

If you expect a result within X km, and you want something your DB will calculate quickly you can do this:

LatMin = currentLatCoord-radiusValInDegrees
LatMax = currentLatCoord+radiusValInDegrees
LonMin = currentLonCoord-radiusValInDegrees
LonMax = currentLonCoord+radiusValInDegrees

SELECT * 
From Locations 
WHERE Lat BETWEEN LatMin AND LatMax
  AND Lon BETWEEN LonMin AND LonMax

Now this gives you all results in a square. It is important that you then check they are actually in the circle - you need to drop any in the corners as there may actually be closer coordinates than those on the edge of the circle. So for each point check if it is inside the circle first (Equation for testing if a point is inside a circle) then calculate the distance and keep the closest one. If you do not get a result, widen the circle.

Again, selecting a good radius will depend on your data.

Answer 2

Have you check this Site of how to count for the distance between two points on Earth?

But just keep in mind that it give the Distance based on Earth Surface not based on the actual path to reach at that position. So if you want to count distance based on the Actual Path to reach that position then you can get it by using Google MAP API.

Google Maps API gives the distance between two point based on the actual path.

Hope this information surly help you.

Enjoy Coding... :)

Answer 3

Distance between two points: ((x1 - x2) ^ 2 + (y1 - y2) ^ 2) ^ 0.5. However, distance between these points are straight lines. Most likely, there are variables like local vs highway, not to mention one-way streets and waterways, where you need to find the nearest bridge. Therefore, I suggest using Google and Bing maps api. The are free for a limited number of searches.

Answer 4

There's a rather clever solution over at Query to get records based on Radius in SQLite? based on precalculating some trigonometric values for each position when inserting the rows which then lets you calculate the distance in your query only using arithmetic functions.

I've used it very successfully in my own code

Answer 5

Let me make sure this is right: You have point a, and a table of points a[]. Currently, you do something like:

• loop over b[]
  • get distance from b[i] to a
  • if distance is less than minimumDistance
    • set minimumDistance = distance
    • set closestPoint = i
• return closestPoint

If so, you're finding it in O(n) time, which can't really be improved upon much. You have to check all the points to see which are the closest.

However, as Matthew points out, you can prune n by assigning points to a grid. This can drastically reduce the amount of points needed to compare. The expense is a bit of time preprocessing, and slightly more complicated logic. If you have a long list of points(or the distance() function takes a long time), you'll definitely want to do something like this.

Answer 6

Depends how much you care about being correct when near the poles

if closest by pythagorean distance is good enough you can use this in the orderby of the sql

eg. SELECT * FROM locations ORDERBY (Lat-myLat)*(Lat-myLat) + (Lon-myLon)*(Lon-myLon) LIMIT 1

Not technically the most correct, but saves getting all locations from the database and looping over them, let sqlite do that for you

Answer 7

You can use my php class hilbert curve @ phpclasses.org. It uses a monster curve and a quadkey to find the shortest distance. To search the quadkey you can use any depth.

Answer 8

Though this is not a best option.
Let you are trying to figure out shortest distance within N mile/km radious for fixed no of locations/your location table data are not changing regularly.
Add another column Distance_Index (DI) a self multi reference key ArrayType. Once run a procedure and update the DI with ID in ascending order accoording to distance from this DI. Now from next time onwords distance is with you. just make a query to the database and use it.

Now, in your problem no of location are less within N, then DI will not be too much long.Just as an opinion.