3

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.

Improve this question

2 Answers 2

Reset to default
3

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.

Improve this answer
8
  • Does this work only if I have one object for each county?
    – James S
    Commented May 30, 2013 at 7:06
  • Do you mean you have 1 polygon [geometry] per county name?
    – Mapperz ♦
    Commented May 30, 2013 at 14:51
  • I have many geometries for each county. I would like to bulk delete a counties data, and then replace it with the new data.
    – James S
    Commented May 30, 2013 at 14:56
  • then select "Truncate table first" which empties the whole table before appending new geometries, and load them from all 26 Shapefiles (if that is possible?). Thats much faster than using the UPDATE mode.
    – Jürgen Zornig
    Commented May 31, 2013 at 7:56
  • if you have country name in database , you could just run SQLExecute which DELETES and after that one which INSERTs new data. Also turn oids off and use serial, oids are global and can run out
    – simpleuser001
    Commented May 31, 2013 at 8:40
3

You can also find the basics on updating data in the FME tutorial for Spatial Databases. It's not updated for 2013 yet, but it should have a lot of useful info regardless.

Improve this answer
1
  • Always find the FME Documentation clear and concise, can be hard to keep it maintained - looking forward to FME Server and Websocks introduced soon (FME 2013/SP2?)
    – Mapperz ♦
    Commented May 27, 2013 at 18:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.