[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