Re: Finding points within 50 miles

I'm interested in doing a project for calculating distances similar to
this.  Anyone have suggestions on how/where this type of data can be
obtained?  Is it freely available anywhere?


On 6/27/05, Uwe C. Schroeder <uwe@oss4u.com> wrote:
>
> 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
inmy 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))::intas 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>