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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of SenTnel Sent: Sunday, June 15, 2008 4:37 AM To: postgis-users@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: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > SenTnel > Sent: Sunday, June 15, 2008 1:34 AM > To: postgis-users@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: [EMAIL PROTECTED] >> [mailto:[EMAIL PROTECTED] On Behalf Of >> SenTnel >> Sent: Saturday, June 07, 2008 4:36 PM >> To: postgis-users@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: [EMAIL PROTECTED] >>> [mailto:[EMAIL PROTECTED] On Behalf Of >>> SenTnel >>> Sent: Saturday, June 07, 2008 2:25 AM >>> To: postgis-users@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@postgis.refractions.net >>> http://postgis.refractions.net/mailman/listinfo/postgis-users >>> >>> >>> >>> _______________________________________________ >>> postgis-users mailing list >>> postgis-users@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@postgis.refractions.net >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> >> >> _______________________________________________ >> postgis-users mailing list >> postgis-users@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@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > _______________________________________________ > postgis-users mailing list > postgis-users@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@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
