<div dir="ltr">Hi,<div><br></div><div><br></div><div style>tl;dr How to select rows that intersect _all_ geometries defined in a one-to-many relationship?</div><div style><br></div><div style><br></div><div style>I'm using PostGIS as the core of a new system for scoring paragliding competitions. In such a competition, a task is defined by sequence of turnpoints (polygons) that must be visited in order. The pilot submits a GPS trace (linestring) as evidence. I'm trying to write a PostGIS query that returns all GPS traces that intersect all turnpoints in a given task. I've spent time Googling, but this is beyond my current PostGIS/SQL abilities.</div>
<div style><br></div><div style><br></div><div style>My schema looks like:</div><div style><br></div><div style>-- a task has many turnpoints</div><div style><div>CREATE TABLE task (</div><div><span class="" style="white-space:pre">        </span>id INTEGER NOT NULL, </div>
<div><span class="" style="white-space:pre">    </span>PRIMARY KEY (id)<br></div><div>);</div><div><br></div><div style>-- a turnpoint belongs to a task and has a geometry</div></div><div style><div>CREATE TABLE turnpoint (</div>
<div><span class="" style="white-space:pre">    </span>id INTEGER NOT NULL, </div><div><span class="" style="white-space:pre">      </span>task_id INTEGER, </div><div><span class="" style="white-space:pre">  </span>seq INTEGER NOT NULL, -- index within its task </div>
<div><span class="" style="white-space:pre">    geom</span> geometry(POLYGON,-1) NOT NULL, <br></div><div><span class="" style="white-space:pre">     </span>PRIMARY KEY (id), </div><div><span class="" style="white-space:pre"> </span>UNIQUE (task_id, seq), </div>
<div><span class="" style="white-space:pre">    </span>FOREIGN KEY(task_id) REFERENCES task (id)</div><div>);</div><div><br></div><div style>-- a flight has a geometry</div><div><div>CREATE TABLE flight (</div><div><span class="" style="white-space:pre">  </span>id INTEGER NOT NULL, </div>
<div><span class="" style="white-space:pre">    </span>geom geometry(LINESTRING,-1) NOT NULL, <br></div><div><span class="" style="white-space:pre">  </span>PRIMARY KEY (id)<br></div><div>);<br></div></div><div><br></div><div>
<br></div><div style>What I want is:</div><div style><br></div><div style>(1) for a given task, return all flight.ids that intersect all of that task's turnpoints' geoms; intersection tests can be approximate (i.e.intersecting with && is sufficient)</div>
<div style><br></div><div style>(2) a list of (<a href="http://task.id">task.id</a>, <a href="http://flight.id">flight.id</a>) pairs where the flight's geom intersects all of the task's turnpoints' geoms (intersecting with && is sufficient)</div>
<div style><br></div><div style><br></div><div style>I'm a bit stuck on how to achieve the above. If I use ST_Collect to combine all the turnpoints of a single task, and then do the intersection test with ST_Intersects then I'll select all flights that intersect ANY of the task's turnpoints; not all flights that intersect ALL of the task's turnpoints.</div>
<div style><br></div><div style>If it is necessary to change the schema (e.g. to represent a task's turnpoints as an array of geometries rather than a one-to-many relationship) then that's fine.</div><div style><br>
</div><div style><br></div><div style>Many thanks for any help or pointers on how to achieve this with PostGIS.</div><div style><br></div><div style>Tom</div><div style><br></div><div style><br></div><div style><br></div>
<div style>P.S. Background information, probably not relevant to question:</div><div style>- I'd like to use the queries above as an initial filter, the precise intersection tests and the checks that the turnpoints are visited in the correct order is done in my application</div>
<div style>- Each task typically has 2-5 turnpoints</div><div style>- The database will eventually contain thousands of tasks and tens of thousands of flights, I plan to make it possible to do query (2) above incrementally</div>
</div></div>