[GIS] Finding cities within radius usingPostGIS

postgis

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?

Best Answer

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);
Related Question