Counting geometry intersections between two linestrings

Question

I am running PostgreSQL 11.2 and PostGIS 2.5.3

I have two linstrings intersecting each other like in the picture below:

The red line passes over itself and have vertices on, in order, A-B-C-B-D, so technically the red line intersects the black one twice, once from B to C and an second time from C to B.

I need to count how many times the red line intersects the black one.

I try ST_Numgeometries(ST_Intersection(red.geom, black.geom)) but only get 1 as a result. And obviously the ST_Intersection(red.geom, black.geom) returns only one geometry.

I can imagine convoluted ways to get the result I want, like for example decomposing the red line in simple lines AB, BC, CB and BD, and then run ST_Intersection, but since I want to run that in a spatial view I believe it will greatly slow down the process. Is there a simple way to achieve what I need, like a PostGIS function I didn't notice?

EDIT 1 Following the answer from Taras, here is an SQL example using his second option, which still returns 1 as a result :

WITH line1(geom) AS (VALUES ('LINESTRING(0 0, 0 1, 3 1, 0 1, 0 3)')),
line2(geom) AS (VALUES ('LINESTRING(1 0, 1 5)'))
SELECT SUM(ST_NumGeometries(ST_Intersection(l1.geom, l2.geom)))
FROM line1 AS l1
JOIN line2 AS l2 ON ST_Intersects(l1.geom, l2.geom)
GROUP BY st_intersection(l1.geom, l2.geom)

Answer 1

For your above example you need to apply one additional step before proceeding with the queries that I provided, for that please check these threads In PostGIS: how to split linestrings into their individual segments? and Splitting lines into basic segments at vertices with PostGIS?

---

Notice: The example below does not include breaking lines into sections at vertices.

Let's assume there are two line layers called "lines" (blue, 5 features) and "lines2" (brown, 1 feature), see image below.

To count how many times the blue line intersects the brown one try the following options.

Option 1. Returns a number of intersections considering the lines' geometry. Use when an exactness of lines' geometry plays a role.

SELECT COUNT()
FROM "lines" AS l1
JOIN "lines2" AS l2 ON st_intersects(l1.geometry, l2.geometry)
GROUP BY l1.geometry

Option 2. Gives a number of intersections when lines' geometry does not play a role.

SELECT SUM(st_numgeometries(st_intersection(l1.geometry, l2.geometry)))
FROM "lines" AS l1
JOIN "lines2" AS l2 ON st_intersects(l1.geometry, l2.geometry)
GROUP BY st_intersection(l1.geometry, l2.geometry)

OR simply

SELECT COUNT()
FROM "lines" AS l1
JOIN "lines2" AS l2 ON st_intersects(l1.geometry, l2.geometry)
GROUP BY st_intersection(l1.geometry, l2.geometry)

Option 3. By means of a MultiPoint

SELECT ST_NumGeometries(ST_Collect(st_intersection(l1.geometry, l2.geometry)))
FROM "lines" AS l1
JOIN "lines2" AS l2 ON st_intersects(l1.geometry, l2.geometry)
GROUP BY st_intersection(l1.geometry, l2.geometry)

---

References:

• Is it ok to group by geometry in PostGIS?
• PostGIS Docs | ST_NumGeometries()
• PostGIS Docs | ST_Collect()