[postgis-users] Publishing WMS from PostGIS table with ARRAY type field
Òscar Vidal Calbet
oscarblo at gmail.com
Sat Mar 19 03:54:12 PDT 2011
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. 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