[postgis-users] loading data

[Gregory S. Williamson]

Gerry --

Sorry about the slow reply -- got distracted and then the message got shuffled out of sight.

We had relatively clean data in that we are exporting from another spatial db. I found that loading with the created sql from the shp2psql tool wasn't working well because I'd hit an error and lose a chunk of data inserts up to the next commit. It got frustrating making sure I got all of the rows that were good and also dealing with the few bad ones. So I unloaded the data into WKT (Informix style) and then used a perl script to massage it into postgres/GIS form, testing for some error conditions and spitting out a seperate file with questionable records that I couldn't anticipate and code for (for instance, one client gives us polygons; they are all called multipolygons, which is "legal" but in turn was causing issues with the JTS code our runtime uses; I could detect this condition and fix the "type"). This perl script also let me make sure I had proper uniqe ids and the like.

Then I'd run the whole table's worth of inserts (usually a few thousand to about 700,000 rows per table) in one transaction. There were relatively few errors at this stage and I'd manually edit the data files (or tweak my perl code and rerun).

I toyed around with a system that would spit data a line at a time into postgres; failures would write the offending data line to a "errors" table that I could then process. This is similar to the way Informix can handle bulk data loads with the high speed loader -- it creates its own tables for exceptions. This hand-coded process was certainly slower than COPY commands and was taking time to code, so I ended up brute-forcing the data instead of this sort of finesse. I may go back to this if we end up needing to process moderate amounts of data of questionable provenance so I can automate most of the process. Not needed currently though, and at this point the bulk loads are using data we generate and so are less problematic.

HTH,

Greg Williamson
DBA
GlobeXplorer LLC

-----Original Message-----
From:	Gerry Creager N5JXS [mailto:gerry.creager at tamu.edu]
Sent:	Wed 4/21/2004 12:41 AM
To:	PostGIS Users Discussion
Cc:	
Subject:	Re: [postgis-users] loading  data
What do you do if there are duplicates?  How're you handling dupes/indices?

gerry

Gregory S. Williamson wrote:
> Copy works like a champ for spatial data, as far as our experience goes. As long as column order and NULL assignments agree, copying from file to table is no problem.
> 
> HTH,
> 
> Greg Williamson
> DBA
> GlobeXplorer LLC
> 
> -----Original Message-----
> From:	Wood Brent [mailto:pcreso at pcreso.com]
> Sent:	Thu 4/15/2004 10:20 AM
> To:	PostGIS Users Discussion
> Cc:	
> Subject:	[postgis-users] loading  data
> 
> Hopefully a simple query.
> 
> I've been using copy as my bulk loader for postgres.
> 
> copy isn't mentioned as working or not for tables with a postgis geometry
> column in the PostGIS docs so I fgured I'd ask here.
> 
> Does copy work? 
> If so, is there any special format for the geometry data?
> If I can copy from a table to file, will it work the other way (with the same  
>  format)?
> 
> (I know I could try this, but I figure it may appear to work but not, & there
> may be gotchas or caveats, so asking the experts for advice is safer :-)
> 
> 
> Thanks,
> 
>   Brent Wood
> _______________________________________________
> 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

-- 
Gerry Creager -- gerry.creager at tamu.edu
Network Engineering -- AATLT, Texas A&M University	
Cell: 979.229.5301 Office: 979.458.4020 FAX: 979.847.8578
Page: 979.228.0173
Office: 903A Eller Bldg, TAMU, College Station, TX 77843

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users