0

I have a few tables:

  • hubs
  • coverage_areas
  • jobs

  • Jobs have a lonlat (st_point) column
  • Coverage areas have a lonlat (st_point) column and a radius (integer).
  • Hubs can have multiple coverage areas.

According to the image above, there are multiple coverage areas colored blue, and a single job colored orange.

Question: How would I query for all hubs where the job lonlat point is within any one of their coverage areas?

  • Welcome to GIS SE! As a new user please take the tour to learn about our focused Q&A format. The tour states that there should be only one question per question, but here you have asked 2 questions. Please edit your question to focus on just one question. – Midavalo Feb 19 '17 at 17:14
  • It also appears that you have somehow created two accounts - see I accidentally created two accounts; how do I merge them? to merge them, so that you can get notifications and are able to edit your question directly. – Midavalo Feb 19 '17 at 17:17
1

You can do a query like this:

SELECT * from jobs
INNER JOIN (
    SELECT c.id as cid, s.id as sid, s.geom as geom, c.distance as distance from coverage_area as c
    OUTER JOIN stations as s
    on s.id = c.id) as cs
ON ST_DWithin(jobs.geom, cs.geom, cs.distance)
WHERE cs.cid is not null

(Untested...)

Here I'm doing it in two parts, first joining the stations and the coverage areas and then selecting with ST_Intersects for the jobs.

You could make it a bit simpler by changing the sub-query into a view of all coverage areas, including the buffered distance around the stations, and then you have a single join on the 'intersects' condition.

  • 2
    Why use ST_Buffer? It's both expensive and imperceptive. Always use ST_DWithin for a distance-based query, which is much cheaper (can use an index) and exact. – Mike T Feb 20 '17 at 4:34

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.