PHP & MySQL - connecting two databases

[Nel]

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.

[Jerry Stuckle]

Re: PHP & MySQL - connecting two databases

Nel wrote:[color=blue]
> 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.
>
>[/color]

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
the hard way. Instead, let the database help you out.

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
mile radius circle.


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

[Nel]

Re: PHP & MySQL - connecting two databases

"Jerry Stuckle" <jstucklex@attg lobal.net> wrote in message
news:kuadnX9eZv JdKNfeRVn-ug@comcast.com. ..[color=blue]
> Nel wrote:[color=green]
>> 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.[/color]
>
> 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.[/color]

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

[color=blue]
> 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.[/color]

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

[color=blue]
> 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.[/color]

Genius. So I can narrow down the processor work to a limited few.
[color=blue]
> 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 mile
> radius circle.[/color]

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.

[Jerry Stuckle]

Re: PHP &amp; MySQL - connecting two databases

Nel wrote:[color=blue]
> "Jerry Stuckle" <jstucklex@attg lobal.net> wrote in message
> news:kuadnX9eZv JdKNfeRVn-ug@comcast.com. ..
>[color=green]
>>
>>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.[/color]
>
>
> The figures are in metres, not degrees so hopefully this will not be a
> problem.
>
>[/color]

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 :-)
[color=blue]
>[color=green]
>>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.[/color]
>
>
> Using a square would make it easier. I had visioned calculating the
> hypotinuse for each member.
>[/color]

You will have to do that eventually (not all locations in the 25km.
square will be within a 25 km. circle).
[color=blue]
>
>[color=green]
>>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.[/color]
>
>
> Genius. So I can narrow down the processor work to a limited few.
>[/color]

Definitely. Calculations such as this are very CPU intensive. You want
to perform the calculation on as few items as necessary.
[color=blue]
>[color=green]
>>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 mile
>>radius circle.[/color]
>
>
> 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?
>[/color]

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=>dis tance and use asort to perform the sort.
[color=blue]
> Thanks,
>
> Nel.
>
>[/color]


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

[George Chapman]

Re: PHP &amp; 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


In article <434a9eca$0$497 73$ed2e19e4@ptn-nntp-reader04.plus.n et>,
nelly@ne14.co.N OSPAMuk says...[color=blue]
> I am trying to understand the best way to extract a list of users from a
> table based upon their location.
>[/color]