Distance and Bearing Query...I'm stumped

[dd92251]

Hi All, I need help with distance and bearing calculation and display in Access 2007.

I have two tables of data, Table A has Lat & Long coordinates in Decimal format ie: 42.123456 degrees lat, -80.123456 lon. and some other data in a few columns

Table B also has coordinate data in the same format along with columns of different information.

I need to take the Table A coordinates, one row at a time, and go thru all of Table B's coordinate data to find locations that are within a radius and print the Table B coordinates that are found to fall within that radius along with the other data that is in that tables row.

I'm a newbie to Access and have spent about a week trying to get this to work but with no success. Can someone help ? I appreciate any help at all.

 

[sneuberg]

I suggest googling "determine if a point is within a circle" and after you've found the equations you like create a function that given the four coordinates gives you a Yes or No.

 

[The_Doc_Man]

The problem with Lat/Lon computations is that you need to know approximately how far apart you will allow the points to be AND what method you want to use for that distance determination. In essence, to compute distance given Lat/Lon pairs, you must choose one of the basic projection methods, which include Mercator, Lambert, or (rarely) Cassini-Soldner. Some "Great Circle" methods also exist that are variants of one or the other of the above. Unfortunately, given the extreme distortion associated with higher latitudes, it won't be enough to just try to take differences in degrees and make a wild guess UNLESS you know for a hard and cold fact that ALL of your points are within a few miles of each other.

To do this properly, I think Steve's "function" suggestion is what you should do. Once you know which projection method you will use, you can write a VBA Public Function to return TRUE/FALSE depending on the two input Lat/Lon pairs and a distance. The Public Function, to truly BE public, must be built in a general module rather than a class module, because if it IS public, then a query can call it. Note that I did not write it for you because you have to choose your projection method and I don't know what you need. The best choice depends on where you are and how far apart your points are. Lambert works better at higher latitudes. Mercator works better at the equator. Cassini-Soldner works best when (a) all points are close to each other, in terms of 10 miles or less, and (b) you are between 45 North and 45 South.

After you've done that, you want to compare every record in table A against every record in table B. To do that, you need to perform what is called a Cartesian JOIN, which you can look up in this forum or on the general web. That style of query, which NORMALLY is an improper JOIN, in this case would do what you want... with the warning that the bigger the tables, the longer this will take to compute.

The function you write depends on your needs. The query MIGHT resemble this...

SELECT tblA.Lat, tblA.Lon, tblA.{other stuff}, tblB.Lat, tblB.Lon, tblB.{other stuff}
FROM tblA, tblB
WHERE DistBtw2Pts( tblA.Lat, tblA.Lon, tblB.Lat, tblB.Lon, MaxDist ) = TRUE ;

Where of course the "other stuff" is the "some other data" as you need for your reports. NOTE that this didn't show the distance because your question was stated as either inside or outside the named distance. You didn't say you needed to actually SEE how far that was. There ARE ways to do this to include the computed distance by rearranging the query just a smidge.

 

[MarkK]

If you don't need absolute perfection on the distance you should be able to just use pythagoras on the difference between your coordinates, like...

SELECT ta.*, tb.*, ((ta.Lat - tb.Lat) ^ 2 + (ta.Lng - tb.Lng) ^ 2) ^ 0.5 As DegreesDistance
FROM TableA As ta INNER JOIN TableB As tb
   ON ((ta.Lat - tb.Lat) ^ 2 + (ta.Lng - tb.Lng) ^ 2) ^ 0.5 < 1

... so that SQL should join the tables such that for every row in A, every row in B that is < 1 degree away will be joined. 1 degree is about 110 km at the equator, and about 85 km at 40 degrees latitude. And you could do a little math to figure out how many degrees you need--instead of the 1 I'm using--to calculate the distance you are looking for.

If that's not accurate enough, here's math that calculates the distance on a sphere more accurately...

Function GetPolarDistance(Coord As cPolarCoordinate, SphericalRadius As Single) As Single
[COLOR="Green"]'  Calculates the distance between two points on a sphere.
'  Specify points using radians.
[/COLOR]   Dim tmp As Double
   tmp = Math.SIN(Me.rLat) * Math.SIN(Coord.rLat) + Math.Cos(Me.rLat) * Math.Cos(Coord.rLat) * Math.Cos(Coord.rLng - Me.rLng)
   GetPolarDistance = ACos(tmp) * SphericalRadius [COLOR="Green"]'for Earth, use 6371[/COLOR]
