ORDER BY Zip Code Distance

**Tom Thackrey** · Jul 17 '05, 03:02 AM

Re: ORDER BY Zip Code Distance

On 8-Feb-2004, "Xenophobe" <xenophobe@plan etx.com> wrote:
[color=blue]
> As described the high and low latitudes and longitudes are pre-calculated
> and passed to the query.
>
> SELECT *
> FROM Locations
> WHERE Latitude <= $HighLatitude
> AND Latitude >= $LowLatitude
> AND Longitude >= $LowLongitude
> AND Longitude <= $HighLongitude
>
> I then calculate the actual distance using the latitude and longitude for
> each zip returned.
>
> This works great and saves lots of cycles. The downside to this technique
> is
> the inability to sort by distance. Can someone suggest a simple way to
> calculate and ORDER BY distance within the query?[/color]

Put the following in your SQL ($lat and $long are the zero distance point)

(69.09*DEGREES( ACOS(SIN(RADIAN S(Latitude))*SI N(RADIANS($lat) )+COS(RADIANS(L atitude))*
COS(RADIANS($la t))*COS(RADIANS (Longitude-$long))))) AS Distance

will create the Distance 'column'

ORDER BY Distance

--
Tom Thackrey

206 Creative Light

https://www.creative-light.com

tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@wil lglen.net (it's reserved for spammers)

**Xenophobe** · Jul 17 '05, 03:02 AM

Re: ORDER BY Zip Code Distance

Tom, thanks for your response.

I modified my query and added the lines you provided as illustrated in your
example.

$sql = "SELECT s.suppliername, s.address1, s.address2, s.city, s.state,
s.zip, s.phone, l.latitude, l.longitude ";
$sql .= "FROM Suppliers s ";
$sql .= "INNER JOIN Locations l ON l.zipcode = s.zip ";
$sql .= "WHERE l.latitude <= ".$highLatitude ." ";
$sql .= "AND l.latitude >= ".$lowLatitude. " ";
$sql .= "AND l.longitude >= ".$lowLongitude ." ";
$sql .= "AND l.longitude <= ".$highLongitud e." ";
$sql .=
"(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) *SIN(RADIANS($l atitude))+COS(R A
DIANS(l.latitud e))*COS(RADIANS ($latitude))*CO S(RADIANS(l.lon gitude-$longitud
e))))) AS distance ";
$sql .= "ORDER BY distance";

I get the following error message:

Warning: Supplied argument is not a valid MySQL result resource

Commenting the last two lines makes the query servicable again.

Any thoughts?

"Tom Thackrey" <use.signature@ nospam.com> wrote in message
news:CWAVb.2283 2$VW4.3380@news svr25.news.prod igy.com...[color=blue]
>
> On 8-Feb-2004, "Xenophobe" <xenophobe@plan etx.com> wrote:
>[color=green]
> > As described the high and low latitudes and longitudes are[/color][/color]
pre-calculated[color=blue][color=green]
> > and passed to the query.
> >
> > SELECT *
> > FROM Locations
> > WHERE Latitude <= $HighLatitude
> > AND Latitude >= $LowLatitude
> > AND Longitude >= $LowLongitude
> > AND Longitude <= $HighLongitude
> >
> > I then calculate the actual distance using the latitude and longitude[/color][/color]
for[color=blue][color=green]
> > each zip returned.
> >
> > This works great and saves lots of cycles. The downside to this[/color][/color]
technique[color=blue][color=green]
> > is
> > the inability to sort by distance. Can someone suggest a simple way to
> > calculate and ORDER BY distance within the query?[/color]
>
> Put the following in your SQL ($lat and $long are the zero distance point)
>
>[/color]
(69.09*DEGREES( ACOS(SIN(RADIAN S(Latitude))*SI N(RADIANS($lat) )+COS(RADIANS(L a
titude))*[color=blue]
> COS(RADIANS($la t))*COS(RADIANS (Longitude-$long))))) AS Distance
>
> will create the Distance 'column'
>
> ORDER BY Distance
>
> --
> Tom Thackrey
> www.creative-light.com
> tom (at) creative (dash) light (dot) com
> do NOT send email to jamesbutler@wil lglen.net (it's reserved for spammers)[/color]

**Doug Hutcheson** · Jul 17 '05, 03:02 AM

Re: ORDER BY Zip Code Distance

--
Remove the blots from my address to reply
"Xenophobe" <xenophobe@plan etx.com> wrote in message
news:9cDVb.2580 63$na.418618@at tbi_s04...[color=blue]
> Tom, thanks for your response.
>
> I modified my query and added the lines you provided as illustrated in[/color]
your[color=blue]
> example.
>
> $sql = "SELECT s.suppliername, s.address1, s.address2, s.city, s.state,
> s.zip, s.phone, l.latitude, l.longitude ";
> $sql .= "FROM Suppliers s ";
> $sql .= "INNER JOIN Locations l ON l.zipcode = s.zip ";
> $sql .= "WHERE l.latitude <= ".$highLatitude ." ";
> $sql .= "AND l.latitude >= ".$lowLatitude. " ";
> $sql .= "AND l.longitude >= ".$lowLongitude ." ";
> $sql .= "AND l.longitude <= ".$highLongitud e." ";
> $sql .=
>[/color]
"(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) *SIN(RADIANS($l atitude))+COS(R A[color=blue]
>[/color]
DIANS(l.latitud e))*COS(RADIANS ($latitude))*CO S(RADIANS(l.lon gitude-$longitud[color=blue]
> e))))) AS distance ";
> $sql .= "ORDER BY distance";
>
> I get the following error message:
>
> Warning: Supplied argument is not a valid MySQL result resource
>
> Commenting the last two lines makes the query servicable again.
>
> Any thoughts?
>
> "Tom Thackrey" <use.signature@ nospam.com> wrote in message
> news:CWAVb.2283 2$VW4.3380@news svr25.news.prod igy.com...[color=green]
> >
> > On 8-Feb-2004, "Xenophobe" <xenophobe@plan etx.com> wrote:
> >[color=darkred]
> > > As described the high and low latitudes and longitudes are[/color][/color]
> pre-calculated[color=green][color=darkred]
> > > and passed to the query.
> > >
> > > SELECT *
> > > FROM Locations
> > > WHERE Latitude <= $HighLatitude
> > > AND Latitude >= $LowLatitude
> > > AND Longitude >= $LowLongitude
> > > AND Longitude <= $HighLongitude
> > >
> > > I then calculate the actual distance using the latitude and longitude[/color][/color]
> for[color=green][color=darkred]
> > > each zip returned.
> > >
> > > This works great and saves lots of cycles. The downside to this[/color][/color]
> technique[color=green][color=darkred]
> > > is
> > > the inability to sort by distance. Can someone suggest a simple way to
> > > calculate and ORDER BY distance within the query?[/color]
> >
> > Put the following in your SQL ($lat and $long are the zero distance[/color][/color]
point)[color=blue][color=green]
> >
> >[/color]
>[/color]
(69.09*DEGREES( ACOS(SIN(RADIAN S(Latitude))*SI N(RADIANS($lat) )+COS(RADIANS(L a[color=blue]
> titude))*[color=green]
> > COS(RADIANS($la t))*COS(RADIANS (Longitude-$long))))) AS Distance
> >
> > will create the Distance 'column'
> >
> > ORDER BY Distance
> >
> > --
> > Tom Thackrey
> > www.creative-light.com
> > tom (at) creative (dash) light (dot) com
> > do NOT send email to jamesbutler@wil lglen.net (it's reserved for[/color][/color]
spammers)[color=blue]
>
>[/color]

Xenophobe,

You need to put the calculated value selection in the 'SELECT' clause, not
the 'WHERE' clause. What you are trying to do is 'select' a calculated value
as if it was just another field in the underlying database.

I have reformatted the following to avoid odd line breaks, but the
SQLstatement is exactly the same as you posted, except that the clauses have
been rearranged as required:

$sql = "SELECT s.suppliername, s.address1, s.address2, s.city, ";
$sql .= "s.state,s. zip, s.phone, l.latitude, l.longitude ";
$sql .= "(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) ";
$sql .= "*SIN(RADIANS($ latitude))+COS( RADIANS(l.latit ude))";
$sql .= "*COS(RADIANS($ latitude))*COS( RADIANS(l.longi tude-$longitude))))) ";
$sql .= " AS distance ";
$sql .= "FROM Suppliers s ";
$sql .= "INNER JOIN Locations l ON l.zipcode = s.zip ";
$sql .= "WHERE l.latitude <= ".$highLatitude ." ";
$sql .= "AND l.latitude >= ".$lowLatitude. " ";
$sql .= "AND l.longitude >= ".$lowLongitude ." ";
$sql .= "AND l.longitude <= ".$highLongitud e." ";
$sql .= "ORDER BY distance";

HTH
Doug

**Xenophobe** · Jul 17 '05, 03:02 AM

Re: ORDER BY Zip Code Distance

Doug,

You're absolutely correct! Wow, what a newbie mistake--I know better than
that.

Ok, I will give the properly constructed query a go.

Best regards,
X

