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.
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)
GROUP BYis a bit useless here since there are only two lines and only one point of intersection.