[postgis-users] RE: Load data with the copy command

[Gregory S. Williamson]

Nicol,

I was taking data from an Infomrix system, so I used a perl script to reformat the text (you want to use the WKT form to use the copy command) into postGIS format (the SRID nomenclature changes a little, for instance). I ended up with data in the form of these line (the following are each single lines, formatting may have suffered in cut and paste):

418876|101738758|375|filer4|382|filer4|41938076|/some/path|/some/otherpath|O3411624.NES.100020246.jpc|UTM83z11|33|SRID=4326;POLYGON((-116.0667139
18 34.753371034,-116.067485053 34.6846640667,-115.996754024 34.6841019763,-115.995923042 34.7528079377,-116.066713918 34.753371034))

418877|101738759|375|filer4|382|filer4|41937470|/some/path|/some/otherpath/|O3511456.SWS.1150749.jpc|UTM83z11|33|SRID=4326;POLYGON((-114.129077964
 35.191984989,-114.131527994 35.1222800723,-114.059186969 35.1205450182,-114.056675918 35.1902460793,-114.129077964 35.191984989))

The "|" is my delimiter, so when I load I use something like
"COPY sid_content_prem1 FROM '/usr/postgres/gexruntime/updates/loadfile.unl' DELIMITER "|" NULL "";

The NULL "" in the COPY command tells postgres to assume NULL from an empty column value. The last part of the load line is the GIS data. If you have data in a postgres database unloading it with
 COPY mytable TO '/fullpathname/unload.unl' WITH DELIMITER "|" NULL "";
will produce the same sort of file.

The COPY command is described in the postgres Reference manual; seems to require an explicit path to the file to be loaded and is not very "smart" ... if you have a column that uses a serial value, for instance, you want to define the values explicitly in the load file OR leave them out entirely and make sure the table being loaded specifies something like "DEFAULT NEXTVAL('my_column_serial')" so that load will see a NULL in the data and correctly calculate the serial to use. This does slow it down slightly.

Tge GIS stuff has a tool, shp2pgsql, which can produce load files from ESRI shape files. USed in its regular mode it will create SQL statements in the form "INSERT INTO tablename VALUES (...);" but there is a -D option that outputs the data in a format that COPY can use.

I have "cc-ed" my response to the GIS list so that other, wiser heads might speak to this. Usually best to post to a public list, if only so that answers get archived for future users.

Let me (and the list) know what specifics you have problems with -- it can be a little fiddly getting it set up but once you do the speed is decent to load data (and index and do statistics for it) and then the fun does begin !

Greg W.

-----Original Message-----
From:	Nicol Hermann [mailto:mapserver at geochem.de]
Sent:	Mon 6/7/2004 12:43 PM
To:	Gregory S. Williamson
Cc:	
Subject:	Load data with the copy command
Hi Greg,

i found a posting of you in the postgis user list where you described
that the fastest way to load data into postgresql is the copy command.
I have difficulties to find the right syntax to use this way with
geometry data. Do you have an example for me where i could learn how to
format the text file and the copy statement? Do i have to use the WKT
oder WKB format?

Many thanks
Greetings from Germany

Nicol

-- 
Nicol Hermann <mapserver at geochem.de>