End Function

Private Function ACos(x As Double) As Single
[COLOR="Green"]'  Returns the inverse cosine of X[/COLOR]
   If x = 1 Then
      ACos = 0
   Else
      ACos = Atn(-x / Sqr(-x * x + 1)) + 2 * Atn(1)
   End If
End Function

Those are a couple of functions from my cPolarCoordinate class, so it's not a turnkey solution to spherical distances, but it does show all the math you need to do. You could definitely do that math in a query, and it should be very fast, fast enough, I bet, to do a join like I did in the first SQL up top.
Oh, and you need this...

Private Function ToRadians(Degree As Single) As Single
[COLOR="Green"]'  Converts degrees to radians[/COLOR]
   ToRadians = Nz(Degree, 0) * PI / 180
End Function

...because the math in GetPolarDistance() needs radians, not degrees. But anyway, let me know if you get stuck. Or post a database with some sample data...

 

[dd92251]

Hi Mark,

Your post is similar to what I have been trying to do. Being a newbie, I'm not sure where to put the code. Should it be in a Macro ? or a Module ?

BTW...the distance between coordinates would be 3 Km.

In your function GetPolarDistance, how would I call that to get the sorting process started ? Like I said...I'm a newbie to this stuff.

I appreciate your your help very much.

 

[jdraw]

Can you provide a little more detail on the requirement?

Does it have to be "perfect" as Markk asked, or would a good approximation do?
As the others have said, you're dealing with distances on a curved surface.

Chip Pearson has an article with examples.

I had a similar- in concept -issue years ago. I had a list of companies and all their products and services (SIC and HS codes) and their addresses. The problem to be answered:
If there were to be a "disaster/incident needing Product X or Service Y" at this location (lat/long), which companies/providers are within 25 km". Mine was an approximation. I went from Address/PostalCode to approximate lat/long (from tables). Then added a + and - factor to the lat and the long to build a "box". Then checked for companies who had the product/service within that box and checked the distance for Q km or less. (The distance was a parameter eg 25, 50, 100)
This was my approximation to limit "the number of distance calculations to a manageable set". Without the box you could be comparing every location to the lat/long, most of which are not relevant to your solution(as Doc said{cartesian})

As I said, mine was an approximation and made use of Google maps. It is similar to Markk's Select... example using plane geometry.

Good luck.

 

[sneuberg]

A simple way of setting this up is demonstrated in the attached database where a cross product of the two tables is formed in qryLocationCrossProduct. The cross product is just the tables in the query without a join which results in the permutation of the entries of both tables. The distance is calculated in an expression name Distance in this query which looks like:

Distance: GetKM([LatA],[LongA],[LatB],[LongB])

The GetKM function can be found in the standard module named Haversine Function. This function came from here.. I only tested this with two locations but the distance it's coming up with looks about right.

 

[MarkK]

Hi Mark,

Your post is similar to what I have been trying to do. Being a newbie, I'm not sure where to put the code. Should it be in a Macro ? or a Module ?

BTW...the distance between coordinates would be 3 Km.

In your function GetPolarDistance, how would I call that to get the sorting process started ? Like I said...I'm a newbie to this stuff.

I appreciate your your help very much.

It seems like you want to have all the math done in a query, right? So what I would do in that case is write a query that does some of the work, like let's say first you convert all your degrees into radians, maybe like....

SELECT 3.14159265358979 As PI, Lat * PI /180 As rLat, Lng * PI / 180 As rLng
FROM YourTable

...and save that as a named query. Then use that query as the input table to do the next step. Then use that query as the input table for the next layer of operations. That way you do all the math step by step, building query upon query.

You can also post a db with a bunch of coordinates if you want, and I'll poke around with it. Also it looks like the Chip Pearson article posted by jdraw is very excellent.

 

[dd92251]

Hi All....I think I have my Coordinate Distance Query working somewhat...there is still a bit of a problem though.

