Обсуждение: locating cities within a radius of another

We need to locate all cities within a certain distance of a single city.
  We have longitude and latitude data for all cities.  I was thinking
postGIS was a viable solution, but I don't see a way to use our existing
data via postGIS.

Is postGIS a viable solution, or should I be looking at a different
approach?  Thanks for any suggestions or RTFM pointers.

--
Until later, Geoffrey

"I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them."
- Thomas Jefferson

On 7/21/2010 8:01 AM, Geoffrey wrote:
> We need to locate all cities within a certain distance of a single city.
> We have longitude and latitude data for all cities. I was thinking
> postGIS was a viable solution, but I don't see a way to use our existing
> data via postGIS.
>
> Is postGIS a viable solution, or should I be looking at a different
> approach? Thanks for any suggestions or RTFM pointers.
>

I'd say PostGIS is a great option.

Did you try:

http://postgis.refractions.net/documentation/manual-1.5/ST_Distance_Sphere.html


or do you mean, how do I turn my lat, long columns into a geomentry column?


-Andy

On 07/21/2010 06:01 AM, Geoffrey wrote:
> We need to locate all cities within a certain distance of a single city.
>  We have longitude and latitude data for all cities.  I was thinking
> postGIS was a viable solution, but I don't see a way to use our existing
> data via postGIS.
>
> Is postGIS a viable solution, or should I be looking at a different
> approach?  Thanks for any suggestions or RTFM pointers.

If you want something simple, and not requiring PostGIS, but plpgsql
instead, see:

http://archives.postgresql.org/pgsql-sql/2003-12/msg00193.php

HTH,

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support

Once PostGIS is installed you can do it with a single SQL query looking like this:

SELECT dest.id, ST_Distance(ST_MakePoint(orig.longitude, orig.latitude), ST_MakePoint(dest.longitude, dest.latitude))
FROM yourcitytable orig, yourcitytable dest
WHERE ST_DWithin(ST_MakePoint(orig.longitude, orig.latitude), ST_MakePoint(dest.longitude, dest.latitude), 20000) AND
orig.id= 378 AND dest.id <> 378 

Pierre

>-----Original Message-----
>From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Joe
>Conway
>Sent: 21 juillet 2010 11:44
>To: Geoffrey
>Cc: PostgreSQL List
>Subject: Re: [GENERAL] locating cities within a radius of another
>
>On 07/21/2010 06:01 AM, Geoffrey wrote:
>> We need to locate all cities within a certain distance of a single city.
>>  We have longitude and latitude data for all cities.  I was thinking
>> postGIS was a viable solution, but I don't see a way to use our existing
>> data via postGIS.
>>
>> Is postGIS a viable solution, or should I be looking at a different
>> approach?  Thanks for any suggestions or RTFM pointers.
>
>If you want something simple, and not requiring PostGIS, but plpgsql
>instead, see:
>
>http://archives.postgresql.org/pgsql-sql/2003-12/msg00193.php
>
>HTH,
>
>Joe
>
>--
>Joe Conway
>credativ LLC: http://www.credativ.us
>Linux, PostgreSQL, and general Open Source
>Training, Service, Consulting, & 24x7 Support

create table cities (
  geog geography,
  name varchar,
  id integer primary key
);

insert into cities
  select
    Geography(ST_SetSRID(ST_MakePoint(lon, lat),4326)) as geog,
    name, id
  from mytable;

create index cities_gix on cities using gist ( geog );

select st_distance(a.geog, b.geog), b.name
from cities a, cities b
where a.name = 'New York';

On Wed, Jul 21, 2010 at 8:10 AM, Andy Colson <andy@squeakycode.net> wrote:
> On 7/21/2010 8:01 AM, Geoffrey wrote:
>>
>> We need to locate all cities within a certain distance of a single city.
>> We have longitude and latitude data for all cities. I was thinking
>> postGIS was a viable solution, but I don't see a way to use our existing
>> data via postGIS.
>>
>> Is postGIS a viable solution, or should I be looking at a different
>> approach? Thanks for any suggestions or RTFM pointers.
>>
>
> I'd say PostGIS is a great option.
>
> Did you try:
>
> http://postgis.refractions.net/documentation/manual-1.5/ST_Distance_Sphere.html
>
>
> or do you mean, how do I turn my lat, long columns into a geomentry column?
>
>
> -Andy
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Hum right... Better follow Paul instructions. We are in geographic coordinates here... Sorry. This would work in a
limitedprojected space. 

>-----Original Message-----
>From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of
>Pierre Racine
>Sent: 21 juillet 2010 12:04
>To: Joe Conway; Geoffrey
>Cc: PostgreSQL List
>Subject: Re: [GENERAL] locating cities within a radius of another
>
>Once PostGIS is installed you can do it with a single SQL query looking like this:
>
>SELECT dest.id, ST_Distance(ST_MakePoint(orig.longitude, orig.latitude), ST_MakePoint(dest.longitude,
>dest.latitude))
>FROM yourcitytable orig, yourcitytable dest
>WHERE ST_DWithin(ST_MakePoint(orig.longitude, orig.latitude), ST_MakePoint(dest.longitude,
>dest.latitude), 20000) AND orig.id = 378 AND dest.id <> 378
>
>Pierre
>
>>-----Original Message-----
>>From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Joe
>>Conway
>>Sent: 21 juillet 2010 11:44
>>To: Geoffrey
>>Cc: PostgreSQL List
>>Subject: Re: [GENERAL] locating cities within a radius of another
>>
>>On 07/21/2010 06:01 AM, Geoffrey wrote:
>>> We need to locate all cities within a certain distance of a single city.
>>>  We have longitude and latitude data for all cities.  I was thinking
>>> postGIS was a viable solution, but I don't see a way to use our existing
>>> data via postGIS.
>>>
>>> Is postGIS a viable solution, or should I be looking at a different
>>> approach?  Thanks for any suggestions or RTFM pointers.
>>
>>If you want something simple, and not requiring PostGIS, but plpgsql
>>instead, see:
>>
>>http://archives.postgresql.org/pgsql-sql/2003-12/msg00193.php
>>
>>HTH,
>>
>>Joe
>>
>>--
>>Joe Conway
>>credativ LLC: http://www.credativ.us
>>Linux, PostgreSQL, and general Open Source
>>Training, Service, Consulting, & 24x7 Support
>
>
>--
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general

