Thread: 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
Attachment
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
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:
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> - 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
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> - 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