[postgis-users] Updated shapefile to update postgres database
Chris Hermansen
chris.hermansen at timberline.ca
Wed May 7 07:42:00 PDT 2008
With respect to Regina's comments on the spatial approach, I think you
should try to answer another question as well. Is this a one-time
exercise or will you be doing this regularly?
Again, pursuing the spatial approach, the key thing is to determine how
congruent the old and new road geometries are. If they are the same,
except for the updates, this process should be more straightforward.
However, if they are different - let's say the old ones are mapped with
a zoom transfer scope or from satellite and the new ones are GPSed, then
I think you'll have centrelines that are near each other at best.
In either case, buffering the old roads (which have the street type
attribute) and intersecting the new roads is the key to the process.
What you are hoping is that the new road falls generally within the old
road's buffer (which is only a hope, and you may find some cases where
that doesn't occur or where you have to make the buffer so large that it
grabs other roads as well).
When you do an st_intersects() to find the new roads that fall within
the old road buffers you will get multiple roads that intersect. You
will need to return st_intersection() of these new roads to get the part
of their geometries that fall within the buffer. You will need to
return st_length() of these partial geometries to get the length. So
now it looks something like this:
select
new.road_id,st_length(st_intersection(new.the_geom)) as new_int_length,
old.road_id,old.st_length(old.the_geom) as old_length,old.road_type
from old,new
where st_intersects(st_buffer(old.the_geom,10.0));
You'll have to experiment with that buffer distance (10.0 in my example
above). If your street network is mostly orthogonal I would think you
would get pretty good results with your buffer distance being less than
half the average block length for example.
You might want to put the results of the above query in a table.
Again, if you're lucky you should be able to just select out the rows
where the new_int_length is longest, or where the absolute value of
new_int_length - old_length is minimum.
Obe, Regina wrote:
> 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
>>
>>
>>
>
>
--
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
More information about the postgis-users
mailing list