<div dir="ltr">That's awesome Nicolas.<div><br></div><div>Many thanks and bonnes fêtes !</div><div><br></div><div>Tom</div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On 24 December 2012 15:30, Nicolas Ribot <span dir="ltr"><<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">(with proper indices, I'm confident this kind of query would be fast enough for your target size)</div>
<div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><br><div class="gmail_quote">On 24 December 2012 15:29, Nicolas Ribot <span dir="ltr"><<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Hi,<div><br></div><div>There must be a simpler solution than the one provided, but can't see it right now.</div>
<div>Maybe using windows functions...</div><div><br></div><div>Anyway...:</div>
<div>Your 2 questions are the same: for the first one, you will add a WHERE clause defining the task id you want results for.</div><div><br></div><div>The following query is using Common Table Expression (CTE) with WITH construct, easier to read and write than subqueries.</div>
<div>The first "tasks" query just gives the number of turnpoints per task. It will be used to filter out flights that do not intersect ALL turnpoints for a task.</div><div>The second "inter" query performs the actual intersection and count the number of intersected turnpoints for a flight-task pair</div>
<div>Finally, the outer query makes a join between the 2 first CTE tables and filters rows to keep only flights intersecting ALL turnpoints.</div><div><font face="courier new, monospace"><br></font></div><div>
<div><font face="courier new, monospace">with tasks as (</font></div><div><font face="courier new, monospace"><span style="white-space:pre-wrap"> </span>select <a href="http://t.id" target="_blank">t.id</a>, count(<a href="http://tu.id" target="_blank">tu.id</a>) as num_turnpoint</font></div>
<div><font face="courier new, monospace"><span style="white-space:pre-wrap"> </span>from task t, turnpoint tu</font></div><div><font face="courier new, monospace"><span style="white-space:pre-wrap"> </span>where <a href="http://t.id" target="_blank">t.id</a> = tu.task_id</font></div>
<div><font face="courier new, monospace"><span style="white-space:pre-wrap"> </span>group by <a href="http://t.id" target="_blank">t.id</a></font></div><div><font face="courier new, monospace"><span style="white-space:pre-wrap"> </span>order by <a href="http://t.id" target="_blank">t.id</a></font></div>
<div><font face="courier new, monospace">), inter as (</font></div><div><font face="courier new, monospace"><span style="white-space:pre-wrap"> </span>select <a href="http://t.id" target="_blank">t.id</a> as taskid, <a href="http://f.id" target="_blank">f.id</a> as flightid, count(<a href="http://p.id" target="_blank">p.id</a>) as cnt</font></div>
<div><font face="courier new, monospace"><span style="white-space:pre-wrap"> </span>from tasks t, flight f, turnpoint p</font></div><div><font face="courier new, monospace"><span style="white-space:pre-wrap"> </span>where <a href="http://t.id" target="_blank">t.id</a> = p.task_id</font></div>
<div><font face="courier new, monospace"><span style="white-space:pre-wrap"> </span>and st_intersects(f.geometry, p.geometry)<span style="white-space:pre-wrap"> </span></font></div><div><font face="courier new, monospace"><span style="white-space:pre-wrap"> </span>group by <a href="http://t.id" target="_blank">t.id</a>, <a href="http://f.id" target="_blank">f.id</a></font></div>
<div><font face="courier new, monospace">) select i.* </font></div><div><font face="courier new, monospace">from inter i, tasks t</font></div><div><font face="courier new, monospace">where i.cnt = t.num_turnpoint</font></div>
<div><font face="courier new, monospace">and <a href="http://t.id" target="_blank">t.id</a> = i.taskid;</font></div></div><div><br></div><div>Nicolas</div></div><div class="gmail_extra"><br><br><div class="gmail_quote"><div>
<div>On 24 December 2012 13:17, Tom Payne <span dir="ltr"><<a href="mailto:twpayne@gmail.com" target="_blank">twpayne@gmail.com</a>></span> wrote:<br>
</div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div><div dir="ltr">Hi,<div><br></div><div><br></div><div>tl;dr How to select rows that intersect _all_ geometries defined in a one-to-many relationship?</div>
<div><br></div><div><br></div><div>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><br></div><div><br></div><div>My schema looks like:</div><div><br></div><div>-- a task has many turnpoints</div><div><div>CREATE TABLE task (</div><div><span style="white-space:pre-wrap"> </span>id INTEGER NOT NULL, </div>
<div><span style="white-space:pre-wrap"> </span>PRIMARY KEY (id)<br></div><div>);</div><div><br></div><div>-- a turnpoint belongs to a task and has a geometry</div></div><div><div>CREATE TABLE turnpoint (</div>
<div><span style="white-space:pre-wrap"> </span>id INTEGER NOT NULL, </div><div><span style="white-space:pre-wrap"> </span>task_id INTEGER, </div><div><span style="white-space:pre-wrap"> </span>seq INTEGER NOT NULL, -- index within its task </div>
<div><span style="white-space:pre-wrap"> geom</span> geometry(POLYGON,-1) NOT NULL, <br></div><div><span style="white-space:pre-wrap"> </span>PRIMARY KEY (id), </div><div><span style="white-space:pre-wrap"> </span>UNIQUE (task_id, seq), </div>
<div><span style="white-space:pre-wrap"> </span>FOREIGN KEY(task_id) REFERENCES task (id)</div><div>);</div><div><br></div><div>-- a flight has a geometry</div><div><div>CREATE TABLE flight (</div><div><span style="white-space:pre-wrap"> </span>id INTEGER NOT NULL, </div>
<div><span style="white-space:pre-wrap"> </span>geom geometry(LINESTRING,-1) NOT NULL, <br></div><div><span style="white-space:pre-wrap"> </span>PRIMARY KEY (id)<br></div><div>);<br></div></div><div><br></div><div>
<br></div><div>What I want is:</div><div><br></div><div>(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><br></div><div>(2) a list of (<a href="http://task.id" target="_blank">task.id</a>, <a href="http://flight.id" target="_blank">flight.id</a>) pairs where the flight's geom intersects all of the task's turnpoints' geoms (intersecting with && is sufficient)</div>
<div><br></div><div><br></div><div>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><br></div><div>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><br>
</div><div><br></div><div>Many thanks for any help or pointers on how to achieve this with PostGIS.</div><div><br></div><div>Tom</div><div><br></div><div><br></div><div><br></div>
<div>P.S. Background information, probably not relevant to question:</div><div>- 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>- Each task typically has 2-5 turnpoints</div><div>- 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>
<br></div></div>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>
</blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>