PostgreSQL COPY FROM STDIN Expressions

Question

I am attempting to use COPY FROM STDIN to import data into my table. One of the columns in my table is of type geometry. My command looks something like this...

COPY "WeatherStations" ("Station_ID", "Station_Code", "Station_Name", "Station_Location") FROM stdin;
1       KAVP    WILKES-BARRE    ST_GeomFromText('POINT(41.338055 -75.724166)')
2       KOKV    WINCHESTER      ST_GeomFromText('POINT(39.143333 -78.144444)')
3       KSHD    SHENANDOAH      ST_GeomFromText('POINT(38.263611 -78.896388)')
...

However, I think it is attempting to insert the text "ST_GeomFromText('POINT..." and failing instead of evaluating the expression and inserting the result of the expression. Does anyone know what might be going on here and how I can get the actual geoms inserted?

score 31 · Accepted Answer · edited May 23 '17 at 11:54

I had a bad time figuring out how to bulk copy/load geometry data into PostGIS using the COPY FROM STDIN command, I couldn't find official documentation on this topic.

Altering the column during the bulk load (the ALTER TABLE / SET DATA TYPE / USING) was not an option to me because it is only supported in PostGIS 2.0+ for the Geometry type, nor was acceptable the use of a temporary table.

There is indeed a direct way to do it (at least in PostGIS 1.5.2+). You can simply rewrite the data for your copy statement this way, using a simple WKT (Well-known text) representation for your Geometry data:

1       KAVP    WILKES-BARRE    POINT(41.338055 -75.724166)
2       KOKV    WINCHESTER      POINT(39.143333 -78.144444)
3       KSHD    SHENANDOAH      POINT(38.263611 -78.896388)

If you have enforced a SRID constraint on the geometry column you'll have to use the following syntax (in this example the SRID is 4326) known as EWKT (Extended Well-Known Text, which is a PostGIS specific format):

1       KAVP    WILKES-BARRE    SRID=4326;POINT(41.338055 -75.724166)
2       KOKV    WINCHESTER      SRID=4326;POINT(39.143333 -78.144444)
3       KSHD    SHENANDOAH      SRID=4326;POINT(38.263611 -78.896388)

Closing note: there must be no space between "POINT" and the opening parenthesis "(", or the COPY will still return error saying your geometry data has an invalid format.

For anyone playing along at home, this will also work for `COPY FROM` for CSV. This should be better documented in PostGIS. It took me a better part of an afternoon to find your post. Thank you very much for your contribution. — kindrobot, Oct 18 '13 at 19:59
Still valid in 2017. At least I can avoid `shapely`'s slow `wkb_hex` function. — Michael, Jan 22 '18 at 12:14

score 2 · Answer 2 · answered Apr 18 '12 at 20:17

2

You could omit the function wrapping the text, import into a temporary table with text column, and then run INSERT/SELECT into the permanent table with the function doing the conversion in that step.

INSERT INTO "WeatherStations"
  ("Station_ID", "Station_Code", "Station_Name", "Station_Location")
  SELECT "Station_ID", "Station_Code", "Station_Name",
         ST_GeomFromText("Station_Location")
    FROM "TempWeatherStations";

answered Apr 18 '12 at 20:17

kgrittn

18,113
3
39
47

This is basically what I ended up doing. I add the data into a text column, create a temporary geometry column, convert the data, then drop the old column and rename the new one. Not the prettiest, but it works. – elynnaie Apr 18 '12 at 22:52
1

If you're going to do that in one table, you could simplify the process slightly, I think, by altering the column type using the USING clause for conversion.. – kgrittn Apr 19 '12 at 12:08
Did not know about the ALTER TABLE / SET DATA TYPE / USING syntax! Much prettier than adding and removing columns. – elynnaie Apr 23 '12 at 18:21

score 0 · Answer 3 · edited Apr 04 '18 at 13:33

0

You will keep all the values in .csv file and try like this:

CAT /path/file/demo.csv | psql -u <username> -h <localhost> -d<database> 
-c "COPY "WeatherStations" ("Station_ID", "Station_Code", "Station_Name", 
"Station_Location") FROM stdin;"

This will work.

edited Apr 04 '18 at 13:33

Eric Aya

69,473
35
181
253

answered Apr 04 '18 at 12:56

Dhananjayan K

175
2
11

score -1 · Answer 4 · answered Apr 18 '12 at 19:57

-1

Point's value looks something like this: 0101000020E6100000DA722EC555552B40CDCCCCCCCC0C4840.

I typically keep latitude and longitude columns in my tables and build spatial data with triggers.

I don't know how to copy POINTs from stdin otherwise.

answered Apr 18 '12 at 19:57

vyegorov

21,787
7
59
73

PostgreSQL COPY FROM STDIN Expressions

4 Answers4