Oliver Kohll - Mailing Lists wrote:
>
> On 21 Jul 2010, at 23:14, Joe Conway <mail@joeconway.com
> <mailto:mail@joeconway.com>> wrote:
>
>> If you want something simple, and not requiring PostGIS, but plpgsql
>> instead, see:
>>
>> http://archives.postgresql.org/pgsql-sql/2003-12/msg00193.php
>
> For completeness, the earthdistance module also provides the distance
> between two lat/longs, the point<@>point syntax is simple to use:
> http://www.postgresql.org/docs/8.3/static/earthdistance.html

I did look at earthdistance before.  Revisiting it now, thanks.

So, I'm trying to figure out this syntax.  The docs say:

point <@> point - float8 - gives the distance in statue miles between
two points on the Earth's surface.

How does longitude and latitude fit into this picture?  I can't find any
other documentation or examples?

I've got the contrib mods installed as 'select earth()' works fine.

>
> Regards
> Oliver Kohll
>
> oliver@agilebase.co.uk <mailto:oliver@agilebase.co.uk> / +44(0)7814
> 828608 / skype:okohll
> www.agilebase.co.uk <http://www.agilebase.co.uk> - software
>


--
Until later, Geoffrey

"I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them."
- Thomas Jefferson

Oliver Kohll - Mailing Lists wrote:
>
> On 21 Jul 2010, at 23:14, Joe Conway <mail@joeconway.com
> <mailto:mail@joeconway.com>> wrote:
>
>> If you want something simple, and not requiring PostGIS, but plpgsql
>> instead, see:
>>
>> http://archives.postgresql.org/pgsql-sql/2003-12/msg00193.php
>
> For completeness, the earthdistance module also provides the distance
> between two lat/longs, the point<@>point syntax is simple to use:
> http://www.postgresql.org/docs/8.3/static/earthdistance.html

Trying to figure out the proper usage.  My assumptions:

use ll_to_earth() to get point values to pass to 'point <@> point'

First issue, ll_to_earth() returns three values, not one.

Second issue, I tried something like:

select (ll_to_earth(46,67)<@>ll_to_earth(57,87));

I get:

ERROR:  operator does not exist: earth <@> earth
LINE 1: select (ll_to_earth(46,67)<@>ll_to_earth(57,87));

So I tried:

select (4618419.15006707<@>4394453.66154081);

And I get:

ERROR:  operator does not exist: numeric <@> numeric
LINE 1: select (4618419.15006707<@>4394453.66154081);
                                 ^
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.

What am I missing???



--
Until later, Geoffrey

"I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them."
- Thomas Jefferson

Oliver Kohll - Mailing Lists wrote:
>
> On 21 Jul 2010, at 23:14, Joe Conway <mail@joeconway.com
> <mailto:mail@joeconway.com>> wrote:
>
>> If you want something simple, and not requiring PostGIS, but plpgsql
>> instead, see:
>>
>> http://archives.postgresql.org/pgsql-sql/2003-12/msg00193.php
>
> For completeness, the earthdistance module also provides the distance
> between two lat/longs, the point<@>point syntax is simple to use:
> http://www.postgresql.org/docs/8.3/static/earthdistance.html

Disgregard my last post, Surely as soon as I hit send, the light went
on...  I'm looking at deriving my points for point <@> point from
ll_to_earth().

>
> Regards
> Oliver Kohll
>
> oliver@agilebase.co.uk <mailto:oliver@agilebase.co.uk> / +44(0)7814
> 828608 / skype:okohll
> www.agilebase.co.uk <http://www.agilebase.co.uk> - software
>


--
Until later, Geoffrey

"I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them."
- Thomas Jefferson

Oliver Kohll - Mailing Lists wrote:
>
> On 22 Jul 2010, at 12:57, Geoffrey wrote:
>
>>> For completeness, the earthdistance module also provides the distance
>>> between two lat/longs, the point<@>point syntax is simple to use:
>>> http://www.postgresql.org/docs/8.3/static/earthdistance.html
>>
>> Disgregard my last post, Surely as soon as I hit send, the light went
>> on...  I'm looking at deriving my points for point <@> point from
>> ll_to_earth().
>
> I constructed mine using point(longitude, latitude), where long and lat
> are double precision, which returns a datatype of type point.
> ll_to_earth() looks like it returns a datatype of type earth, so not
> sure if it will work. Maybe things have changed in a recent release,
> please let me know if so.
>
> So an example would be
> select point(-2.2171,56.8952)<@>point(-1.2833,51.6667) as miles;
>       miles
> ------------------
>  363.202864676916
> (1 row)

Perfect, that appears to work for me as well, thanks.

>
> Regards
> Oliver Kohll
>
> oliver@agilebase.co.uk <mailto:oliver@agilebase.co.uk> / +44(0)7814
> 828608 / skype:okohll
> www.agilebase.co.uk <http://www.agilebase.co.uk> - software
>
>
>


--
Until later, Geoffrey

"I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them."
- Thomas Jefferson