1

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

3

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.

1
  • Thanks @tinlyx it clarifies a lot of doubts. I'll give PostGIS a try :) – arjones Dec 21 '17 at 1:32
1

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...

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.