Finding cities within radius usingPostGIS?

Question

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?

Answer 1

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