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

[Tom Payne]

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