Thanks Stefan. I tried already, but is happening the same. Geoserver is only publishing the fields "gid, name, description, the_geom" but no the "files". "files" is a ARRAY field with elements of a TYPE created for me.
thanks, oscar 2011/3/19 Stefan Keller <[email protected]>: > Ok. > > I think you can still turn your original view into one that is > recognized by GeoServer, like this: > > CREATE VIEW view_my_point_feature_with_files > AS SELECT gid, name, description, files, the_geom > FROM my_feature, resource_file > WHERE resource_file.from_table='my_point_feature' > AND resource_file.with_id=gid; > > I just added gid because I think GeoServer needs a numeric id. > > Yours, S. > > 2011/3/19 Òscar Vidal Calbet <[email protected]>: >> Hi Stefan, >> >> Thanks for the answer. You are right, my explanation is somewhat confusing. >> I think is easier just post the following example code: >> >> =========================================== >> -- >> CREATE TYPE file AS ( >> file_name varchar(64), >> file_description varchar(256), >> file_url varchar(256), >> file_type varchar(32), >> file_size decimal, >> file_updated date >> ); >> -- >> CREATE TABLE resource_file >> ( >> id serial NOT NULL, >> from_table varchar(32) NOT NULL, >> with_id integer NOT NULL, >> files file[], >> CONSTRAINT resource_file_pkey PRIMARY KEY (id) >> ); >> -- >> INSERT INTO resource_file_test(id, from_table, with_id, files) >> VALUES (nextval('resource_file_id_seq'), 'my_point_feature', '54', >> ARRAY[[row('my_file.pdf', 'test', >> '/where/is/the/file/my_file01.pdf', 'pdf', 1.5, >> statement_timestamp())::file], >> [row('my_file.jpg', 'test', >> '/where/is/the/file/my_file02.jpg', 'image', 1.5, >> statement_timestamp())::file]]); >> -- >> CREATE VIEW view_my_point_feature_with_files >> AS SELECT name, description, files, the_geom >> FROM my_feature, resource_file >> WHERE resource_file.from_table='my_point_feature' AND >> resource_file.with_id=gid >> >> =========================================== >> Hope its clear now. >> >> Stefan, I tried already the UNNEST() function >> >> SELECT name, description, files, unnest(the_geom) >> >> But it is giving me back a one line per each file, my objective is >> receive N files in the same row. >> >> I think my approach is wrong. That is because Im using objects ("TYPE >> file" and ARRAY) that are not supported like attributes of spatial >> entity, by the WMS or geoserver. This objects are not standards. >> >> Right now Im making another New Approach, its not so elegant, but its >> working. >> >> =========================================== >> -- >> CREATE TYPE type_file AS ENUM ('PDF', 'IMAGE', 'VIDEO', 'GPX', 'ZIP'); >> -- >> CREATE TABLE resource_file >> ( >> id serial NOT NULL, >> from_table varchar(32) NOT NULL, >> with_id integer NOT NULL, >> file_name varchar(64) NOT NULL, >> file_description varchar(256) NOT NULL, >> file_host varchar(256) NOT NULL, >> file_path varchar(256) NOT NULL, >> file_type type_file NOT NULL, >> file_size decimal NOT NULL, >> file_updated date, >> CONSTRAINT resource_file_pkey PRIMARY KEY (id) >> ); >> -- >> INSERT INTO resource_file(id, from_table, with_id, file_name, >> file_description, file_host, file_path, file_type, file_size, >> file_updated) >> VALUES (nextval('resource_file_id_seq'),'my_point_feature',54, >> 'file.pdf', 'teste', >> 'http://localhost/','where/filesFTP/are/050334.pdf','PDF',1.5,statement_timestamp()); >> -- >> INSERT INTO resource_file(id, from_table, with_id, file_name, >> file_description, file_host, file_path, file_type, file_size, >> file_updated) >> VALUES >> (nextval('resource_file_id_seq'),'my_point_feature',54,'file.jpg','teste', >> 'http://localhost/', >> 'where/filesFTP/are/050334.jpg','IMAGE',1.5,statement_timestamp()); >> -- >> CREATE or REPLACE FUNCTION getFilesHTML(text, int) RETURNS text AS $$ >> DECLARE >> _from_table alias for $1; >> _with_id alias for $2; >> r resource_file%rowtype; >> HTML text := ''; >> url text := ''; >> description text := ''; >> BEGIN >> FOR r IN SELECT * FROM resource_file WHERE from_table = >> _from_table AND with_id = _with_id >> LOOP >> url := r.file_host || r.file_path; >> description := r.file_description ||' ('|| r.file_updated ||')'; >> HTML:= HTML || '<a href="'|| url ||'" title="'|| description ||'" >> target="_blank" >'|| r.file_name ||'</a>'; >> END LOOP; >> RETURN HTML; >> END; >> $$ LANGUAGE plpgsql; >> -- >> CREATE VIEW view_my_point_feature_with_files >> AS SELECT name, description, getFilesHTML('my_point_feature', gid) as >> HTMLfiles, the_geom >> FROM my_point_feature; >> =========================================== >> >> So, in one request, containing one row per feature, I can show/link the N >> files. >> The 'bad' thing, is that I'm mixing already the HTML with the data, in >> the database but I'm open to new suggestions/approaches. >> >> Thanks! >> Oscar >> >> 2011/3/19 Stefan Keller <[email protected]>: >>> Hi Oscar >>> >>> I don't understand what you really did. >>> >>> 1. GeoServer can only serve tables with id and geometry. >>> >>> 2. Can you specify the view you defined and the join? >>> >>> To me that would work too (except that the geometry attribute is lacking): >>> >>> CREATE VIEW resource_file_v(id, files) AS ( >>> SELECT id, unnest(files) AS files FROM resource_file >>> ); >>> >>> Yours, S. >>> >>> 2011/3/15 Òscar Vidal Calbet <[email protected]>: >>>> Hey everyone, >>>> >>>> I published some WMS services in Geoserver from PostGIS database and I >>>> need to make a GetFeaureInfo that gives me data from different tables, >>>> which can be solved by creating a VIEW. The problem is that I need >>>> that one of the fields in the VIEW be allowed to give me N entities >>>> referring to N files (strings with the path) related with the feature. >>>> To solve that in Postgres I created an ARRAY of Composite Types in >>>> the table: >>>> >>>> CREATE TYPE file AS ( >>>> >>>> file_name varchar(64), >>>> >>>> file_url varchar(256), >>>> >>>> ... >>>> ); >>>> >>>> >>>> >>>> CREATE TABLE resource_file >>>> >>>> ( >>>> >>>> id serial NOT NULL, >>>> >>>> from_table varchar(32) NOT NULL, >>>> >>>> with_id integer NOT NULL, >>>> >>>> files ARRAY file, >>>> >>>> CONSTRAINT resource_file_pkey PRIMARY KEY (id) >>>> >>>> ) >>>> >>>> So I'm creating a VIEW with the feature JOIN resource_file to get the >>>> info of the feature with all the (N) files associated. But when I'm >>>> publishing the VIEW, apparently there is no error but the ARRAY field >>>> is missing. I check in the OpenGIS® Web Map Server Implementation >>>> Specification >>>> , but I didn't find anything about if it can be done or not. >>>> >>>> Is it possible? >>>> I would like to solve the problem by having only one request to the >>>> server to get the N files. Any suggestions? >>>> Maybe I can create a function in Postgres that builds already the >>>> content (a string with html tags) to show the N files and pass this to >>>> the view? >>>> >>>> Thanks in advance! >>>> oscar >>>> _______________________________________________ >>>> postgis-users mailing list >>>> [email protected] >>>> http://postgis.refractions.net/mailman/listinfo/postgis-users >>>> >>> >> > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
