Thread: Finding points within 50 miles

Finding points within 50 miles

From
CSN
Date:
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

Re: Finding points within 50 miles

From
Janning Vygen
Date:
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

Re: Finding points within 50 miles

From
"Uwe C. Schroeder"
Date:
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

Re: Finding points within 50 miles

From
Oleg Bartunov
Date:
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

Re: Finding points within 50 miles

From
Alban Hertroys
Date:
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?


Re: Finding points within 50 miles

From
Vivek Khera
Date:
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



Attachment

Re: Finding points within 50 miles

From
Vivek Khera
Date:
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



Attachment

Re: Finding points within 50 miles

From
Bruno Wolff III
Date:
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.

Re: Finding points within 50 miles

From
"Uwe C. Schroeder"
Date:
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

Re: Finding points within 50 miles

From
John Browne
Date:
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)
>

Re: Finding points within 50 miles

From
Vivek Khera
Date:
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



Attachment

Re: Finding points within 50 miles

From
Bruno Wolff III
Date:
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".

Re: Finding points within 50 miles

From
Vivek Khera
Date:
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



Attachment