SQL update values using the closest result

[sambrierley]

hi all,

I have 2 tables, the first is an inventory list and the second is a log file from an asset.

Table 1
Lat, Long, Road
Table 2
Lat, Long, Data

I want to join the two to get this table
Table 3
Lat, Long, Road, Data

problem is the lats and longs dont match alot of the time. is there a way to get a join based on the 'closest' match?

i plan on executing the SQL statement from a vba module

thanks

 

[Ranman256]

1st run a query that joins on the exact data. (u did this)

next make an extra fields in each tbl, say lat2,long2
and 'round' the values from lat,long ...to, say the integer value.
then join THESE fields and build tbl 3 with the inexact matches.

 

[The_Doc_Man]

By closest, since you are using latitude and longitude, I presume you are talking "geographically closest." The answer isn't so easy because you don't really JOIN tables based on "close" - you JOIN on "match." I've never tried (never needed to), but I don't think a JOIN will work on fuzzy logic.

It would be possible, given latitude and longitude of two points (which you have), to write a function that does a "great circle distance" between those two points. You can research "Great Circle Distance" on the web to see the formula, which is basically a bunch of Sine and Cosine and ArcTangent functions. Eminently doable in VBA. It might look formidable, but the hardest point will be getting data into the function in a usable format. Once you have the latitude and longitude in degrees and fractions thereof (or radians, which is what you really wanted, believe it or not), the GCD formula is maybe less than 20 lines of VBA math.

You could write the function to give you the distance between two points, and a query can call a function. You could then write VBA or a 2nd-layer query to order the records from a given point X in table 1 to each point in your table 2. When you do that, you can open the recordset and do a .FindFirst to get that point in table 2 that has the least distance from point X in table 1. Use the VBA code to update your table.

The down-side of this is that your query will be very busy even though it is simple enough to write, because double-precision math isn't trivial and VBA is interpreted, not compiled.