ArcGIS 9.3 FME import shapefile into Postgresql PostGIS

Here is the task that I am tackling or I should say finished tackling.

Import some Shapefiles into an already existing Postgresql / PostGIS database schema called "demo" (not public schema) with already existing tables for each shapefile that have predefined fields but are lacking any data what so ever. Basically I’m importing only 2 fields one is the geometry column aka the actual geometry of the data in the shapefile and one field with attribute data.

Prerequisits:

1. Postgresql 8.4 with PostGIS 1.4 or 1.5 installed on local machine (I'm using Win 7 Pro)
2. ArcGIS 9.3 with FME  Interoperability Extension enabled

Lets get started: 

1. First firing up my trusty ArcCatalog 9.3 I created a new “Spatial ETL Tool” to handle the work (Right Click an existing Toolbox  select create new.create new Spatial ETL ). 
2. Then following the instructions filling in the source files and specifying the Postgresql PostGIS database on my localhost as the destination all things good so far. 
3. Take the defaults and hit run.

oops some not so expected results happend as I trusted the defaults blindly. So what actually went wrong

1. wrong schema namely ‘public’ ( I wanted my schema ‘demo’)
2. wrong SRID is defined (my local srid 31258 is what I wanted I got 900914)
3. new field is created namely gid (not wanted since I already have ‘id’ defined as the primary key with its appropriate sequence).

To deal with the errors or I should say misconfigurations I made were easily handled by the following three points:
 

1. Error 1 Solution. we need to go to Postgresql and change the default search path value to my database as defined in the PG connection string in FME

• SQL: ALTER USER myusername SET search_path =’$user’,'demo’,'public’;
• SQL: ALTER DATABASE mydbname SET search_path = demo, public;

see this helpful link http://groups.google.com/group/fmetalk/browse_thread/thread/cd0b14509351e61f for more info.

2. Error 2 Solution. is to set the Geometry Column SRID to 31258 not “Inherit_FROM_WRITER”

3. Error 3 Solution. clicking each feature in my FME model and changing the Feature Type Properties to the following settings here in this screenshot. 

Notice that “Create with OIDs” NO is what prevents the creation of the ‘gid’ column that I didn’t want.

Run the model again and woola it worked. The shapefiles were uploaded into the correct Postgresql schema, the data had the correct SRID defined and no more silly GID column added to my predefined table structure.
What a fun night I had playing with this. I hope this helps you out in your need to transform and play with ETL with ArcGIS and Postgresql-PostGIS.

Cheereo
Michael