PostGIS latitude longitude radius

Question

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?

Answer 1

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