6

Is it possible to use COPY on a file full of WKT POINTs without dropping the explicit SRID on the destination column?

I'm trying to insert several million rows of WGS84 points and am using COPY for better performance over individual INSERTs.

Content of foo.txt:

POINT(0 0)
POINT(0 1)
POINT(0 2)

Table:

CREATE TABLE foo (
    geom GEOMETRY(POINT, 4326)
);

Command:

COPY foo FROM 'foo.txt';

Error message:

ERROR:  Geometry SRID (0) does not match column SRID (4326)
CONTEXT:  COPY foo, line 1, column geom: "POINT(0 0)"

Versions:

  • PostgreSQL 9.2
  • PostGIS 2.3
4
  • I don't know if it was a better solution, but as workaround, import your txt file in a text column and after, with SQL, insert it in your final table by specifying SRID. Commented Aug 1, 2019 at 21:02
  • Try: SRID = 4326; POINT (0 0) Commented Aug 2, 2019 at 8:49
  • 1
    Cyril's comment and CL's answer are both totally legit. One other approach is to load the data into a column without any SRID set and then run postgis.net/docs/UpdateGeometrySRID.html, noting that you need to cast to correct SRID, see the USING ST_Transform in the docs. This is almost certainly the quickest of the three approach, as COPY is super fast, and you will be doing the spatial transform all at once in the DB after. Commented Aug 2, 2019 at 10:57
  • @Cyril: Could you post that as an answer so I can accept it?
    – dbazile
    Commented Aug 2, 2019 at 16:27

2 Answers 2

5

To solve your question, use the following syntax:

Content of foo.txt:

SRID=4326; POINT(0 0)
SRID=4326; POINT(0 1)
SRID=4326; POINT(0 2)

with respect, :-)

4
  • 2
    Here it didn't work with spaces around = sign, I used SRID=4326; POINT(0 0) instead. Commented May 22, 2020 at 1:05
  • 2
    @caiohamamura, you're right, it's a typo, I'll put my answer in order :-)... the mistake came from my comment... Commented May 22, 2020 at 8:13
  • Thanks for this. I used shapely wkt and added the SRID in a copy statement with pyscopg(3) and it worked! Commented Mar 14, 2023 at 6:53
  • @Tim Trewartha, it's good that it worked for you too :-)... Commented Mar 14, 2023 at 17:14
1

To change the inserted data, you can use an INSTEAD OF trigger on a view:

CREATE VIEW bar AS
SELECT ST_AsText(geom) AS geom FROM foo;

CREATE FUNCTION insert_bar() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO foo(geom) VALUES('SRID=4326;' || NEW.geom);
  RETURN NEW;
END;
$$;

CREATE TRIGGER insert_bar
INSTEAD OF INSERT ON bar
FOR EACH ROW
EXECUTE PROCEDURE insert_bar();

COPY bar FROM 'foo.txt';
4
  • 1
    This will work and is a good answer, but, I would think for performance reasons concatenating SRID=4326 in front of the WKT and then doing copy would be faster, as @Cyril suggests. Commented Aug 2, 2019 at 10:47
  • Yeah, the view can also use a completely different data type.
    – CL.
    Commented Aug 2, 2019 at 10:58
  • @JohnPowell: Thanks, SRID=4326;POINT(0 0) worked perfectly! Could you post that as an answer so I can accept it?
    – dbazile
    Commented Aug 2, 2019 at 15:47
  • 1
    To be fair, it was @Cyril who suggested it first. Glad it worked, though. Commented Aug 2, 2019 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.