Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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,

share|improve this question

2 Answers 2

Your SQL is not complete, you reference 4 tables in your view but only create one (camino_layer).

Once you have your SQL View working, you can create a GeoServer SQL View that basically retrieves everything the view does like so SELECT * from camino_view;

You can also add parameters.

share|improve this answer
    
I know that my SQL is not complete. I have all 4 tables, and without the the_geom column, the view works fine. –  Federico Sep 11 '12 at 13:18
1  
You're going to need provide more information in the form of a complete DDL. The above is in wrong order. You say you can't see the content of the view, but you haven't provided any example content in the form of inserts. What is camino_layer for anyway? Isn't that what the view is for? –  CaptDragon Sep 11 '12 at 13:59
    
I'm telling you that if u remove the the_geom column, the view works fine= I can see data. There is data. Anyway, thanks! –  Federico Sep 11 '12 at 14:12

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.

share|improve this answer
1  
What version are you using? It sounds like 1.5. In 2.0 you no longer need to add to the geometry_columns manually. See the 2.0 documentation on AddGeometryColumn. –  RyanDalton Sep 11 '12 at 23:37
    
You're right. I'll to upgrade my Geoserver and give it a try. –  Federico Sep 13 '12 at 12:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.