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_geomFROM camino c, camino_tipo t, camino_layer l, camino_link eWHERE 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,
أكثر...
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_geomFROM camino c, camino_tipo t, camino_layer l, camino_link eWHERE 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,
أكثر...