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