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@xxxxxxxxx> 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 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)
>
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
