mjhjmtu

I have two tables:

User
id

and

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

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.

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;

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?