Invalid edge (no two distinct vertices exist)

[andehhh]

Picking up following conversation (10:47:49 - 12:02:32) on #postgis: ​http://irclogs.geoapt.com/postgis/%23postgis.2017-02-09.log

I am getting following error message while updating geometries within a topology.

SQL Error [XX000]: ERROR: Invalid edge (no two distinct vertices exist)
Where: PL/pgSQL function topology.totopogeom(geometry,topology.topogeometry,double precision) line 111 at FOR over SELECT rows

Following subset of the data can be used to reproduce the error:

create table test.mini (
    id serial, 
    src text, 
    geom geometry(Multipolygon,4326)
);

-- fill the table
insert into test.mini (src, geom) 
    values 
        ('orig', st_geomfromewkt('SRID=4326;MULTIPOLYGON(((-5.113 50.2643862560647,-5.113006 50.264387,-5.114048 50.264377,-5.114106 50.264376,-5.11425 50.264374,-5.114291 50.264374,-5.114854 50.264367,-5.115803 50.264359,-5.115859 50.264359,-5.11626 50.264356,-5.116537 50.264353,-5.116581 50.264353,-5.118384 50.264338,-5.118422 50.264338,-5.118885 50.264334,-5.119122 50.264332,-5.11915 50.264207,-5.119288 50.2636,-5.119185 50.263454,-5.118613 50.263448,-5.118556 50.263293,-5.118641 50.263161,-5.11872 50.263037,-5.11906 50.263071,-5.119224 50.263074,-5.119273 50.263075,-5.119515 50.26308,-5.119295 50.262249,-5.119283 50.262202,-5.119242 50.262048,-5.119339 50.26202,-5.119398 50.262003,-5.120092 50.261803,-5.120125 50.261794,-5.120956 50.261554,-5.120869 50.261412,-5.120516 50.26083,-5.12029 50.260599,-5.120192 50.260499,-5.12009 50.260394,-5.120005 50.260055,-5.119984 50.259972,-5.119871 50.259712,-5.119971 50.259505,-5.120458 50.259197,-5.120363 50.259127,-5.11921 50.258276,-5.118999 50.257862,-5.118948 50.257652,-5.118934 50.257593,-5.125247 50.256854,-5.125631 50.257041,-5.127288 50.25785,-5.127077 50.258466,-5.126626 50.259523,-5.126434 50.259988,-5.126492 50.260494,-5.126249 50.260532,-5.125334 50.260676,-5.123779 50.259652,-5.122835 50.259806,-5.123177 50.260209,-5.123252 50.260297,-5.123723 50.260438,-5.123965 50.26051,-5.12523 50.260692,-5.125194 50.261171,-5.125172 50.261466,-5.125409 50.262131,-5.12543 50.262461,-5.125433 50.262507,-5.125451 50.262781,-5.125472 50.263103,-5.125343 50.263377,-5.125208 50.263666,-5.125185 50.263703,-5.125167 50.263732,-5.124252 50.265221,-5.125725 50.265752,-5.125814 50.265784,-5.125852 50.265798,-5.126139 50.265901,-5.12669 50.266094,-5.127098 50.266236,-5.129076 50.266928,-5.129183 50.266965,-5.129961 50.267237,-5.13 50.267251,-5.131081 50.26763,-5.132104 50.268,-5.134811 50.268861,-5.1349 50.2688832940013,-5.1349 50.2695,-5.113 50.2695,-5.113 50.2643862560647)))')),
        ('orig', st_geomfromewkt('SRID=4326;MULTIPOLYGON(((-5.12523 50.260692,-5.125334 50.260676,-5.126249 50.260532,-5.126492 50.260494,-5.126434 50.259988,-5.126626 50.259523,-5.127077 50.258466,-5.127288 50.25785,-5.125631 50.257041,-5.125247 50.256854,-5.125119 50.256791,-5.124987 50.256727,-5.12285 50.25617,-5.122946 50.25607,-5.123096 50.255991,-5.123756 50.255915,-5.123967 50.255802,-5.123937 50.255409,-5.12404 50.255148,-5.123956 50.25483,-5.124106 50.254415,-5.124191 50.254237,-5.124236 50.254143,-5.124531 50.253528,-5.125362 50.25229,-5.125764 50.251672,-5.125907 50.250856,-5.125861 50.250695,-5.125753 50.250318,-5.12555616566265 50.25,-5.1349 50.25,-5.1349 50.2688832940013,-5.134811 50.268861,-5.132104 50.268,-5.131081 50.26763,-5.13 50.267251,-5.129961 50.267237,-5.129183 50.266965,-5.129076 50.266928,-5.127098 50.266236,-5.12669 50.266094,-5.126139 50.265901,-5.125852 50.265798,-5.125814 50.265784,-5.125725 50.265752,-5.124252 50.265221,-5.125167 50.263732,-5.125185 50.263703,-5.125208 50.263666,-5.125343 50.263377,-5.125472 50.263103,-5.125451 50.262781,-5.125433 50.262507,-5.12543 50.262461,-5.125409 50.262131,-5.125172 50.261466,-5.125194 50.261171,-5.12523 50.260692)))')),
        ('orig', st_geomfromewkt('SRID=4326;MULTIPOLYGON(((-5.12555616566265 50.25,-5.125753 50.250318,-5.125861 50.250695,-5.125907 50.250856,-5.125764 50.251672,-5.125362 50.25229,-5.124531 50.253528,-5.124236 50.254143,-5.124191 50.254237,-5.124106 50.254415,-5.123956 50.25483,-5.12404 50.255148,-5.123937 50.255409,-5.123967 50.255802,-5.123756 50.255915,-5.123096 50.255991,-5.122946 50.25607,-5.12285 50.25617,-5.124987 50.256727,-5.125119 50.256791,-5.125247 50.256854,-5.118934 50.257593,-5.118948 50.257652,-5.118999 50.257862,-5.11921 50.258276,-5.120363 50.259127,-5.120458 50.259197,-5.119971 50.259505,-5.119871 50.259712,-5.119984 50.259972,-5.120005 50.260055,-5.12009 50.260394,-5.120192 50.260499,-5.12029 50.260599,-5.120516 50.26083,-5.120869 50.261412,-5.120956 50.261554,-5.120125 50.261794,-5.120092 50.261803,-5.119398 50.262003,-5.119339 50.26202,-5.119242 50.262048,-5.119283 50.262202,-5.119295 50.262249,-5.119515 50.26308,-5.119273 50.263075,-5.119224 50.263074,-5.11906 50.263071,-5.11872 50.263037,-5.118641 50.263161,-5.118556 50.263293,-5.118613 50.263448,-5.119185 50.263454,-5.119288 50.2636,-5.11915 50.264207,-5.119122 50.264332,-5.118885 50.264334,-5.118422 50.264338,-5.118384 50.264338,-5.116581 50.264353,-5.116537 50.264353,-5.11626 50.264356,-5.115859 50.264359,-5.115803 50.264359,-5.114854 50.264367,-5.114291 50.264374,-5.11425 50.264374,-5.114106 50.264376,-5.114048 50.264377,-5.113006 50.264387,-5.113 50.2643862560647,-5.113 50.25,-5.12555616566265 50.25)),((-5.125334 50.260676,-5.12523 50.260692,-5.123965 50.26051,-5.123723 50.260438,-5.123252 50.260297,-5.123177 50.260209,-5.122835 50.259806,-5.123779 50.259652,-5.125334 50.260676)))')),
        ('edited', st_geomfromewkt('SRID=4326;MULTIPOLYGON(((-5.121593 50.260234,-5.124099 50.260207,-5.124068 50.258374,-5.12517 50.258654,-5.126523 50.259773,-5.126434 50.259988,-5.126492 50.260494,-5.126249 50.260532,-5.125334 50.260676,-5.125194 50.261171,-5.125172 50.261466,-5.125409 50.262131,-5.12543 50.262461,-5.125433 50.262507,-5.125451 50.262781,-5.125472 50.263103,-5.125343 50.263377,-5.125208 50.263666,-5.125185 50.263703,-5.125167 50.263732,-5.124252 50.265221,-5.125725 50.265752,-5.125814 50.265784,-5.125852 50.265798,-5.126139 50.265901,-5.12669 50.266094,-5.127098 50.266236,-5.129076 50.266928,-5.129183 50.266965,-5.129961 50.267237,-5.13 50.267251,-5.131081 50.26763,-5.132104 50.268,-5.134811 50.268861,-5.1349 50.2688832940013,-5.1349 50.2695,-5.113 50.2695,-5.113 50.2643862560647,-5.113006 50.264387,-5.114048 50.264377,-5.114106 50.264376,-5.11425 50.264374,-5.114291 50.264374,-5.114854 50.264367,-5.115803 50.264359,-5.115859 50.264359,-5.11626 50.264356,-5.116537 50.264353,-5.116581 50.264353,-5.117946 50.264342,-5.118384 50.264338,-5.118422 50.264338,-5.118885 50.264334,-5.120389 50.264424,-5.120417 50.264299,-5.120555 50.263692,-5.120452 50.263546,-5.118613 50.263448,-5.118556 50.263293,-5.118641 50.263161,-5.11872 50.263037,-5.11906 50.263071,-5.119224 50.263074,-5.119273 50.263075,-5.119515 50.26308,-5.119295 50.262249,-5.119283 50.262202,-5.119242 50.262048,-5.119339 50.26202,-5.119398 50.262003,-5.120092 50.261803,-5.120125 50.261794,-5.120956 50.261554,-5.120869 50.261412,-5.120516 50.26083,-5.120486 50.260799,-5.12029 50.260599,-5.120192 50.260499,-5.12009 50.260394,-5.120005 50.260055,-5.119984 50.259972,-5.119871 50.259712,-5.119971 50.259505,-5.120458 50.259197,-5.120363 50.259127,-5.11921 50.258276,-5.120889 50.257364,-5.122878 50.258209,-5.121593 50.260234)))')),
        ('edited', st_geomfromewkt('SRID=4326;MULTIPOLYGON(((-5.125194 50.261171,-5.125334 50.260676,-5.126249 50.260532,-5.126492 50.260494,-5.126434 50.259988,-5.126523 50.259773,-5.126626 50.259523,-5.127077 50.258466,-5.127288 50.25785,-5.125631 50.257041,-5.125247 50.256854,-5.125119 50.256791,-5.124987 50.256727,-5.12285 50.25617,-5.122946 50.25607,-5.123096 50.255991,-5.123756 50.255915,-5.123967 50.255802,-5.123937 50.255409,-5.12404 50.255148,-5.123956 50.25483,-5.124106 50.254415,-5.124191 50.254237,-5.124236 50.254143,-5.124531 50.253528,-5.125362 50.25229,-5.125764 50.251672,-5.125907 50.250856,-5.125861 50.250695,-5.125753 50.250318,-5.12555616566265 50.25,-5.1349 50.25,-5.1349 50.2688832940013,-5.134811 50.268861,-5.132104 50.268,-5.131081 50.26763,-5.13 50.267251,-5.129961 50.267237,-5.129183 50.266965,-5.129076 50.266928,-5.127098 50.266236,-5.12669 50.266094,-5.126139 50.265901,-5.125852 50.265798,-5.125814 50.265784,-5.125725 50.265752,-5.124252 50.265221,-5.125167 50.263732,-5.125185 50.263703,-5.125208 50.263666,-5.125343 50.263377,-5.125472 50.263103,-5.125451 50.262781,-5.125433 50.262507,-5.12543 50.262461,-5.125409 50.262131,-5.125172 50.261466,-5.125194 50.261171)))')),
        ('edited', st_geomfromewkt('SRID=4326;MULTIPOLYGON(((-5.126523 50.259773,-5.125554 50.258972,-5.12517 50.258654,-5.124068 50.258374,-5.124099 50.260207,-5.121593 50.260234,-5.122878 50.258209,-5.120889 50.257364,-5.11921 50.258276,-5.120363 50.259127,-5.120458 50.259197,-5.119971 50.259505,-5.119871 50.259712,-5.119984 50.259972,-5.120005 50.260055,-5.12009 50.260394,-5.120192 50.260499,-5.12029 50.260599,-5.120486 50.260799,-5.120516 50.26083,-5.120869 50.261412,-5.120956 50.261554,-5.120125 50.261794,-5.120092 50.261803,-5.119398 50.262003,-5.119339 50.26202,-5.119242 50.262048,-5.119283 50.262202,-5.119295 50.262249,-5.119515 50.26308,-5.119273 50.263075,-5.119224 50.263074,-5.11906 50.263071,-5.11872 50.263037,-5.118641 50.263161,-5.118556 50.263293,-5.118613 50.263448,-5.120452 50.263546,-5.120555 50.263692,-5.120417 50.264299,-5.120389 50.264424,-5.118885 50.264334,-5.118422 50.264338,-5.118384 50.264338,-5.116581 50.264353,-5.116537 50.264353,-5.11626 50.264356,-5.115859 50.264359,-5.115803 50.264359,-5.114854 50.264367,-5.114291 50.264374,-5.11425 50.264374,-5.114106 50.264376,-5.114048 50.264377,-5.113006 50.264387,-5.113 50.2643862560647,-5.113 50.25,-5.12555616566265 50.25,-5.125753 50.250318,-5.125861 50.250695,-5.125907 50.250856,-5.125764 50.251672,-5.125362 50.25229,-5.124531 50.253528,-5.124236 50.254143,-5.124191 50.254237,-5.124106 50.254415,-5.123956 50.25483,-5.12404 50.255148,-5.123937 50.255409,-5.123967 50.255802,-5.123756 50.255915,-5.123096 50.255991,-5.122946 50.25607,-5.12285 50.25617,-5.124987 50.256727,-5.125119 50.256791,-5.125247 50.256854,-5.125631 50.257041,-5.127288 50.25785,-5.127077 50.258466,-5.126626 50.259523,-5.126523 50.259773)))'))
;

-- select topology.dropTopology('topo_test_mini');

-- create the topology
select topology.CreateTopology('topo_test_mini',
                               4326);

-- add the topogeom column
select topology.AddTopoGeometryColumn('topo_test_mini',
                                      'test',
                                      'mini',
                                      'topo_geom',
                                      'POLYGON');

-- import the polygons to the topology which represent the status of the topology before the geometries were edited                                      
update test.mini
set topo_geom = topology.toTopoGeom(geom, 'topo_test_mini', 1, 1e-6)
where src = 'orig';

-- import the polygons to the topology which represent the status of the topology after the geometries were edited - the error will occur here
update test.mini
set topo_geom = topology.toTopoGeom(geom, 'topo_test_mini', 1, 1e-6)
where src = 'edited';

PostGIS Version: POSTGIS="2.3.2 r15302" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3" LIBJSON="0.11.99" (core procs from "2.3.1 r15264" need upgrade) TOPOLOGY (topology procs from "2.3.1 r15264" need upgrade) RASTER (raster procs from "2.3.1 r15264" need upgrade)

Would there be implications which speak against allowing line-segments with a length of 0?

How else besides reducing the tolerance (e.g. to 1e-7) could one deal with such a problem? In my test data set this error occurs more than once. As this represents a real world example I am worried, that this might actually be a really common error to happen.

[strk]

you have a lot of "need upgrade" warnings in your postgis version, does it make any difference if you fix that ? At least the topology part. If reducing the tolerance works for you, go for it ! Line-segments with length 0 should not be a problem on their own. The message complains about a full edge with 0 length, possibly produced by an internal processing (hopefully changed by changing tolerance).

[andehhh]

I had the same problem before the upgrade to postgis 2.3.2. (any advice on how to fix that issue is very appreciated ) so the "need upgrade" messages should not be of relevance here.

If the tolerance is lowered then small gaps are not being automatically snapped, which is exactly what I want.

Would a 0-length edge (sorry, I thought all the time we are talking about line segments) be a (topological) problem? If yes, how about internally handling those errors? Is that even possible in a robust way?

[strk]

You had it *before*, but you didn't upgrade correctly yet so please do so before continuing with the debugging.

A 0-length edge would be an error, yes. The toTopoGeom (and the lower level TopoGeo_addXXX) function should not allow entering such edges. You can enable debugging to see what's *generating* that 0-length edge, if it's not in input. Yes, the code should ideally handle this case internally and simply skip 0-length edges.

[andehhh]

I found the upgrade error. I upgraded my main database but not the one I used for testing. Here is now the correct output from postgis_full_version():

POSTGIS="2.3.2 r15302" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3" LIBJSON="0.11.99" TOPOLOGY RASTER

The error prevails.

Which debugging would I have to enable to see those geometry constructions in detail? Changing log_min_messages to DEBUG5 would only log the error message and the statement but nothing more detailed.

[strk]

You'll need to rebuild postgis yourself passing —enable-debug to the ./configure step

[strk]

I cannot reproduce this issue as of r15330, can you ?

[strk]

Sorry I was wrong, I do can reproduce in r15330 too. I suggest you reduce the testcase to the smallest possible .sql file. Get rid of SRIDs, GeomFromText calls, temporary tables…

[strk]

This issue is also solved by merging 0911624e7570fb026cea56772ac746e64bf3a457 from the pull-request associated with bug #3838

[strk]

This is fixed as of r16035