Skip to main content
  • EN
  • Home
  • Forums
  • FME Form
  • Data
  • Delete records from a geodatabase based on County from an incoming shapefile (NOT TRUNCATE) then load the shapefile
Good morning,

 

 

I have lease shapefiles we receive from a company (numerous leases for each county). So for example if I have 3 counties already in an existing geodatabase, those counties for this example are named Harris County, Thomas County, and Johnson County. If the shapefile the company has provided has Johnson and Thomas counties (No Harris County data), I will want to delete all records with Johnson County and Thomas County from the existing geodatabase ... and replace them with the Johnson and Thomas County data from the shapefile. But here's the kicker, I want to keep all existing Harris County leases in the geodatabase. A truncate will not work because I want those existing Harris County leases.

 

 

Here's what would happen in a perfect world. Again this is just me dreaming and I'm asking all of you if this dream can be a reality.

 

 

Step 1: Get a list of what counties are in the incoming shapefile (Johnson and Thomas)

 

 

Step 2: Delete all records in the geodatabase that have a county of Johnson and Thomas

 

 

Step 3: Insert the Johnson and Thomas lease data

 

 

Step 4: End up with a geodatabase that has Harris (old data) Johnson (new data) and Thomas (new data)

 

 

This all sounds simple, but I'm stuck trying to make the idea work inside of a single FME Desktop job.

 

 

Any ideas out there on how to successfully do this?

 

 

Thanks in advance for any help and/or advice you can provide.

 

Tony
Hi,

 

I would read in both  fgbd and shape and use a FeatureMerger on them, according to the FeatureMerger ports you can either insert or delete.
But will that allow me to delete and insert using the same records? I'm not sure it will but I can test it out?

 

 
You canuse the same records for multiple actions, matter of taking in account the order of the actions.

 

btw why not update instead of delete and insert?
Hi

 

 

You can set the fme_db_operation attribute before sending each feature to the Writer, thereby specifying INSERT, UPDATE or DELETE on a per-feature basis.

 

 

More info here: https://knowledge.safe.com/articles/How_To/Using-Feature-Based-Attributes-for-Database-updates

 

 

David
ETLS-Itay What happens is we get updates, inserts, and deletes from each batch for the entire county. Most have a combination of all 3. we always found it easier to do a full truncate when the entire set of counties was provided. It may be a better choice to see if update, delete, and insert is a better option now.
Hi,

 

 

Did you try David's suggestion?

 

If you send a feature having these two attributes to the geodatabase writer, all Johnson and Thomas counties will be deleted.

 

----

 

fme_db_operation = DELETE

 

fme_where = County in ('Johnson', 'Thomas')

 

----

 

I think you can create the value of the "fme_where" attribute based on the source shape dataset (DuplicateRemover, StringConcatenator etc.).

 

And then, write (insert) new Johnson and Thomas counties into the same geodatabase writer.

 

Just be aware the order of feature writing. Firstly delete, then write new features later.

 

 

Takashi
p.s. "fme_where" seems to be unavailable in the Geodatabase (File Geodb API) writer. If your geodatabase is File Geodatabase, try using the Geodatabase (File Geodb ArcObjects) writer.

Reply


Community Stats

30,108
Posts
114,294
Replies
37,743
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.