Each entry of Table A does a Query of every entry in Table B and calculates the distance...I would like to limit the output display to only those that are within a specified radius.


BTW:

I used the solution provided by "sneuberg"....for the Haversine Module and Query. Evidently my problems were all in the Radians/Degrees conversions that the formulas need...his solution addressed those problems.

Now if I can limit my output/report to only those that fall within a radius of 3 Km I will be very happy.

THanks again for all your help.

 

[sneuberg]

Now if I can limit my output/report to only those that fall within a radius of 3 Km I will be very happy.

Just put <3 in the criteria for the distance. I've updated the query in the attached database with that and also put in the code from Jdraw's link. You can see that for your distances either would work fine.

 

[jdraw]

Seems you are experiencing the very same issue that I was referring to in my post #6

This was my approximation to limit "the number of distance calculations to a manageable set". Without the box you could be comparing every location to the lat/long, most of which are not relevant to your solution(as Doc said{cartesian})

 

[sneuberg]

Both distance functions in the database I posted take less than 1 millisecond to execute as measured with this function

Public Declare Function GetClock Lib "winmm.dll" Alias "timeGetTime" () As Long

So whether or not you will have to narrow down the number of records with a faster expression will depend on how many records you are comparing. How many is that?

 

[dd92251]

Hi Jdraw....You are correct !

"Seems you are experiencing the very same issue that I was referring to in my post #6"

Being a newbie with DB programs, I'm learning as I go, and making mistakes every minute

How or where do I implement the "Lat/Lon Box".... I'm guessing that I have to filter out only locations that lie between Table A Lat +/- some value and the same for Table A Lon.

I'm not sure how to accomplish that...any thoughts ? I feel like I'm so close to getting this thing working but yet still a bit far away.

Best Wishes,

 

[jdraw]

how many records are in you tables?
Perhaps you could show us your table designs.

I wouldn't expect to see noticeable slow down with only a few records.

 

[dd92251]

Jdraw....There are two Tables that comprise this project.

Station Tower Data=80,000 Records
Cell Tower Data= 3,000 Records

The project is supposed to take a record from the Cell Tower Data get the coordinates of that Cell Tower and then search the Station Tower Database and output the Station Towers that are within a radius of a few Km.

I intended to go thru the Cell Tower Data sequentially, Record #1, Record #2, Record #3, etc and do a comparison with the 80,000 records in the Radio Station Data....spitting out only those that are within ...say... 3 Km. Also the ones that meet that criteria will output the radio station call letters and frequency which I have in fields of each record.

At this moment I get an output of 80,000 Station Towers and their distances which can be anything from 1 to 9,000 Km.

It appears that Access is comparing the Cell Tower Data record with every Station Tower Data record and outputting that....which of course contains lots and lots of unnecessary data.

Do you know of a way to narrow this down to only the pertinent output that is ...say.... within 3 Km ?

 

[sneuberg]

With that many records a cross product of them certainly wouldn't be a good solution. I suggest narrowing the result by adding a join between the tables that basically implement's Jdraw's box idea. This would be just to narrow it down ruffly. You would follow this query with another one that would narrow the results more precisely. In the following query you will see in the join the numbers 0.1 and 0.08 which represent about 9 kilometers for longitude and latitude here in Tucson. So this should catch locations within about 9 kilometers in Tucson but the longitude number would vary depending on where you are. So I suggest using Google maps to find numbers that will work for you in your area. Here's the query which is based on the tables in the database I uploaded.

SELECT tblLocationA.LocationName, tblLocationB.LocationName, tblLocationA.LatA, tblLocationA.LongA, tblLocationB.LatB, tblLocationB.LongB
FROM tblLocationA INNER JOIN tblLocationB ON (tblLocationA.LongA BETWEEN tblLocationB.LongB  - 0.1 AND tblLocationB.LongB  + 0.1  ) AND (tblLocationA.LatA BETWEEN  tblLocationB.LatB - 0.08 AND tblLocationB.LatB + 0.08);

Please note that because I used BETWEEN AND in the join Access can't not display this in the grid view. You can only modify this in the SQL view.

I'd like to play with this to see how well or badly it performs. Could you zip and upload you database so that I'd have some data to test this on?

 

