# Counting geometry intersections between two linestrings

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)
``````
• Why wouldn't this return one geometry? the red line intersects twice but at the same point, so there is technically only one intersection. Jun 22, 2020 at 8:52
• @DrorBogin Indeed it is logical that only one geometry is returned. In my case let's say the black line is a border, and the red one the path an individual follows through time. I want to be able to count how many times the border was crossed. So even if the geometrical logic makes sense it doesn't meet my need. Jun 23, 2020 at 7:07
• @Taras I just did, and edited my question accordingly, but unfortunately it doesn't change anything. And the `GROUP BY` is a bit useless here since there are only two lines and only one point of intersection. Jun 23, 2020 at 8:01

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:

• Thank you for the first option that I did not consider. However I get the same result in my case where it still counts only one occurence. Jun 23, 2020 at 7:03
• Yes. The second one works better because the first one only returns 1 as a result. But in my specific case I still get 1 as a result. I am editing my question to provide an example, with your second option. Jun 23, 2020 at 7:22