Re: Import from CSV error
От | James David Smith |
---|---|
Тема | Re: Import from CSV error |
Дата | |
Msg-id | CAMu32AAczipKdSpaR=D_AY9G10=rgXHtLWQxCQ5PQeXyqNZNgw@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Import from CSV error (David Johnston <polobo@yahoo.com>) |
Список | pgsql-novice |
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
В списке pgsql-novice по дате отправления: