[postgis-users] ST_CreateTopoGeo

Sandro Santilli strk at kbt.io
Wed Mar 4 03:23:30 PST 2020

On Wed, Mar 04, 2020 at 07:11:30AM +0000, paul.malm at lfv.se wrote:
> In both topo1- and topo2.face shows a box where Egypt is located, as for all other countries (type of bounding box?).
> In both topo1- and topo2.edge_data shows all countries as well as Egypt.

Ok, so the topology was fully constructed, nothing missing so far.
Additional checks you may run:

  1. Visually inspect all face geometries (load them in a new table, or
     view, or use QGIS TopologyViewer which does it for you).
     [ NOTE: it takes ST_GetFaceGeometry to see the geoms, otherwise,
       as you found out, you only see the bounding box ]

  2. Run: select * from topology.validatetopology('topo1');

> Egypt is however intersecting an island in topo2.

"Egypt", as you call it, does not exist anymore at this stage.
It's only in your brain.  Same goes for what you call "an island".

What exists in this stage is a set of connected edges, nodes and faces.
There's no overlap between faces, no intersection other than sharing edges.

> The island is formed like a banana in topo1 (not intersecting the
> Egypt polygon) and like a triangle in topo2, due to the simplification
> (5000 m). Therefore a headland in the Egypt-polygon is intersecting the
> island in topo2.edge_data.

What you are describing is that a face in the resulting topology is
taking the space which was occupied by both the original Egypt polygon
and the original Island polygon.

Since neither Egypt nor Island exist, it's up to you to decide how
to make them up: do you want to assign that face to Egypt or to
Island? Or you want to further split that face into two, to assign
a slice of it to each of the "features" you want to build ?

The TopoGeometry object is what allows you to build a single entity
out of a set of topology primitives (faces, for example), and is what
you were using in your later query.

> I think that's why I can't create a polygon geometry for Egypt. It is
> the same for Finland (also has an island that becomes intersecting the
> main polygon of Finland in topo2).

You CAN create a geometry, both for Egypt and for Findland, you just
have to decide which faces will take part of those definitions.

> WITH simple_face AS (
>        SELECT topology.st_getFaceGeometry('topo2', face_id) AS the_geom
>        FROM topo2.face
>        WHERE face_id > 0
> ) 
> UPDATE "simple_countries" d set geom = sf.the_geom
> FROM simple_face sf
> WHERE st_intersects(d.geom, sf.the_geom)
> AND st_area(st_intersection(sf.the_geom, d.geom))/st_area(sf.the_geom) > 0.5;

In the above query you are:

  1. extracting the geometries of _all_ faces
  2. updating "simple_countries" by assigning to its
     "geom" column a _single_ face from the set extracted
     in 1, even if multiple faces intersect.

If more than one face interests Egypt, you'll only get one, which
explains why you're missing the whole polygon.

Instead, you'll want to make sure each and every face is assigned
to one (and only one, if you want to avoid overlaps) final geometry.


More information about the postgis-users mailing list