Database Reference
In-Depth Information
4. First,trytocomputetheintersectionforjustthefirsttwocases(intersections
composed of the
ST_Point
and
ST_MultiPoint
geometries). Just gen-
erate a table with the
Point
and
MultiPoint
geometries, excluding the
records that have an intersection composed of a geometric collection. By
executing the following commands, 1444 of the 1448 records are imported
(the four records with geometry collections are ignored using the
ST_GeometryType
function):
postgis_cookbook=# CREATE TABLE
chp03.intersections_simple AS
SELECT r1.gid AS gid1, r2.gid AS
gid2,ST_Multi(ST_Intersection(r1.the_geom,r2.the_geom))::geometry(MultiPoint,
4326) AS the_geom
FROM chp03.rivers r1
JOIN chp03.rivers r2
ON ST_Intersects(r1.the_geom,
r2.the_geom)
WHERE r1.gid != r2.gid
AND
ST_GeometryType(ST_Intersection(r1.the_geom,r2.the_geom))
!= 'ST_GeometryCollection';
5. Incaseyouwanttoimportthepointsfromthegeometrycollection,too(but
just the points, ignoring the eventual linestrings), one way to go is by us-
ingthe
ST_CollectionExtract
functioninthecontextofa
SELECT CASE
PostgreSQL conditional statement; this way you can import all the 1448 in-
tersections, as follows:
postgis_cookbook=# CREATE TABLE
chp03.intersections_all AS
SELECT gid1, gid2,
the_geom::geometry(MultiPoint, 4326) FROM
(
SELECT r1.gid AS gid1, r2.gid AS gid2,
CASE
WHEN
ST_GeometryType(ST_Intersection(r1.the_geom,
r2.the_geom)) != 'ST_GeometryCollection'
