[Gdal-dev] Basic Pg Question

[Matthew Perry]

On 9/27/07, Brian Hamlin <maplabs at light42.com> wrote:
> >  ok, I found another paragraph in the docs that provided enough clues
> > to move forward... I just use 4326 as SRID and the right things
> > happen..  I wrote the insert script using Perl and DBI, so its pretty
> > easy to change and make experiments..
> >
> > unfortunately, the import is WAY slower with the Update table in the
> > loop, and it essentially crashed after only 10% of the rows done!
> > ERROR: shared buffer has table corrupted    :(
>
> I re-ran the script, it took almost 20 minutes, then the server process
> closed the connection (on localhost), and less than 20% of the rows are
> imported!  this is not working... The UPDATE with SETSRID(MakePoint())
> is way too slow
>
> I think I should write a script that generates the WKT as a field, and
> import that.. Pointers welcome
>     -Brian

Brian,

 Strange; my experience is that UPDATE with MakePoint is pretty fast
and I've used it on multi-million row tables.

 Not having seen your perl script I can't say for sure but  It sounds
like you're running UPDATE in a loop after each row is inserted? If
this is the case, the update will have to be performed on the entire
table for every row inserted making it exponentially slower.

Try either a) running update once after all the rows are inserted or
b) using a WHERE clause on the UPDATE to make sure you're only
updating the row that was just inserted.




-- 
Matthew T. Perry
http://www.perrygeo.net

"Never ascribe to malice, that which can be adequately explained by
incompetence."