[postgis-devel] How define a view that use a case operator for geometry field

aperi2007 aperi2007 at gmail.com
Fri Oct 28 09:48:10 PDT 2011


Hi,

using the postgis200-trunk for win32 from Robe experimental

I have an error on qgis, but I guess is a wrong defined view.

Infact I use this view:

{{{
CREATE VIEW other_schema.v_elemento_divisorio AS
SELECT
	a.oid as oid,
	a.coddivisorio as coddivisor,
	d.documentation as codtipo,
	a.codlinea as codlinea,
	a.codlineasoprasotto as codlineass,
	case
		when a.codlinea<>'' then b.geom::geometry(LINESTRING,3003)
		when a.codlineasoprasotto<>'' then c.geom::geometry(LINESTRING,3003)
	end as geom
FROM
	((( other_schema.elemento_divisorio a
     LEFT OUTER JOIN other_schema.linee_elementari b ON (a.codlinea = 
b.codlinea) )
     LEFT OUTER JOIN other_schema.geometria_lineare_ss c ON 
(a.codlineasoprasotto = c.codlineasoprasotto) )
     LEFT OUTER JOIN other_schema.zz_tipoelementodivisoriotype d ON 
(a.codtipo = d.value) )
;
}}}

The geometry column can came from one of two tables:
the "geometria_lineare_ss" table or from "linee_elementari" table both 
are table having a LINESTRING geomtype and 3003 as SRID.

After define the view I was able to add it to qgis canvas and see the 
geometry (linestring) draw in the mapcanvas.

But I have an error when try to do an identify on it.
The error from qgis is this:

{{{
1 cursor states lost.
SQL: CLOSE qgisf5
Result: 7 (ERROR:  current transaction is aborted, commands ignored 
until end of transaction block
)
}}}

I see also the log of postgis and notice that it report a mixed srs.

 >2011-10-28 18:24:58 CEST ERROR:  Operation on mixed SRID geometries
 >2011-10-28 18:24:58 CEST STATEMENT:  fetch forward 200 from qgisf5
 >2011-10-28 18:24:58 CEST ERROR:  current transaction is aborted, 
commands ignored until end of transaction block
 >2011-10-28 18:24:58 CEST STATEMENT:  CLOSE qgisf5

To try to understand why it report two mixed SRID I see the 
geometry_columns and I found that the geometry_columns don't understand 
the real srid of the view (sigh).

select * from geometry_columns;

=>
f_table_name = v_elemento_divisorio
f_geometry_column = geom
coord_dimension = 2
srid = 0  (!)
type = geometry

Is correct that geometry_columns don't report the geometry-type and the 
srid of a view defined like this ?

Thx,

Andrea.




More information about the postgis-devel mailing list