1

I'm new to PostGIS and I'm just trying to get a good grasp of the basics.

I've taken a dataset containing information on the world's cities, from https://www.maxmind.com/en/free-world-cities-database and imported it into a table.

I added a geometry column with:

SELECT AddGeometryColumn('cities', 'geom', 4326, 'POINT', 2);

And then populated the column with:

UPDATE cities SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

I want to find all cities within the radius of a specific lat/lon. To do that I tried:

SELECT *
FROM cities
WHERE ST_DWithin(geom, ST_MakePoint(79.3832,43.6532)::geography, 50000);

...but no results are returned. The lat/lon I entered is that of the city of Toronto and I've verified that a record for Toronto exists in the table with the correct lat/lon.

If I increase the radius to something ridiculous, such as "5000000" a list of cities - most of which appear to be places on other continents - is returned. What am I doing wrong? Is it my query? Am I filling the geom column incorrectly?

  • You're comparing geometry with geography. Did you try WHERE ST_DWithin(geom::geography, ST_MakePoint(79.3832,43.6532)::geography, 50000); ? – pLumo Jul 25 '17 at 6:46
  • @RoVo Thank you for the suggestion. I tried it now but still, no results. :( – Leighton F. Jul 25 '17 at 7:17
  • your point is somewhere in Kasachstan, with Almaty the next bigger city being 200 km away... but you search a radius of 50km. Sure that you're doing the right thing? – pLumo Jul 25 '17 at 7:23
  • I guess you want to search for -79... – pLumo Jul 25 '17 at 7:25
  • @RoVo If I enter those coordinates into Google Maps it drops a pin in the center of Toronto. I also got the coordinates by looking them up. – Leighton F. Jul 25 '17 at 7:59
4

You should compare geography with geography OR geometry with geometry. And also you need to set the SRID.

So in your case you need

WHERE ST_DWithin(geom::geography, ST_SetSRID(ST_MakePoint(79.3832,43.6532),4326)::geography, 50000);

OR

WHERE ST_DWithin(geom::geography, ST_GeogFromText('SRID=4326;Point(79.3832 43.6532)'), 50000);

Then, from your profile details, I bet the longitude must be -79.3832, because currently you're searching for cities around a place in southeastern Kasachstan with not much around, while you probably want to search cities around Toronto.

WHERE ST_DWithin(geom::geography, ST_GeogFromText('SRID=4326;Point(-79.3832 43.6532)'), 50000);
  • Thanks! :) I really appreciate the help. That solved my problem. – Leighton F. Jul 25 '17 at 10:23

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.