Re: Import from CSV error

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(at)yahoo(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice