Editing Multiple Spatial Tables
- Simon Greener
Database Objects
This document is based on the following database tables within a PostgreSQL/PostGIS database.
DROP TABLE IF EXISTS public.graffiti_p;
CREATE TABLE public.graffiti_p (
ID serial not null primary key,
DESCRIPTION text,
STATUS varchar(1) check (STATUS IN ('N','V','S','R')) default ('N'),
FIELDVERIFIED varchar(1) check (FIELDVERIFIED in ('Y','N')),
/* Who reported the graffiti */
REPORTEDBY varchar(100),
REPORTEDON timestamp,
/* record / edit level metadata ie who created/updated record in database */
CREATEDBY varchar(100),
CREATEDON timestamp,
MODIFIEDBY varchar(100),
MODIFIEDON timestamp,
/* Geometry restricted to single points in SRID 4326 */
GEOM geometry(POINT,4326)
);
INSERT INTO public.graffiti_p (DESCRIPTION,STATUS,FIELDVERIFIED,REPORTEDBY,REPORTEDON,CREATEDBY,CREATEDON,GEOM)
VALUES ('Banksy like mural',
'V',
'Y',
'Cohga',
CURRENT_TIMESTAMP,
'Cohga',
CURRENT_TIMESTAMP,
ST_GeomFromEWKT('SRID=4326;POINT(144.953454336348 -37.8060850834926)'));
-- ****************************************************************************
DROP TABLE IF EXISTS public.graffiti_a;
CREATE TABLE public.graffiti_a (
ID serial not null primary key,
DESCRIPTION text,
STATUS varchar(1) check (STATUS IN ('N','V','S','R')) default ('N'),
FIELDVERIFIED varchar(1) check (FIELDVERIFIED in ('Y','N')),
/* Who reported the graffiti */
REPORTEDBY varchar(100),
REPORTEDON timestamp,
/* record / edit level metadata ie who created/updated record in database */
CREATEDBY varchar(100),
CREATEDON timestamp,
MODIFIEDBY varchar(100),
MODIFIEDON timestamp,
/* Geometry restricted to single polygons in SRID 4326 */
GEOM geometry(POLYGON,4326)
);
INSERT INTO public.graffiti_a (DESCRIPTION,STATUS,FIELDVERIFIED,REPORTEDBY,REPORTEDON,CREATEDBY,CREATEDON,GEOM)
VALUES ('Multiple snakes drawn on the road',
'V',
'Y',
'Cohga',
CURRENT_TIMESTAMP,
'Cohga',
CURRENT_TIMESTAMP,
ST_GeomFromEWKT('SRID=4326;POLYGON((
144.953454 -37.806085,
144.953455 -37.806085,
144.953455 -37.806086,
144.953454 -37.806086,
144.953454 -37.806085))'));
GRANT ALL ON PUBLIC.graffiti_p to public;
GRANT ALL ON PUBLIC.graffiti_a to public;
SELECT *
FROM public.geometry_columns
WHERE table_name in ('graffiti_a','graffiti_p');
Requirements
If an entity is composed of multiple spatial tables, then it may be the case that the entity id may not be unique across all of the tables. If for example the entity is composed of more than one geometry type and the underlying spatial engine (e.g. ArcSDE, shapefiles) does not support different geometries in a single table, then the geometry would need to be spread over more than one table. In this situation, the entity id would have to be the same in all of the spatial tables, but it must at least be unique within each table. This is the only way that Weave can determine which row in the table that it needs to update as Weave determines which spatial table to update based on the geometry type stored in the spatial table.
The editing of multiple spatial tables is only fully supported when a different geometry type is stored in each table. That is, if an entity is linked to more than one spatial table then each spatial table should contain a different geometry type (point, line or polygon). This is so that Weave knows which table to write the appropriate geometries to. For example, if you were to have an entity linked to two spatial tables, both containing polygon geometry, then when an edit is submitted that contains a polygon, Weave would not know which table the geometry was supposed to be added to or updated in.
In the case of an entity represented by multiple spatial tables, the schemas for the tables should all be the same. This is a requirement because the same attribute values will be written to each table when an edit is submitted that contains different geometry types.
Spatial engines that do not differentiate between geometry types, (i.e. the spatial table stores "geometry" and not specifically points, lines or polygons) are not currently supported in multi-table configurations. This is because in these cases, Weave cannot determine which table to write the geometry to (they are supported in single table configurations). This may change in the future.
Multi-Table Configuration
Our example is based on the graffiti_p and graffiti_a tables (see above and Database Objects). The graffiti_p table has a geometry column defined to hold single points and the graffiti_a table has a geometry column that is defined to hold single polygons. (A piece of graffiti could be at a specific point or could be spread over an area.)
The entity and mapper definitions for multi-table editing is as follows:
<entity:entity id="m.graffiti" label="Graffiti (multi)" />
<mapper:mapper id="m.graffiti">
<spatialengine>se.postgresql</spatialengine>
<mapping>
<entity>m.graffiti</entity>
<dynamic>true</dynamic>
<cache>false</cache>
<mapping><table>graffiti_p</table></mapping>
<mapping><table>graffiti_a</table></mapping>
<key>id</key>
</mapping>
</mapper:mapper>A dedicated map engine is defined and added to the client XML. (The layer SLDs are defined here.)
<weave:mapengine id='me.multi'>
<spatialengine>se.postgresql</spatialengine>
<format>image/png32</format>
<layers>
<layer id='l_graffiti_p' label='Graffiti (Points)' layer='graffiti_p' style='/weave/platform/workspace/SpatialDataEditing/graffiti_p.sld'/>
<layer id='l_graffiti_a' label='Graffiti (Areas)' layer='graffiti_a' style='/weave/platform/workspace/SpatialDataEditing/graffiti_a.sld'/>
</layers>
<crs>EPSG:4326</crs>
</weave:mapengine>This me.multi mapengine is added to the client as follows.
The new table of contents looks like this:
The following edit configuration allows for editing of the multiple table scenario. Note the <geometry> definition and the definition for the id column (i.e., auto() - the value for the field is auto-generated by the database).
Setting either of the graffiti tables as Active will cause the Graffiti (multi) Edit Configuration to become active.
Examples of the creation of point and polygon graffiti objects are as follows:
Point:
Polygon: