Introduction:
Multiple Geometry writing in PostGIS
As of FME 2016.1, the Postgis writer doesn't yet support the "Handle Multiple Spatial Columns" option that is available on the Oracle and SQL Server writers. So writing to a Postgis Table which has multiple spatial columns requires a two step process.
Note: We have logged PR 17730 for PostGIS multiple geometry support.
Requirements
The PostGIS table with multiple geometry columns must have been pre-built using pgAdmin to get the correct table layout. The PostGIS table has been pre-built for the demo example, postgis-insert-update-2016.fmwt.
Downloads
postgis-insert-update-2016.fmwt
workspaces-for-inserts-and-updates.zip
Instructions
FME 2016+
With FME 2016+, it is easier to write multiple geometries to PostGIS. To write multiple geometry, a feature must first be inserted with one geometry type, then updated with another. Before this has to be done in 2 separate workspaces. It is now possible to order the runtime processing of features, using Connection Runtime Order, so that the Insert and Update can be done in the same workspace.
The download postgis-insert-update-2016.fmwt is the complete workspace. If you would like to create the workspace yourself, please download Parks.zip, and follow the steps below to create it.
Completed workspace
1. Create Source Table
To prepare for the exercise, please run createtable-city-parks.fmw to ensure the table is correctly set-up.
2. Add Reader
Add a MapInfo (MTAB) Reader and set Dataset to Parks.tab.
3. Add INSERT Writer for Polygons
Go to Writers, Add Writer and add a PostGIS Writer. Set Dataset to Embed Connection Parameters. Under Parameters... please enter:
Host: postgis.train.safe.com
Port: 5432
Database: fmedata
Username: fmedata
Password: fmedata
Connect the PostGIS Writer feature type to the MapInfo Reader. In the Feature Type Properties of the INSERT Writer, under the General tab, please set the Table Qualifier to fmedata2016. In the Format Parameters tab, please set "Insert" for the "Feature Operation" and "Truncate Existing" for the "Table Handling" parameter. In this example, the INSERT Writer will handle the polygon geometries.
INSERT writer Format Parameters
4. Create and Reproject Points
Connect a CenterPointReplacer to the MapInfo (MTAB) Reader, to create another set of data flow. The CenterPointReplacer creates points, then add a Reprojector to change the coordinate system to LL84. Finally, add a AttributeRemover to remove all attributes except for the key field, parkid. It is only necessary to pass through to the UPDATE writer the key field of "parkid" - all other attributes can be removed from the feature.
5. Add UPDATE Writer for Points
Go to Writers, Add Writer and add a PostGIS Writer. Set Dataset to Embed Connection Parameters using the same parameters as above.
Connect the PostGIS Writer feature type to the AttributeRemover. In the Feature Type Properties of the UPDATE Writer, under the General tab, please set the Table Qualifier to fmedata2016. In the Format Parameters tab, please set "Update" for the "Feature Operation" and "Use Existing" for the "Table Handling" parameters.
UPDATE writer Format Parameters
6. Set Connection Runtime Order
On the output port of the MapInfo (MITAB) Reader, right-click and select Set Connection Runtime Order... Please set Parks -> fmedata2016.City_Parks in position 1 and Parks -> CenterPointReplacer in position 2.
Set Connection Runtime Order
7. Run workspace and examine output using pgAdmin
Please examine the output in pgAdmin, as the PostGIS reader also does not support multiple geometry columns, and by default FME's PostGIS reader will pick the first geometry column. In pgAdmin, please right-click on the table, select View Data then View All Rows.
Output viewed in pgAdmin
Pre FME 2016
Prior to FME 2016, a two workspace process is required, workspaces-for-inserts-and-updates.zip is the demo. The attached zip file contains two workspaces which show how this can be done.
1) Run the none2postgis_insert.fmw workspace to INSERT your first geometry type into the table
2) Then run the other none2postgis_update.fmw workspace to UPDATE the table with the second geometry type. When you run the update workspace (Writer Mode = UPDATE), you must set the fme_where format attribute to a key value (in example table it could be fme_where id = 1). In addition you will need to change the spatial column name parameter to the second Geometry. In the case above you would first INSERT to spatial column name Geom and in the UPDATE workspace you would change the spatial column name to Geom2.
Here's an example SQL you can use to create the tables:
-- 2 spatial columns --DROP TABLE public."ParksTwoSpatial"; CREATE TABLE public."ParksTwoSpatial" ( parkid smallint, refparkid smallint, parkname character varying(40), neighborhoodname character varying(40), ewstreet character varying(30), nsstreet character varying(30), dogpark character varying(1), washrooms character varying(1), specialfeatures character varying(1), geom geometry(Geometry,26910), geog geography, CONSTRAINT parks_2_spatial_pkey PRIMARY KEY (parkid) ) WITH ( OIDS=FALSE ); ALTER TABLE public."ParksTwoSpatial" OWNER TO postgres; -- DROP INDEX public."Parks_geom_si"; --DROP INDEX public."Parks_geog_si"; CREATE INDEX "Parks_geom_si" ON public."ParksTwoSpatial" USING gist (geom); CREATE INDEX "Parks_geog_si" ON public."ParksTwoSpatial" USING gist (geog);
Contributors
