In this post I want to have a look at the behaviour of the OpenGIS functions ST_Intersects, ST_Overlaps and ST_Intersection. The database used is of course PostgreSQL which gives us all we need when used together with PostGIS.
First let’s create three tables with geographic data, one for points, lines and polygons each:
CREATE TABLE example_points (
"id" SERIAL NOT NULL PRIMARY KEY,
"name" char(1) NOT NULL,
"the_geom" geometry NOT NULL
);
CREATE TABLE example_lines (
"id" SERIAL NOT NULL PRIMARY KEY,
"name" char(1) NOT NULL,
"the_geom" geometry NOT NULL
);
CREATE TABLE example_polygons (
"id" SERIAL NOT NULL PRIMARY KEY,
"name" char(1) NOT NULL,
"the_geom" geometry NOT NULL
);
Now let’s populate those tables with data, we’ll insert 3 points, 3 lines and 4 polygons:
-- some points
INSERT INTO example_points (name, the_geom) VALUES (
'A',
'POINT(6 1)'::geometry
), (
'B',
'POINT(5.5 4.5)'::geometry
), (
'C',
'POINT(4.5 5.5)'::geometry
);
-- some lines
INSERT INTO example_lines (name, the_geom) VALUES (
'D',
'LINESTRING(3 1, 5 1)'::geometry
), (
'E',
'LINESTRING(3 7, 5 5)'::geometry
), (
'F',
'LINESTRING(5 6, 5 8)'::geometry
);
-- some polygons
INSERT INTO example_polygons (name, the_geom) VALUES (
'G',
'POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))'::geometry
), (
'H',
'POLYGON((2.5 2.5, 2.5 4.5, 4.5 4.5, 4.5 2.5, 2.5 2.5))'::geometry
), (
'I',
'POLYGON((4 4, 4 6, 6 6, 6 4, 4 4))'::geometry
), (
'J',
'POLYGON((6 5, 6 7, 8 7, 8 5, 6 5))'::geometry
), (
'K',
'POLYGON((6 2, 6 4, 8 4, 8 2, 6 2))'::geometry
);
For further examinations let’s create a view containing all the above geometries:
CREATE VIEW example_geometries AS (
(
SELECT name, the_geom
FROM example_points
) UNION ALL (
SELECT name, the_geom
FROM example_lines
) UNION ALL (
SELECT name, the_geom
FROM example_polygons
)
);
If you cannot imagine the relationship of these entities, here is a picture showing the locations of each geometry:
The geometries we just inserted
Now where do these geometries intersect? Let’s create a table which we can use to diplay the intersection geometries:
CREATE TABLE example_intersections_a AS (
SELECT ST_Intersection(part_1.the_geom, part_2.the_geom)
FROM example_geometries AS part_1,
example_geometries AS part_2
WHERE part_1.name <> part_2.name
AND ST_Intersects(part_1.the_geom, part_2.the_geom)
);
-- add a primary key so we can display the data within QGIS
ALTER TABLE example_intersections_a ADD COLUMN id SERIAL;
ALTER TABLE example_intersections_a ADD PRIMARY KEY (id);
Here are the intersections:
The inserted geometries and their intersections (in green)
Note that we have 16 intersections of many kinds: Point with line, line with polygon, polygon with polygon, etc. All intersections are listed twice since if geometry X intersects with geometry Y, both X ∩ Y and Y ∩ X are counted.
ST_Intersection() actually gave us the geometries of the intersection, which can be of any geometry type, e.g. a polygon/polygon intersection can result in a point intersection, if the geometries share exactly one point (see the intersection of K with I).
Here is a tabular view of all the computation that took place to create the intersection table:
A visualisation of the computed iterations to find intersections
If you only want distinct intersections, use the DISTINCT SQL-Keyword:
CREATE TABLE example_intersections_b AS (
SELECT DISTINCT ST_Intersection(part_1.the_geom, part_2.the_geom)
FROM example_geometries AS part_1,
example_geometries AS part_2
WHERE part_1.name <> part_2.name
AND ST_Intersects(part_1.the_geom, part_2.the_geom)
);
-- add a primary key so we can display the data within QGIS
ALTER TABLE example_intersections_b ADD COLUMN id SERIAL;
ALTER TABLE example_intersections_b ADD PRIMARY KEY (id);
In our case there are 7 distinct intersections. If you expected 8, thinking X ∩ Y and Y ∩ X would only be counted once, you’re only halfway right: since the intersection of C with I and C with E are the same, only one get counted of these.
If you are interested in intersection of the same dimension (see OGC SPEC s2.1.1.1 – dimension is 0 for points, 1 for lines, 2 for polygons) of the geometries you compare, use ST_Overlaps() in the WHERE-clause instead of ST_Intersects():
CREATE TABLE example_intersections_c as (
SELECT DISTINCT ST_Intersection(part_1.the_geom, part_2.the_geom)
FROM example_geometries AS part_1,
example_geometries AS part_2
WHERE part_1.name <> part_2.name
AND ST_Overlaps(part_1.the_geom, part_2.the_geom)
);
-- add a primary key so we can display the data within QGIS
ALTER TABLE example_intersections_c ADD COLUMN id SERIAL;
ALTER TABLE example_intersections_c ADD PRIMARY KEY (id);
Selectoid's Weblog 