Problems creating a polygon from a closed linestring

[mj10777]

I have a table of polygons created by gdal_polygonize.py

The original images were of Postal Code Borders with the Postal Code written in the middle.
This text showed up aa internal rings in the polygon.
For this reason I extracted the external ring, which returns a linestring.
Since I need the final result as a polygon, I tried using BuildArea (and later ST_BdPolyFromWKB) to 'cast' this to a polygon.
This has however always fails for reasons I cannot see.
The linestring is correct (first and last point are the same) and if I have read the documentation correctly
BuildArea and Polygonize should accept a single linestring.

In the end I added 'fnct_ExteriorRing2' with an integer parameter (0=linestring,1=polygon) which returns the desired Geometry type.
- InteriorRingN would also be a candidate for such an extension
-- and 'InteriorRings' could then be easily made to return all the Interior-Rings as a Multi-Linestring/Polygon

I am including the diff with 'fnct_ExteriorRing2' in case there is interest in using this.

BTW: the summary text of 'NumGeometries' is incorrect (html 4.0.0 and 4.1.0):
it should be 'return the number of Geometries' and not 'return the number of interiorRings'

Mark Johnson

[sandro furieri]

Hi Mark,

sorry for this delayed reply.

For this reason I extracted the external ring, which returns a linestring.
Since I need the final result as a polygon, I tried using BuildArea (and later ST_BdPolyFromWKB) to 'cast' this to a polygon.
This has however always fails for reasons I cannot see.
The linestring is correct (first and last point are the same) and if I have read the documentation correctly
BuildArea and Polygonize should accept a single linestring.

Yes, that's indisputably true; let's go to directly check using
a reasonably sized dataset taken from the wild.
So I've started my test by loading the Italian Local Council
Boundaries from ISTAT: http://www.istat.it/uploads/com2011.zip

This dataset contains about 8,100 MultiPolygons, but the vast
majority of Local Councils simply have one single Polygon.
Then I've created a further table just containing the Exterior
Ring (Linestring) for all single-Polygon Local Councils (they
are about 7,600):

CREATE TABLE com2 AS
SELECT nome_com, ST_ExteriorRing(geometry) AS geom
FROM com2011
WHERE ST_NumGeometries(geometry) = 1;

And finally I've invoked ST_BuildArea so to get back
yet again a Polygon from its Exterior Ring for each
Local Council:

CREATE TABLE com3 AS
SELECT nome_com, ST_BuildArea(geom) AS geom
FROM com2;

Let's now examine "com3" so to check the output from ST_BuildArea:

SELECT Count(*), GeometryType(geom) AS type
FROM com3
GROUP BY type;
----
9|NULL
7609|POLYGON

Provisional conclusion: ST_BuildArea() seems to work as expected
in many cases, but sometimes it will "mysteriously" fail (NULL).

Just a simple further check and I was finally able to discovered
why ST_BuildArea() sometimes returns NULL values:

SELECT nome_com
FROM com3
WHERE geom IS NULL
INTERSECT
SELECT nome_com
FROM com2011
WHERE ST_IsValid(geometry) = 0;
-----
Bogliasco
Bronte
Calasetta
Castellammare del Golfo
Giovinazzo
Isola Sant'Antonio
Rutigliano
Sannicandro di Bari
Tropea

Final conclusion: ST_BuidArea() will correctly refuse to return
a Polygon if the candidate Exterior Ring does contain some
invalidity cause (e.g. self-intersections, spikes and alike).
So after all it's not a a bug, and looks more like a good-minded
feature.
That's not at all surprising, once considered that SpatiaLite
simply delegates the actual task to reassemble Polygons from
Linestrings to GEOS, and GEOS is notoriously fussy about
invalidities.

 

In the end I added 'fnct_ExteriorRing2' with an integer parameter (0=linestring,1=polygon) which returns the desired Geometry type.
- InteriorRingN would also be a candidate for such an extension
-- and 'InteriorRings' could then be easily made to return all the Interior-Rings as a Multi-Linestring/Polygon

I am including the diff with 'fnct_ExteriorRing2' in case there is interest in using this.

Introducing further SQL functions just intended to suppress any
validity check (and thus prone to propagate invalidities in an
endless way) doesn't seems to be a good solution.
We have at least two better alternatives simply using the
currently available SQL functions:

A) carefully clean all invalidities using ST_MakeValid()

B) using ST_DissolveSegments() so to allow GEOS to correctly
   re-node any malformed Ring; e,g, as in:
  
CREATE TABLE com4 AS
SELECT nome_com, ST_BuildArea(ST_DissolveSegments(geom)) AS geom
FROM com2;

SELECT Count(*), GeometryType(geom) AS type
FROM com4
GROUP BY type;
----
7618|POLYGON

 

BTW: the summary text of 'NumGeometries' is incorrect (html 4.0.0 and 4.1.0):
it should be 'return the number of Geometries' and not 'return the number of interiorRings'

Many thanks for noticing this typo (it was there since a very
long time, I suppose since the earlier versions).
I've already corrected all affected HTML doc pages (v.3.x and v4.x).

bye,
Sandro