"Doug Hutcheson" <doug.blot.hutc heson@nrm.blot. qld.blot.gov.bl ot.au> wrote
in message news:DDDVb.512$ KW.33452@news.o ptus.net.au...[color=blue]
> --
> Remove the blots from my address to reply
> "Xenophobe" <xenophobe@plan etx.com> wrote in message
> news:9cDVb.2580 63$na.418618@at tbi_s04...[color=green]
> > Tom, thanks for your response.
> >
> > I modified my query and added the lines you provided as illustrated in[/color]
> your[color=green]
> > example.
> >
> > $sql = "SELECT s.suppliername, s.address1, s.address2, s.city, s.state,
> > s.zip, s.phone, l.latitude, l.longitude ";
> > $sql .= "FROM Suppliers s ";
> > $sql .= "INNER JOIN Locations l ON l.zipcode = s.zip ";
> > $sql .= "WHERE l.latitude <= ".$highLatitude ." ";
> > $sql .= "AND l.latitude >= ".$lowLatitude. " ";
> > $sql .= "AND l.longitude >= ".$lowLongitude ." ";
> > $sql .= "AND l.longitude <= ".$highLongitud e." ";
> > $sql .=
> >[/color]
>[/color]
"(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) *SIN(RADIANS($l atitude))+COS(R A[color=blue][color=green]
> >[/color]
>[/color]
DIANS(l.latitud e))*COS(RADIANS ($latitude))*CO S(RADIANS(l.lon gitude-$longitud[color=blue][color=green]
> > e))))) AS distance ";
> > $sql .= "ORDER BY distance";
> >
> > I get the following error message:
> >
> > Warning: Supplied argument is not a valid MySQL result resource
> >
> > Commenting the last two lines makes the query servicable again.
> >
> > Any thoughts?
> >
> > "Tom Thackrey" <use.signature@ nospam.com> wrote in message
> > news:CWAVb.2283 2$VW4.3380@news svr25.news.prod igy.com...[color=darkred]
> > >
> > > On 8-Feb-2004, "Xenophobe" <xenophobe@plan etx.com> wrote:
> > >
> > > > As described the high and low latitudes and longitudes are[/color]
> > pre-calculated[color=darkred]
> > > > and passed to the query.
> > > >
> > > > SELECT *
> > > > FROM Locations
> > > > WHERE Latitude <= $HighLatitude
> > > > AND Latitude >= $LowLatitude
> > > > AND Longitude >= $LowLongitude
> > > > AND Longitude <= $HighLongitude
> > > >
> > > > I then calculate the actual distance using the latitude and[/color][/color][/color]
longitude[color=blue][color=green]
> > for[color=darkred]
> > > > each zip returned.
> > > >
> > > > This works great and saves lots of cycles. The downside to this[/color]
> > technique[color=darkred]
> > > > is
> > > > the inability to sort by distance. Can someone suggest a simple way[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> > > > calculate and ORDER BY distance within the query?
> > >
> > > Put the following in your SQL ($lat and $long are the zero distance[/color][/color]
> point)[color=green][color=darkred]
> > >
> > >[/color]
> >[/color]
>[/color]
(69.09*DEGREES( ACOS(SIN(RADIAN S(Latitude))*SI N(RADIANS($lat) )+COS(RADIANS(L a[color=blue][color=green]
> > titude))*[color=darkred]
> > > COS(RADIANS($la t))*COS(RADIANS (Longitude-$long))))) AS Distance
> > >
> > > will create the Distance 'column'
> > >
> > > ORDER BY Distance
> > >
> > > --
> > > Tom Thackrey
> > > www.creative-light.com
> > > tom (at) creative (dash) light (dot) com
> > > do NOT send email to jamesbutler@wil lglen.net (it's reserved for[/color][/color]
> spammers)[color=green]
> >
> >[/color]
>
> Xenophobe,
>
> You need to put the calculated value selection in the 'SELECT' clause, not
> the 'WHERE' clause. What you are trying to do is 'select' a calculated[/color]
value[color=blue]
> as if it was just another field in the underlying database.
>
> I have reformatted the following to avoid odd line breaks, but the
> SQLstatement is exactly the same as you posted, except that the clauses[/color]
have[color=blue]
> been rearranged as required:
>
> $sql = "SELECT s.suppliername, s.address1, s.address2, s.city, ";
> $sql .= "s.state,s. zip, s.phone, l.latitude, l.longitude ";
> $sql .= "(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) ";
> $sql .= "*SIN(RADIANS($ latitude))+COS( RADIANS(l.latit ude))";
> $sql .=[/color]
"*COS(RADIANS($ latitude))*COS( RADIANS(l.longi tude-$longitude))))) ";[color=blue]
> $sql .= " AS distance ";
> $sql .= "FROM Suppliers s ";
> $sql .= "INNER JOIN Locations l ON l.zipcode = s.zip ";
> $sql .= "WHERE l.latitude <= ".$highLatitude ." ";
> $sql .= "AND l.latitude >= ".$lowLatitude. " ";
> $sql .= "AND l.longitude >= ".$lowLongitude ." ";
> $sql .= "AND l.longitude <= ".$highLongitud e." ";
> $sql .= "ORDER BY distance";
>
> HTH
> Doug
>
>[/color]

**Xenophobe** · Jul 17 '05, 03:16 AM

Re: ORDER BY Zip Code Distance

Doug,

I cut & pasted the query, but alas it still returns the same error as
before.

Perhaps it's the MySQL version. I'm currently running 3.23.29a-gamma on
Linux.

I don't have any problems with a variety of other queries, although most do
not have the math.

Here's an example of a populated query:

SELECT DISTINCT s.suppliername, s.address1, s.address2, s.city, s.state,
s.zip, s.phone, l.latitude, l.longitude
(69.09*DEGREES( ACOS(SIN(RADIAN S(l.latitude))* SIN(RADIANS(41. 386533))+COS(RA D
IANS(l.latitude ))*COS(RADIANS( 41.386533))*COS (RADIANS(l.long itude--82.234199
))))) AS distance FROM Suppliers s INNER JOIN Locations l ON l.zipcode =
s.zip WHERE l.latitude <= 41.531365126399 AND l.latitude >= 41.241700873601
AND l.longitude >= -82.427239911609 AND l.longitude <= -82.041158088391
ORDER BY distance

This generates the following error:

Supplied argument is not a valid MySQL result resource

I would love to find a solution for this, as it's holding up the completion
of the project.

Thanks.

"Doug Hutcheson" <doug.blot.hutc heson@nrm.blot. qld.blot.gov.bl ot.au> wrote
in message news:DDDVb.512$ KW.33452@news.o ptus.net.au...[color=blue]
> --
> Remove the blots from my address to reply
> "Xenophobe" <xenophobe@plan etx.com> wrote in message
> news:9cDVb.2580 63$na.418618@at tbi_s04...[color=green]
> > Tom, thanks for your response.
> >
> > I modified my query and added the lines you provided as illustrated in[/color]
> your[color=green]
> > example.
> >
> > $sql = "SELECT s.suppliername, s.address1, s.address2, s.city, s.state,
> > s.zip, s.phone, l.latitude, l.longitude ";
> > $sql .= "FROM Suppliers s ";
> > $sql .= "INNER JOIN Locations l ON l.zipcode = s.zip ";
> > $sql .= "WHERE l.latitude <= ".$highLatitude ." ";
> > $sql .= "AND l.latitude >= ".$lowLatitude. " ";
> > $sql .= "AND l.longitude >= ".$lowLongitude ." ";
> > $sql .= "AND l.longitude <= ".$highLongitud e." ";
> > $sql .=
> >[/color]
>[/color]
"(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) *SIN(RADIANS($l atitude))+COS(R A[color=blue][color=green]
> >[/color]
>[/color]
DIANS(l.latitud e))*COS(RADIANS ($latitude))*CO S(RADIANS(l.lon gitude-$longitud[color=blue][color=green]
> > e))))) AS distance ";
> > $sql .= "ORDER BY distance";
> >
> > I get the following error message:
> >
> > Warning: Supplied argument is not a valid MySQL result resource
> >
> > Commenting the last two lines makes the query servicable again.
> >
> > Any thoughts?
> >
> > "Tom Thackrey" <use.signature@ nospam.com> wrote in message
> > news:CWAVb.2283 2$VW4.3380@news svr25.news.prod igy.com...[color=darkred]
> > >
> > > On 8-Feb-2004, "Xenophobe" <xenophobe@plan etx.com> wrote:
> > >
> > > > As described the high and low latitudes and longitudes are[/color]
> > pre-calculated[color=darkred]
> > > > and passed to the query.
> > > >
> > > > SELECT *
> > > > FROM Locations
> > > > WHERE Latitude <= $HighLatitude
> > > > AND Latitude >= $LowLatitude
> > > > AND Longitude >= $LowLongitude
> > > > AND Longitude <= $HighLongitude
> > > >
> > > > I then calculate the actual distance using the latitude and[/color][/color][/color]
longitude[color=blue][color=green]
> > for[color=darkred]
> > > > each zip returned.
> > > >
> > > > This works great and saves lots of cycles. The downside to this[/color]
> > technique[color=darkred]
> > > > is
> > > > the inability to sort by distance. Can someone suggest a simple way[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> > > > calculate and ORDER BY distance within the query?
> > >
> > > Put the following in your SQL ($lat and $long are the zero distance[/color][/color]
> point)[color=green][color=darkred]
> > >
> > >[/color]
> >[/color]
>[/color]
(69.09*DEGREES( ACOS(SIN(RADIAN S(Latitude))*SI N(RADIANS($lat) )+COS(RADIANS(L a[color=blue][color=green]
> > titude))*[color=darkred]
> > > COS(RADIANS($la t))*COS(RADIANS (Longitude-$long))))) AS Distance
> > >
> > > will create the Distance 'column'
> > >
> > > ORDER BY Distance
> > >
> > > --
> > > Tom Thackrey
> > > www.creative-light.com
> > > tom (at) creative (dash) light (dot) com
> > > do NOT send email to jamesbutler@wil lglen.net (it's reserved for[/color][/color]
> spammers)[color=green]
> >
> >[/color]
>
> Xenophobe,
>
> You need to put the calculated value selection in the 'SELECT' clause, not
> the 'WHERE' clause. What you are trying to do is 'select' a calculated[/color]
value[color=blue]
> as if it was just another field in the underlying database.
>
> I have reformatted the following to avoid odd line breaks, but the
> SQLstatement is exactly the same as you posted, except that the clauses[/color]
have[color=blue]
> been rearranged as required:
>
> $sql = "SELECT s.suppliername, s.address1, s.address2, s.city, ";
> $sql .= "s.state,s. zip, s.phone, l.latitude, l.longitude ";
> $sql .= "(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) ";
> $sql .= "*SIN(RADIANS($ latitude))+COS( RADIANS(l.latit ude))";
> $sql .=[/color]
"*COS(RADIANS($ latitude))*COS( RADIANS(l.longi tude-$longitude))))) ";[color=blue]
> $sql .= " AS distance ";
> $sql .= "FROM Suppliers s ";
> $sql .= "INNER JOIN Locations l ON l.zipcode = s.zip ";
> $sql .= "WHERE l.latitude <= ".$highLatitude ." ";
> $sql .= "AND l.latitude >= ".$lowLatitude. " ";
> $sql .= "AND l.longitude >= ".$lowLongitude ." ";
> $sql .= "AND l.longitude <= ".$highLongitud e." ";
> $sql .= "ORDER BY distance";
>
> HTH
> Doug
>
>[/color]

**Doug Hutcheson** · Jul 17 '05, 03:16 AM

Re: ORDER BY Zip Code Distance

Top posting corrected...see below[color=blue]
> "Doug Hutcheson" <doug.blot.hutc heson@nrm.blot. qld.blot.gov.bl ot.au> wrote
> in message news:DDDVb.512$ KW.33452@news.o ptus.net.au...[color=green]
> > --
> > Remove the blots from my address to reply
> > "Xenophobe" <xenophobe@plan etx.com> wrote in message
> > news:9cDVb.2580 63$na.418618@at tbi_s04...[color=darkred]
> > > Tom, thanks for your response.
> > >
> > > I modified my query and added the lines you provided as illustrated in[/color]
> > your[color=darkred]
> > > example.
> > >
> > > $sql = "SELECT s.suppliername, s.address1, s.address2, s.city,[/color][/color][/color]
s.state,[color=blue][color=green][color=darkred]
> > > s.zip, s.phone, l.latitude, l.longitude ";
> > > $sql .= "FROM Suppliers s ";
> > > $sql .= "INNER JOIN Locations l ON l.zipcode = s.zip ";
> > > $sql .= "WHERE l.latitude <= ".$highLatitude ." ";
> > > $sql .= "AND l.latitude >= ".$lowLatitude. " ";
> > > $sql .= "AND l.longitude >= ".$lowLongitude ." ";
> > > $sql .= "AND l.longitude <= ".$highLongitud e." ";
> > > $sql .=
> > >[/color]
> >[/color]
>[/color]
"(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) *SIN(RADIANS($l atitude))+COS(R A[color=blue][color=green][color=darkred]
> > >[/color]
> >[/color]
>[/color]
DIANS(l.latitud e))*COS(RADIANS ($latitude))*CO S(RADIANS(l.lon gitude-$longitud[color=blue][color=green][color=darkred]
> > > e))))) AS distance ";
> > > $sql .= "ORDER BY distance";
> > >
> > > I get the following error message:
> > >
> > > Warning: Supplied argument is not a valid MySQL result resource
> > >
> > > Commenting the last two lines makes the query servicable again.
> > >
> > > Any thoughts?
> > >
> > > "Tom Thackrey" <use.signature@ nospam.com> wrote in message
> > > news:CWAVb.2283 2$VW4.3380@news svr25.news.prod igy.com...
> > > >
> > > > On 8-Feb-2004, "Xenophobe" <xenophobe@plan etx.com> wrote:
> > > >
> > > > > As described the high and low latitudes and longitudes are
> > > pre-calculated
> > > > > and passed to the query.
> > > > >
> > > > > SELECT *
> > > > > FROM Locations
> > > > > WHERE Latitude <= $HighLatitude
> > > > > AND Latitude >= $LowLatitude
> > > > > AND Longitude >= $LowLongitude
> > > > > AND Longitude <= $HighLongitude
> > > > >
> > > > > I then calculate the actual distance using the latitude and[/color][/color]
> longitude[color=green][color=darkred]
> > > for
> > > > > each zip returned.
> > > > >
> > > > > This works great and saves lots of cycles. The downside to this
> > > technique
> > > > > is
> > > > > the inability to sort by distance. Can someone suggest a simple[/color][/color][/color]
way[color=blue]
> to[color=green][color=darkred]
> > > > > calculate and ORDER BY distance within the query?
> > > >
> > > > Put the following in your SQL ($lat and $long are the zero distance[/color]
> > point)[color=darkred]
> > > >
> > > >
> > >[/color]
> >[/color]
>[/color]
(69.09*DEGREES( ACOS(SIN(RADIAN S(Latitude))*SI N(RADIANS($lat) )+COS(RADIANS(L a[color=blue][color=green][color=darkred]
> > > titude))*
> > > > COS(RADIANS($la t))*COS(RADIANS (Longitude-$long))))) AS Distance
> > > >
> > > > will create the Distance 'column'
> > > >
> > > > ORDER BY Distance
> > > >
> > > > --
> > > > Tom Thackrey
> > > > www.creative-light.com
> > > > tom (at) creative (dash) light (dot) com
> > > > do NOT send email to jamesbutler@wil lglen.net (it's reserved for[/color]
> > spammers)[color=darkred]
> > >
> > >[/color]
> >
> > Xenophobe,
> >
> > You need to put the calculated value selection in the 'SELECT' clause,[/color][/color]
not[color=blue][color=green]
> > the 'WHERE' clause. What you are trying to do is 'select' a calculated[/color]
> value[color=green]
> > as if it was just another field in the underlying database.
> >
> > I have reformatted the following to avoid odd line breaks, but the
> > SQLstatement is exactly the same as you posted, except that the clauses[/color]
> have[color=green]
> > been rearranged as required:
> >
> > $sql = "SELECT s.suppliername, s.address1, s.address2, s.city, ";
> > $sql .= "s.state,s. zip, s.phone, l.latitude, l.longitude ";
> > $sql .= "(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) ";
> > $sql .= "*SIN(RADIANS($ latitude))+COS( RADIANS(l.latit ude))";
> > $sql .=[/color]
> "*COS(RADIANS($ latitude))*COS( RADIANS(l.longi tude-$longitude))))) ";[color=green]
> > $sql .= " AS distance ";
> > $sql .= "FROM Suppliers s ";
> > $sql .= "INNER JOIN Locations l ON l.zipcode = s.zip ";
> > $sql .= "WHERE l.latitude <= ".$highLatitude ." ";
> > $sql .= "AND l.latitude >= ".$lowLatitude. " ";
> > $sql .= "AND l.longitude >= ".$lowLongitude ." ";
> > $sql .= "AND l.longitude <= ".$highLongitud e." ";
> > $sql .= "ORDER BY distance";
> >
> > HTH
> > Doug
> >
> >[/color]
>
>[/color]
"Xenophobe" <xenophobe@plan etx.com> wrote in message
news:a%gWb.2687 22$I06.2878665@ attbi_s01...[color=blue]
> Doug,
>
> I cut & pasted the query, but alas it still returns the same error as
> before.
>
> Perhaps it's the MySQL version. I'm currently running 3.23.29a-gamma on
> Linux.
>
> I don't have any problems with a variety of other queries, although most[/color]
do[color=blue]
> not have the math.
>
> Here's an example of a populated query:
>
> SELECT DISTINCT s.suppliername, s.address1, s.address2, s.city, s.state,
> s.zip, s.phone, l.latitude, l.longitude
>[/color]
(69.09*DEGREES( ACOS(SIN(RADIAN S(l.latitude))* SIN(RADIANS(41. 386533))+COS(RA D[color=blue]
>[/color]
IANS(l.latitude ))*COS(RADIANS( 41.386533))*COS (RADIANS(l.long itude--82.234199[color=blue]
> ))))) AS distance FROM Suppliers s INNER JOIN Locations l ON l.zipcode =
> s.zip WHERE l.latitude <= 41.531365126399 AND l.latitude >=[/color]
41.241700873601[color=blue]
> AND l.longitude >= -82.427239911609 AND l.longitude <= -82.041158088391
> ORDER BY distance
>
>
> This generates the following error:
>
> Supplied argument is not a valid MySQL result resource
>
>
> I would love to find a solution for this, as it's holding up the[/color]
completion[color=blue]
> of the project.
>
> Thanks.
>[/color]

