"Near Me" database algorithm

[Eric Pearson]

Let's say I have a huge list of latitude/longitude pairs, and I want to find all of the pairs within (say) a 500 mile radius of a user-input lat/lon, sorted in order of distance from the specified point.

First I calculate the points 500 miles N, S, E, and W, to specify a latitude/longitude grid rectangle that quickly pre-filters the records. Next I perform a (slower) Great Circle calculation for the remaining rows. Toss anything over 500 miles, and the rest of the distance values are saved in an array. Sort the array, all done. The closest point to the specified location is first, and so on.

I'm curious how that might be implemented in a SQL database, either local or (especially) online. Nested statements? Calculated fields? Would something other than the DBMS itself (php, VBA, etc.) do the calculating?

The most common online answer is "use the Google Maps API" but I want to know how it works under the hood.

[Mr. Kevin Diggins]

Hi Eric ...

if you have the luxury of using MS Sql Server, this might be a jumping off point.

https://docs.microsoft.com/en-us/sql...l-server-ver15

[Michael Mattias]

Oracle, too:

https://docs.oracle.com/cd/B12037_01...jrelschema.htm

I never worked with these, but the Oracle DBMS is #1 in my book and in my heart.

[Michael Mattias]

I never answered your question..

I'm curious how that might be implemented in a SQL database, either local or (especially) online. Nested statements? Calculated fields? Would something other than the DBMS itself (php, VBA, etc.) do the calculating?

You perform all the calculations you are currently doing outside the DBMS in a stored procedure or DB function. Then you you just call the procedure passing your input points and you get back your answer.

In Oracle these procedures are written in a script language called PL/SQL. I know you can do this using MS SQL/Server but I don't know what they call their script language. I did a bunch of Oracle stuff in PL/SQL and I can guarantee you if you are a half-decent BASIC programmer you could, too, probably in a day or less.

If you are worried about performance, don't be: Oracle PL/SQL procedures are compiled to binary, executable code when they are stored... much like the "plan" developed for any DML statement.

[Michael Mattias]

OR...

You can learn but ONE thing about the "language of the particular RDBMS procedure" and that would be "how to call an external function" and package your logic into a PB-created DLL.

That's how I did a whole bunch of stuff with Inno Setup when I did not care to become a skilled Pascal programmer.. I learned how to call an external and wrote the "heavy logic" in PB.

[Eric Pearson]

Thank you both! I really am out of the loop; a "geometric" datatype with built-in GIS functions is more that I'd hoped for.

The core dataset is stored binary arrays, so I can theoretically create importable files for any DBMS. I favor MySQL and apparently it has the same GIS functionality. Amazing.