[GIS] Force SRID in views in Postgis 2.0

Question

I have a very tricky problem:
I have a Postgis 2.0 database, used as datastore for Geoserver 2.1.3

I have a table with a geometry column (named shape), with SRID 4326

I have created a view like this:

CREATE VIEW pippo AS SELECT st_geometryn(shape,1) as geom FROM events

And then I published the view using geoserver. Everything works, except for GetFeatureInfo requests, that fails with the error 'Operation on mixed SRID geometries
A simple

select * from geometry_columns

returns that the SRID of pippo.geom is 0 (but should be 4326)

I tried also with this definition for the view

CREATE VIEW pippo AS SELECT st_setsrid(st_geometryn(shape,1), 4326) as geom FROM events

but I still have SRID=0 in the geometry_columns….any help?

Here is how the layer is defined in Geoserver:

Workaround

I found a workaround to fix not the problem itself, but to let Geoserver to perform spatial search on GetFeatureInfo requests:

instead of creating a view and publish it, I published an SQL-layer defined directly in geoserver. In the layer-definition form it allowed me to select the right gemoetry type and SRID and everything is working now!

Answer 1

You can use a typmod to specify the srid in the geometry_columns view, something like

CREATE VIEW pippo AS
SELECT st_geometryn(shape,1)::geometry(Geometry, 4326) as geom
FROM events

The manual has more info.