Xenophobe,
Hmmmm....must get the upgrade to my finger driver....!
We are missing a comma between " l.longitude" and "(69.09..."

The corrected lines are:

<snip>
$sql .= "s.state,s. zip, s.phone, l.latitude, l.longitude, ";
$sql .= "(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) ";
<snip>

Of course, I will claim I deliberately omitted the comma to test your SQL
skills ...<grin>
Cheers,
Doug

--
Remove the blots from my address to reply

**Xenophobe** · Jul 17 '05, 03:17 AM

Re: ORDER BY Zip Code Distance

Doug,

I missed that and failed the test. The last item in the SELECT clause
doesn't need a comma, but "l.longitud e" isn't the last item with the
addition of "distance". <excuse>I've been pounding my head for too long
trying to resolve this problem.</excuse> In addition to learning some handy
SQL syntax, it's also reminded me that things are not always what they
appear to be.

ANWAY, I sincerely appreciate your assistance--thanks!

"Doug Hutcheson" <doug.blot.hutc heson@nrm.blot. qld.blot.gov.bl ot.au> wrote
in message news:kciWb.612$ KW.37661@news.o ptus.net.au...[color=blue]
> Top posting corrected...see below[color=green]
> > "Doug Hutcheson" <doug.blot.hutc heson@nrm.blot. qld.blot.gov.bl ot.au>[/color][/color]
wrote[color=blue][color=green]
> > in message news:DDDVb.512$ KW.33452@news.o ptus.net.au...[color=darkred]
> > > --
> > > Remove the blots from my address to reply
> > > "Xenophobe" <xenophobe@plan etx.com> wrote in message
> > > news:9cDVb.2580 63$na.418618@at tbi_s04...
> > > > Tom, thanks for your response.
> > > >
> > > > I modified my query and added the lines you provided as illustrated[/color][/color][/color]
in[color=blue][color=green][color=darkred]
> > > your
> > > > example.
> > > >
> > > > $sql = "SELECT s.suppliername, s.address1, s.address2, s.city,[/color][/color]
> s.state,[color=green][color=darkred]
> > > > s.zip, s.phone, l.latitude, l.longitude ";
> > > > $sql .= "FROM Suppliers s ";
> > > > $sql .= "INNER JOIN Locations l ON l.zipcode = s.zip ";
> > > > $sql .= "WHERE l.latitude <= ".$highLatitude ." ";
> > > > $sql .= "AND l.latitude >= ".$lowLatitude. " ";
> > > > $sql .= "AND l.longitude >= ".$lowLongitude ." ";
> > > > $sql .= "AND l.longitude <= ".$highLongitud e." ";
> > > > $sql .=
> > > >
> > >[/color]
> >[/color]
>[/color]
"(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) *SIN(RADIANS($l atitude))+COS(R A[color=blue][color=green][color=darkred]
> > > >
> > >[/color]
> >[/color]
>[/color]
DIANS(l.latitud e))*COS(RADIANS ($latitude))*CO S(RADIANS(l.lon gitude-$longitud[color=blue][color=green][color=darkred]
> > > > e))))) AS distance ";
> > > > $sql .= "ORDER BY distance";
> > > >
> > > > I get the following error message:
> > > >
> > > > Warning: Supplied argument is not a valid MySQL result resource
> > > >
> > > > Commenting the last two lines makes the query servicable again.
> > > >
> > > > Any thoughts?
> > > >
> > > > "Tom Thackrey" <use.signature@ nospam.com> wrote in message
> > > > news:CWAVb.2283 2$VW4.3380@news svr25.news.prod igy.com...
> > > > >
> > > > > On 8-Feb-2004, "Xenophobe" <xenophobe@plan etx.com> wrote:
> > > > >
> > > > > > As described the high and low latitudes and longitudes are
> > > > pre-calculated
> > > > > > and passed to the query.
> > > > > >
> > > > > > SELECT *
> > > > > > FROM Locations
> > > > > > WHERE Latitude <= $HighLatitude
> > > > > > AND Latitude >= $LowLatitude
> > > > > > AND Longitude >= $LowLongitude
> > > > > > AND Longitude <= $HighLongitude
> > > > > >
> > > > > > I then calculate the actual distance using the latitude and[/color]
> > longitude[color=darkred]
> > > > for
> > > > > > each zip returned.
> > > > > >
> > > > > > This works great and saves lots of cycles. The downside to this
> > > > technique
> > > > > > is
> > > > > > the inability to sort by distance. Can someone suggest a simple[/color][/color]
> way[color=green]
> > to[color=darkred]
> > > > > > calculate and ORDER BY distance within the query?
> > > > >
> > > > > Put the following in your SQL ($lat and $long are the zero[/color][/color][/color]
distance[color=blue][color=green][color=darkred]
> > > point)
> > > > >
> > > > >
> > > >
> > >[/color]
> >[/color]
>[/color]
(69.09*DEGREES( ACOS(SIN(RADIAN S(Latitude))*SI N(RADIANS($lat) )+COS(RADIANS(L a[color=blue][color=green][color=darkred]
> > > > titude))*
> > > > > COS(RADIANS($la t))*COS(RADIANS (Longitude-$long))))) AS Distance
> > > > >
> > > > > will create the Distance 'column'
> > > > >
> > > > > ORDER BY Distance
> > > > >
> > > > > --
> > > > > Tom Thackrey
> > > > > www.creative-light.com
> > > > > tom (at) creative (dash) light (dot) com
> > > > > do NOT send email to jamesbutler@wil lglen.net (it's reserved for
> > > spammers)
> > > >
> > > >
> > >
> > > Xenophobe,
> > >
> > > You need to put the calculated value selection in the 'SELECT' clause,[/color][/color]
> not[color=green][color=darkred]
> > > the 'WHERE' clause. What you are trying to do is 'select' a calculated[/color]
> > value[color=darkred]
> > > as if it was just another field in the underlying database.
> > >
> > > I have reformatted the following to avoid odd line breaks, but the
> > > SQLstatement is exactly the same as you posted, except that the[/color][/color][/color]
clauses[color=blue][color=green]
> > have[color=darkred]
> > > been rearranged as required:
> > >
> > > $sql = "SELECT s.suppliername, s.address1, s.address2, s.city, ";
> > > $sql .= "s.state,s. zip, s.phone, l.latitude, l.longitude ";
> > > $sql .= "(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) ";
> > > $sql .= "*SIN(RADIANS($ latitude))+COS( RADIANS(l.latit ude))";
> > > $sql .=[/color]
> > "*COS(RADIANS($ latitude))*COS( RADIANS(l.longi tude-$longitude))))) ";[color=darkred]
> > > $sql .= " AS distance ";
> > > $sql .= "FROM Suppliers s ";
> > > $sql .= "INNER JOIN Locations l ON l.zipcode = s.zip ";
> > > $sql .= "WHERE l.latitude <= ".$highLatitude ." ";
> > > $sql .= "AND l.latitude >= ".$lowLatitude. " ";
> > > $sql .= "AND l.longitude >= ".$lowLongitude ." ";
> > > $sql .= "AND l.longitude <= ".$highLongitud e." ";
> > > $sql .= "ORDER BY distance";
> > >
> > > HTH
> > > Doug
> > >
> > >[/color]
> >
> >[/color]
> "Xenophobe" <xenophobe@plan etx.com> wrote in message
> news:a%gWb.2687 22$I06.2878665@ attbi_s01...[color=green]
> > Doug,
> >
> > I cut & pasted the query, but alas it still returns the same error as
> > before.
> >
> > Perhaps it's the MySQL version. I'm currently running 3.23.29a-gamma on
> > Linux.
> >
> > I don't have any problems with a variety of other queries, although most[/color]
> do[color=green]
> > not have the math.
> >
> > Here's an example of a populated query:
> >
> > SELECT DISTINCT s.suppliername, s.address1, s.address2, s.city, s.state,
> > s.zip, s.phone, l.latitude, l.longitude
> >[/color]
>[/color]
(69.09*DEGREES( ACOS(SIN(RADIAN S(l.latitude))* SIN(RADIANS(41. 386533))+COS(RA D[color=blue][color=green]
> >[/color]
>[/color]
IANS(l.latitude ))*COS(RADIANS( 41.386533))*COS (RADIANS(l.long itude--82.234199[color=blue][color=green]
> > ))))) AS distance FROM Suppliers s INNER JOIN Locations l ON l.zipcode =
> > s.zip WHERE l.latitude <= 41.531365126399 AND l.latitude >=[/color]
> 41.241700873601[color=green]
> > AND l.longitude >= -82.427239911609 AND l.longitude <= -82.041158088391
> > ORDER BY distance
> >
> >
> > This generates the following error:
> >
> > Supplied argument is not a valid MySQL result resource
> >
> >
> > I would love to find a solution for this, as it's holding up the[/color]
> completion[color=green]
> > of the project.
> >
> > Thanks.
> >[/color]
>
> Xenophobe,
> Hmmmm....must get the upgrade to my finger driver....!
> We are missing a comma between " l.longitude" and "(69.09..."
>
> The corrected lines are:
>
> <snip>
> $sql .= "s.state,s. zip, s.phone, l.latitude, l.longitude, ";
> $sql .= "(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) ";
> <snip>
>
> Of course, I will claim I deliberately omitted the comma to test your SQL
> skills ...<grin>
> Cheers,
> Doug
>
> --
> Remove the blots from my address to reply
>
>[/color]

