Search code examples
databasepostgresqlcsvimportpostgis

Transfer polygons coordination to a format which PostgreSQL can understand

I'm using Django with PostGIS and I'm trying to import some data in my Postgres SQL. I have a geometry(Polygon, 4326) column in my database:

poly            | geometry(Polygon,4326) | not null

Now I want to import some CSV data to my database: This is my data:

poly, district_number
"((51.356568, 35.765461), (51.356589, 35.765382), (51.356461, 35.76536), (51.356441, 35.765429), (51.356436, 35.765446), (51.356408, 35.765561), (51.356537, 35.765582), (51.356566, 35.76547), (51.356568, 35.765461))", 2
"((51.356568, 35.765461), (51.356589, 35.765382), (51.356461, 35.76536), (51.356441, 35.765429), (51.356436, 35.765446), (51.356408, 35.765561), (51.356537, 35.765582), (51.356566, 35.76547), (51.356568, 35.765461))", 3
....

And I'm using this command:

COPY get_house_info_house(poly, district_number)
FROM '/tmp/data.csv' DELIMITER ',' CSV HEADER;

And I get this error:

ERROR:  parse error - invalid geometry
HINT:  "((" <-- parse error at position 2 within geometry
CONTEXT:  COPY get_house_info_house, line 2, column poly: " 
((51.356568, 35.765461), (51.356589, 35.765382), (51.356461, 
35.76536), (51.356441, 35.765429), (51...."

I think Postgres doesn't recognize the poly column format.I can transfer my data to any format like GeoJson or any other format But the problem is how to transfer it to a format which Postgres can understand it.

Also in similar questions, I found "ST_GeomFromGeoJSON" but I don't know how to use it in a CSV import.

Solution

  • In COPY data, you may use the EWKT format for geometry values, with the SRID at the beginning.

    Example (verbatim; in the context of a CSV file, the field would be surrounded by double quotes):

    SRID=4326;POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239,-71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))

    See https://postgis.net/docs/UpdateGeometrySRID.html or PostGIS COPY geometry with SRID? for more.