Dear list,
The Primary Key Metadata table option (when adding a new PostGIS store) is
poorly documented. This metadata table is required to get (updatable)
database VIEWs to work for WFS-T. Some hints can be found here:
http://getsatisfaction.com/opengeo/topics/postgis_index_in_opengeo/replies
I will quote that information below.
No one responded to my post regarding the issues I encountered, yet it seems
I managed to get it to work... :))
For those who experience similar issues, be sure you think of the following:
1) check the GRANTs on the view (duh!)
2) by default, the primary key metadata is named "gt_pk_metadata"; if you
have the opportunity, stick with this name. Doing so, allows you to leave
the Primary key metadata table"-field blank when adding your new store to
GeoServer.
3) Similar to 1): check the GRANTs on the metadata table ("gt_pk_metadata")
(duh!)
4) when adding the VIEW's metadata to the metadata table, setting pk_policy
to "assigned" seems to leave PK-assignment to the database back-end. Not
sure what "sequence" and "autoincrement" do... It is easy to think of some
behaviour, yet details are not available...
Any response is still appreciated; some explanation on the values for
pk_policy ('sequence', 'assigned', 'autoincrement') is very much welcome!
Regards,
Rob
Quoting from
http://getsatisfaction.com/opengeo/topics/postgis_index_in_opengeo/replies:
*Andrea Aime <http://getsatisfaction.com/people/andreaaime>* replied on June
23, 2010
08:35<http://getsatisfaction.com/opengeo/topics/postgis_index_in_opengeo#reply_2806447>
geomatikfab, there is no standard way to recognize the primary keys sitting
behind an sql view. In GeoServer trunk we are discussing a way to add sql
query as a data source and also state which fields you want to use to
generate the feature identifier (among other things), see here:
http://geoserver.org/display/GEOS/GSI...<http://geoserver.org/display/GEOS/GSIP+48+-+Parametric+SQL+views+in+GeoServer>
However that will be available only in GeoServer 2.1, which is going to
released as stable sometimes in autumn.
In the meantime there is another way (which is kind of undocumented).
The datastore configuration page has that mysterious primary key metadata
table field, right?
The metadata table has this structure:
CREATE TABLE gt_pk_metadata (
table_schema VARCHAR(32) NOT NULL,
table_name VARCHAR(32) NOT NULL,
pk_column VARCHAR(32) NOT NULL,
pk_column_idx INTEGER,
pk_policy VARCHAR(32),
pk_sequence VARCHAR(64),
unique (table_schema, table_name, pk_column),
check (pk_policy in ('sequence', 'assigned', 'autoincrement'))
)
where:
* table_schema (varchar): schema name
* table_name (varchar): table name
* pk_column (varchar): column name
* pk_column_idx (integer): column index if pk is multicolumn (nullable)
* pk_policy (varchar): pk assignment policy: "assigned", "sequence",
"autogenerated"
* pk_sequence (varchar): full name of the sequence to be used to generate
the next value, if any
If you create that table in the database and populate it GeoServer will use
to determine the fields to be used to generate a stable feature identifier.
Ah, right, what do you have to provide as the "primary key metadata table"
in the store configuration? Nothing, if you can create the above table in
the default schema, or schema.tablename if you have to create it in another
schema or you want to name it a different way.
2011/1/17 InterRob <[email protected]>
> Unfortunately, I was mistaken... It seems that GeoServer does not recognize
> the Primary Key metadata entry...
>
> Yet, all seems rightly configured:
>
> SELECT * FROM gt_pk_metadata_table;
> table_schema | table_name | pk_column | pk_column_idx | pk_policy |
> pk_sequence
>
>
> --------------+------------+-----------+---------------+-----------+------------
> public | vw_dum | id | | assigned |
> (1 row)
>
> \d vw_dum:
> View "public.vw_dum"
> Column | Type | Modifiers
> ----------+----------+-----------
> id | integer |
> geom | geometry |
> category | integer |
> View definition:
> SELECT cp_dum.id, cp_dum.geom, cp_dum.category
> FROM cp_dum;
> Rules:
>
> ins_cp_dum AS
> ON INSERT TO vw_dum DO INSTEAD INSERT INTO cp_dum (geom, category)
> SELECT
> new.geom, new.category
> upd_cp_dum AS
> ON UPDATE TO vw_dum DO INSTEAD UPDATE cp_dum SET geom = new.geom,
> category
> = new.category
> WHERE cp_dum.id = new.id
>
> SELECT * FROM geometry_columns;
>
> f_table_catalog | f_table_schema | f_table_name | f_geometry_column |
> coord_dim
> ension | srid | type
>
> -----------------+----------------+--------------+-------------------+----------
> -------+-------+---------
>
> | public | vw_dum | geom |
> 2 | 28992 | POLYGON
> | public | cp_dum | geom |
> 2 | 28992 | POLYGON
> (2 rows)
>
>
> Does anyone see the mistake I'm making?
>
>
>
> 2011/1/17 InterRob <[email protected]>
>
>> A little additional information: downloading some of the layer data (in
>> GML) through GeoServer's "Layer Preview" page, shows that GeoServer (still?)
>> comes up with self-computed values for fid, e.g.:
>> fid="vw_dum.fid-59f3a68d_12d9484d48d_-8000"
>>
>> Whereas the "id" column is part of the output (it should be suppressed,
>> being the PK).
>>
>> What's going wrong here?
>>
>>
>> Rob
>>
>> 2011/1/17 InterRob <[email protected]>
>>
>> Dear list,
>>>
>>> Like others have been struggling with also, I am trying to get some
>>> PostGIS-views to get to work (that is: write through GeoServer/WFS-T).
>>> Through SQL, this VIEW has been made writeable by the use of RULEs; with
>>> some INSERTs and UPDATEs this has been tested.
>>>
>>> The view is defined as follows:
>>> CREATE VIEW vw_dum AS SELECT * FROM cp_dum;
>>> CREATE OR REPLACE RULE ins_cp_dum AS
>>> ON INSERT TO vw_dum DO INSTEAD INSERT INTO cp_dum (geom, category)
>>> SELECT new.geom, new.category;
>>> CREATE OR REPLACE RULE upd_cp_dum AS
>>> ON UPDATE TO vw_dum DO INSTEAD UPDATE cp_dum SET geom = new.geom,
>>> category = new.category
>>> WHERE cp_dum.id = new.id;
>>>
>>> The table behind that view is defined such:
>>> CREATE TABLE cp_dum (id SERIAL PRIMARY KEY, geom GEOMETRY, category
>>> INTEGER);
>>>
>>> The VIEW has been added to PostGIS' geometry_columns metadata table:
>>> INSERT INTO geometry_columns(f_table_catalog, f_table_schema,
>>> f_table_name, f_geometry_column, coord_dimension, srid, "type") VALUES('',
>>> 'public', 'vw_dum', 'geom', 2, 28992, 'POLYGON');
>>>
>>> Some polygons have been inserted into the cp_dum table. The VIEW is not
>>> of much use, you would say; it's merely for testing purposes...
>>>
>>> Then I created the following table:
>>> CREATE TABLE gt_pk_metadata_table (
>>> table_schema VARCHAR(32) NOT NULL,
>>> table_name VARCHAR(32) NOT NULL,
>>> pk_column VARCHAR(32) NOT NULL,
>>> pk_column_idx INTEGER,
>>> pk_policy VARCHAR(32),
>>> pk_sequence VARCHAR(64),
>>> unique (table_schema, table_name, pk_column),
>>> check (pk_policy in ('sequence', 'assigned', 'autoincrement'))
>>> );
>>>
>>> Within the GeoServer webconfig I created a PostGIS Vector Data Store
>>> refering to this metadata table (it is called the "Primary key metadata
>>> table").
>>>
>>> The following record is expected to have GeoServer treating the VIEW's id
>>> column as PK:
>>> INSERT INTO gt_pk_metadata_table VALUES('public', 'vw_dum', 'id', NULL,
>>> 'assigned', NULL);
>>>
>>> The new layer is added to GeoServer. (GeoServer's output to console
>>> suggests that nothing is wrong and the PK is applied [before, without the
>>> gt_pk_metadata table entrie, I got warnings about missing PK) When accessed
>>> through WFS, a nice map is shown in my desktop GIS client (MapInfo).
>>> However, when updating the dataset with MapInfo through WFS-T, a
>>> WFSTransactionException is thrown by GeoServer: "vw_dum is read-only".
>>>
>>> Any idea's? Your help is very much appreciated.
>>>
>>> I am running GeoServer 2.0.2
>>>
>>>
>>> Regards,
>>> Rob
>>>
>>>
>>
>
------------------------------------------------------------------------------
Protect Your Site and Customers from Malware Attacks
Learn about various malware tactics and how to avoid them. Understand
malware threats, the impact they can have on your business, and how you
can protect your company and customers by using code signing.
http://p.sf.net/sfu/oracle-sfdevnl
_______________________________________________
Geoserver-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
