Wrong result using ST_Intersection with postgis
I am trying to do a simple intersection with postgis, but the result I get is wrong. There is a segment missing of about 7 meters at the start of the feature.
Here is the sql-command that I use for the intersection:
select ST_Intersection(t2.wkb_geometry,
t1.wkb_geometry), t2.sde_id as s_sde, t2.mtb_id,
ST_Length(ST_Intersection(t1.wkb_geometry,t2.wkb_geometry))
from
public.mountainbike_pavement_temp2 t1 inner join public.mountainbike_strecken_temp2 t2 on
t1.wkb_geometry && t2.wkb_geometry;
The result-length should be about 187 meters, but the result-length is only about 180 meters. I have tried a lot, but I cannot find any reason, why this happens, and thus how to fix it.
When I do this intersection in ArcGis, everything works as intended.
I tried this with postgres 9.6.15 and postgis 2.4
and also with
postgres 12.1 and postgis 3.0, same results.
Tables and data can be found here:
Tablenames: mountainbike_pavement_temp2 public.mountainbike_strecken_temp2
The distance of the first vertices from mountainbike_pavement_temp2 to mountainbike_strecken_temp2 is all 0, calculated with ST_Distance in postgis (see comment below). So I would assume, that there is an intersection.
The problem is that the pavement lines do not exactly coincide with the strecken lines. Specifically, they contain a vertex POINT ( -43663.049707713886 245429.5072260416 ) which does not lie on the strecken linestring. This means the intersection does not include the entire length of the strecken line.
The picture below shows this (with the point location magnified to show the difference). pavement is red, strecken is blue.
This may work in ArcGIS because it may use some snapping heuristic when computing the intersection.
