[postgis-users] Updated shapefile to update postgres database

[Paragon Corporation]

Forgot make sure you 

Vacuum analyze yourtable

After you create indexes and load lots of data. 

-----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-tp
> 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-tp170535
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