Re: Finding points within 50 miles

Actually it does.
I'm using a bounding box too. I have a stored procedure to get me what I need - here's the relevant part of it.
Explanation: zc is the record holding the point of origin. I just added the maxdistance definition for this, because in my function its a parameter.

SELECT INTO zc z.* FROM v_profile p JOIN zipcodes z ON z.zipcode=p.zipcode WHERE p.uid=uid;
IF NOT FOUND THEN
RAISE EXCEPTION \'Cant find member %\',uid;
END IF;
maxdistance:=50;
la_min:=(zc.latn - (maxdistance::float8/70.0));
la_max:=(zc.latn + (maxdistance::float8/70.0));
lo_min:=(zc.longw - (maxdistance::float8/70.0));
lo_max:=(zc.longw + (maxdistance::float8/70.0));

stmt:=''SELECT n.username, n.uid, n.areacode, n.zipcode
geo_distance(point('' || zc.longw ||'',''|| zc.latn ||''),point(z.longw, z.latn))::int as distance,
n.image_thumbnail,n.city, n.state_code
FROM v_new_members n JOIN zipcodes z ON z.zipcode=n.zipcode
AND (z.latn BETWEEN '' || la_min || '' AND '' || la_max || '')
AND (z.longw BETWEEN '' || lo_min || '' AND '' || lo_max || '') AND
geo_distance(point(''|| zc.longw ||'',''||zc.latn||''),point(z.longw, z.latn))::int <= ''||maxdistance ;

hope that helps

UC

On Monday 27 June 2005 02:08 am, you wrote:
> Uwe C. Schroeder wrote:
> >in the where clause use something like (requires the earthdistance contrib
> > to be installed):
> >
> >geo_distance(point([origin longitude],[origin latitude]),point([target
> >longitude column],[target latitude column])))::int <= 50
>
> I don't suppose geo_distance really returns a number in miles, does it?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

--
UC

--
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417