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.

Answer 2

When in Layers you can see the CRS (Coordinate Reference System)

You can Force the CRS in Geoserver

http://docs.geoserver.org/2.1.3/user/webadmin/data/layers.html#webadmin-layers