topogeo_addlinestring: error - geometry intersects edge

[amartin]

Just found a scenario where the order of calling topogeo_addlinestring affects the final result.

The queries

SELECT topogeo_addlinestring('alberca_topo',ST_GeomFromText('LINESTRING(493339.655027555 4364376.38759966, 493340.351099381 4364380.30551897, 493349.447316675 4364380.22546209, 493357.127624414 4364380.58832002, 493363.534047898 4364383.70829175, 493369.531348115 4364387.81043901, 493375.776788431 4364392.72706278, 493383.13466486 4364395.75384862)', 25830), 0.01);

SELECT topogeo_addlinestring('alberca_topo',ST_GeomFromText('LINESTRING(493340.350449389 4364380.30185901, 493340.351099381 4364380.30551897, 493349.447316675 4364380.22546209, 493357.127624414 4364380.58832002, 493363.534047898 4364383.70829175, 493369.531348115 4364387.81043901, 493375.776788431 4364392.72706278)', 25830), 0.01);

will create 2 topogeom objects.

The same queries in different order will result on an error (ERROR: Spatial exception - geometry intersects edge)

SELECT topogeo_addlinestring('alberca_topo',ST_GeomFromText('LINESTRING(493340.350449389 4364380.30185901, 493340.351099381 4364380.30551897, 493349.447316675 4364380.22546209, 493357.127624414 4364380.58832002, 493363.534047898 4364383.70829175, 493369.531348115 4364387.81043901, 493375.776788431 4364392.72706278)', 25830), 0.01);

SELECT topogeo_addlinestring('alberca_topo',ST_GeomFromText('LINESTRING(493339.655027555 4364376.38759966, 493340.351099381 4364380.30551897, 493349.447316675 4364380.22546209, 493357.127624414 4364380.58832002, 493363.534047898 4364383.70829175, 493369.531348115 4364387.81043901, 493375.776788431 4364392.72706278, 493383.13466486 4364395.75384862)', 25830), 0.01);

[strk]

The fact that query order affects result is not a bug, but a well known behavior. Incremental construction of the topology tries to keep the elements which were inserted before as immutable as possible, while is more liberal about modifying (snapping, for instance) the items inserted later.

The "geometry intersects edge", on the other hand, is a bug.

[strk]

NOTE: there's a distance of 0.0037 units between two close-by vertices in the two linestrings around 493340.35,4364380.30

[strk]

I take it back, it's not between the input, but between two vertices in the first geometry (the one which gets successfully added).

The snap tolerance you're passing (0.01) is higher than this distance between adjacent vertices of the first geometry, which makes a mess.

This is another case in which an analysis function telling you what's the closest non-zero distance between any two vertices of a geometry could help. Also, you may want to ST_Simplify your input geometries by the tolerance value, prior to pass to addLinestring.

Not sure if PostGIS should do this automatically.

Anyway for this case, one of the correctly computed set of edges to add from the second geometry becomes invalid once snapped to existing nodes. The snapping _moves_ one vertex to collapse onto another. It _may_ or may not be improved by disallowing snapping to a vertex of the input geometry.

It is basically this:

MULTILINESTRING((493339.655027555 4364376.38759966,493340.350449389 4364380.30185901),(493375.776788431 4364392.72706278,493383.13466486 4364395.75384862),(493340.350449389 4364380.30185901,493340.351099381 4364380.30551897,493349.447316675 4364380.22546209,493357.127624414 4364380.58832002,493363.534047898 4364383.70829175,493369.531348115 4364387.81043901,493375.776788431 4364392.72706278))

Snapped to this:

MULTIPOINT(493340.350449389 4364380.30185901,493375.776788431 4364392.72706278)

[strk]

A way to compute the minimum distance between any two points:

with 
 inp as ( select YOUR_GEOM_HERE as g ), 
 d as ( select (st_dumppoints(g)).* from inp ), 
 dist as (  select st_distance(a.geom,b.geom) as distance from d a, d b
 where b.path[1] > a.path[1] ) 
 select min(distance) from dist where distance > 0;

Maybe such function should be made available to simplify picking a tolerance for topologies. The TopoGeo_add* functions could refuse to run if the given tolerance is > the geometry possibility to hold it. But running on every attempt would degrade performance a lot, so probably better to do it outside…

[amartin]

Thanks for the fast response!.

Preprocessing the geometry with

ST_SimplifyPreserveTopology(:geom, 0.01)

did the trick for this case.

[amartin]

Just found another example of the topogeo_addlinestring: error - geometry intersects edge error.

I this case minimum distance between points is > topology tolerance.

The offending geometries are (SRID: 25830)

MULTIPOLYGON(((308602.91 4614696.3,308609.22 4614708.19,308619.74 4614723.97,308630.98 4614746.21,308636.76 4614764.04,308641.05 4614779.52,308652.12 4614791.86,308670.07 4614797.76,308686.51 4614795.59,308699.72 4614771.95,308710.27 4614752.2,308602.91 4614696.3)))

and

MULTILINESTRING((308609.32 4614708.34,308609.22 4614708.19,308619.74 4614723.97,308630.98 4614746.21,308636.76 4614764.04,308641.05 4614779.52,308652.12 4614791.86,308670.07 4614797.76,308686.51 4614795.59,308699.72 4614771.95,308710.27 4614752.2),(308710.27 4614752.2,308609.32 4614708.34))

