On 2/13/07, Merlin Moncure <mmoncure@gmail.com> wrote:
> On 2/12/07, Mark Stosberg <mark@summersault.com> wrote:
> > Merlin Moncure wrote:
> > >
> > >> Here the basic query I'm using:
> > >> SELECT
> > >> -- 1609.344 is a constant for "meters per mile"
> > >> cube_distance( (SELECT earth_coords from zipcodes WHERE zipcode =
> > >> '90210') , earth_coords)/1609.344
> > >> AS RADIUS
> > >> FROM pets
> > >> -- "shelters_active" is a view where "shelter_state = 'active'"
> > >> JOIN shelters_active as shelters USING (shelter_id)
> > >> -- The zipcode fields here are varchars
> > >> JOIN zipcodes ON (
> > >> shelters.postal_code_for_joining = zipcodes.zipcode )
> > >> -- search for just 'dogs'
> > >> WHERE species_id = 1
> > >> AND pet_state='available'
> > >> AND earth_box(
> > >> (SELECT earth_coords from zipcodes WHERE zipcode = '90210') ,
> > >> 10*1609.344
> > >> ) @ earth_coords
> > >> ORDER BY RADIUS;
> > >
> > It may not have been clear from the query, but only the 'zipcodes' table
> > has an 'earth_coords' column. Also, I think your refactoring means
> > something different. My query expresses "number of miles this pet is
> > from 90210", while I think the refactor expresses a distance between a
> > pet and another calculated value.
>
> my mistake, i misunderstood what you were trying to do...can you try
> removing the 'order by radius' and see if it helps? if not, we can try
> working on this query some more. There is a better, faster way to do
> this, I'm sure of it.
try this:
SELECT * FROM
(
SELECT
earth_coords(q.earth_coords, s.earth_coords)/1609.344 as radius
FROM pets
JOIN shelters_active as shelters USING (shelter_id)
JOIN zipcodes s ON shelters.postal_code_for_joining = zipcodes.zipcode
JOIN zipcodes q ON q.zipcode = '90210'
WHERE species_id = 1
AND pet_state='available'
AND earth_box(q.earth_coords, 10*1609.344) @ s.earth_coords
) p order by radius
merlin