Creating new table of buffered geometries yields "RecoverGeometryColumn(): validation failed" error

[Peter]

Hi everybody,

I have a table of polylines that I am trying to buffer into a table of polygons:

CREATE TABLE mypolys10 AS
SELECT id, ST_Buffer(p.GEOMETRY, 10) AS GEOMETRY
FROM mypolys p;

SELECT RecoverGeometryColumn('mypolys10', 'GEOMETRY', 2285, 'MULTIPOLYGON', 'XY')

This first part appears to succeed and creates my table. However, the second query fails with a an error "RecoverGeometryColumn(): validation failed".

I found this page (http://lists.gfoss.it/pipermail/gfoss/2011-April/018772.html), and through my broken understanding of Spanish and Google's less-broken understanding of Italian made out that I should try to identify bogus geometries like this:

$ /usr/local/bin/spatialite Prioritization.sqlite 
SpatiaLite version ..: 3.0.1 Supported Extensions:
- 'VirtualShape' [direct Shapefile access]
- 'VirtualDbf' [direct DBF access]
- 'VirtualXL' [direct XLS access]
- 'VirtualText' [direct CSV/TXT access]
- 'VirtualNetwork' [Dijkstra shortest path]
- 'RTree' [Spatial Index - R*Tree]
- 'MbrCache' [Spatial Index - MBR cache]
- 'VirtualSpatialIndex' [R*Tree metahandler]
- 'VirtualFDO' [FDO-OGR interoperability]
- 'SpatiaLite' [Spatial SQL - OGC]
PROJ.4 version ......: Rel. 4.8.0, 6 March 2012
GEOS version ........: 3.3.5-CAPI-1.7.5
SQLite version ......: 3.7.13
Enter ".help" for instructions
spatialite> SELECT * FROM mypolys10
       ...> WHERE ST_GeometryType(GEOMETRY) IS NULL;
spatialite>

Unfortunately, that doesn't appear to be the issue, and so I am stumped. Any other clues as to what might be failing?

Cheers,

Peter

[Peter]

The plot thickens. It appears as my buffer operation is returning both multipolygons and polygons,:

spatialite> SELECT DISTINCT
       ...> ST_GeometryType(p.GEOMETRY), ST_Srid(p.GEOMETRY), CoordDimension(p.GEOMETRY) FROM mypolys p;
MULTILINESTRING|2285|XY

Okay, only multilines in my table. But look when I buffer them... 

spatialite> SELECT DISTINCT
       ...> ST_GeometryType(p.GEOMETRY), ST_GeometryType(ST_Buffer(p.GEOMETRY, 10)), ST_Srid(p.GEOMETRY), CoordDimension(p.GEOMETRY) FROM mypolys p;
MULTILINESTRING|POLYGON|2285|XY
MULTILINESTRING|MULTIPOLYGON|2285|XY

Is this normal behavior for ST_Buffer?

Cheers,

Peter

[sandro furieri]

Hi Peter,

it's the regular behavior of ST_Buffer(); usually a Polygon
will be returned, but sometimes a MultiPolygon.
it substantially depends on the very specific shape of
the input geometry being buffered.

anyway resolving this annoying inconsistency is really
easy: you simply have to explicitly cast any result to
the same geometry type. e.g.

CREATE TABLE mypolys10 AS
SELECT id, CastToMultiPolygon(ST_Buffer(geom, 10)) AS geom
FROM mypolys p;

bye Sandro

[Peter]

Thank you, Sandro! That works perfectly.

Cheers,

Peter