**Doug Hutcheson** · Jul 17 '05, 03:17 AM

Re: ORDER BY Zip Code Distance

Xenophobe,

Glad to help. Just proves how easily we can get engrossed in the interesting
bits of complexity and overlook simple things like punctuation.

Anyway, perhaps all this help from around the globe will go some way toward
curing your xenophobia.
<grin>

Cheers,
Doug (Brisbane, Australia)
--
Remove the blots from my address to reply
"Xenophobe" <xenophobe@plan etx.com> wrote in message
news:QHsWb.2728 19$I06.2925114@ attbi_s01...[color=blue]
> Doug,
>
> I missed that and failed the test. The last item in the SELECT clause
> doesn't need a comma, but "l.longitud e" isn't the last item with the
> addition of "distance". <excuse>I've been pounding my head for too long
> trying to resolve this problem.</excuse> In addition to learning some[/color]
handy[color=blue]
> SQL syntax, it's also reminded me that things are not always what they
> appear to be.
>
> ANWAY, I sincerely appreciate your assistance--thanks!
>
> "Doug Hutcheson" <doug.blot.hutc heson@nrm.blot. qld.blot.gov.bl ot.au> wrote
> in message news:kciWb.612$ KW.37661@news.o ptus.net.au...[color=green]
> > Top posting corrected...see below[color=darkred]
> > > "Doug Hutcheson" <doug.blot.hutc heson@nrm.blot. qld.blot.gov.bl ot.au>[/color][/color]
> wrote[color=green][color=darkred]
> > > in message news:DDDVb.512$ KW.33452@news.o ptus.net.au...
> > > > --
> > > > Remove the blots from my address to reply
> > > > "Xenophobe" <xenophobe@plan etx.com> wrote in message
> > > > news:9cDVb.2580 63$na.418618@at tbi_s04...
> > > > > Tom, thanks for your response.
> > > > >
> > > > > I modified my query and added the lines you provided as[/color][/color][/color]
illustrated[color=blue]
> in[color=green][color=darkred]
> > > > your
> > > > > example.
> > > > >
> > > > > $sql = "SELECT s.suppliername, s.address1, s.address2, s.city,[/color]
> > s.state,[color=darkred]
> > > > > s.zip, s.phone, l.latitude, l.longitude ";
> > > > > $sql .= "FROM Suppliers s ";
> > > > > $sql .= "INNER JOIN Locations l ON l.zipcode = s.zip ";
> > > > > $sql .= "WHERE l.latitude <= ".$highLatitude ." ";
> > > > > $sql .= "AND l.latitude >= ".$lowLatitude. " ";
> > > > > $sql .= "AND l.longitude >= ".$lowLongitude ." ";
> > > > > $sql .= "AND l.longitude <= ".$highLongitud e." ";
> > > > > $sql .=
> > > > >
> > > >
> > >[/color]
> >[/color]
>[/color]
"(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) *SIN(RADIANS($l atitude))+COS(R A[color=blue][color=green][color=darkred]
> > > > >
> > > >
> > >[/color]
> >[/color]
>[/color]
DIANS(l.latitud e))*COS(RADIANS ($latitude))*CO S(RADIANS(l.lon gitude-$longitud[color=blue][color=green][color=darkred]
> > > > > e))))) AS distance ";
> > > > > $sql .= "ORDER BY distance";
> > > > >
> > > > > I get the following error message:
> > > > >
> > > > > Warning: Supplied argument is not a valid MySQL result resource
> > > > >
> > > > > Commenting the last two lines makes the query servicable again.
> > > > >
> > > > > Any thoughts?
> > > > >
> > > > > "Tom Thackrey" <use.signature@ nospam.com> wrote in message
> > > > > news:CWAVb.2283 2$VW4.3380@news svr25.news.prod igy.com...
> > > > > >
> > > > > > On 8-Feb-2004, "Xenophobe" <xenophobe@plan etx.com> wrote:
> > > > > >
> > > > > > > As described the high and low latitudes and longitudes are
> > > > > pre-calculated
> > > > > > > and passed to the query.
> > > > > > >
> > > > > > > SELECT *
> > > > > > > FROM Locations
> > > > > > > WHERE Latitude <= $HighLatitude
> > > > > > > AND Latitude >= $LowLatitude
> > > > > > > AND Longitude >= $LowLongitude
> > > > > > > AND Longitude <= $HighLongitude
> > > > > > >
> > > > > > > I then calculate the actual distance using the latitude and
> > > longitude
> > > > > for
> > > > > > > each zip returned.
> > > > > > >
> > > > > > > This works great and saves lots of cycles. The downside to[/color][/color][/color]
this[color=blue][color=green][color=darkred]
> > > > > technique
> > > > > > > is
> > > > > > > the inability to sort by distance. Can someone suggest a[/color][/color][/color]
simple[color=blue][color=green]
> > way[color=darkred]
> > > to
> > > > > > > calculate and ORDER BY distance within the query?
> > > > > >
> > > > > > Put the following in your SQL ($lat and $long are the zero[/color][/color]
> distance[color=green][color=darkred]
> > > > point)
> > > > > >
> > > > > >
> > > > >
> > > >
> > >[/color]
> >[/color]
>[/color]
(69.09*DEGREES( ACOS(SIN(RADIAN S(Latitude))*SI N(RADIANS($lat) )+COS(RADIANS(L a[color=blue][color=green][color=darkred]
> > > > > titude))*
> > > > > > COS(RADIANS($la t))*COS(RADIANS (Longitude-$long))))) AS Distance
> > > > > >
> > > > > > will create the Distance 'column'
> > > > > >
> > > > > > ORDER BY Distance
> > > > > >
> > > > > > --
> > > > > > Tom Thackrey
> > > > > > www.creative-light.com
> > > > > > tom (at) creative (dash) light (dot) com
> > > > > > do NOT send email to jamesbutler@wil lglen.net (it's reserved for
> > > > spammers)
> > > > >
> > > > >
> > > >
> > > > Xenophobe,
> > > >
> > > > You need to put the calculated value selection in the 'SELECT'[/color][/color][/color]
clause,[color=blue][color=green]
> > not[color=darkred]
> > > > the 'WHERE' clause. What you are trying to do is 'select' a[/color][/color][/color]
calculated[color=blue][color=green][color=darkred]
> > > value
> > > > as if it was just another field in the underlying database.
> > > >
> > > > I have reformatted the following to avoid odd line breaks, but the
> > > > SQLstatement is exactly the same as you posted, except that the[/color][/color]
> clauses[color=green][color=darkred]
> > > have
> > > > been rearranged as required:
> > > >
> > > > $sql = "SELECT s.suppliername, s.address1, s.address2, s.city, ";
> > > > $sql .= "s.state,s. zip, s.phone, l.latitude, l.longitude ";
> > > > $sql .= "(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) ";
> > > > $sql .= "*SIN(RADIANS($ latitude))+COS( RADIANS(l.latit ude))";
> > > > $sql .=
> > > "*COS(RADIANS($ latitude))*COS( RADIANS(l.longi tude-$longitude))))) ";
> > > > $sql .= " AS distance ";
> > > > $sql .= "FROM Suppliers s ";
> > > > $sql .= "INNER JOIN Locations l ON l.zipcode = s.zip ";
> > > > $sql .= "WHERE l.latitude <= ".$highLatitude ." ";
> > > > $sql .= "AND l.latitude >= ".$lowLatitude. " ";
> > > > $sql .= "AND l.longitude >= ".$lowLongitude ." ";
> > > > $sql .= "AND l.longitude <= ".$highLongitud e." ";
> > > > $sql .= "ORDER BY distance";
> > > >
> > > > HTH
> > > > Doug
> > > >
> > > >
> > >
> > >[/color]
> > "Xenophobe" <xenophobe@plan etx.com> wrote in message
> > news:a%gWb.2687 22$I06.2878665@ attbi_s01...[color=darkred]
> > > Doug,
> > >
> > > I cut & pasted the query, but alas it still returns the same error as
> > > before.
> > >
> > > Perhaps it's the MySQL version. I'm currently running 3.23.29a-gamma[/color][/color][/color]
on[color=blue][color=green][color=darkred]
> > > Linux.
> > >
> > > I don't have any problems with a variety of other queries, although[/color][/color][/color]
most[color=blue][color=green]
> > do[color=darkred]
> > > not have the math.
> > >
> > > Here's an example of a populated query:
> > >
> > > SELECT DISTINCT s.suppliername, s.address1, s.address2, s.city,[/color][/color][/color]
s.state,[color=blue][color=green][color=darkred]
> > > s.zip, s.phone, l.latitude, l.longitude
> > >[/color]
> >[/color]
>[/color]
(69.09*DEGREES( ACOS(SIN(RADIAN S(l.latitude))* SIN(RADIANS(41. 386533))+COS(RA D[color=blue][color=green][color=darkred]
> > >[/color]
> >[/color]
>[/color]
IANS(l.latitude ))*COS(RADIANS( 41.386533))*COS (RADIANS(l.long itude--82.234199[color=blue][color=green][color=darkred]
> > > ))))) AS distance FROM Suppliers s INNER JOIN Locations l ON l.zipcode[/color][/color][/color]
=[color=blue][color=green][color=darkred]
> > > s.zip WHERE l.latitude <= 41.531365126399 AND l.latitude >=[/color]
> > 41.241700873601[color=darkred]
> > > AND l.longitude >= -82.427239911609 AND l.longitude[/color][/color][/color]
<= -82.041158088391[color=blue][color=green][color=darkred]
> > > ORDER BY distance
> > >
> > >
> > > This generates the following error:
> > >
> > > Supplied argument is not a valid MySQL result resource
> > >
> > >
> > > I would love to find a solution for this, as it's holding up the[/color]
> > completion[color=darkred]
> > > of the project.
> > >
> > > Thanks.
> > >[/color]
> >
> > Xenophobe,
> > Hmmmm....must get the upgrade to my finger driver....!
> > We are missing a comma between " l.longitude" and "(69.09..."
> >
> > The corrected lines are:
> >
> > <snip>
> > $sql .= "s.state,s. zip, s.phone, l.latitude, l.longitude, ";
> > $sql .= "(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) ";
> > <snip>
> >
> > Of course, I will claim I deliberately omitted the comma to test your[/color][/color]
SQL[color=blue][color=green]
> > skills ...<grin>
> > Cheers,
> > Doug
> >
> > --
> > Remove the blots from my address to reply
> >
> >[/color]
>
>[/color]