Test case:

SELECT CreateTopology('test_topo', 25830, 0.01);
SELECT AddTopoGeometryColumn('test_topo', 'public', 'test_lines', 'geom', 'LINE'); -- 1
SELECT AddTopoGeometryColumn('test_topo', 'public', 'test_polygons', 'geom', 'POLYGON'); -- 2


SELECT ToTopoGeom (st_geomfromtext('MULTIPOLYGON(((308602.91 4614696.3,308609.22 4614708.19,308619.74 4614723.97,308630.98 4614746.21,308636.76 4614764.04,308641.05 4614779.52,308652.12 4614791.86,308670.07 4614797.76,308686.51 4614795.59,308699.72 4614771.95,308710.27 4614752.2,308602.91 4614696.3)))', 25830), 'test_topo', 2, 0.01);
WITH 
  inp as (SELECT st_geomfromtext('MULTILINESTRING((308609.32 4614708.34,308609.22 4614708.19,308619.74 4614723.97,308630.98 4614746.21,308636.76 4614764.04,308641.05 4614779.52,308652.12 4614791.86,308670.07 4614797.76,308686.51 4614795.59,308699.72 4614771.95,308710.27 4614752.2),(308710.27 4614752.2,308609.32 4614708.34))', 25830) as g),
  topo as (SELECT ToTopoGeom(g, 'test_topo', 1, 0.01) as t FROM inp)
  SELECT st_astext(t) from topo ;

test fails with error on both postgis 2.0.2 & 2.0.3

ERROR:  Spatial exception - geometry intersects edge 2
CONTEXT:  PL/pgSQL function topogeo_addlinestring(character varying,geometry,double precision) line 124 at assignment
PL/pgSQL function totopogeom(geometry,character varying,integer,double precision) line 100 at FOR over SELECT rows

Topology tolerance = 0.01

Minimum distance between any two points on both geometries = 0.180277563327628

with 
  inp1 as ( select st_geomfromtext('MULTIPOLYGON(((308602.91 4614696.3,308609.22 4614708.19,308619.74 4614723.97,308630.98 4614746.21,308636.76 4614764.04,308641.05 4614779.52,308652.12 4614791.86,308670.07 4614797.76,308686.51 4614795.59,308699.72 4614771.95,308710.27 4614752.2,308602.91 4614696.3)))', 25830) as g ), 
  inp2 as (select  st_geomfromtext('MULTILINESTRING((308609.32 4614708.34,308609.22 4614708.19,308619.74 4614723.97,308630.98 4614746.21,308636.76 4614764.04,308641.05 4614779.52,308652.12 4614791.86,308670.07 4614797.76,308686.51 4614795.59,308699.72 4614771.95,308710.27 4614752.2),(308710.27 4614752.2,308609.32 4614708.34))', 25830) as g),
  inp as (select st_collect(inp1.g, inp2.g) as g from inp1, inp2),
  d as ( select (st_dumppoints(g)).* from inp ), 
  dist as (  select st_distance(a.geom,b.geom) as distance from d a, d b
  where b.path[1] > a.path[1] ) 
  select min(distance) from dist where distance > 0;

[strk]

The testcase in comment:8 is not self-contained (references an uncreated "test_lines").

[amartin]

Updated complete test cas:

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
SET search_path TO public, topology;

CREATE TABLE test_lines (id int4);
CREATE TABLE test_polygons (id int4);

SELECT CreateTopology('test_topo', 25830, 0.01);
SELECT AddTopoGeometryColumn('test_topo', 'public', 'test_lines', 'geom', 'LINE'); -- 1
SELECT AddTopoGeometryColumn('test_topo', 'public', 'test_polygons', 'geom', 'POLYGON'); -- 2


SELECT ToTopoGeom (st_geomfromtext('MULTIPOLYGON(((308602.91 4614696.3,308609.22 4614708.19,308619.74 4614723.97,308630.98 4614746.21,308636.76 4614764.04,308641.05 4614779.52,308652.12 4614791.86,308670.07 4614797.76,308686.51 4614795.59,308699.72 4614771.95,308710.27 4614752.2,308602.91 4614696.3)))', 25830), 'test_topo', 2, 0.01);
WITH 
  inp as (SELECT st_geomfromtext('MULTILINESTRING((308609.32 4614708.34,308609.22 4614708.19,308619.74 4614723.97,308630.98 4614746.21,308636.76 4614764.04,308641.05 4614779.52,308652.12 4614791.86,308670.07 4614797.76,308686.51 4614795.59,308699.72 4614771.95,308710.27 4614752.2),(308710.27 4614752.2,308609.32 4614708.34))', 25830) as g),
  topo as (SELECT ToTopoGeom(g, 'test_topo', 1, 0.01) as t FROM inp)
  SELECT st_astext(t) from topo ;

Could not reproduce defect on postgis 2.3.0

[strk]

Good news, that 2.3.0 works fine. I guess the 2.2 branch is also fine. If you confirm, this can be closed as fixed as previous versions have a too different topology implementation to deal with.

[robe]

I didn't test but assume it works cause someone said it worked in 2.3