Database Reference
In-Depth Information
FROM chp03.rivers r1
JOIN chp03.rivers r2
ON ST_Intersects(r1.the_geom,
r2.the_geom)
WHERE r1.gid != r2.gid;
2. Youmayhastilyassumethatalloftheintersectionsaresinglepoints,butthis
is not the case—if you check the geometry type of the geometric intersec-
tionsusingthe
ST_GeometryType
function,youhavethreedifferentcases
of intersection, resulting in the following geometries:
• An
ST_POINT
geometryforasimpleintersectionbetweentwolinear
geometries.
• An
ST_MultiPoint
geometry, if two linear geometries intersect
each other at more points.
• An
ST_GeometryCollection
geometry in cases where the two
MultiLineString
objects intersect and share part of the line. In
suchacase,thegeometrycollectioniscomposedof
ST_Point
and/
or
ST_Line
geometries.
3. You can check the different cases with a query, shown as follows:
postgis_cookbook=# SELECT COUNT(*),
ST_GeometryType(ST_Intersection(r1.the_geom,
r2.the_geom)) AS geometry_type
FROM chp03.rivers r1
JOIN chp03.rivers r2
ON ST_Intersects(r1.the_geom,
r2.the_geom)
WHERE r1.gid != r2.gid
GROUP BY geometry_type;
count | geometry_type
-------+-----------------------
4 | ST_GeometryCollection
356 | ST_MultiPoint
1088 | ST_Point
(3 rows)
