# 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. Commented 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. Commented 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. Commented 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. Commented 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. Commented Jun 23, 2020 at 7:22