We have been using a self-spatial join of a linear PostGIS spatial layer to find intersections generated by the features of that layer.

To generate the self-spatial join, we used the ST_Intersects function. This way, we found that all of the features have at least an intersection in their respective geometries.

In the same self-spatial join context, we found out the intersections, using the ST_Intersection function.

The problem is that the computed intersections are not always single points. In fact, two intersecting lines can produce the origin for a single-point geometry (ST_Point) if the two lines just intersect once. But, the two intersecting lines can produce the origin for a point collection (ST_MultiPoint) or even a geometric collection if the two lines intersect at more points and/or share common parts.

As our target was to compute all the point intersections (ST_Point and ST_MultiPoint) using the ST_GeometryType function, we filtered out the values using a SQL SELECT CASE construct where the feature had a GeometryCollection geometry, for which we extracted just the points (and not the eventual linestrings) using the ST_CollectionExtract function (parameter type = 1) from the composing collections.

Finally, we compared the two result sets, both with plain SQL and a desktop GIS. The intersecting points computed filtered out the geometric collections from the output geometries and the intersecting points computed from all the geometries generated from the intersections, including the GeometryCollection features.