Locations finding

Originally Posted by tbobo

I have 198 locations that given in a form or coordinates (Latitude and longitude). If I pick location 182 (Loc182) and I want to find out all other locations that are 10 miles or more from from Loc182 and each ohters (for example Loc182, Loc175, Loc153, Loc154, Loc149, Loc94,.....are 10 miles or more from Loc182 and each other). Can some someone help me with a script please?

To start, what you could do is have a lookup table that stores the distances between each location,
like the distance charts in road atlases.

This can be implemented in a 2D array (198 * 198).

There will be a lot of wasted space in this data structure,
(e.g. it stores the distance from Loc1 to Loc2, but doesn't need to store the distance from Loc2 to Loc1 or from Loc1 to Loc1 etc.).

The advantage is that it will be easy to visualise and simple to work with.

The lookup table is initialised by taking the latitude and longitude co-ordinates of two locations and calculating the distance between them.

This is repeated for each permutation, with the exceptions mentioned above.

Below is some code to initialise a lookup table for 8 locations (kept small for testing).

Please Login or Register  to view this content.

Here is some code to display the lookup table.

Please Login or Register  to view this content.

Hi

The attached file is a bit contrived in terms of fixing the output / input areas, but see if it is going in the right direction. I've had to get rid of the distances in the matrix to save space, so copy the formula from D2 down to D199, then across to GS199 to complete the matrix.

Then run the macro. It will build a matrix starting at row 204 that gives the locations, and the matrix output is merely to show that the distances are really more than 10 apart from all the others.

HTH

rylo

Thank you very much for helping T-J and Rylo. Sorry for the delay!

T-J ==> I tried your programs. They yield no results...What am I missing?

Rylo ==> I tried your program it works.

I have additional questions:

A. Let's say that I have a list of data up to 1000 locations. How can we get around the matrix solution?? Knowing that there only 255 colums in excel.

B. Rylo, your program provide the list of locations with in 10 miles of location 182. Can we remove these locations from the master list? because we choose another location, the master list will get smaller til no locations left to be chosen.

Again thank you much!!!

Originally Posted by tbobo

T-J ==> I tried your programs. They yield no results...What am I missing?

If you read what I wrote again you'll see that my code creates a lookup table of distances between each location and displays it on a sheet.
That's all it does. More code is needed to do what you want.

It was only meant to give you a start on tackling the problem.
Using the lookup table it should be fairly easy to write code to find locations within a given distance.

Originally Posted by tbobo

I have additional questions:

A. Let's say that I have a list of data up to 1000 locations. How can we get around the matrix solution?? Knowing that there only 255 colums in excel.

You could have a third dimension in the array.

1st dimension = sheet
2nd dimension = row
3rd dimension = column

Hi

1) Try this. It works on the source data only, and doesn't need the matrix. Again, I've used the fixed sources while testing, and the output will be put into row 206.

I've used T-J's function to calculate the distance, but converted the output from integer to single (cudos T-J).

This should be easily expanded to cover the 10k rows, but you would have to change the output to be column based, rather than row based.

2) You should be able to use the final array as the basis for deleting the entries, before moving onto the next query.

Please Login or Register  to view this content.

rylo