**Xenophobe** · Jul 17 '05, 03:19 AM

Re: ORDER BY Zip Code Distance

Doug,

This is just a quick follow-up. The zip sort by distance works great
except... the performance is pokey for searches of 20+ miles. I could simply
sort the results by company name (ditching the beautiful syntax you help me
with!), but even this won't save enough cycles for broader searches.

The options (in my view) are either break out the results in multiple pages
or pre-calculate every possible lat and long and store them in the database
(creating one huge table!) Can you think of anything that might help?

Thanks again for all your help!

p.s. the handle "Xenophobe" comes from the arcade game, specifically the
excellent Atari Lynx port. I still link several systems together and play
from time to time. :-)

"Doug Hutcheson" <doug.blot.hutc heson@nrm.blot. qld.blot.gov.bl ot.au> wrote
in message news:WSxWb.632$ KW.38860@news.o ptus.net.au...[color=blue]
> Xenophobe,
>
> Glad to help. Just proves how easily we can get engrossed in the[/color]
interesting[color=blue]
> bits of complexity and overlook simple things like punctuation.
>
> Anyway, perhaps all this help from around the globe will go some way[/color]
toward[color=blue]
> curing your xenophobia.
> <grin>
>
> Cheers,
> Doug (Brisbane, Australia)
> --
> Remove the blots from my address to reply
> "Xenophobe" <xenophobe@plan etx.com> wrote in message
> news:QHsWb.2728 19$I06.2925114@ attbi_s01...[color=green]
> > Doug,
> >
> > I missed that and failed the test. The last item in the SELECT clause
> > doesn't need a comma, but "l.longitud e" isn't the last item with the
> > addition of "distance". <excuse>I've been pounding my head for too long
> > trying to resolve this problem.</excuse> In addition to learning some[/color]
> handy[color=green]
> > SQL syntax, it's also reminded me that things are not always what they
> > appear to be.
> >
> > ANWAY, I sincerely appreciate your assistance--thanks!
> >
> > "Doug Hutcheson" <doug.blot.hutc heson@nrm.blot. qld.blot.gov.bl ot.au>[/color][/color]
wrote[color=blue][color=green]
> > in message news:kciWb.612$ KW.37661@news.o ptus.net.au...[color=darkred]
> > > Top posting corrected...see below
> > > > "Doug Hutcheson" <doug.blot.hutc heson@nrm.blot. qld.blot.gov.bl ot.au>[/color]
> > wrote[color=darkred]
> > > > in message news:DDDVb.512$ KW.33452@news.o ptus.net.au...
> > > > > --
> > > > > Remove the blots from my address to reply
> > > > > "Xenophobe" <xenophobe@plan etx.com> wrote in message
> > > > > news:9cDVb.2580 63$na.418618@at tbi_s04...
> > > > > > Tom, thanks for your response.
> > > > > >
> > > > > > I modified my query and added the lines you provided as[/color][/color]
> illustrated[color=green]
> > in[color=darkred]
> > > > > your
> > > > > > example.
> > > > > >
> > > > > > $sql = "SELECT s.suppliername, s.address1, s.address2, s.city,
> > > s.state,
> > > > > > s.zip, s.phone, l.latitude, l.longitude ";
> > > > > > $sql .= "FROM Suppliers s ";
> > > > > > $sql .= "INNER JOIN Locations l ON l.zipcode = s.zip ";
> > > > > > $sql .= "WHERE l.latitude <= ".$highLatitude ." ";
> > > > > > $sql .= "AND l.latitude >= ".$lowLatitude. " ";
> > > > > > $sql .= "AND l.longitude >= ".$lowLongitude ." ";
> > > > > > $sql .= "AND l.longitude <= ".$highLongitud e." ";
> > > > > > $sql .=
> > > > > >
> > > > >
> > > >
> > >[/color]
> >[/color]
>[/color]
"(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) *SIN(RADIANS($l atitude))+COS(R A[color=blue][color=green][color=darkred]
> > > > > >
> > > > >
> > > >
> > >[/color]
> >[/color]
>[/color]
DIANS(l.latitud e))*COS(RADIANS ($latitude))*CO S(RADIANS(l.lon gitude-$longitud[color=blue][color=green][color=darkred]
> > > > > > e))))) AS distance ";
> > > > > > $sql .= "ORDER BY distance";
> > > > > >
> > > > > > I get the following error message:
> > > > > >
> > > > > > Warning: Supplied argument is not a valid MySQL result resource
> > > > > >
> > > > > > Commenting the last two lines makes the query servicable again.
> > > > > >
> > > > > > Any thoughts?
> > > > > >
> > > > > > "Tom Thackrey" <use.signature@ nospam.com> wrote in message
> > > > > > news:CWAVb.2283 2$VW4.3380@news svr25.news.prod igy.com...
> > > > > > >
> > > > > > > On 8-Feb-2004, "Xenophobe" <xenophobe@plan etx.com> wrote:
> > > > > > >
> > > > > > > > As described the high and low latitudes and longitudes are
> > > > > > pre-calculated
> > > > > > > > and passed to the query.
> > > > > > > >
> > > > > > > > SELECT *
> > > > > > > > FROM Locations
> > > > > > > > WHERE Latitude <= $HighLatitude
> > > > > > > > AND Latitude >= $LowLatitude
> > > > > > > > AND Longitude >= $LowLongitude
> > > > > > > > AND Longitude <= $HighLongitude
> > > > > > > >
> > > > > > > > I then calculate the actual distance using the latitude and
> > > > longitude
> > > > > > for
> > > > > > > > each zip returned.
> > > > > > > >
> > > > > > > > This works great and saves lots of cycles. The downside to[/color][/color]
> this[color=green][color=darkred]
> > > > > > technique
> > > > > > > > is
> > > > > > > > the inability to sort by distance. Can someone suggest a[/color][/color]
> simple[color=green][color=darkred]
> > > way
> > > > to
> > > > > > > > calculate and ORDER BY distance within the query?
> > > > > > >
> > > > > > > Put the following in your SQL ($lat and $long are the zero[/color]
> > distance[color=darkred]
> > > > > point)
> > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >[/color]
> >[/color]
>[/color]
(69.09*DEGREES( ACOS(SIN(RADIAN S(Latitude))*SI N(RADIANS($lat) )+COS(RADIANS(L a[color=blue][color=green][color=darkred]
> > > > > > titude))*
> > > > > > > COS(RADIANS($la t))*COS(RADIANS (Longitude-$long))))) AS[/color][/color][/color]
Distance[color=blue][color=green][color=darkred]
> > > > > > >
> > > > > > > will create the Distance 'column'
> > > > > > >
> > > > > > > ORDER BY Distance
> > > > > > >
> > > > > > > --
> > > > > > > Tom Thackrey
> > > > > > > www.creative-light.com
> > > > > > > tom (at) creative (dash) light (dot) com
> > > > > > > do NOT send email to jamesbutler@wil lglen.net (it's reserved[/color][/color][/color]
for[color=blue][color=green][color=darkred]
> > > > > spammers)
> > > > > >
> > > > > >
> > > > >
> > > > > Xenophobe,
> > > > >
> > > > > You need to put the calculated value selection in the 'SELECT'[/color][/color]
> clause,[color=green][color=darkred]
> > > not
> > > > > the 'WHERE' clause. What you are trying to do is 'select' a[/color][/color]
> calculated[color=green][color=darkred]
> > > > value
> > > > > as if it was just another field in the underlying database.
> > > > >
> > > > > I have reformatted the following to avoid odd line breaks, but the
> > > > > SQLstatement is exactly the same as you posted, except that the[/color]
> > clauses[color=darkred]
> > > > have
> > > > > been rearranged as required:
> > > > >
> > > > > $sql = "SELECT s.suppliername, s.address1, s.address2, s.city, ";
> > > > > $sql .= "s.state,s. zip, s.phone, l.latitude, l.longitude ";
> > > > > $sql .= "(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) ";
> > > > > $sql .= "*SIN(RADIANS($ latitude))+COS( RADIANS(l.latit ude))";
> > > > > $sql .=
> > > > "*COS(RADIANS($ latitude))*COS( RADIANS(l.longi tude-$longitude))))) ";
> > > > > $sql .= " AS distance ";
> > > > > $sql .= "FROM Suppliers s ";
> > > > > $sql .= "INNER JOIN Locations l ON l.zipcode = s.zip ";
> > > > > $sql .= "WHERE l.latitude <= ".$highLatitude ." ";
> > > > > $sql .= "AND l.latitude >= ".$lowLatitude. " ";
> > > > > $sql .= "AND l.longitude >= ".$lowLongitude ." ";
> > > > > $sql .= "AND l.longitude <= ".$highLongitud e." ";
> > > > > $sql .= "ORDER BY distance";
> > > > >
> > > > > HTH
> > > > > Doug
> > > > >
> > > > >
> > > >
> > > >
> > > "Xenophobe" <xenophobe@plan etx.com> wrote in message
> > > news:a%gWb.2687 22$I06.2878665@ attbi_s01...
> > > > Doug,
> > > >
> > > > I cut & pasted the query, but alas it still returns the same error[/color][/color][/color]
as[color=blue][color=green][color=darkred]
> > > > before.
> > > >
> > > > Perhaps it's the MySQL version. I'm currently running 3.23.29a-gamma[/color][/color]
> on[color=green][color=darkred]
> > > > Linux.
> > > >
> > > > I don't have any problems with a variety of other queries, although[/color][/color]
> most[color=green][color=darkred]
> > > do
> > > > not have the math.
> > > >
> > > > Here's an example of a populated query:
> > > >
> > > > SELECT DISTINCT s.suppliername, s.address1, s.address2, s.city,[/color][/color]
> s.state,[color=green][color=darkred]
> > > > s.zip, s.phone, l.latitude, l.longitude
> > > >
> > >[/color]
> >[/color]
>[/color]
(69.09*DEGREES( ACOS(SIN(RADIAN S(l.latitude))* SIN(RADIANS(41. 386533))+COS(RA D[color=blue][color=green][color=darkred]
> > > >
> > >[/color]
> >[/color]
>[/color]
IANS(l.latitude ))*COS(RADIANS( 41.386533))*COS (RADIANS(l.long itude--82.234199[color=blue][color=green][color=darkred]
> > > > ))))) AS distance FROM Suppliers s INNER JOIN Locations l ON[/color][/color][/color]
l.zipcode[color=blue]
> =[color=green][color=darkred]
> > > > s.zip WHERE l.latitude <= 41.531365126399 AND l.latitude >=
> > > 41.241700873601
> > > > AND l.longitude >= -82.427239911609 AND l.longitude[/color][/color]
> <= -82.041158088391[color=green][color=darkred]
> > > > ORDER BY distance
> > > >
> > > >
> > > > This generates the following error:
> > > >
> > > > Supplied argument is not a valid MySQL result resource
> > > >
> > > >
> > > > I would love to find a solution for this, as it's holding up the
> > > completion
> > > > of the project.
> > > >
> > > > Thanks.
> > > >
> > >
> > > Xenophobe,
> > > Hmmmm....must get the upgrade to my finger driver....!
> > > We are missing a comma between " l.longitude" and "(69.09..."
> > >
> > > The corrected lines are:
> > >
> > > <snip>
> > > $sql .= "s.state,s. zip, s.phone, l.latitude, l.longitude, ";
> > > $sql .= "(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) ";
> > > <snip>
> > >
> > > Of course, I will claim I deliberately omitted the comma to test your[/color][/color]
> SQL[color=green][color=darkred]
> > > skills ...<grin>
> > > Cheers,
> > > Doug
> > >
> > > --
> > > Remove the blots from my address to reply
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