[The_Doc_Man]

If you look at the query I posted earlier, it would only show you answers with 3 km if your "distance" parameter to the suggested function was 3 km (and the function worked correctly).

The problem you face is that you have no method for filtration that doesn't involve every Cell Tower being tested against every Station Tower, even if only to decide that they are too far apart for a more detailed computation.

Even the "inside the bigger box" test still requires a Cartesian JOIN type to decide whether you wanted a more precise result. From those numbers of records, Access will have to touch 240,000,000 combinations (80K*3K). No matter WHAT you do, it ain't gonna be fast.

The ONLY way I could see making it relatively faster is a direct comparison between the Lat values and the Lon values. You want distances < 3 km. Make a first-level query that only shows you records where the Latitudes are within 3/110 of a degree. Ditto for the Longitudes. This is an APPROXIMATION that should be fast to compute.

Then in a second layer you can do your "real" computation based on what's left using the first query as a data source that has been filtered to remove things that are blatantly not close enough.

 

[jdraw]

I downloaded some city/zip/lat/long data from
https://www.gaslampmedia.com/download-zip-code-latitude-longitude-city-state-county-csv/
There's about 43000 records. I found there were about 690 with NULL values in lat and Long so removed them.
I took 100 of those and used them for TableA in Steve's database.And built this query for testing - the criteria is just to get some calculating. The total record (cross product) is 4204900 records when no criteria used.

SELECT Zip_codes_states.zip_code
, Zip_codes_states.city
, Zip_codes_states.state
, Zip_codes_states.latitude AS latb
, Zip_codes_states.longitude AS LongB
, tblLocationA.LocationName
, tblLocationA.LatA, tblLocationA.LongA
, GreatCircleDistance([LatA],[LongA],[latitude],[Longitude],False,True) AS GreateCircleDistance
FROM Zip_codes_states, tblLocationA

WHERE (((GreatCircleDistance([LatA],[LongA],[latitude],[Longitude],False,True))<67.9494))

ORDER BY GreatCircleDistance([LatA],[LongA],[latitude],[Longitude],False,True);

It runs in about 15 sec (lenovo intel I7)

But your issue is different than mine was. I had to find those companies within the radius that offered related products/services. So I didn't have to compare each company with all others. I could start with a potential disaster site, put a box around it, isolate the companies within the radius and then limit it to products/services I needed. So the box won't work generally for you.

As I'm typing this I see Doc has just responded with similar comment.

 

[sneuberg]

I did a test similar to the one JDraw did. I put the zip codes for the state of California in a table name ZipCodesCA (2666 records) and the rest in a table named ZipCodesAll (42049 records) and ran the following query:

SELECT ZipCodesAll.zip_code, ZipCodesCA.zip_code, ZipCodesAll.latitude, ZipCodesCA.latitude, ZipCodesAll.longitude, ZipCodesCA.longitude, ZipCodesAll.city, ZipCodesCA.city
FROM ZipCodesAll INNER JOIN ZipCodesCA ON (ZipCodesAll.latitude BETWEEN ZipCodesCA.latitude - 0.001 AND ZipCodesCA.latitude + 0.001 ) AND (ZipCodesAll.longitude BETWEEN  ZipCodesCA.longitude - 0.001 AND  ZipCodesCA.longitude + 0.001 );

This took about five minutes to run. The results were weird (too many records) but that's because there are lots of zip codes in the data that have the same longitude and latitude. Anyway if it takes 15 minute to get an output is that necessarily a problem? How often is this going to be run?

The database with the test data is attached.

 

[Galaxiom]

In essence, to compute distance given Lat/Lon pairs, you must choose one of the basic projection methods, which include Mercator, Lambert, or (rarely) Cassini-Soldner.

Projections involve the approximation of the spherical surface of the planet projected onto a flat map. It influences the error determining distance using the scale on the map legend. Projections are completely irrelevant for calculations of the distance between two pairs of latitude/longitude coordinates.

Some "Great Circle" methods also exist that are variants of one or the other of the above.

As far as I am aware, Great Circle is the only way to calculate the distance between two points on the surface of a sphere.[/QUOTE]