Match 2 tables based on distance

[johann59]

Hi all. I have 2 tables and I'm using VFP 9.

Tbl1 fields/records
name1, naddress, nlatitude, nlongitude, etc
name2, naddress, nlatitude, nlongitude, etc

Tbl2 fields/records
StoreName1, sAddress, slatitude, slongitude, etc
StoreName2, sAddress, slatitude, slongitude, etc

I want to find up to six records in tbl2 that are up to 5 miles in distance from records in Tbl1 based on the latitude and longitude, and then append them to that record in Tbl1 by distance order.

Fox example, if a record from Tbl1 has a "123 Anywhere St, Los Angeles, CA 90001" address, I want up to 6 records from Tbl2 that are within 5 miles of distance, and then appended to Tbl1 in distance order. My end result would be:

name1, naddress, nLatitude, nLongitude, StoreName1, sAddress, StoreName2, sAddress, StoreName3, sAddress, and so on, up to six storenames.

I've never worked with this kind of data, Latitude and Longitudes, and to be honest, I have never matched based on these kind fields, it's kind of matching based on an approximation in distance...

I know the answer lies some place hidden within VFP. Any ideas on how to start 'asking' VFP to do this for me will be appreciated.

Thanks,

 

[mm0000]

Take a look at the following link, which should help in getting what you want :

http://www.tek-tips.com/viewthread.cfm?qid=1612993&page=5

 

[Mike Lewis]

I know the answer lies some place hidden within VFP.

No, it doesn't. You're going to have to program it yourself.

You've asked three separate questions:

1. How to calculate the distance between two points, given their respective longitudes and latitudes.

The formula for that is in the link that MM gave you (above).

2. How to find all records which are a given distance from a certain point.

You will need to loop through your table. For each record, apply the formula mentioned above. If the distance is below five miles, copy the record to a cursor. The cursor will need an additional field to hold the distance.

3. How to append the five closest records to the one you are testing.

In the cursor mentioned above, get the bottom five records (the five with the lowest distances). Copy the name and address fields from each of those records to the record under test.

This assumes that the table has the additional fields you require. If it doesn't, you will need either to add them, or to create a new table to hold the required information.

I've only given you an overview of the procedure. If you need any details, come back.

By the way, if I was doing this, I wouldn't add the five names and addresses to the base record. Instead, I would create a new table that links the base record to five closest. The table would have a pointer to the base record, and another to one of the five closest. There would be up to five separate records for each base record. That would be much more efficient. But it's not essential for achieving your goal.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

I can just add: listen to mike. It's called relational database, not heap of data.

Bye, Olaf.

 

[johann59]

Thanks to all. I'll start working with it.

 

[TamarGranor]

Just one additional point. You can actually use a query to find all the points within a given distance of another. Just make the join condition use <= rather than <.

Something like:

WHERE YourDistanceFunction(pass relevant parameters) < 5

You'll probably actually be computing distance is something other than miles, so you won't really have 5 there.


Tamar

 

[johann59]

Mike, you said I could come back and ask for details, and I'm doing that now. Specifically, I'm having trouble understanding the formula, the link supplied by MM. To be honest, I never understood trigonometry. If you guys could just explain a bit more that part, I just need it explained a little more what is it the formula it trying to do. I would appreciate it very much. Thanks.

 

[Mike Lewis]

Johann,

I'm a bit rusty on trigonometry as well. I can't really explain how the formula works, but basically it gives you an "as the crow flies" distance, given any two pairs of latitudes and longitudes.

In fact, I found a better example, written in Javascript, which I've translated to Foxpro for you:

lnR = 6371 && radius of earth in km.
lnLat = DTOR(lnLat2 - lnLat1)  
lnLon = DTOR(lnLon2-lnLon1)
lnA = SIN(lnLat / 2) * SIN(lnLat / 2) + COS(DTOR(lnLat1)) ;
 * COS(DTOR(lnLat2)) * SIN(lnLon / 2) * SIN(lnLon / 2)
lnC = 2 * ATN2(SQRT(lnA), SQRT(1 - lnA))
lnD = lnR * lnC  && distance between the points in km.

I haven't tested this, so can't be sure that it will work, but you can try it for yourself.

You just need to set lnLat1 and lnLon1 to the co-ordinates of the starting point, and lnLat2 and lnLon2 to those of the end point. If it works, lnD should give you the distance (in kilometres).

I found the Javascript code at:

http://www.movable-type.co.uk/scripts/latlong.html

If you want to research it further, the term is to search for is "Haversine".

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Mike Lewis]

Johann,

Have you had time to try my code yet? It would be nice to know if it worked, if only for the benefit of other people who have the same requirement.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[johann59]

Hi Mike, I REALLY appreciate you asking. No, I still have not tried this, I'm planning to do work on it this coming weekend. The job is not due till the end of the month, but I wanted to get ahead on this project. I'll be sure to post back any results I get. Thanks.

 

[Mike Lewis]

That's fine, Johann. Let us know what happens.

Keep in mind that I didn't test the code. I just translated it from the JavaScript, so it might possibly have errors.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business