[GIS] Use FME to delete records from PostGIS before loading new records

fmepostgisshapefile

I have some shapefiles. Each shapefile contains the data on a topic for a county. I have data for 26 counties.

In the filename of the shapefile, the county ID is given.

I load these shapefiles into PostGIS using FME. The data for all counties is loaded into one table (so 26 shapefiles get loaded into 1 PostGIS table).

This data gets updated every now and then, and I can request the data for the county where the data has been changed. I get supplied with a new shapefile with all data for that county (not just the changed records).

I would like to delete the data in the PostGIS table for the county that I have the new data for before I load the new data.

How do I do this? Is this possible if I get the county ID from the filename of the shapefile and each record in the PostGIS table has a county ID attached to it?

My plan is to just add the new shapefile to a specified folder, and run my FME workspace.

Edit 31/05/2013

Just to clarify, I have multiple records for each county (i.e. all roads, buildings etc in a county). I basically want to bulk delete a counties data, and replace it with the new data.

Best Answer

If you have a unique county ID that is consistent with your re-supplied data then you just need to set the POSTGIS Writer to update.

enter image description here

PostGIS set to update (geometry)

enter image description here

So as long as the input shapefile is the same name you can automate the process. FME 2013 SP1 shown here.

Related Solutions

[GIS] Problem loading OS OpenData multipoint shapefile into PostGIS

As suggested by other answers, the problem is related with the POINT and MULTIPOINT types. shp2pgsql (and ogrinfo) detects the geometry type as MULTIPOINT, but then, when creating the EWKB representation of the geometry (the value to insert in the_geom column), it seems to be generating a POINT geometry:

select geometrytype('0101000020346C000000000000302A0A4100000000304F1C41');
--POINT

(I took the first generated record)

According to the shp2pgsql man page, there is a flag that should force new geometries to be created simple, but apparently, it does not work on points:

   -S     Generate simple Geometries instead of MULTIgeometries. Shape files don't differ between LINESTRINGs and MULTILINESTRINGs, so shp2pgsql generates MUL‐
          TILINESTRINGs  by  default. This switch will produce LINESTRINGs instead, but shp2pgsql will fail when it hits a real MULTILINESTRING. The same works
          for POLYGONs vs. MULTIPOLYGONs.

Instead of dropping the offending constraint, I will suggest changing the geometry column in the generated SQL script before executing it with psql:

SELECT AddGeometryColumn('','strategi_point','the_geom','27700','POINT',2);

This way you will get your records loaded, and at the same time keep a constrain that may ensure your data integrity in the future.

[GIS] FME Workbench: Merging Multiple Shapefiles into One Database Table

You seem to be using a dynamic workspace, which I don't think is right here.

Assuming you want FME to create the new table, the following steps should be what you need to do:

1) Open the Generate Workspace dialog (Ctrl+G)

2) Enter Shape as the source format

3) Use the browse button and select ALL of the shape files to be converted

4) Set the output format and parameters

5) Ensure Workflow Option is set to Static Schema and click OK.

6) In the newly generated workspace there are shape files on the left and tables on the right. You only want one table, so delete the others.

7) Draw connections from each input shape file to the remaining table

8) Open that table parameters and change the table name

If you want to write to an existing table, then you would delete all the Writer objects in step 6 (i.e. not leave one) then use Writers > Import Feature Type to import that table's definition.

I created a brief movie demonstrating these techniques. You can find it at: http://screencast.com/t/p7EeWAdJ

Best Answer

Related Solutions

[GIS] Problem loading OS OpenData multipoint shapefile into PostGIS

[GIS] FME Workbench: Merging Multiple Shapefiles into One Database Table

Related Question