Re: locating cities within a radius of another

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(at)squeakycode(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>