**Doug Hutcheson** · Jul 17 '05, 03:19 AM

Re: ORDER BY Zip Code Distance

Xenophobe,
Glad it runs, but I'm not the really clever one who gave you the algorithm,
so
I can't help much with that side of things.

In general, I would suggest you try:

1. Writing the algorithm as a tiny executable in something which executes
fast: eg C and call it from your script, to see if that works faster. I
expect it will not be any better, because of the overhead of context
switching from PHP to shell and back.

2. Buy, beg, borrow or steal appropriate code from a geographic information
system. I did a Google on "php gis" and came up with oodles of hits, so that
might be profitable to pursue.

3. Offload the calculation thread to a Cray II or better <grin>.

4. Tell your user that "this might take a while..." when they kick it off
and run the process as an asynchronous thread, which writes its results to a
file. Give the user a link to a page which will tell him when his results
are available and, in turn, link him to them when they are ready.

The problem is you are asking for a complex triginometric calculation to be
performed on every row you select from the database. There is just no easy
way around the issue, as long as you have an arbitrary geographic coordinate
to start from and look for results within an arbitrary radius.

Don't forget that the select statement has to run over ALL your rows which
fall inside the bounding box defined by your high/low Lat/Long values, in
order to decide whether they meet the radius condition. You might try
reducing the bounding box in order to reduce the number of records selected,
but that might defeat your object.

