locating cities within a radius of another

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

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';

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

On 21 Jul 2010, at 23:14, Joe Conway <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

Regards
Oliver Kohll

oliver@agilebase.co.uk / +44(0)7814 828608 / skype:okohll
www.agilebase.co.uk - software

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&gt; - 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

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&gt; - 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

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)

Regards
Oliver Kohll

oliver@agilebase.co.uk / +44(0)7814 828608 / skype:okohll
www.agilebase.co.uk - software

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&gt; - 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