Re: locating cities within a radius of another

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(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)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(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
>
>
>--
>Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general