[postgis-users] Method to remove overlaps in a layer

[Pierre Racine]

An even simpler example leads to another type of error:

SELECT CreateTopology('topo3',0, 10);

DROP TABLE IF EXISTS ovlp.test03;
CREATE TABLE ovlp.test03 AS
SELECT 1 id, ST_GeomFromText('POLYGON((0 1, 2 2, 2 0, 0 1))') geom
UNION ALL
SELECT 2 id, ST_GeomFromText('POLYGON((1 1, 3 2, 3 0, 1 1))') geom

CREATE TABLE ovlp.test03_topo (id integer);

SELECT AddTopoGeometryColumn('topo3', 'ovlp', 'test03_topo', 'topo', 'POLYGON'); -- 1

INSERT INTO ovlp.test03_topo (id, topo)
SELECT id, topology.toTopoGeom(geom, 'topo3', 1)
FROM ovlp.test03

gives:

********** Error **********

ERROR: SQL/MM Spatial exception - edge crosses node.
SQL state: P0001
Context: PL/pgSQL function "topogeo_addpoint" line 96 at assignment
PL/pgSQL function "topogeo_addlinestring" line 125 at assignment
SQL statement "SELECT array_cat(edges, array_agg(x)) FROM ( select topology.TopoGeo_addLinestring(atopology, rec.geom, tol) as x ) as foo"
PL/pgSQL function "topogeo_addpolygon" line 27 at assignment
SQL statement "INSERT INTO topo3.relation(topogeo_id, layer_id, element_type, element_id) SELECT 2, 1, 3, topogeo_addPolygon('topo3', '01030000000100000004000000000000000000F03F000000000000F03F0000000000000840000000000000004000000000000008400000000000000000000000000000F03F000000000000F03F'::geometry, 10);"
PL/pgSQL function "totopogeom" line 129 at EXECUTE statement

Pierre

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-
> bounces at postgis.refractions.net] On Behalf Of Pierre Racine
> Sent: Wednesday, April 11, 2012 2:40 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Method to remove overlaps in a layer
> 
> So I have been creating a very simple example based on the example in the
> toTopoGeom() documentation page:
> 
> http://www.postgis.org/documentation/manual-svn/toTopoGeom.html
> 
> SELECT CreateTopology('topo1',31467, 10);
> 
> CREATE TABLE ovlp.test02_topo (id integer);
> 
> SELECT AddTopoGeometryColumn('topo1', 'ovlp', 'test02_topo', 'topo',
> 'POLYGON');
> 
> INSERT INTO ovlp.test02_topo (id, topo)
> SELECT id, topology.toTopoGeom(geom, 'topo1', 1)
> FROM ovlp.test02
> 
> And I get:
> 
> ********** Error **********
> 
> ERROR: interpolate_point4d: invalid F (1)
> SQL state: XX000
> Context: PL/pgSQL function "st_modedgesplit" line 94 at assignment
> PL/pgSQL function "topogeo_addpoint" line 91 at assignment
> PL/pgSQL function "topogeo_addlinestring" line 132 at assignment
> SQL statement "SELECT array_cat(edges, array_agg(x)) FROM ( select
> topology.TopoGeo_addLinestring(atopology, rec.geom, tol) as x ) as foo"
> PL/pgSQL function "topogeo_addpolygon" line 27 at assignment
> SQL statement "INSERT INTO topo1.relation(topogeo_id, layer_id,
> element_type, element_id) SELECT 5, 1, 3, topogeo_addPolygon('topo1',
> '0103000020EB7A0000010000000800000000000080FD384B4100000000F46154
> 4100000000D84B4B41000000405069544100000000B55D4B41000000005C6354
> 4100000000BD594B4100000000965B544100000000C8694B4100000000625554
> 4100000080E15C4B4100000080474D544100000080ED404B41000000006C5054
> 4100000080FD384B4100000000F4615441'::geometry, 10);"
> PL/pgSQL function "totopogeom" line 129 at EXECUTE statement
> 
> I join a picture of the original layer to be converted to a topologic layer.
> 
> I'm still at beta 5... Should upgrading solve that particular problem?
> 
> Pierre
> 
> > > So what would be the normal/easiest steps to convert a messy polygon
> > coverage into a clean topology? Is it documented somewhere?
> > >
> > > My guess:
> > >
> > > 1- SET search_path TO topology,public;
> >
> > You shouldn't need this, when you load topology.sql you should
> > get "topology" already appended to the end of the search_path
> > associated with the database.
> >
> > > 2- SELECT CreateTopoGeom('test')
> >
> > Yep.
> >
> > > 3- SELECT toTopoGeom(geom, 'test', 1)
> > > FROM mymessyone;
> >
> > You didn't create a layer, see AddTopoGeometryColumn.
> > The third argument is a layer id, as returned by that function.
> >
> > > or
> > >
> > > SELECT ST_CreateTopoGeo('test', geom)
> > > FROM mymessyone;
> >
> > This one only works starting with an empty topology so
> > you'll need to pass it a full collection:
> >
> > SELECT ST_CreateTopoGeo('test', ST_Collect(geom)) FROM mymessyone;
> >
> > But I'd recommend using toTopoGeom instead, to keep the linking
> > between attributes and geometries.
> >
> > > What happens when a polygon to be added to the topology overlaps a
> > polygon already in the topology?
> >
> > Two overlapping rectangles would produce a total of 3 faces.
> > If you're returning TopoGeometry objects (your step 2) both
> > of them will be defined by 2 faces, having one face in common.
>