On 2/13/07, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:
On 2/12/07, Mark Stosberg <mark@xxxxxxxxxxxxxxx> 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
