Обсуждение: Finding points within 50 miles
If I have a table of items with latitude and longitude coordinates, is it possible to find all other items that are within, say, 50 miles of an item, using the geometric functions (http://www.postgresql.org/docs/8.0/interactive/functions-geometry.html)? If so, how? Thanks, CSN __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Am Montag, 27. Juni 2005 01:40 schrieb CSN: > If I have a table of items with latitude and longitude > coordinates, is it possible to find all other items > that are within, say, 50 miles of an item, using the > geometric functions > (http://www.postgresql.org/docs/8.0/interactive/functions-geometry.html)? > If so, how? I did it without some special features and datatypes some time ago. feel free to modify and use for your own. It should give you an idea how to do it. SELECT c1.zip, c2.zip, 6378.388 * acos( sin(radians(c1.latitude)) * sin(radians(c2.latitude)) + cos(radians(c1.latitude)) * cos(radians(c2.latitude)) * cos(radians(c1.longitude - c2.longitude)) ) AS distance FROM coordinates AS c1 CROSS JOIN coordinates AS c2 I had some problems with the calculation inside acos() sometimes being greater than 1, which should not occur. Please use a CASE WHEN sin(...) > 1 THEN 1 ELSE sin(...) END if you have the same problem. kind regards, janning
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 On Sunday 26 June 2005 04:40 pm, CSN wrote: > If I have a table of items with latitude and longitude > coordinates, is it possible to find all other items > that are within, say, 50 miles of an item, using the > geometric functions > (http://www.postgresql.org/docs/8.0/interactive/functions-geometry.html)? > If so, how? > > Thanks, > CSN > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- 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
How big is your data ? There are rather sophisticated and very effective methods in astronomy. For example, http://www.sai.msu.su/~megera/oddmuse/index.cgi/SkyPixelization, http://www.sai.msu.su/~megera/oddmuse/index.cgi/pg_sphere Oleg On Mon, 27 Jun 2005, Janning Vygen wrote: > Am Montag, 27. Juni 2005 01:40 schrieb CSN: >> If I have a table of items with latitude and longitude >> coordinates, is it possible to find all other items >> that are within, say, 50 miles of an item, using the >> geometric functions >> (http://www.postgresql.org/docs/8.0/interactive/functions-geometry.html)? >> If so, how? > > I did it without some special features and datatypes some time ago. feel free > to modify and use for your own. It should give you an idea how to do it. > > SELECT > c1.zip, > c2.zip, > 6378.388 * > acos( > sin(radians(c1.latitude)) * sin(radians(c2.latitude)) > + cos(radians(c1.latitude)) * cos(radians(c2.latitude)) > * cos(radians(c1.longitude - c2.longitude)) > ) AS distance > FROM > coordinates AS c1 > CROSS JOIN coordinates AS c2 > > I had some problems with the calculation inside acos() sometimes being greater > than 1, which should not occur. Please use a > CASE WHEN sin(...) > 1 THEN 1 ELSE sin(...) END > if you have the same problem. > > kind regards, > janning > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
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?
On Jun 27, 2005, at 3:47 AM, Janning Vygen wrote: > I had some problems with the calculation inside acos() sometimes > being greater > than 1, which should not occur. Please use a > CASE WHEN sin(...) > 1 THEN 1 ELSE sin(...) END > if you have the same problem. > We've seen this as well with the distance radius calculation. It doesn't happen in 8.x but did happen 7.4, and then was easily worked around by reducing the precision of the arguments. Ie, we would use 73.13 rather than 73.1343593421 as pulled from the database for the lat/lon values of the center point. In any case, I urge you to derive the formulas yourself from basic research so you *know* you're getting what you think you're getting. Vivek Khera, Ph.D. +1-301-869-4449 x806
Вложения
On Jun 26, 2005, at 7:40 PM, CSN wrote: > If I have a table of items with latitude and longitude > coordinates, is it possible to find all other items > that are within, say, 50 miles of an item, using the > geometric functions > (http://www.postgresql.org/docs/8.0/interactive/functions- > geometry.html)? > If so, how? We optimize this query by first finding the bounding square, then comparing the lat/lon of the other objects (in our case zip codes) for radius. This has the advantage of deleting a *lot* of possible values before passing them to the heavy math formulas. so ours boils down to something along these lines ($zip_radius is the miles we're looking for) the distance computation: (acos((sin($input_lat/57.2958) * sin(zip_latitude/57.2958)) + (cos ($input_lat/57.2958) * cos(zip_latitude/57.2958) * cos(zip_longitude/ 57.2958 - $input_long/57.2958))) * 3963) <= $zip_radius and the bounding box is done like this: $lat_range = $zip_radius / ((6076. / 5280.) * 60); $long_range = $zip_radius / (((cos($input_lat * 3.141592653589 / 180) * 6076.) / 5280.) * 60); so just do a +/- of the center point lat/lon with the above values and you have your square bounding box inside which you run your distance computation. Putting it together is left as an exercise for the reader (hint: just AND your pieces together...) Vivek Khera, Ph.D. +1-301-869-4449 x806
Вложения
On Sun, Jun 26, 2005 at 16:40:03 -0700, CSN <cool_screen_name90001@yahoo.com> wrote: > If I have a table of items with latitude and longitude > coordinates, is it possible to find all other items > that are within, say, 50 miles of an item, using the > geometric functions > (http://www.postgresql.org/docs/8.0/interactive/functions-geometry.html)? > If so, how? You could take a look at using the parts of the earthdistance contrib module based on the cube data type. Gist indexes are supposed to make this kind of query fast.
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
myfunction 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
asdistance,
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
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)
>
On Jun 27, 2005, at 4:36 PM, John Browne wrote: > 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? > Google is your friend. There are places that sell very well kept zipcode databases for under $50. Vivek Khera, Ph.D. +1-301-869-4449 x806
Вложения
On Mon, Jun 27, 2005 at 17:09:37 -0400, Vivek Khera <vivek@khera.org> wrote: > > On Jun 27, 2005, at 4:36 PM, John Browne wrote: > > >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? > > > > Google is your friend. There are places that sell very well kept > zipcode databases for under $50. The US government gives it away for free. Look for "tiger".
On Jun 27, 2005, at 8:42 PM, Bruno Wolff III wrote: >> Google is your friend. There are places that sell very well kept >> zipcode databases for under $50. >> > > The US government gives it away for free. Look for "tiger". > That is stale data. Vivek Khera, Ph.D. +1-301-869-4449 x806