HTH
Doug

--
Remove the blots from my address to reply
"Xenophobe" <xenophobe@plan etx.com> wrote in message
news:cQVWb.2975 27$xy6.1461753@ attbi_s02...[color=blue]
> Doug,
>
> This is just a quick follow-up. The zip sort by distance works great
> except... the performance is pokey for searches of 20+ miles. I could[/color]
simply[color=blue]
> sort the results by company name (ditching the beautiful syntax you help[/color]
me[color=blue]
> with!), but even this won't save enough cycles for broader searches.
>
> The options (in my view) are either break out the results in multiple[/color]
pages[color=blue]
> or pre-calculate every possible lat and long and store them in the[/color]
database[color=blue]
> (creating one huge table!) Can you think of anything that might help?
>
> Thanks again for all your help!
>
> p.s. the handle "Xenophobe" comes from the arcade game, specifically the
> excellent Atari Lynx port. I still link several systems together and play
> from time to time. :-)
>
> "Doug Hutcheson" <doug.blot.hutc heson@nrm.blot. qld.blot.gov.bl ot.au> wrote
> in message news:WSxWb.632$ KW.38860@news.o ptus.net.au...[color=green]
> > Xenophobe,
> >
> > Glad to help. Just proves how easily we can get engrossed in the[/color]
> interesting[color=green]
> > bits of complexity and overlook simple things like punctuation.
> >
> > Anyway, perhaps all this help from around the globe will go some way[/color]
> toward[color=green]
> > curing your xenophobia.
> > <grin>
> >
> > Cheers,
> > Doug (Brisbane, Australia)
> > --
> > Remove the blots from my address to reply
> > "Xenophobe" <xenophobe@plan etx.com> wrote in message
> > news:QHsWb.2728 19$I06.2925114@ attbi_s01...[color=darkred]
> > > Doug,
> > >
> > > I missed that and failed the test. The last item in the SELECT clause
> > > doesn't need a comma, but "l.longitud e" isn't the last item with the
> > > addition of "distance". <excuse>I've been pounding my head for too[/color][/color][/color]
long[color=blue][color=green][color=darkred]
> > > trying to resolve this problem.</excuse> In addition to learning some[/color]
> > handy[color=darkred]
> > > SQL syntax, it's also reminded me that things are not always what they
> > > appear to be.
> > >
> > > ANWAY, I sincerely appreciate your assistance--thanks!
> > >
> > > "Doug Hutcheson" <doug.blot.hutc heson@nrm.blot. qld.blot.gov.bl ot.au>[/color][/color]
> wrote[color=green][color=darkred]
> > > in message news:kciWb.612$ KW.37661@news.o ptus.net.au...
> > > > Top posting corrected...see below
> > > > > "Doug Hutcheson"[/color][/color][/color]
<doug.blot.hutc heson@nrm.blot. qld.blot.gov.bl ot.au>[color=blue][color=green][color=darkred]
> > > wrote
> > > > > in message news:DDDVb.512$ KW.33452@news.o ptus.net.au...
> > > > > > --
> > > > > > Remove the blots from my address to reply
> > > > > > "Xenophobe" <xenophobe@plan etx.com> wrote in message
> > > > > > news:9cDVb.2580 63$na.418618@at tbi_s04...
> > > > > > > Tom, thanks for your response.
> > > > > > >
> > > > > > > I modified my query and added the lines you provided as[/color]
> > illustrated[color=darkred]
> > > in
> > > > > > your
> > > > > > > example.
> > > > > > >
> > > > > > > $sql = "SELECT s.suppliername, s.address1, s.address2, s.city,
> > > > s.state,
> > > > > > > s.zip, s.phone, l.latitude, l.longitude ";
> > > > > > > $sql .= "FROM Suppliers s ";
> > > > > > > $sql .= "INNER JOIN Locations l ON l.zipcode = s.zip ";
> > > > > > > $sql .= "WHERE l.latitude <= ".$highLatitude ." ";
> > > > > > > $sql .= "AND l.latitude >= ".$lowLatitude. " ";
> > > > > > > $sql .= "AND l.longitude >= ".$lowLongitude ." ";
> > > > > > > $sql .= "AND l.longitude <= ".$highLongitud e." ";
> > > > > > > $sql .=
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >[/color]
> >[/color]
>[/color]
"(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) *SIN(RADIANS($l atitude))+COS(R A[color=blue][color=green][color=darkred]
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >[/color]
> >[/color]
>[/color]
DIANS(l.latitud e))*COS(RADIANS ($latitude))*CO S(RADIANS(l.lon gitude-$longitud[color=blue][color=green][color=darkred]
> > > > > > > e))))) AS distance ";
> > > > > > > $sql .= "ORDER BY distance";
> > > > > > >
> > > > > > > I get the following error message:
> > > > > > >
> > > > > > > Warning: Supplied argument is not a valid MySQL result[/color][/color][/color]
resource[color=blue][color=green][color=darkred]
> > > > > > >
> > > > > > > Commenting the last two lines makes the query servicable[/color][/color][/color]
again.[color=blue][color=green][color=darkred]
> > > > > > >
> > > > > > > Any thoughts?
> > > > > > >
> > > > > > > "Tom Thackrey" <use.signature@ nospam.com> wrote in message
> > > > > > > news:CWAVb.2283 2$VW4.3380@news svr25.news.prod igy.com...
> > > > > > > >
> > > > > > > > On 8-Feb-2004, "Xenophobe" <xenophobe@plan etx.com> wrote:
> > > > > > > >
> > > > > > > > > As described the high and low latitudes and longitudes are
> > > > > > > pre-calculated
> > > > > > > > > and passed to the query.
> > > > > > > > >
> > > > > > > > > SELECT *
> > > > > > > > > FROM Locations
> > > > > > > > > WHERE Latitude <= $HighLatitude
> > > > > > > > > AND Latitude >= $LowLatitude
> > > > > > > > > AND Longitude >= $LowLongitude
> > > > > > > > > AND Longitude <= $HighLongitude
> > > > > > > > >
> > > > > > > > > I then calculate the actual distance using the latitude[/color][/color][/color]
and[color=blue][color=green][color=darkred]
> > > > > longitude
> > > > > > > for
> > > > > > > > > each zip returned.
> > > > > > > > >
> > > > > > > > > This works great and saves lots of cycles. The downside to[/color]
> > this[color=darkred]
> > > > > > > technique
> > > > > > > > > is
> > > > > > > > > the inability to sort by distance. Can someone suggest a[/color]
> > simple[color=darkred]
> > > > way
> > > > > to
> > > > > > > > > calculate and ORDER BY distance within the query?
> > > > > > > >
> > > > > > > > Put the following in your SQL ($lat and $long are the zero
> > > distance
> > > > > > point)
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >[/color]
> >[/color]
>[/color]
(69.09*DEGREES( ACOS(SIN(RADIAN S(Latitude))*SI N(RADIANS($lat) )+COS(RADIANS(L a[color=blue][color=green][color=darkred]
> > > > > > > titude))*
> > > > > > > > COS(RADIANS($la t))*COS(RADIANS (Longitude-$long))))) AS[/color][/color]
> Distance[color=green][color=darkred]
> > > > > > > >
> > > > > > > > will create the Distance 'column'
> > > > > > > >
> > > > > > > > ORDER BY Distance
> > > > > > > >
> > > > > > > > --
> > > > > > > > Tom Thackrey
> > > > > > > > www.creative-light.com
> > > > > > > > tom (at) creative (dash) light (dot) com
> > > > > > > > do NOT send email to jamesbutler@wil lglen.net (it's reserved[/color][/color]
> for[color=green][color=darkred]
> > > > > > spammers)
> > > > > > >
> > > > > > >
> > > > > >
> > > > > > Xenophobe,
> > > > > >
> > > > > > You need to put the calculated value selection in the 'SELECT'[/color]
> > clause,[color=darkred]
> > > > not
> > > > > > the 'WHERE' clause. What you are trying to do is 'select' a[/color]
> > calculated[color=darkred]
> > > > > value
> > > > > > as if it was just another field in the underlying database.
> > > > > >
> > > > > > I have reformatted the following to avoid odd line breaks, but[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > > > SQLstatement is exactly the same as you posted, except that the
> > > clauses
> > > > > have
> > > > > > been rearranged as required:
> > > > > >
> > > > > > $sql = "SELECT s.suppliername, s.address1, s.address2, s.city,[/color][/color][/color]
";[color=blue][color=green][color=darkred]
> > > > > > $sql .= "s.state,s. zip, s.phone, l.latitude, l.longitude ";
> > > > > > $sql .= "(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) ";
> > > > > > $sql .= "*SIN(RADIANS($ latitude))+COS( RADIANS(l.latit ude))";
> > > > > > $sql .=
> > > > >[/color][/color][/color]
"*COS(RADIANS($ latitude))*COS( RADIANS(l.longi tude-$longitude))))) ";[color=blue][color=green][color=darkred]
> > > > > > $sql .= " AS distance ";
> > > > > > $sql .= "FROM Suppliers s ";
> > > > > > $sql .= "INNER JOIN Locations l ON l.zipcode = s.zip ";
> > > > > > $sql .= "WHERE l.latitude <= ".$highLatitude ." ";
> > > > > > $sql .= "AND l.latitude >= ".$lowLatitude. " ";
> > > > > > $sql .= "AND l.longitude >= ".$lowLongitude ." ";
> > > > > > $sql .= "AND l.longitude <= ".$highLongitud e." ";
> > > > > > $sql .= "ORDER BY distance";
> > > > > >
> > > > > > HTH
> > > > > > Doug
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > "Xenophobe" <xenophobe@plan etx.com> wrote in message
> > > > news:a%gWb.2687 22$I06.2878665@ attbi_s01...
> > > > > Doug,
> > > > >
> > > > > I cut & pasted the query, but alas it still returns the same error[/color][/color]
> as[color=green][color=darkred]
> > > > > before.
> > > > >
> > > > > Perhaps it's the MySQL version. I'm currently running[/color][/color][/color]
3.23.29a-gamma[color=blue][color=green]
> > on[color=darkred]
> > > > > Linux.
> > > > >
> > > > > I don't have any problems with a variety of other queries,[/color][/color][/color]
although[color=blue][color=green]
> > most[color=darkred]
> > > > do
> > > > > not have the math.
> > > > >
> > > > > Here's an example of a populated query:
> > > > >
> > > > > SELECT DISTINCT s.suppliername, s.address1, s.address2, s.city,[/color]
> > s.state,[color=darkred]
> > > > > s.zip, s.phone, l.latitude, l.longitude
> > > > >
> > > >
> > >[/color]
> >[/color]
>[/color]
(69.09*DEGREES( ACOS(SIN(RADIAN S(l.latitude))* SIN(RADIANS(41. 386533))+COS(RA D[color=blue][color=green][color=darkred]
> > > > >
> > > >
> > >[/color]
> >[/color]
>[/color]
IANS(l.latitude ))*COS(RADIANS( 41.386533))*COS (RADIANS(l.long itude--82.234199[color=blue][color=green][color=darkred]
> > > > > ))))) AS distance FROM Suppliers s INNER JOIN Locations l ON[/color][/color]
> l.zipcode[color=green]
> > =[color=darkred]
> > > > > s.zip WHERE l.latitude <= 41.531365126399 AND l.latitude >=
> > > > 41.241700873601
> > > > > AND l.longitude >= -82.427239911609 AND l.longitude[/color]
> > <= -82.041158088391[color=darkred]
> > > > > ORDER BY distance
> > > > >
> > > > >
> > > > > This generates the following error:
> > > > >
> > > > > Supplied argument is not a valid MySQL result resource
> > > > >
> > > > >
> > > > > I would love to find a solution for this, as it's holding up the
> > > > completion
> > > > > of the project.
> > > > >
> > > > > Thanks.
> > > > >
> > > >
> > > > Xenophobe,
> > > > Hmmmm....must get the upgrade to my finger driver....!
> > > > We are missing a comma between " l.longitude" and "(69.09..."
> > > >
> > > > The corrected lines are:
> > > >
> > > > <snip>
> > > > $sql .= "s.state,s. zip, s.phone, l.latitude, l.longitude, ";
> > > > $sql .= "(69.09*DEGREES (ACOS(SIN(RADIA NS(l.latitude)) ";
> > > > <snip>
> > > >
> > > > Of course, I will claim I deliberately omitted the comma to test[/color][/color][/color]
your[color=blue][color=green]
> > SQL[color=darkred]
> > > > skills ...<grin>
> > > > Cheers,
> > > > Doug
> > > >
> > > > --
> > > > Remove the blots from my address to reply
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

**Jochen Daum** · Jul 17 '05, 03:19 AM

Re: ORDER BY Zip Code Distance

Hi!

On Fri, 13 Feb 2004 01:34:00 GMT, "Xenophobe" <xenophobe@plan etx.com>
wrote:
[color=blue]
>Doug,
>
>This is just a quick follow-up. The zip sort by distance works great
>except... the performance is pokey for searches of 20+ miles. I could simply
>sort the results by company name (ditching the beautiful syntax you help me
>with!), but even this won't save enough cycles for broader searches.
>
>The options (in my view) are either break out the results in multiple pages
>or pre-calculate every possible lat and long and store them in the database
>(creating one huge table!) Can you think of anything that might help?[/color]

Well, depends what you think is huge. It might still perform very well
with proper indexing. How many rows are we talking?

Eg. half the world fits in around 4 GB of data for going down to
minutes, without even thinking about a good encoding for your
latitude/ longitude index. So this would mean around 4 disk accesses,
after that you read payload data.

Of course I expect you are measuring only one country, so you'll have
much less data.

HTH, Jochen
--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.

PHP DB Edit Toolkit

http://sourceforge.net/projects/phpdbedittk/

Download PHP DB Edit Toolkit for free. PHP DB Edit Toolkit is a set of PHP classes makes the generation of database edit interfaces easier and faster. The main class builds tabular and form views based on a data dictionary and takes over handling of insert/update/delete and user input.

**Jochen Daum** · Jul 17 '05, 03:19 AM

Re: ORDER BY Zip Code Distance

Hi!
[color=blue][color=green]
>>
>>This is just a quick follow-up. The zip sort by distance works great
>>except... the performance is pokey for searches of 20+ miles. I could simply
>>sort the results by company name (ditching the beautiful syntax you help me
>>with!), but even this won't save enough cycles for broader searches.
>>
>>The options (in my view) are either break out the results in multiple pages
>>or pre-calculate every possible lat and long and store them in the database
>>(creating one huge table!) Can you think of anything that might help?[/color]
>
>Well, depends what you think is huge. It might still perform very well
>with proper indexing. How many rows are we talking?
>
>Eg. half the world fits in around 4 GB,[/color]
sorry 5 GB, but doesn't makea difference in this range.

Jochen
--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.

PHP DB Edit Toolkit

http://sourceforge.net/projects/phpdbedittk/

Download PHP DB Edit Toolkit for free. PHP DB Edit Toolkit is a set of PHP classes makes the generation of database edit interfaces easier and faster. The main class builds tabular and form views based on a data dictionary and takes over handling of insert/update/delete and user input.

**Doug Hutcheson** · Jul 17 '05, 03:19 AM

Re: ORDER BY Zip Code Distance

Jochen,
That is interesting. Can you give more details? As I read the op, the
problem was to find how many points from his database lay within x miles of
given point y. I am not a GIS expert by any menas, but I can't see how to
avoid doing the trig. each time? What am I missing? I love to learn new
stuff like this.
Cheers,
Doug

--
Remove the blots from my address to reply
"Jochen Daum" <jochen.daum@ca ns.co.nz> wrote in message
news:e0do20dua5 usfm9iu0p258phn 1kvfoi7q5@4ax.c om...[color=blue]
> Hi!
>[color=green][color=darkred]
> >>
> >>This is just a quick follow-up. The zip sort by distance works great
> >>except... the performance is pokey for searches of 20+ miles. I could[/color][/color][/color]
simply[color=blue][color=green][color=darkred]
> >>sort the results by company name (ditching the beautiful syntax you help[/color][/color][/color]
me[color=blue][color=green][color=darkred]
> >>with!), but even this won't save enough cycles for broader searches.
> >>
> >>The options (in my view) are either break out the results in multiple[/color][/color][/color]
pages[color=blue][color=green][color=darkred]
> >>or pre-calculate every possible lat and long and store them in the[/color][/color][/color]
database[color=blue][color=green][color=darkred]
> >>(creating one huge table!) Can you think of anything that might help?[/color]
> >
> >Well, depends what you think is huge. It might still perform very well
> >with proper indexing. How many rows are we talking?
> >
> >Eg. half the world fits in around 4 GB,[/color]
> sorry 5 GB, but doesn't makea difference in this range.
>
> Jochen
> --
> Jochen Daum - Cabletalk Group Ltd.
> PHP DB Edit Toolkit -- PHP scripts for building
> database editing interfaces.
> http://sourceforge.net/projects/phpdbedittk/[/color]

**Jochen Daum** · Jul 17 '05, 03:19 AM

Re: ORDER BY Zip Code Distance

Hi!

On Fri, 13 Feb 2004 03:32:45 GMT, "Doug Hutcheson"
<doug.blot.hutc heson@nrm.blot. qld.blot.gov.bl ot.au> wrote:
[color=blue]
>Jochen,
>That is interesting. Can you give more details? As I read the op, the
>problem was to find how many points from his database lay within x miles of
>given point y. I am not a GIS expert by any menas, but I can't see how to
>avoid doing the trig. each time? What am I missing? I love to learn new
>stuff like this.[/color]

Made a calculation mistake there, but it still goes with less area and
accuracy:

Store a table with fields

srclong tinyint
srclongmin tinyint
srclat tinyint
srclatmin tinyint
distance float
trglong tinyint
trglongmin tinyint
trglat tinyint
trglatmin tinyint

with a clustered index on the first 5 fields.

Then save all possible combinations with their distance into the
database. A calculation example:

All of US is less than 30 by 60 degrees latitude/longitude (sorry if I
mix it up). If we store 0 and 30 minutes positions only, thats

((30*2*60*2)^2) *20 bytes = 1036800000 bytes

Thats for 7200 locations. Good thing is, it performs always the same
for the same number of locations, no matter what actual value you
store for minutes. The other performance factor is only the number of
records returned, but you would want to limit that anyway for a web
page.

HTH, Jochen

[color=blue]
>Cheers,
>Doug[/color]

--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.

PHP DB Edit Toolkit

http://sourceforge.net/projects/phpdbedittk/

Download PHP DB Edit Toolkit for free. PHP DB Edit Toolkit is a set of PHP classes makes the generation of database edit interfaces easier and faster. The main class builds tabular and form views based on a data dictionary and takes over handling of insert/update/delete and user input.