9

I downloaded the Geonames database (cities1000) and wrote a small Ruby program to import them into my table (geo_cities). I then added geography column called geog.

Then I converted all of the lat/lon numbers into the geog column using:

update public.geo_cities set geog = st_GeogFromText('SRID=4326;POINT(' || longitude || ' ' || latitude || ')');

Things are looking good. Now what I want to do is find all cities within 100 miles of Prague.

So I can get Prague like:

select * from geo_cities where asciiname = 'Prague' and countrycode = 'CZ';

I'm still learning GIS and Postgres so could someone help me with the simple query?

12

First, make sure you have an index on your geography column. It will speed up the spatial searches:

CREATE INDEX geo_cities_geog_idx ON geo_cities USING GIST geog;
VACUUM ANALYZE geo_cities(geog);

Then, you can use ST_DWithin (with conversions from miles to metres) on a self-joined query:

SELECT gc.*, ST_Distance(gc.geog, pt.geog)/1609.344 AS distance_miles
FROM geo_cities gc, geo_cities pt
WHERE pt.asciiname = 'Prague' and pt.countrycode = 'CZ'
  AND ST_DWithin(gc.geog, pt.geog, 160934.4)
ORDER BY ST_Distance(gc.geog, pt.geog);

If you are in the market for a good book, check out PostGIS in Action.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.