[postgis-users] Publishing WMS from PostTGIS table with ARRAY type field
Stefan Keller
sfkeller at gmail.com
Sat Mar 19 09:27:34 PDT 2011
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 <oscarblo at gmail.com>:
> 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 <sfkeller at gmail.com>:
>> 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 <oscarblo at gmail.com>:
>>> 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
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>
>
More information about the postgis-users
mailing list