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