1

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.

  • In the first query you are select the Data from only one table. Which is "use". But in the second query in "from" statement you are writing the two tables name in single statement. Both table query only possible through join. – Md Haidar Ali Khan Nov 7 '18 at 4:52
  • Okay, so are you suggesting I use my original query, but add some type of JOIN? – KKendall Nov 7 '18 at 4:57
  • I have add the alias in your first query and the join condition. – Md Haidar Ali Khan Nov 7 '18 at 5:02
2
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,

1

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;
  • 1
    Thank you for the answer! I'm trying to use it. I changed FROM "regions" AS "region" as r to FROM "regions" AS r. But I'm still getting schema "r" does not exist. – KKendall Nov 7 '18 at 5:27
  • Oh, I found it. I need to remove r. from r.ST_Distance(... – KKendall Nov 7 '18 at 5:28

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.