[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