[GIS] Publish SQL Views from Postgres/Postgis to Geoserver

postgispostgresqlspatial-viewsql

I've trying and reading how to publish a Postgres/Postgis View in Geoserver. I know that I can create an SQL View in Geoserver, but I'd like to define the View in Postgres, not in Geoserver.

Does anyone has an example of how it's done?

So far I've tried this, but I can't see the content of the View, not even in Postgres.

CREATE OR REPLACE VIEW camino_view AS 
SELECT c.id, c.nombre, t.id AS tipo_id, t.nombre AS tipo, l.gid AS geo_id, st_transform(l.the_geom, (-1)) AS the_geom
FROM camino c, camino_tipo t, camino_layer l, camino_link e
WHERE c.tipo_id = t.id AND e.camino_id = c.id AND e.camino_geo_id = l.gid;

CREATE TABLE camino_layer
(
gid serial NOT NULL,
"Tipo" character varying(50),
"Nombre" character varying(50),
the_geom geometry,
CONSTRAINT camino_layer_pkey PRIMARY KEY (gid),
CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'LINESTRING'::text OR the_geom IS NULL),
CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = (-1))
)
WITH (
OIDS=FALSE
);

CREATE TABLE camino_link
(
 camino_id integer NOT NULL,
 camino_geo_id integer NOT NULL,
 CONSTRAINT camino_link_pkey PRIMARY KEY (camino_id, camino_geo_id),
CONSTRAINT camino_link_camino_fkey FOREIGN KEY (camino_id)
  REFERENCES camino (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT camino_link_camino_layer_fkey FOREIGN KEY (camino_geo_id)
  REFERENCES camino_layer (gid) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);

CREATE TABLE camino_tipo
(
id serial NOT NULL,
nombre character varying(48) NOT NULL,
CONSTRAINT camino_tipo_pkey PRIMARY KEY (id) 
)

CREATE TABLE camino
(
id serial NOT NULL,
nombre character varying(40) NOT NULL,
tipo_id integer NOT NULL,
CONSTRAINT camino_pkey PRIMARY KEY (id),
CONSTRAINT camino_tipo_fkey FOREIGN KEY (tipo_id)
  REFERENCES camino_tipo (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT camino_name_uq UNIQUE (nombre)
)
WITH (
OIDS=FALSE
);

Any help will be appreciated.
Thanks,

Best Answer

Found one way to do it. Basically you have to add it to the geometry_columns manually.

http://postgis.refractions.net/documentation/manual-1.5/ch04.html

--Lets say you have a view created like this
CREATE VIEW  public.vwmytablemercator AS
SELECT gid, ST_Transform(the_geom,3395) As the_geom, f_name
FROM public.mytable;

--To register this table in AddGeometry columns - do the following
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'public', 'vwmytablemercator', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM public.vwmytablemercator LIMIT 1;

Then you can publish it on Geoserver and use it like any WMS layer.

Related Question