# PHP & MySQL - connecting two databases

#### Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

•  Subject
• Author
• Posted on
I am trying to understand the best way to extract a list of users from a
table based upon their location.

To do this I have a table containing all the UK postcodes with a grid
reference x & y.
postcode    x             y
AB10        392900    804900
AB11        394500    805300
AB12        393300    801100
AB13        385600    801900
AB14        383600    801100
AB15        390000    805300
AB16        390600    807800
AB21        387900    813200
AB22        392800    810700
AB23        394700    813500
AB25        393200    806900
AB30        370900    772900

In a seperate table I have a list of users including a postcode.
id       firstname     postcode
1        Heather         BH4
2        Vicky            OL1
3        Paola            CF8
4        Joanna          W14
5        Steve            BD13
6        Sally             NN1

In short I would like your opinoin on how best (most efficiently) to
calculate the nearest 20 users.  The method I am using to calculate the
distance between the users and any chosen postcode is good old Pythagoras
dist = sq root ( (userX * userX) + (userY * userY) - (locationX * locationX)
+ (locationY * locationY) )

It seems that looping through the users to first calculate the distance is
essential.  After that point I am not sure about the besr way to proceed.
Do I write the results to a temp table (with distances) then call them in
order of distance or is there a better way?

Thanks Nel.

## Re: PHP & MySQL - connecting two databases

Nel wrote:

Nel,

First of all, you have a problem with your equation.  It assumes 1
degree if longitude has the same distance as 1 degree of latitude -
which only occurs at the equator.  Everywhere else, 1 degree of
longitude is less than 1 degree of latitude.  I don't have the correct
equation handy, but I found it one time quite easily with a google search.

Once you have that straightened out, you can do it this way - but it's

For instance, say you want everything within 25 miles.  In your program,
define a 50x50 mi. square with the "from" point in the center.  That
is,  find the longitude 25 mi. east and west with no change in latitude.
Then find the maximum and minimum latitude with no change in longitude.

This now gives you a range that all the target post codes must be in.
Now let the database do the work for you.  Search the database for all
postcodes where the longitude is between the min and max above, and the
latitude is within the min and max.

Once you have this (much smaller) set of postcodes, you can run your
equation again against every one to determine if it is within the 25

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

## Re: PHP & MySQL - connecting two databases

The figures are in metres, not degrees so hopefully this will not be a
problem.

Using a square would make it easier.  I had visioned calculating the
hypotinuse for each member.

Genius.  So I can narrow down the processor work to a limited few.

One final question, once I have narrowed down the sqlresult to those 25, and
I calculate the distance, how do I sort the sql results in the order of the
distance calculation?

Thanks,

Nel.

## Re: PHP & MySQL - connecting two databases

Nel wrote:

Metres from where?  Some central point (i.e. Greenwich Observatory)?
For a (relatively) small country like GB, it probably wouldn't be that
much of a problem.  But for Russia or Canada it could be a problem :-)

You will have to do that eventually (not all locations in the 25km.
square will be within a 25 km. circle).

Definitely.  Calculations such as this are very CPU intensive.  You want
to perform the calculation on as few items as necessary.

You really can't, because you can't determine the actual distance until
you perform the final calculations.  I'd suggest placing them in an
array of postalcode=>distance and use asort to perform the sort.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

## Re: PHP & MySQL - connecting two databases

You might be interested in taking a peek at the Location module for
Drupal...

http://drupal.org/project/location

Actually USING the whole thing will most likely be overkill. But the
source code may prove useful.

Chow,
GC

nelly@ne14.co.NOSPAMuk says...