Thread: Import from CSV error

Import from CSV error

From

James David Smith

Date:

28 October 2013, 14:58:45

Hi,

Does anyone know why this doesn't work please?

DROP TABLE temp;
CREATE TABLE temp(id integer);
SELECT AddGeometryColumn('temp', 'the_geom', 4326, 'LINESTRING', 2);
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
51.50392,-0.11327 51.50384,-0.11329 51.50381,-0.11329
51.50381,-0.11512 51.50310,-0.11497 51.50312,-0.11500
51.50321,-0.11513 51.50350,-0.11555 51.50383,-0.11602
51.50405,-0.11814 51.50467,-0.11880 51.50497,-0.12130
51.50631,-0.12184 51.50647,-0.12285 51.50668,-0.12285
51.50668,-0.12487 51.50710,-0.12590 51.50724,-0.12807
51.50743,-0.12807 51.50743,-0.12965 51.50757,-0.13080
51.50769,-0.13104 51.50776,-0.13125 51.50788,-0.13176
51.50851,-0.13203 51.50884,-0.13306 51.50983,-0.13346
51.50997,-0.13392 51.51001,-0.13527 51.50996,-0.13527
51.50996,-0.13631 51.50992,-0.13696 51.50991,-0.13735
51.50997,-0.13769 51.51007,-0.13800 51.51023,-0.13832
51.51050,-0.13876 51.51102,-0.14143 51.51405,-0.14187
51.51451,-0.14226 51.51570,-0.14226 51.51570,-0.14154
51.51524,-0.14157 51.51532,-0.14163 51.51532,-0.14178 51.51530)',
4326)

Thanks

James

Re: Import from CSV error

From

Steve Crawford

Date:

30 October 2013, 20:46:12

On 10/28/2013 04:58 AM, James David Smith wrote:
> Hi,
>
> Does anyone know why this doesn't work please?
>
> DROP TABLE temp;
> CREATE TABLE temp(id integer);
> SELECT AddGeometryColumn('temp', 'the_geom', 4326, 'LINESTRING', 2);
> 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
> 51.50392,-0.11327 51.50384,-0.11329 51.50381,-0.11329
> 51.50381,-0.11512 51.50310,-0.11497 51.50312,-0.11500
> 51.50321,-0.11513 51.50350,-0.11555 51.50383,-0.11602
> 51.50405,-0.11814 51.50467,-0.11880 51.50497,-0.12130
> 51.50631,-0.12184 51.50647,-0.12285 51.50668,-0.12285
> 51.50668,-0.12487 51.50710,-0.12590 51.50724,-0.12807
> 51.50743,-0.12807 51.50743,-0.12965 51.50757,-0.13080
> 51.50769,-0.13104 51.50776,-0.13125 51.50788,-0.13176
> 51.50851,-0.13203 51.50884,-0.13306 51.50983,-0.13346
> 51.50997,-0.13392 51.51001,-0.13527 51.50996,-0.13527
> 51.50996,-0.13631 51.50992,-0.13696 51.50991,-0.13735
> 51.50997,-0.13769 51.51007,-0.13800 51.51023,-0.13832
> 51.51050,-0.13876 51.51102,-0.14143 51.51405,-0.14187
> 51.51451,-0.14226 51.51570,-0.14226 51.51570,-0.14154
> 51.51524,-0.14157 51.51532,-0.14163 51.51532,-0.14178 51.51530)',
> 4326)
>
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?

Cheers,
Steve

Re: Import from CSV error

From

David Johnston

Date:

30 October 2013, 21:18:30

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.

Re: Import from CSV error

From

James David Smith

Date:

31 October 2013, 02:49:19

Thanks both. Yeah, what I needed to do was to get rid of the
St_GeomFromText bit and just insert the LINESTRING bit. It works now.

James




On 30 October 2013 18:18, David Johnston <polobo@yahoo.com> wrote:
> 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.
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice