[postgis-users] error: by topology.topogeometry

[Sandro Santilli]

On Tue, Mar 26, 2019 at 02:07:55PM +0000, paul.malm at lfv.se wrote:

> UPDATE merged SET topo_geom = topology.toTopoGeom(the_geom, 'roads_topo',  1,  0.001);

[...]

> I get the following error:
> ERROR: Error: SQL/MM Spatial exception - geometry crosses edge 8618 CONTEXT: PL/pgSQL-function topology.totopogeom(geometry,topology.topogeometry,double precision) line 112 at FOR over SELECT-rows PL/pgSQL-function topology.totopogeom(geometry,character varying,integer,double precision) line 88 at assignment
> 
> The error appears at different edges  when changing the tolerance on CreateTopology and toTopoGeom.
> I added the ST_makeValid function call on the input data without any success.
> Does anyone know what I can do?

My suggestion is to do the topology loading in chunks rather
than all a once, like:

  ALTER TABLE merged ADD id serial PRIMARY KEY;

  UPDATE merged
    SET topo_geom = topology.toTopoGeom(the_geom, 'roads_topo', 1)
    WHERE id >= 0 AND id < 100;

  UPDATE merged
    SET topo_geom = topology.toTopoGeom(the_geom, 'roads_topo', 1)
    WHERE id >= 100 AND id < 200;

  ...

Note I've removed the tolerance parameter, don't use it unless the
chunk fails and you're trying to move things a little bit.

Another thing to do, if a chunk fails, is to cleanup what you have
in the topology already, before trying again to move on.

My preferred method would be to use a wrapper function catching
exceptions and moving on if toTopoGeom fails. Often you'll find
that other geometries will get converted fine, so at the end
of the UPDATE call you'd have a number of "topo_geom" values
begin NULL (for the imports that failed) and a number of valid
ones, then you could try again to import the geometries after
some cleaning (and some times you don't even need a new cleaning
as the presence of more geometries would be enough to attract
vertices for snaps..)

--strk;