Update data in postgis table with new version from shapefile

Question

I have a postgis table created by importing a shapefile containing land parcels. The county releases a new shapefile containing updated data monthly and I want to incorporate that into my database.

The original data was uploaded using QGIS' database manager plugin. I'm fairly new to postgis.

I have views based on the data, so dropping the table and re-uploading isn't a great option. What I did last time was to use a DELETE command to completely empty the table then uploading the shapefile into a temporary table and using a INSERT * INTO ... command to copy all the data over.

This seems like a cumbersome process to go through monthly, especially since only a small percent of the data changes. Is there a better way to go about this?

• user30184: Use ogr2ogr but read first how to use the OGR_TRUNCATE configuration option from gdal.org/drv_pg.html.
• user52245: I'm getting this error: Warning 1: Value 13007217.0359000005 of field Shape_area of feature 193487 not successfully written. Possibly due to too larger number with respect to field width... any idea what would cause that? My Here's the command I was using: ogr2ogr -append -f "PostgreSQL" PG:"dbname=issaquah_territory" parcel_address/parcel_address.shp -nlt MULTIPOLYGON -nln parcels_kcgis -a_srs spatialreference.org/ref/esri/102748 --config OGR_TRUNCATE YES
• user30184: I guess that your attribute was created to suit with the initial input data, but now in the new dataset you have a record with higher precision "Value 13007217.0359000005". Maybe you could prevent that by making the initial import with lco PRECISION=NOwhich would not create attributes as fixed NUMERIC(width,precision). See gdal.org/drv_pg.html. Anyway, Shape_area is IMHO rather useless attribute outside ESRI landscape and the loss in accuracy you are getting now because of truncated numbers may not be important for you.
• user52245: I'll give it a try. The weird thing is I deleted the original table, did an import with ogr2ogr (without append flag) then immediately tried the command above from the same shapefile. I got the same error as well as an error complaining it's over 2GB. (About 600k features in layer) Oddly, I tried adding -select ... and listing only a couple attributes (specifically avoiding shape_area) and received the same error still referencing shape area
• user52245: Found the issue. Although I was using -select... to not insert the shape_area field, I was putting it at the end of the command. Only worked properly when I put -select... right after the ogr2ogr command

Answer 1

You can use --config OGR_TRUNCATE YES and also from reading the comments, try using the option -unsetFieldWidth

OGR_TRUNCATE config option won't actually delete the table, is will delete all the records in the table. This will make it so VIEWS and other stuff in the database that rely on that table won't have dependency issues.

unsetFieldWidth option sets the width and precision of certain fields to 0 or simply tells it to ignore hard coded constraints that might limit features from being imported because they are "too long".