2

I am running PostgreSQL 11.2 and PostGIS 2.5.3

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

enter image description here

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. – Dror Bogin Jun 22 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. – GuiOm Clair Jun 23 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. – GuiOm Clair Jun 23 at 8:01
5

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.

input

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:

| improve this answer | |
  • 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. – GuiOm Clair Jun 23 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. – GuiOm Clair Jun 23 at 7:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.