[postgis-users] Updated shapefile to update postgres database

[Stephen Woodbridge]

SenTnel wrote:
> Hello!
> 
> I would like some help updating a postgres database. This is the problem: We
> created the database using shp2pgsql to convert a shapefile that contains a
> city's street details (centerline), after we created the database we also
> created an aditional column to classify the street types (eg: street,
> avenues, highways, etc.), now we are working on the original shapefile,
> updating new roads, changes made to highways due to constructions
> modifications, etc., and we want to upload the "updated" shapefile, without
> afecting the actual database, another words, we would like to convert to
> postgres the updated shapefile with the new information (it cuold be the
> whole shapefile) but to keep the added clasiffication column intact. How can
> we do that?
> 
> Thanks

Well hopefully you have some key field for match the old data to the new 
data.

I would load the new data into a new table and then add a classification 
column to the new table and then update it based on the common key values.

update new_table set class='highway' where uid in ((select uid from 
old_table where class='highway'));

and repeat for each class type you have.

There is probably a better way to fo this with a join of some kind, but 
I'll let other chime in on that :)

if the new_table looks good you can rename it in a transaction like:

begin;
alter table old_table rename to archive_table;
alter table new_table rename to old_table;
commit;

and you will be using the new table.

-Steve W.