[postgis-users] Updated shapefile to update postgres database

Paragon Corporation lr at pcorp.us
Sun Jun 15 02:51:27 PDT 2008 

Take a look at our article on the subject if you haven't already.

It covers how to create gist and btree indexes

http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut01

Samples exist in the postgis docs too.



-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of SenTnel
Sent: Sunday, June 15, 2008 4:37 AM
To: postgis-users at postgis.refractions.net
Subject: RE: [postgis-users] Updated shapefile to update postgres database


Thanks Regina for your help! I must tell you that the worst part of this is
not knowing what most of what you tell me means! :confused: Im trying to
become familiar with this but don't know much...  Totally ignore btree, gist
index, but I'll google it to see what I can find. As far as the
configuration it is the installation's default, suggestions are welcome!
=^D 

... again... Thanks!


Paragon Corporation-2 wrote:
> 
> It shouldn't take that long I don't think.  123,000 records is not a lot.
> Granted this is a suboptimal delete.
> 
> First thing to verify
> 
> 1) Make sure to have a btree index on street
> 2) I assume you have a gist index on your the_geom field already.  If 
> not that could be the culprit.
> 3) What are your postgresql.conf settings like for work_mem etc.  If 
> these are at there defuats, then that would be a problem too.
> 
> The other alternative is to do with an IN instead of an EXISTS.  That 
> would at least not be correlated, but I'm not clear if an
> 
> the_geom IN (SELECT ....)
> 
> Would match up correctly since I suspect it may use = for compare
> 
> 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: Sunday, June 15, 2008 1:34 AM
> To: postgis-users at postgis.refractions.net
> Subject: RE: [postgis-users] Updated shapefile to update postgres 
> database
> 
> 
> Hi Regina!
> 
> Sorry took me so long to reply to this post, crazy week around here, 
> but thank god there's always a weekend to look forward!
> 
> This week I tried this method twice but finally had to stop it. First 
> time I stopped the query after 4 hours thinking there was something 
> wrong. Two nights later I tried again, left it running all night, next 
> day, almost 10 hours later the query was running still. My table has 
> 123,000+ rows, and just want to ask you if for such size is normal to 
> take that long, if so I'll try again, if not, if you don't think it 
> should take that long then you have any other suggestion?
> 
> Thanks again!
> 
> 
> 
> Paragon Corporation-2 wrote:
>> 
>> I wonder if its just called Equals in the old version.
>> 
>> Try doing just ~=. If your geometries are exactly the same that will 
>> probably be better anyway
>> 
>>  DELETE FROM mystreets_table
>>  WHERE street = 'N/A' AND
>>  EXISTS (SELECT n.street FROM mystreets_table n  WHERE n.street <> 'N/A'
>>  AND n.street > ''  AND n.the_geom ~= mystreets_table.the_geom)
>> 
>> 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: Saturday, June 07, 2008 4:36 PM
>> To: postgis-users at postgis.refractions.net
>> Subject: RE: [postgis-users] Updated shapefile to update postgres 
>> database
>> 
>> 
>> Hello Regina! Thanks for your help, but when I try running it I got 
>> this error message:
>> 
>> 
>> ERROR:  function st_equals(geometry, geometry) does not exist
>> HINT:  No function matches the given name and argument types. You may 
>> need to add explicit type casts.
>> 
>> :confused:
>> Thanks!
>> 
>> 
>> 
>> Paragon Corporation-2 wrote:
>>> 
>>> Here is a thought, but not sure how slow this will be
>>> 
>>> DELETE FROM mystreets_table
>>> WHERE street = 'N/A' AND
>>> EXISTS (SELECT n.street FROM mystreets_table n  WHERE (n.street <> 'N/A'
>>> AND
>>> n.street > '')  AND ST_Equals(n.the_geom, mystreets_table.the_geom))
>>> 
>>> The st_equals might be better to replace with ~=  if you want to 
>>> match exact vertices.  I presume ~= is actually more efficient
>>> 
>>> 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: Saturday, June 07, 2008 2:25 AM
>>> To: postgis-users at postgis.refractions.net
>>> Subject: Re: [postgis-users] Updated shapefile to update postgres 
>>> database
>>> 
>>> 
>>> Hello! Im confused about this issue: 
>>> 
>>> I have a street (polyline) duplicated, one on top of the other, 
>>> exact same features, same "the_geom", but one bears the street name
>>> (column:"street") "main street" and the other bears "N/A" as street 
>>> name. Now my maps shows the street but the name that shows is "N/A", 
>>> and I would like to delete the "N/A" polyline, how can i do 
>>> basically
>>> this:
>>> 
>>> delete from mystreets_table street = 'N/A' where 'N/A' = street 
>>> 'main street'.the_geom (delete from mystreets table the street named
'N/A'
>>> where the_geom in 'N/A'
>>> equals the_geom in street "main street") another words, delete only 
>>> the N/A streets that the_geom is same as "main street", I hope you 
>>> understand,
>>> 
>>> Thanks! 
>>> 
>>> 
>>> 
>>> 
>>> SenTnel wrote:
>>>> 
>>>> Thanks Regina, Chris and all of you for your help. Im truly newby 
>>>> but with such help Im ready to start the procedures, Ill keep you 
>>>> posted of my progress or else if some issue comes along that may 
>>>> require some light from you, Thanks again and I hope my 
>>>> problem/solution may be of help to many others,
>>>> 
>>>> Thanks again! 
>>>> 
>>>> 
>>>> 
>>>> 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
>>>>> 
>>>> 
>>>> 
>>> 
>>> --
>>> View this message in context:
>>> http://www.nabble.com/Updated-shapefile-to-update-postgres-database-
>>> t
>>> p
>>> 170535
>>> 62p17705777.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
>>> 
>>> 
>>> 
>>> _______________________________________________
>>> 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-t
>> p
>> 170535
>> 62p17712835.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
>> 
>> 
>> 
>> _______________________________________________
>> 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-tp
> 170535
> 62p17846638.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
> 
> 
> _______________________________________________
> 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-tp170535
62p17847480.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

More information about the postgis-users mailing list