Joining relations based on distance between geometry columns?

Question

I have two tables:

User(id)
Region(id, userID, origin Geometry('point'), radius))

User has a one-to-one relationship with Region.

I want to query all users that have a region that contains a point (lat and long). To define contains, if the distance between the given point and region.origin is less than region.radius then that region contains the point.

I'm able to query just regions that contain a given point like this:

SELECT "id", "origin", "radius", "regionID", ST_Distance("origin", ST_MakePoint(lat, long), false) AS "distance" 
FROM "regions" AS "region" 
WHERE 
  ST_DWithin("origin", ST_MakePoint(lat, long), maxDistance, false) = true
AND 
  ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0
ORDER BY distance ASC
LIMIT 10;

But I really want the users and regions and don't want to query each user individually based of the region. Here is what I have:

SELECT * 
FROM user, regions as region 
WHERE 
  user.id = region.userID
AND 
  ST_DWithin("origin", ST_MakePoint(lat, long), maxDistance, false) = true 
AND 
  ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0 
LIMIT 10;

With this query, I don't get any results, where the first query I get results.

Answer 1

ST_MakePoint(lat, long)

That's the wrong point order. You want ST_MakePoint(long,lat), check the docs here ST_MakePoint

AND ST_Distance("origin", ST_MakePoint(lat, long), false) - radius <= 0 

That doesn't make any sense, it should be

ST_DWithin(origin, ST_MakePoint(long,lat), radius)

I think this is what you want,

SELECT * 
FROM user AS u
INNER JOIN regions AS r 
  ON u.id = r.userID
  AND ST_DWithin(r.origin, ST_MakePoint(long,lat)::geog, radius)
ORDER BY ST_MakePoint(long,lat) <-> r.origin
FETCH FIRST 10 ROWS ONLY

maxdistance and radius are the same thing. You have a LIMIT 10 there, what is the ORDER that you want? Do you just want any 10 rows?

Don't forget to add your spatial index. In this case you can actually add a compound index.

CREATE EXTENSION btree_gist;
CREATE INDEX ON region USING GIST (userID, origin);

See also,

• Finding the nearest geo points across two tables?

Answer 2

I have just modified your first query and add the join condition.

SELECT r."id", r."origin",r. "radius",r."regionID", r.ST_Distance("origin", ST_MakePoint(?, ?), FALSE) AS "distance",
    u."id" 
    FROM "regions" AS "region" as r LEFT OUTER JOIN user as u ON r.id=u.id
    WHERE 
      ST_DWithin("origin", ST_MakePoint(lat, LONG), maxDistance, FALSE) = TRUE
    AND 
      ST_Distance("origin", ST_MakePoint(lat, LONG), FALSE) - radius <= 0
    ORDER BY distance ASC
    LIMIT 10;