ORDER BY Zip Code Distance

[Xenophobe]

I have successfully converted the ASP code included in the following article
to PHP:

http://www.4guysfromrolla.com/webtech/040100-1.shtml

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?

Thanks!

[Tom Thackrey]

On 8-Feb-2004, "Xenophobe" <xe*******@planetx.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 this technique
is
the inability to sort by distance. Can someone suggest a simple way to
calculate and ORDER BY distance within the query?

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

(69.09*DEGREES(ACOS(SIN(RADIANS(Latitude))*SIN(RAD IANS($lat))+COS(RADIANS(Latitude))*
COS(RADIANS($lat))*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 ja*********@willglen.net (it's reserved for spammers)

[Xenophobe]

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 <= ".$highLongitude." ";
$sql .=
"(69.09*DEGREES(ACOS(SIN(RADIANS(l.latitude))*SIN( RADIANS($latitude))+COS(RA
DIANS(l.latitude))*COS(RADIANS($latitude))*COS(RAD IANS(l.longitude-$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" <us***********@nospam.com> wrote in message
news:CW******************@newssvr25.news.prodigy.c om...

On 8-Feb-2004, "Xenophobe" <xe*******@planetx.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 this technique is
the inability to sort by distance. Can someone suggest a simple way to
calculate and ORDER BY distance within the query?
Put the following in your SQL ($lat and $long are the zero distance point)

(69.09*DEGREES(ACOS(SIN(RADIANS(Latitude))*SIN(RAD IANS($lat))+COS(RADIANS(La
titude))* COS(RADIANS($lat))*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 ja*********@willglen.net (it's reserved for spammers)

[Doug Hutcheson]

--
Remove the blots from my address to reply
"Xenophobe" <xe*******@planetx.com> wrote in message
news:9cDVb.258063$na.418618@attbi_s04...

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 <= ".$highLongitude." ";
$sql .=
"(69.09*DEGREES(ACOS(SIN(RADIANS(l.latitude))*SIN( RADIANS($latitude))+COS(RA DIANS(l.latitude))*COS(RADIANS($latitude))*COS(RAD IANS(l.longitude-$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" <us***********@nospam.com> wrote in message
news:CW******************@newssvr25.news.prodigy.c om...

On 8-Feb-2004, "Xenophobe" <xe*******@planetx.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 this technique is
the inability to sort by distance. Can someone suggest a simple way to
calculate and ORDER BY distance within the query?
Put the following in your SQL ($lat and $long are the zero distance point)

(69.09*DEGREES(ACOS(SIN(RADIANS(Latitude))*SIN(RAD IANS($lat))+COS(RADIANS(La titude))*

COS(RADIANS($lat))*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 ja*********@willglen.net (it's reserved for

spammers)

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(RADIANS(l.latitude))" ;
$sql .= "*SIN(RADIANS($latitude))+COS(RADIANS(l.latitude)) ";
$sql .= "*COS(RADIANS($latitude))*COS(RADIANS(l.longit ude-$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 <= ".$highLongitude." ";
$sql .= "ORDER BY distance";

HTH
Doug

[Xenophobe]

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" <do*****************@nrm.blot.qld.blot.gov.blot.au > wrote
in message news:DD****************@news.optus.net.au...

--
Remove the blots from my address to reply
"Xenophobe" <xe*******@planetx.com> wrote in message
news:9cDVb.258063$na.418618@attbi_s04...

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 <= ".$highLongitude." ";
$sql .=

"(69.09*DEGREES(ACOS(SIN(RADIANS(l.latitude))*SIN( RADIANS($latitude))+COS(RA

DIANS(l.latitude))*COS(RADIANS($latitude))*COS(RAD IANS(l.longitude-$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" <us***********@nospam.com> wrote in message
news:CW******************@newssvr25.news.prodigy.c om...

On 8-Feb-2004, "Xenophobe" <xe*******@planetx.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 this

technique

> is
> the inability to sort by distance. Can someone suggest a simple way

to > calculate and ORDER BY distance within the query?

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

(69.09*DEGREES(ACOS(SIN(RADIANS(Latitude))*SIN(RAD IANS($lat))+COS(RADIANS(La titude))*

COS(RADIANS($lat))*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 ja*********@willglen.net (it's reserved for

spammers)

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(RADIANS(l.latitude))" ;
$sql .= "*SIN(RADIANS($latitude))+COS(RADIANS(l.latitude)) ";
$sql .= "*COS(RADIANS($latitude))*COS(RADIANS(l.longit ude-$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 <= ".$highLongitude." ";
$sql .= "ORDER BY distance";

HTH
Doug

[Xenophobe]

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(RADIANS(l.latitude))*SIN(R ADIANS(41.386533))+COS(RAD
IANS(l.latitude))*COS(RADIANS(41.386533))*COS(RADI ANS(l.longitude--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" <do*****************@nrm.blot.qld.blot.gov.blot.au > wrote
in message news:DD****************@news.optus.net.au...

--
Remove the blots from my address to reply
"Xenophobe" <xe*******@planetx.com> wrote in message
news:9cDVb.258063$na.418618@attbi_s04...

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 <= ".$highLongitude." ";
$sql .=

"(69.09*DEGREES(ACOS(SIN(RADIANS(l.latitude))*SIN( RADIANS($latitude))+COS(RA

DIANS(l.latitude))*COS(RADIANS($latitude))*COS(RAD IANS(l.longitude-$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" <us***********@nospam.com> wrote in message
news:CW******************@newssvr25.news.prodigy.c om...

On 8-Feb-2004, "Xenophobe" <xe*******@planetx.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 this

technique

> is
> the inability to sort by distance. Can someone suggest a simple way

to > calculate and ORDER BY distance within the query?

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

(69.09*DEGREES(ACOS(SIN(RADIANS(Latitude))*SIN(RAD IANS($lat))+COS(RADIANS(La titude))*

COS(RADIANS($lat))*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 ja*********@willglen.net (it's reserved for

spammers)

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(RADIANS(l.latitude))" ;
$sql .= "*SIN(RADIANS($latitude))+COS(RADIANS(l.latitude)) ";
$sql .= "*COS(RADIANS($latitude))*COS(RADIANS(l.longit ude-$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 <= ".$highLongitude." ";
$sql .= "ORDER BY distance";

HTH
Doug

[Doug Hutcheson]

Top posting corrected...see below

"Doug Hutcheson" <do*****************@nrm.blot.qld.blot.gov.blot.au > wrote
in message news:DD****************@news.optus.net.au...

--
Remove the blots from my address to reply
"Xenophobe" <xe*******@planetx.com> wrote in message
news:9cDVb.258063$na.418618@attbi_s04...

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 <= ".$highLongitude." ";
$sql .=

"(69.09*DEGREES(ACOS(SIN(RADIANS(l.latitude))*SIN( RADIANS($latitude))+COS(RA

DIANS(l.latitude))*COS(RADIANS($latitude))*COS(RAD IANS(l.longitude-$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" <us***********@nospam.com> wrote in message
news:CW******************@newssvr25.news.prodigy.c om...
>
> On 8-Feb-2004, "Xenophobe" <xe*******@planetx.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 this
technique
> > is
> > the inability to sort by distance. Can someone suggest a simple way to

> > calculate and ORDER BY distance within the query?
>
> Put the following in your SQL ($lat and $long are the zero distance point)

>
>

(69.09*DEGREES(ACOS(SIN(RADIANS(Latitude))*SIN(RAD IANS($lat))+COS(RADIANS(La titude))*
> COS(RADIANS($lat))*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 ja*********@willglen.net (it's reserved for

spammers)

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(RADIANS(l.latitude))" ;
$sql .= "*SIN(RADIANS($latitude))+COS(RADIANS(l.latitude)) ";
$sql .=

"*COS(RADIANS($latitude))*COS(RADIANS(l.longit ude-$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 <= ".$highLongitude." ";
$sql .= "ORDER BY distance";

HTH
Doug

"Xenophobe" <xe*******@planetx.com> wrote in message
news:a%gWb.268722$I06.2878665@attbi_s01... 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(RADIANS(l.latitude))*SIN(R ADIANS(41.386533))+COS(RAD IANS(l.latitude))*COS(RADIANS(41.386533))*COS(RADI ANS(l.longitude--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.

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(RADIANS(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]

Doug,

I missed that and failed the test. The last item in the SELECT clause
doesn't need a comma, but "l.longitude" 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" <do*****************@nrm.blot.qld.blot.gov.blot.au > wrote
in message news:kc****************@news.optus.net.au...

Top posting corrected...see below

"Doug Hutcheson" <do*****************@nrm.blot.qld.blot.gov.blot.au > wrote

in message news:DD****************@news.optus.net.au...

--
Remove the blots from my address to reply
"Xenophobe" <xe*******@planetx.com> wrote in message
news:9cDVb.258063$na.418618@attbi_s04...
> 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 <= ".$highLongitude." ";
> $sql .=
>

"(69.09*DEGREES(ACOS(SIN(RADIANS(l.latitude))*SIN( RADIANS($latitude))+COS(RA

>

DIANS(l.latitude))*COS(RADIANS($latitude))*COS(RAD IANS(l.longitude-$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" <us***********@nospam.com> wrote in message
> news:CW******************@newssvr25.news.prodigy.c om...
> >
> > On 8-Feb-2004, "Xenophobe" <xe*******@planetx.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 this
> technique
> > > is
> > > the inability to sort by distance. Can someone suggest a simple way

to

> > > calculate and ORDER BY distance within the query?
> >
> > Put the following in your SQL ($lat and $long are the zero distance point)
> >
> >
>

(69.09*DEGREES(ACOS(SIN(RADIANS(Latitude))*SIN(RAD IANS($lat))+COS(RADIANS(La

> titude))*
> > COS(RADIANS($lat))*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 ja*********@willglen.net (it's reserved for
spammers)
>
>

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(RADIANS(l.latitude))" ;
$sql .= "*SIN(RADIANS($latitude))+COS(RADIANS(l.latitude)) ";
$sql .=

"*COS(RADIANS($latitude))*COS(RADIANS(l.longit ude-$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 <= ".$highLongitude." ";
$sql .= "ORDER BY distance";

HTH
Doug

"Xenophobe" <xe*******@planetx.com> wrote in message
news:a%gWb.268722$I06.2878665@attbi_s01...

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(RADIANS(l.latitude))*SIN(R ADIANS(41.386533))+COS(RAD

IANS(l.latitude))*COS(RADIANS(41.386533))*COS(RADI ANS(l.longitude--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.

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(RADIANS(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

[Doug Hutcheson]

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" <xe*******@planetx.com> wrote in message
news:QHsWb.272819$I06.2925114@attbi_s01...

Doug,

I missed that and failed the test. The last item in the SELECT clause
doesn't need a comma, but "l.longitude" 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" <do*****************@nrm.blot.qld.blot.gov.blot.au > wrote
in message news:kc****************@news.optus.net.au...

Top posting corrected...see below

"Doug Hutcheson" <do*****************@nrm.blot.qld.blot.gov.blot.au > wrote in message news:DD****************@news.optus.net.au...
> --
> Remove the blots from my address to reply
> "Xenophobe" <xe*******@planetx.com> wrote in message
> news:9cDVb.258063$na.418618@attbi_s04...
> > 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 <= ".$highLongitude." ";
> > $sql .=
> >
>

"(69.09*DEGREES(ACOS(SIN(RADIANS(l.latitude))*SIN( RADIANS($latitude))+COS(RA > >
>

DIANS(l.latitude))*COS(RADIANS($latitude))*COS(RAD IANS(l.longitude-$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" <us***********@nospam.com> wrote in message
> > news:CW******************@newssvr25.news.prodigy.c om...
> > >
> > > On 8-Feb-2004, "Xenophobe" <xe*******@planetx.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 this > > technique
> > > > is
> > > > the inability to sort by distance. Can someone suggest a simple

way

to
> > > > calculate and ORDER BY distance within the query?
> > >
> > > Put the following in your SQL ($lat and $long are the zero distance > point)
> > >
> > >
> >
>

(69.09*DEGREES(ACOS(SIN(RADIANS(Latitude))*SIN(RAD IANS($lat))+COS(RADIANS(La > > titude))*
> > > COS(RADIANS($lat))*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 ja*********@willglen.net (it's reserved for
> spammers)
> >
> >
>
> 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(RADIANS(l.latitude))" ;
> $sql .= "*SIN(RADIANS($latitude))+COS(RADIANS(l.latitude)) ";
> $sql .=
"*COS(RADIANS($latitude))*COS(RADIANS(l.longit ude-$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 <= ".$highLongitude." ";
> $sql .= "ORDER BY distance";
>
> HTH
> Doug
>
>

"Xenophobe" <xe*******@planetx.com> wrote in message
news:a%gWb.268722$I06.2878665@attbi_s01...

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(RADIANS(l.latitude))*SIN(R ADIANS(41.386533))+COS(RAD

IANS(l.latitude))*COS(RADIANS(41.386533))*COS(RADI ANS(l.longitude--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.

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(RADIANS(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]

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" <do*****************@nrm.blot.qld.blot.gov.blot.au > wrote
in message news:WS****************@news.optus.net.au...

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" <xe*******@planetx.com> wrote in message
news:QHsWb.272819$I06.2925114@attbi_s01...

Doug,

I missed that and failed the test. The last item in the SELECT clause
doesn't need a comma, but "l.longitude" 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" <do*****************@nrm.blot.qld.blot.gov.blot.au > wrote

in message news:kc****************@news.optus.net.au...

Top posting corrected...see below
> "Doug Hutcheson" <do*****************@nrm.blot.qld.blot.gov.blot.au >

wrote

> in message news:DD****************@news.optus.net.au...
> > --
> > Remove the blots from my address to reply
> > "Xenophobe" <xe*******@planetx.com> wrote in message
> > news:9cDVb.258063$na.418618@attbi_s04...
> > > 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 <= ".$highLongitude." ";
> > > $sql .=
> > >
> >
>

"(69.09*DEGREES(ACOS(SIN(RADIANS(l.latitude))*SIN( RADIANS($latitude))+COS(RA

> > >
> >
>

DIANS(l.latitude))*COS(RADIANS($latitude))*COS(RAD IANS(l.longitude-$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" <us***********@nospam.com> wrote in message
> > > news:CW******************@newssvr25.news.prodigy.c om...
> > > >
> > > > On 8-Feb-2004, "Xenophobe" <xe*******@planetx.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 this > > > technique
> > > > > is
> > > > > the inability to sort by distance. Can someone suggest a simple way
> to
> > > > > calculate and ORDER BY distance within the query?
> > > >
> > > > Put the following in your SQL ($lat and $long are the zero

distance

> > point)
> > > >
> > > >
> > >
> >
>

(69.09*DEGREES(ACOS(SIN(RADIANS(Latitude))*SIN(RAD IANS($lat))+COS(RADIANS(La

> > > titude))*
> > > > COS(RADIANS($lat))*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 ja*********@willglen.net (it's reserved for > > spammers)
> > >
> > >
> >
> > 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(RADIANS(l.latitude))" ;
> > $sql .= "*SIN(RADIANS($latitude))+COS(RADIANS(l.latitude)) ";
> > $sql .=
> "*COS(RADIANS($latitude))*COS(RADIANS(l.longit ude-$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 <= ".$highLongitude." ";
> > $sql .= "ORDER BY distance";
> >
> > HTH
> > Doug
> >
> >
>
>
"Xenophobe" <xe*******@planetx.com> wrote in message
news:a%gWb.268722$I06.2878665@attbi_s01...
> 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(RADIANS(l.latitude))*SIN(R ADIANS(41.386533))+COS(RAD

>

IANS(l.latitude))*COS(RADIANS(41.386533))*COS(RADI ANS(l.longitude--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.
>

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(RADIANS(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

[Doug Hutcheson]

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" <xe*******@planetx.com> wrote in message
news:cQVWb.297527$xy6.1461753@attbi_s02...

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" <do*****************@nrm.blot.qld.blot.gov.blot.au > wrote
in message news:WS****************@news.optus.net.au...

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" <xe*******@planetx.com> wrote in message
news:QHsWb.272819$I06.2925114@attbi_s01...

Doug,

I missed that and failed the test. The last item in the SELECT clause
doesn't need a comma, but "l.longitude" 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" <do*****************@nrm.blot.qld.blot.gov.blot.au > wrote in message news:kc****************@news.optus.net.au...
> Top posting corrected...see below
> > "Doug Hutcheson" <do*****************@nrm.blot.qld.blot.gov.blot.au > wrote
> > in message news:DD****************@news.optus.net.au...
> > > --
> > > Remove the blots from my address to reply
> > > "Xenophobe" <xe*******@planetx.com> wrote in message
> > > news:9cDVb.258063$na.418618@attbi_s04...
> > > > 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 <= ".$highLongitude." ";
> > > > $sql .=
> > > >
> > >
> >
>

"(69.09*DEGREES(ACOS(SIN(RADIANS(l.latitude))*SIN( RADIANS($latitude))+COS(RA

> > > >
> > >
> >
>

DIANS(l.latitude))*COS(RADIANS($latitude))*COS(RAD IANS(l.longitude-$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" <us***********@nospam.com> wrote in message
> > > > news:CW******************@newssvr25.news.prodigy.c om...
> > > > >
> > > > > On 8-Feb-2004, "Xenophobe" <xe*******@planetx.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

this

> > > > technique
> > > > > > is
> > > > > > the inability to sort by distance. Can someone suggest a

simple

> way
> > to
> > > > > > calculate and ORDER BY distance within the query?
> > > > >
> > > > > Put the following in your SQL ($lat and $long are the zero
distance
> > > point)
> > > > >
> > > > >
> > > >
> > >
> >
>

(69.09*DEGREES(ACOS(SIN(RADIANS(Latitude))*SIN(RAD IANS($lat))+COS(RADIANS(La

> > > > titude))*
> > > > > COS(RADIANS($lat))*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 ja*********@willglen.net (it's reserved for > > > spammers)
> > > >
> > > >
> > >
> > > 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(RADIANS(l.latitude))" ;
> > > $sql .= "*SIN(RADIANS($latitude))+COS(RADIANS(l.latitude)) ";
> > > $sql .=
> > "*COS(RADIANS($latitude))*COS(RADIANS(l.longit ude-$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 <= ".$highLongitude." ";
> > > $sql .= "ORDER BY distance";
> > >
> > > HTH
> > > Doug
> > >
> > >
> >
> >
> "Xenophobe" <xe*******@planetx.com> wrote in message
> news:a%gWb.268722$I06.2878665@attbi_s01...
> > 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(RADIANS(l.latitude))*SIN(R ADIANS(41.386533))+COS(RAD > >
>

IANS(l.latitude))*COS(RADIANS(41.386533))*COS(RADI ANS(l.longitude--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.
> >
>
> 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(RADIANS(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
>
>

[Jochen Daum]

Hi!

On Fri, 13 Feb 2004 01:34:00 GMT, "Xenophobe" <xe*******@planetx.com>
wrote:

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?

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.
http://sourceforge.net/projects/phpdbedittk/

[Jochen Daum]

Hi!

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?

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,

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/

[Doug Hutcheson]

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" <jo*********@cans.co.nz> wrote in message
news:e0********************************@4ax.com...

Hi!

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 simplysort the results by company name (ditching the beautiful syntax you help mewith!), but even this won't save enough cycles for broader searches.

The options (in my view) are either break out the results in multiple pagesor 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?

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,

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/

[Jochen Daum]

Hi!


On Fri, 13 Feb 2004 03:32:45 GMT, "Doug Hutcheson"
<do*****************@nrm.blot.qld.blot.gov.blot.au > wrote:

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.
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

Cheers,
Doug

--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/

[Doug Hutcheson]

"Jochen Daum" <jo*********@cans.co.nz> wrote in message
news:er********************************@4ax.com...

Hi!


On Fri, 13 Feb 2004 03:32:45 GMT, "Doug Hutcheson"
<do*****************@nrm.blot.qld.blot.gov.blot.au > wrote:

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 ofgiven 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.

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

Cheers,
Doug

--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/

Jochen,
That looks like a great idea.
Thank you very much for the insight.
Cheers,
Doug
--
Remove the blots from my address to reply

[Xenophobe]

Jochen,

You're right, "huge" is relative.

The zip code table consists of over 52K records. The company supplier lists
it's joined with is under 3K.

By "indexing", do you mean MySQL indexing? If so, I've didn't have much luck
with that. It didn't seem to speed things up although it's quite possible
the wrong columns were indexed or not enough space was allocated.

Here's the schema for the two tables. Can you recommend which tables to
index and what sizes to define?

Any suggestions would be appreciated.

Thanks!

----------------------------------------

TABLE: Locations

LocationID
ZipCode
City
State
StatusCode
AreaCode
TimeZone
Latitude
Longitude

TABLE: Suppliers

SupplierID
SupplierName1
SupplierName2
Address1
Address2
City
State
Zip
Phone
"Jochen Daum" <jo*********@cans.co.nz> wrote in message
news:lb********************************@4ax.com...

Hi!

On Fri, 13 Feb 2004 01:34:00 GMT, "Xenophobe" <xe*******@planetx.com>
wrote:

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 simplysort the results by company name (ditching the beautiful syntax you help mewith!), but even this won't save enough cycles for broader searches.

The options (in my view) are either break out the results in multiple pagesor 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?

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.
http://sourceforge.net/projects/phpdbedittk/

[Xenophobe]

Doug,

As always, thanks for your detailed reply.

I understand the reasons why the query takes time. It's probably working as
fast as possible and can't really be tweaked much more. I will probably have
to break up the results in multiple pages. Pre-calulating every combination
of lat and long seems like overkill for this particular project.

To be continued...

"Doug Hutcheson" <do*****************@nrm.blot.qld.blot.gov.blot.au > wrote
in message news:Cc****************@news.optus.net.au...

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" <xe*******@planetx.com> wrote in message
news:cQVWb.297527$xy6.1461753@attbi_s02...

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" <do*****************@nrm.blot.qld.blot.gov.blot.au > wrote in message news:WS****************@news.optus.net.au...

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" <xe*******@planetx.com> wrote in message
news:QHsWb.272819$I06.2925114@attbi_s01...
> Doug,
>
> I missed that and failed the test. The last item in the SELECT clause > doesn't need a comma, but "l.longitude" 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" <do*****************@nrm.blot.qld.blot.gov.blot.au >

wrote

> in message news:kc****************@news.optus.net.au...
> > Top posting corrected...see below
> > > "Doug Hutcheson" <do*****************@nrm.blot.qld.blot.gov.blot.au > > wrote
> > > in message news:DD****************@news.optus.net.au...
> > > > --
> > > > Remove the blots from my address to reply
> > > > "Xenophobe" <xe*******@planetx.com> wrote in message
> > > > news:9cDVb.258063$na.418618@attbi_s04...
> > > > > 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 <= ".$highLongitude." ";
> > > > > $sql .=
> > > > >
> > > >
> > >
> >
>

"(69.09*DEGREES(ACOS(SIN(RADIANS(l.latitude))*SIN( RADIANS($latitude))+COS(RA

> > > > >
> > > >
> > >
> >
>

DIANS(l.latitude))*COS(RADIANS($latitude))*COS(RAD IANS(l.longitude-$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" <us***********@nospam.com> wrote in message
> > > > > news:CW******************@newssvr25.news.prodigy.c om...
> > > > > >
> > > > > > On 8-Feb-2004, "Xenophobe" <xe*******@planetx.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 this
> > > > > technique
> > > > > > > is
> > > > > > > the inability to sort by distance. Can someone suggest a
simple
> > way
> > > to
> > > > > > > calculate and ORDER BY distance within the query?
> > > > > >
> > > > > > Put the following in your SQL ($lat and $long are the zero
> distance
> > > > point)
> > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>

(69.09*DEGREES(ACOS(SIN(RADIANS(Latitude))*SIN(RAD IANS($lat))+COS(RADIANS(La

> > > > > titude))*
> > > > > > COS(RADIANS($lat))*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 ja*********@willglen.net (it's reserved for

> > > > spammers)
> > > > >
> > > > >
> > > >
> > > > 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(RADIANS(l.latitude))" ;
> > > > $sql .= "*SIN(RADIANS($latitude))+COS(RADIANS(l.latitude)) ";
> > > > $sql .=
> > > "*COS(RADIANS($latitude))*COS(RADIANS(l.longit ude-$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 <= ".$highLongitude." ";
> > > > $sql .= "ORDER BY distance";
> > > >
> > > > HTH
> > > > Doug
> > > >
> > > >
> > >
> > >
> > "Xenophobe" <xe*******@planetx.com> wrote in message
> > news:a%gWb.268722$I06.2878665@attbi_s01...
> > > 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(RADIANS(l.latitude))*SIN(R ADIANS(41.386533))+COS(RAD > > >
> >
>

IANS(l.latitude))*COS(RADIANS(41.386533))*COS(RADI ANS(l.longitude--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.
> > >
> >
> > 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(RADIANS(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
> >
> >
>
>

[Jochen Daum]

Hi !

On Sat, 14 Feb 2004 06:09:13 GMT, "Xenophobe" <xe*******@planetx.com>
wrote:

Jochen,

You're right, "huge" is relative.

The zip code table consists of over 52K records. The company supplier lists
it's joined with is under 3K.

By "indexing", do you mean MySQL indexing? If so, I've didn't have much luck
with that. It didn't seem to speed things up although it's quite possible
the wrong columns were indexed or not enough space was allocated.

Here's the schema for the two tables. Can you recommend which tables to
index and what sizes to define?

Any suggestions would be appreciated.
I suggest you implement the table schema I suggested in the other
thread, with no minute information and also put an index on
Suppliers.zipcode.

HTH, Jochen


Thanks!

----------------------------------------

TABLE: Locations

LocationID
ZipCode
City
State
StatusCode
AreaCode
TimeZone
Latitude
Longitude

TABLE: Suppliers

SupplierID
SupplierName1
SupplierName2
Address1
Address2
City
State
Zip
Phone
"Jochen Daum" <jo*********@cans.co.nz> wrote in message
news:lb********************************@4ax.com.. .

Hi!

On Fri, 13 Feb 2004 01:34:00 GMT, "Xenophobe" <xe*******@planetx.com>
wrote:

>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 couldsimply >sort the results by company name (ditching the beautiful syntax you helpme >with!), but even this won't save enough cycles for broader searches.
>
>The options (in my view) are either break out the results in multiplepages >or pre-calculate every possible lat and long and store them in thedatabase >(creating one huge table!) Can you think of anything that might help?

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.
http://sourceforge.net/projects/phpdbedittk/

--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/

[Jochen Daum]

Hi Doug!
On Fri, 13 Feb 2004 04:33:56 GMT, "Doug Hutcheson"
<do*****************@nrm.blot.qld.blot.gov.blot.au > wrote:

"Jochen Daum" <jo*********@cans.co.nz> wrote in message
news:er********************************@4ax.com.. .

On Fri, 13 Feb 2004 03:32:45 GMT, "Doug Hutcheson"
<do*****************@nrm.blot.qld.blot.gov.blot.au > wrote:

>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 milesof >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.

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
Had been thinking in the car about this and here are some
optimisations:

Firstly, if you have a database which supports clustered indexes, its
only 12 bytes per row, as you don't need to store the data again.

Also, you could limit the maximum distance stored for most
applications, eg. I don't wanna walk 34km to my next busstop. This
should maybe save another two bytes on the distance field.

A minimal save in the index pages could be gained, if you remove
distance from the index. With the current setup, there'll be 10 bytes
per row, thats around 800 records per page. That means all resultsets
will probably be in exactly one page (just cannot imagine to have 800
busstops at hand, which I would want to walk to, similar for any type
of shop), so you don't need any range scanning for the distance.

HTH, Jochen

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

>Cheers,
>Doug

--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/

Jochen,
That looks like a great idea.
Thank you very much for the insight.
Cheers,
Doug

--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/

[Doug Hutcheson]

"Jochen Daum" <jo*********@cans.co.nz> wrote in message
news:ke********************************@4ax.com...

Hi Doug!
On Fri, 13 Feb 2004 04:33:56 GMT, "Doug Hutcheson"
<do*****************@nrm.blot.qld.blot.gov.blot.au > wrote:

"Jochen Daum" <jo*********@cans.co.nz> wrote in message
news:er********************************@4ax.com.. .

On Fri, 13 Feb 2004 03:32:45 GMT, "Doug Hutcheson"
<do*****************@nrm.blot.qld.blot.gov.blot.au > wrote:

>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.

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

Had been thinking in the car about this and here are some
optimisations:

Firstly, if you have a database which supports clustered indexes, its
only 12 bytes per row, as you don't need to store the data again.

Also, you could limit the maximum distance stored for most
applications, eg. I don't wanna walk 34km to my next busstop. This
should maybe save another two bytes on the distance field.

A minimal save in the index pages could be gained, if you remove
distance from the index. With the current setup, there'll be 10 bytes
per row, thats around 800 records per page. That means all resultsets
will probably be in exactly one page (just cannot imagine to have 800
busstops at hand, which I would want to walk to, similar for any type
of shop), so you don't need any range scanning for the distance.

HTH, Jochen

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
>Cheers,
>Doug

--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/

Jochen,
That looks like a great idea.
Thank you very much for the insight.
Cheers,
Doug

--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/

Jochen,
Another one for my 'keepers' file...thanks again!
Cheers,
Doug

--
Remove the blots from my address to reply