[postgis-users] Intersection testing with one-to-many relationships

[Tom Payne]

That's awesome Nicolas.

Many thanks and bonnes fêtes !

Tom


On 24 December 2012 15:30, Nicolas Ribot <nicolas.ribot at gmail.com> 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 <nicolas.ribot at gmail.com> 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 <twpayne at gmail.com> 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
>>> postgis-users at lists.osgeo.org
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>
>>>
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20121224/16a1190a/attachment.html>