postgis – How to COPY Geometry with SRID in PostGIS

postgispostgresqlwell-known-text

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

Best Answer

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, :-)

Related Solutions

[GIS] Ensuring SRID on geometry column in PostGIS

You don't need constraints for PostGIS 2.x, just us typmods. For 2D geometries with SRID=4326, the typmod is geometry(Geometry,4326). Or for M-dimension geometries, it would be geometry(GeometryM,4326) (you get the idea).

Example:

create temp table sometable(wkt geometry(Geometry,4326));

-- works
insert into sometable(wkt) values('SRID=4326;POINT(1 2)')
insert into sometable(wkt) values('SRID=4326;LINESTRING(1 2, 3 4)')

-- does not work
insert into sometable(wkt) values('POINT(1 2)')
-- ERROR:  Geometry SRID (0) does not match column SRID (4326)
insert into sometable(wkt) values('SRID=4326;POINT M(1 2 3)')
-- ERROR:  Geometry has M dimension but column does not

But as others have cautioned, make sure you can use the wkt data in the end-user applications. Also, it isn't technically stored as well-known text—it is stored as well-known binary or WKB.

[GIS] Copy geometry data as HEXEWKB from csv to table in PostGIS

When you see an error like:

st_setsrid(unknown, integer)

it doesn't mean the ST_SetSRID does not exist, it means that there is a problem with the argument type unknown.

So, you can fix this by casting the HexEWKB directly to a geometry, using the cast operator, ::, and then call ST_SetSRID on that:

INSERT INTO  tout.battest(geom) VALUES 
(ST_SetSRID('010600000001000000010300000001000000050000007041F528CB332C413B509BE9710A594134371E05CC332C4111F40B87720A594147E56566CD332C4198DF5D7F720A594185EF3C8ACC332C41C03BEDE1710A59417041F528CB332C413B509BE9710A5941'
::geometry, 2154));

This works because the HexWBK is already a geometry representation, but in a string format, hence the need for a cast (see this Stack Overflow answer explaining the :: operator as a shortcut for cast). This also means that you do not need any of the ST_From...WKB type operators.

If you want to see the geometry directly, just use ST_AsText, again demonstrating that it is already a direct representation of a geometry.

SELECT ST_AsText('010600000001000000010300000001000000050000007041F528CB332C413B509BE9710A594134371E05CC332C4111F40B87720A594147E56566CD332C4198DF5D7F720A594185EF3C8ACC332C41C03BEDE1710A59417041F528CB332C413B509BE9710A5941');

which returns:

MULTIPOLYGON(((924133.579996152 6564295.65010458,924134.009996152 6564298.11010458,924134.699996152 6564297.99010458,924134.269996152 6564295.53010458,924133.579996152 6564295.65010458)))

EDIT: It is worth mentioning that as you mention CSV in the title, if you have lots of rows to insert, it might be quicker to run the COPY command on the CSV file to load the data into a geometry (mulipolygon) column with no SRID set and then use:

ALTER TABLE 'battest' ALTER COLUMN geom TYPE geometry(MULTIPOLYGON, 2154) USING ST_SetSRID(geom, 2154)

where you need the using clause to prevent unknown SRID errors in the alter table part.

Content of foo.txt:

Table:

Command:

Error message:

Versions:

Best Answer

Related Solutions

[GIS] Ensuring SRID on geometry column in PostGIS

[GIS] Copy geometry data as HEXEWKB from csv to table in PostGIS

Related Question