geometry_column wrong to establish the geometrytype in some views

[aperi2007]

Hi, In the new postgis200, I notice the geometry_columns view wrong to understand the geometrytype of some views a bit complex.

I have this view:

CREATE VIEW v_unita_volumetrica AS
SELECT
	a.id as oid,
	a.codunitavolumetrica as unita_vol,
	a.altezza_volume as alt_volume,
	a.quota_estrusione as quotestr,
	a.cod_edificio as cod_ed,
	a.cod_edificato_minore as cod_ed_min,
	c.documentation as tipoporz,
	a.codpoligono as codpoligon,
	a.codsuperficie as codsuperf,
	'SUOLO' as from,
	ST_Force_3D(b.geom) as geom
FROM
	(( unita_volumetrica a 
    LEFT OUTER JOIN suolo b ON (a.codpoligono = b.codpoligono) )
    LEFT OUTER JOIN zz_tipoporzioneunitavolumetricatype c ON (a.cod_tipo_porzione=c.value))
WHERE (
	a.codsuperficie = ''
)
UNION ALL
SELECT
	a.id as oid,
	a.codunitavolumetrica as unita_vol,
	a.altezza_volume as alt_volume,
	a.quota_estrusione as quotestr,
	a.cod_edificio as cod_ed,
	a.cod_edificato_minore as cod_ed_min,
	c.documentation as tipoporz,
	a.codpoligono as codpoligon,
	a.codsuperficie as codsuperf,
	'SUP3D' as from,
	ST_Force_3D(b.geom) as geom
FROM
	(( unita_volumetrica a 
    LEFT OUTER JOIN superficie_3d b ON (a.codsuperficie = b.codsuperficie) )
    LEFT OUTER JOIN zz_tipoporzioneunitavolumetricatype c ON (a.cod_tipo_porzione=c.value))
WHERE (
	a.codpoligono = ''
)
;

The geometry_view report me this having a GEOMETRY type. Instead it should report me a POLYGON type.

I notice this happened on all my views with the "UNION ALL " option. Other views not having the UNION ALL has correctly report the geometrytype.

I use postgis 2.0.0 trunk on windows. I don't remenber the trunk version, but is the version experimental released from Robe in the 4 august.

Regards.

[robe]

Andrea,

Unfortunately I fear that is one of those gotchas which I'll be explaining in the FOSS4G talk we are giving. When you apply a postgis function, there is no way for PostgreSLQ to know the geometry output type since our functions just return a goemetry.

The work around is to cast it.

Can you try that and see if it makes a difference?

on both

ST_Force_3D(b.geom)::geometry(POLYGONZ,yoursridhere)

Do that for both parts of your union all.

[aperi2007]

Hi Robe,

thx for workaround it work pretty well.

I guess this ticket could be closed.

[aperi2007]

Hi, I discover the work-around work with QGIS and with Kosmo-GIS but don't work with ogr2ogr (from gdal). Infact it still produce a wrong geometry type (linestring instead of MultiPolygonZ). It seem don't understand the cast. I try also with the last release (1.8.1) without success.

However now I study it better. So temporary I re-open the ticket.

Regards.

[aperi2007]

I definitively can close this ticket. I discovered that gdal give wrong result due to the size of the dataset. The dbf was bigger than 2GB. Reducing the dbf size gdal was begin to give right result. Is really curios that a over size on dbf could give as effect the geometry from polygon became linestring. However of course this is not a problem of postgis.