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
