Access Distance Issue

[Shaggy Hiker]

I'm trying to get the record in an Access database table holding known locations that is closest to some waypoint. Normally, to find the distance between two geographic points, you would use the Haversine formula, but that isn't possible in an Access query because some necessary math formulas are not available. That's no big deal, because I can readily narrow the search down to just a handful of points, but that is proving odd.

My idea was to get some fields, and the smallest distance using the Pythagorean Theorem:

SELECT TOP 1 HName,RouteGUID,Lat_WGS84,Lon_WGS84, MIN(SQR(
                                                                ((" & latitude.ToString & " - Lat_WGS84) * (" & latitude.ToString & " - Lat_WGS84))
                                                                + ((" & longitude.ToString & " - Lon_WGS84) * (" & longitude.ToString & " - Lon_WGS84))
                                                                )) AS Dist
		                                FROM GIS_Routing
                                        WHERE GIS_Routing.LLID = '" & LLID & "'
                                        GROUP BY HName, RouteGUID,Lat_WGS84,Lon_WGS84"

For the sake of discussion, the query could be simplified to:

SELECT TOP 1 HName,RouteGUID,Lat_WGS84,Lon_WGS84, MIN(Pythagoras) AS Dist
		                                FROM GIS_Routing
                                        WHERE GIS_Routing.LLID = '" & LLID & "'
                                        GROUP BY HName, RouteGUID,Lat_WGS84,Lon_WGS84"

That doesn't work, because Min appears to usually do nothing in this query. I get the records back in whatever order Access chooses.

I also thought to use Order By, which would be simpler...except that I can't order by Dist (the alias I gave the column). I believe I COULD use Order By, so long as I was willing to write out that entire equation in the Order By statement. That's not hard to do, since I can copy and paste, but it seems terribly inefficient, as it seems like it would require that the calculation be performed twice.


So, what is the best way to get the record with the smallest distance?

[sapator]

Wow. Access!
Maybe, from what I read here: https://stackoverflow.com/questions/...correct-result
You might get MIN to work if you use "Val" ,of course there is no environment anywhere I could test this.

[Shaggy Hiker]

Access has many peculiarities. I had not seen that one. I don't believe it will work, but what the heck, might as well try it. The reason I don't believe it will work is that, while Min may not ensure things are a number (can't be an integer, as many of these will differ only by the fractional part), SQR certainly should. Therefore, SQR should serve the same purpose as Val in that example. Still, the only way to be certain is to test it.

[Zvoni]

In your original Query (or the second one), leave out the TOP 1 and put it in a Sub-Query using an ORDER BY there (!!) --> you receive ALL Datapoints Ordered acc. to your Criteria!
And from the OUTER SELECT you grab the Data from the ordered Subselect and THEN apply your TOP 1

SELECT TOP 1 Your Fields Here.... FROM 
(SELECT ID, blablabl,  MIN(Pythagoras) As DIST FROM MyTable WHERE SomeID=42 GROUP BY SomeFIelds ORDER BY MIN(Pythagoras))

Though i dare say the performance might be poor depending on how many Datapoints the Subselect is really returning.
IF the Subselect only returns a few hundred, you might not notice anything, if there are a few million datapoints though.....

test test test it... as always

[Shaggy Hiker]

I had a design kind of like that earlier, but not quite like that. Specifically, it left out the Order By in the sub-query. As it turns out, the size will not be an issue. Though the table itself is large, that WHERE statement will drop the number of results down to between about 2 and two dozen, which should be trivial.

[Shaggy Hiker]

I simplified that a bit by just ordering by the Pythagorean result. I knew that was possible, I was just hoping it wasn't necessary. Seems like extra work. Perhaps the query engine optimizes away the double calculation, but perhaps not.

[Zvoni]

I simplified that a bit by just ordering by the Pythagorean result. I knew that was possible, I was just hoping it wasn't necessary. Seems like extra work. Perhaps the query engine optimizes away the double calculation, but perhaps not.

Could be (untested) since the ORDER BY is the last operation of an SQL-statement, and at that point the calculated value is known.
IIRC, in the ORDER BY you should be able to use the Alias of the „Field“

[Zvoni]

I've just reread the Order of Execution for an SQL-Statement:

• FROM and/or JOIN clause.
• WHERE clause.
• GROUP BY clause.
• HAVING clause.
• SELECT clause.
• DISTINCT clause.
• ORDER BY clause.
• LIMIT and/or OFFSET clause.

That said: You shouldn't need the Sub-select.
The ORDER BY is the second-to-last, followed by the Limit (TOP X in Access/MS SQL).
And i just did a test with SQLite: You can definitely use the Field-Alias of the Aggregate in the ORDER-Clause

SELECT TOP 1 HName,RouteGUID,Lat_WGS84,Lon_WGS84, 
MIN(SQR(((" & latitude.ToString & " - Lat_WGS84) * (" & latitude.ToString & " - Lat_WGS84))
    + ((" & longitude.ToString & " - Lon_WGS84) * (" & longitude.ToString & " - Lon_WGS84)))) AS Dist
FROM GIS_Routing
WHERE GIS_Routing.LLID = '" & LLID & "'
GROUP BY HName, RouteGUID,Lat_WGS84,Lon_WGS84
ORDER BY Dist

should do it.

OTOH, it's MS Access we are talking about, so there....

[Shaggy Hiker]

Yeah, I was thinking I could use the alias, but that does not work. That throws an exception about no value given for one or more parameters, so that alias...well, it is no longer an alias by the Order By phrase.

In any case, it works with the full mess in the Order By statement. Whether it does the calculations twice or not, I can't say, but there are so few records returned that it doesn't really matter.