[postgis-users] Publishing WMS from PostTGIS table with ARRAY type field
Òscar Vidal Calbet
oscarblo at gmail.com
Sat Mar 19 14:27:29 PDT 2011
Thanks Stefan,
Some helper functions always are a good practice.
Brent,
Yeah, if I understood well, that was one of my initial hypotheses, but I
think, its difficult to manage when you can have a big amount (ex. 15) of
files associated to one feature. That becomes in a table with a lot of
columns and you need to create a way to give different names for the columns
as well (ex. file_name01, file_name02...), consequently will be difficult
create the content.tpl that I'm looking for show all the files.
I tried to create the view (its not working to me, not show the values), but
I think that I will need a view with a dynamic number of columns like this:
create view view_files_split as select
id,
from_table,
with_id,
files[1] as file_name,
files[1] as file_description,
files[1] as file_url,
files[1] as file_type,
files[1] as file_size,
files[1] as file_updated,
files[2] as file_name2,
files[2] as file_description2,
files[2] as file_url2,
files[2] as file_type2,
files[2] as file_size2,
files[2] as file_updated2,
...
files[N] as file_nameN,
from resource_file_test;
I will (Im going) go to use the approach to create the HTML string with all
the links in just only one string field. But Its going helpful as well to me
hear about others approaches.
Thanks to share the ideas here.
Oscar
2011/3/19 Brent Wood <pcreso at yahoo.com>
> Hi Oscar,
>
> Unless I'm missing something here...
>
> Can you open the array up in a query/view so the elements become columns
> for the web service? While an array is convenient within Postgres, it is a
> non-standard & not widely supported capability, as is the user defined
> composite data type.
>
> It seems to me that while you may want to take advantage of these
> capabilities for data management, you should transform the structure into
> something more widely supported for interoperability purposes, so decompose
> the array into it's constituent fields for the web service.
>
> I'm not sure if the view below will work as is, or if you would be better
> off explicitly casting the array fields to specified data types.
>
> Create a decomposed view which geoserver can access, like:
>
> create view view_files_split as select
> id,
> from_table,
> with_id,
> files[1] as file_name,
> files[2] as file_description,
> files[3] as file_url,
> files[4] as file_type,
> files[5] as file_size,
> files[6] as file_updated
> from <table>;
>
> Then join this for geoserver as you were the array table, or create a view
> like the above which includes the join...
>
> Brent Wood
>
> --- On *Sun, 3/20/11, Stefan Keller <sfkeller at gmail.com>* wrote:
>
>
> From: Stefan Keller <sfkeller at gmail.com>
> Subject: Re: [postgis-users] Publishing WMS from PostTGIS table with ARRAY
> type field
> To: "Òscar Vidal Calbet" <oscarblo at gmail.com>
> Cc: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> Date: Sunday, March 20, 2011, 9:16 AM
>
>
> 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<http://mc/compose?to=oscarblo@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<http://mc/compose?to=sfkeller@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<http://mc/compose?to=oscarblo@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<http://mc/compose?to=sfkeller@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<http://mc/compose?to=oscarblo@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://mc/compose?to=postgis-users@postgis.refractions.net>
> >>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>>>>
> >>>>
> >>>
> >>
> >
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net<http://mc/compose?to=postgis-users@postgis.refractions.net>
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110319/39e7e8d0/attachment.html>
More information about the postgis-users
mailing list