[postgis-users] Publishing WMS from PostTGIS table with ARRAY type field
Stefan Keller
sfkeller at gmail.com
Sat Mar 19 13:16:11 PDT 2011
Now I see.
But then I would use filetype helper functions like below.
Yours, S.
CREATE or REPLACE FUNCTION filetype_to_href(filetype)
RETURNS text AS $$
DECLARE
file alias for $1;
href text := '';
BEGIN
RETURN '<a href="'|| file.file_url ||'" target="_blank" >'||
file.file_name ||'</a>';
END;
$$ LANGUAGE plpgsql;
-- Test: SELECT filetype_to_href(files[1]) FROM resource_file;
CREATE or REPLACE FUNCTION filetype_array_to_href(filetype[])
RETURNS text AS $$
DECLARE
files alias for $1;
href_list text := '';
BEGIN
FOR n IN 1 .. array_upper($1, 1)
LOOP
href_list := href_list || filetype_to_href(files[n]) || ' ';
END LOOP;
RETURN href_list;
END;
$$ LANGUAGE plpgsql;
-- Test: SELECT id, filetype_array_to_href(files) FROM resource_file;
2011/3/19 Òscar Vidal Calbet <oscarblo at gmail.com>:
> 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