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

[Nicolas Ribot]

(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
>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20121224/e06e5e4e/attachment.html>