Hum right... Better follow Paul instructions. We are in geographic coordinates here... Sorry. This would work in a limited projected space. >-----Original Message----- >From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of >Pierre Racine >Sent: 21 juillet 2010 12:04 >To: Joe Conway; Geoffrey >Cc: PostgreSQL List >Subject: Re: 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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Joe >>Conway >>Sent: 21 juillet 2010 11:44 >>To: Geoffrey >>Cc: PostgreSQL List >>Subject: Re: 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@xxxxxxxxxxxxxx) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
