Search code examples
geolocationpostgislatitude-longitude

PostGIS latitude longitude radius

I have two tables A and B:

  • A contains lat and long values for entity.
  • B contains lat, long, point and radius values.

I need to make a SQL query that selects all rows from A table inside the radius from B table.

Can somebody show me a SQL query example?

Solution

  • Probably you can do something like this:

    SELECT 
    a.*
    , b.id
FROM
    a
JOIN
    b
ON
    ST_DWithin(b.point, ST_MakePoint(a.long, a.lat), b.radius)

    Take into account that:

    • This query does not remove duplicated rows on a. Those that are whihin the distance of more that one row on b
    • The coordinates of bot tables are expected to be in the same srid
    • b.radius is expected to be in the same distance unit that the srid you are using