Re: Import from CSV error - Mailing list pgsql-novice

Steve Crawford wrote
>> COPY temp FROM 'C:/Program Files/PostgreSQL/9.2/data/tube_o_r.txt'
>> DELIMITER ';';
>>
>> ERROR:  parse error - invalid geometry
>> HINT:  "ST" <-- parse error at position 2 within geometry
>> CONTEXT:  COPY temp, line 1, column the_geom:
>> "ST_GeomFromText('LINESTRING(-0.11453 51.50366,-0.11450
>> 51.50369,-0.11422 51.50404,-0.11392 51.50397,..."
>>
>> My file is a txt file, with no headers, with the two columns separated
>> by a semi-colon. Line one of the file is shown below:
>>
>> 1;ST_GeomFromText('LINESTRING(-0.11453 51.50366,-0.11450
>> 51.50369,-0.11422 51.50404,-0.11392 51.50397,-0.11367
>
> Since this has gone unanswered for a couple days I'll venture a _guess_
> based on little (~0) experience with PostGIS.
>
> I *suspect* that the input file should only have the Well Known Text
> (WKT) representation of the linestring and should not have the
> ST_GeomFromText constructor function as part of the input.
>
> Geogeeks? Is this right or wrong?

This is not PostGIS specific.  COPY does not allow for anything other than
implicit casting to occur against the input data.  Because of this the
supplied data in the file has to be considered "raw" input data that will be
directly copied to the table without alteration.

If you really need to have the database engine process the input data you
will have to construct an SQL insert statement and execute that directly.

INSERT INTO temp (id, the_geom)
VALUES
(1, ST_GeomFromText('......'),
(2, ST_GeomFromText('......'),
(3, ST_GeomFromText('......')
;

The only way to make a COPY work is if the data in the column could
correctly be processed if written as:

'..data..'::geometry --(or whatever would be correct PostGIS syntax for
this).  This is a PostGIS question that I cannot answer.

The INSERT INTO ... VALUES is actually pretty well performing as long as you
avoid repeating INSERT for every record.  If that is not possible/desireable
you get only slightly less-bad performance by using a transaction:

BEGIN;
INSERT INTO ...;
INSERT INTO ...;
INSERT INTO ...;
COMMIT;

There is more parsing involved this way but you avoid the WAL/checkpoint hit
that you'd encounter without the transaction.

David J.





--
View this message in context: http://postgresql.1045698.n5.nabble.com/Import-from-CSV-error-tp5776075p5776424.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.