[postgis-users] Publishing WMS from PostTGIS table with ARRAY type field

Òscar Vidal Calbet oscarblo at gmail.com
Sat Mar 19 09:53:20 PDT 2011


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 <sfkeller at gmail.com>:
> 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