[postgis-users] Updated shapefile to update postgres database

[Obe, Regina]

SenTnel,

To add to what Chris and Steve W. said there are a couple of ways to do
this.

1) If you have some uniquely identifiable attribute fields you can use,
then that is the best approach
2) If you are basing simply on geometry, then you will need to decide
how different enough geometries can be to be considered the same road.

Basic approach
1) Import new shape file into a temp table, put indexes on identifying
attributes
2) update existing roads
3) add new roads

The attribute update way - you can do as Steve W. had mentioned or you
can do with a 2 table update statement

So lets say you have 2 fields road_name, road_dir that uniquely
identifies a road, then you can do something like
--update existing roads
UPDATE currenttable
	SET the_geom = t.the_geom, field2 = t.field2, field2 = t.field3
, etc..
FROM temptable As t
WHERE t.road_name = currenttable.road_name AND t.road_dir =
currenttable.road_dir

To add new records, you would do
--For the below this only works if you insure you have no nulls in
road_name of currenttable (and ideally you have a unique index on the
field or set of
fields you will use for identification

INSERT INTO currenttable(the_geom, road_name, road_dir, field2,field3,
...etc)
SELECT t.the_geom, t.road_name, t.road_dir, t.field2, t.field3, ....
FROM temptable As t LEFT JOIN currenttable c ON (t.road_name =
c.road_name AND t.road_dir = c.road_dir)
WHERE c.road_name IS NULL
	 
To do it based on geometry alone, that is kind of tricky and involves
too much guess work e.g. using ST_Difference, ST_Intersection,
ST_Distance etc. and comparing how different the lengths, areas are
based on some tolerance.  So I would suggest coming up with some
scheme of uniquely tagging your roads if you don't have one already.

Make sure to 

vacuum analyze currenttable

after done - since this will create a ton of junk for many records.

Hope that helps,
Regina

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
SenTnel
Sent: Wednesday, May 07, 2008 1:50 AM
To: postgis-users at postgis.refractions.net
Subject: Re: [postgis-users] Updated shapefile to update postgres
database


Thanks! Thats exactly what I want to do, but just don't know how to. I
don't
pretend for you to give me a step by step indication but Ill apreciate
some
instructions since Im really new to this and im eager to learn more
about
it. Thanks again!


Chris Hermansen wrote:
> 
> Presumably what you mean by "update" is that you want to replace old
> versions of roads already in PostGIS with new versions of the same
roads
> from shapefiles, and you want to keep the old street type in
conjunction
> with the updated geometry?
> 
> 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
>>   
> 
> 
> -- 
> Regards,
> 
> Chris Hermansen         mailto:chris.hermansen at timberline.ca
> tel+1.604.714.2878 * fax+1.604.733.0631 * mob+1.778.232.0644
> Timberline Natural Resource Group * http://www.timberline.ca
> 401 * 958 West 8th Avenue  * Vancouver BC * Canada * V5Z 1E5
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 

-- 
View this message in context:
http://www.nabble.com/Updated-shapefile-to-update-postgres-database-tp17
053562p17097758.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.