Is it possible to use PostgreSQL Geometric Operators for Latitude, Longitude?

Question

I'm capturing tweets and saving them into a PostgreSQL table like:

CREATE TABLE tweets (
  id bigint,
  location point
)

And I'd like to select all tweets within a radius from a city, ie:

Buenos Aires (lat: -34.603722, lon: -58.381592, radius: 2Km)

As far I could understand I need to run a query similar to:

SELECT * FROM tweets WHERE location <@ circle '((-34.603722, -58.381592), 2000)'

I'm not sure if this approach makes sense, can I use Latitude/Longitude on simple PostgreSQL? Or I need to install PostGIS, I rather prefer not having more dependencies if it is possible.

I saw a few posts where people says a point should be (longitude, latitude) and I'm not sure unit is metric (meters) or imperial (miles).

Reference: Postgres Contained in operator

Answer 1

PostgreSQL points are just ordinary points in the 2-D Cartesian system. So lat,long will be treated as if they were X-Y coordinates, which is generally not correct. (For example, consider the boundaries)

SELECT * FROM tweets WHERE location <@ circle '((-34.603722, -58.381592), 2000)'

Also, your query above would be incorrect because the radius 2000 will be treated as degrees. How much is 2 km in degrees varies depending on where you are on earth.

It's probably easier to go with PostGIS, unless you are restricted to a small area where the above errors are not an issue.

Answer 2

Check Paul Ramsey's post on this subject: https://carto.com/blog/lateral-joins/

Then this documentation:
http://postgis.net/docs//geometry_distance_knn.html
http://postgis.net/docs//geometry_distance_box.html

I asked this question on the PostGIS mailing list and Stephen Mather gave me the answer...