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(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)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