[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