Re: ORDER BY Zip Code Distance

From: Doug Hutcheson (doug.blot.hutcheson_at_nrm.blot.qld.blot.gov.blot.au)
Date: 02/09/04 

Date: Mon, 09 Feb 2004 04:02:43 GMT



--
Remove the blots from my address to reply
"Xenophobe" <xenophobe@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(RADIANS(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" <use.signature@nospam.com> wrote in message
> news:CWAVb.22832$VW4.3380@newssvr25.news.prodigy.com...
> >
> > On  8-Feb-2004, "Xenophobe" <xenophobe@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(RADIANS($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 jamesbutler@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.longitude-$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

Relevant Pages

  • Re: DBRW generating ADODB.Command error 800a0cc1
    ... Source: Microsoft JET Database Engine ... My guess the Distance variable is causing the problem. ... When I verify the query it says it was verified with the database ... I have 2 text boxes on the form, EnterZip and Distance, and I have the ...
    (microsoft.public.frontpage.programming)
  • Re: DBRW generating ADODB.Command error 800a0cc1
    ... Distance is assumed to be numeric, and EnterZip is a string value - you should have code to ensure these have the correct value types. ... Also note that the sql statement above has been reformatted for the FrontPage database results wizard - it is not valid SQL. ... When I verify the query it says it was verified with the database ...
    (microsoft.public.frontpage.programming)
  • Re: DBRW generating ADODB.Command error 800a0cc1
    ... When I verify the query it says it was verified with the database ... I have 2 text boxes on the form, EnterZip and Distance, and I have the ...
    (microsoft.public.frontpage.programming)
  • Re: ORDER BY Zip Code Distance
    ... Tom, thanks for your response. ... e))))) AS distance "; ... Commenting the last two lines makes the query servicable again. ... >> As described the high and low latitudes and longitudes are ...
    (comp.lang.php)
  • Re: DBRW generating ADODB.Command error 800a0cc1
    ... and minimum longitudes and latitudes to pass to the next form, ... When I verify the query it says it was verified with the database ... I have 2 text boxes on the form, EnterZip and Distance, and I have the form ...
    (microsoft.public.frontpage.programming)