Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

OK, so 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.

So after all of that, 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?

Thanks!

share|improve this question

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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