Trying to import a CSV file with polygons to PostGIS

Question

I have a CSV file with polygons. I have created table 'sinikia' like this:

CREATE TABLE askisi.sinikia ( 
 sinikia_id SERIAL PRIMARY KEY,
 name VARCHAR(50), 
 area NUMERIC NOT NULL, 
 perimeter NUMERIC NOT NULL, 
 geom geometry(polygon, 2100));

and tried to import my CSV file, which looks like this: enter image description here

and I got a PARSE ERROR. I have also tried to put brackets (single and double) to the geometry column, taking the same error.

Afterwards I created another table and instead of geometry I set column 'geom' as a TEXT.

CREATE TABLE askisi.sinikia ( 
 sinikia_id SERIAL PRIMARY KEY,
 name VARCHAR(50), 
 area NUMERIC NOT NULL, 
 perimeter NUMERIC NOT NULL, 
 geom TEXT);

Importing CSV worked fine but now I can not ALTER column geom from TEXT to GEOMETRY. I am probably missing something. Is there any idea?

• Ian Turton: where is your polygon stored? can you add a single (small) example of what it looks like
• destheo: sorry but i am new to postgis and i don't undrestand your question. What do you mean where is my polygon stored?
• destheo: i edited my post with my .csv first line attached
• Cyril Mikhalchenko: Try, or provide a link to download an example data in *.csv format, or insert it into the body of the text as a block in *.txt format ...

Answer 1

Those fields only contain numbers that are getting parsed as TEXT into a single string; PostGIS can only cast from valid WKT/WKB to GEOMETRY implicitly!

Best option would be to wrap those numbers per line in 'POLYGON(( <numbers> ))'; assuming your TEXT column approach, try

ALTER TABLE askisi.sinikia
 ALTER COLUMN geom TYPE GEOMETRY(POLYGON, 2100)
 USING ST_GeomFromText('POLYGON((' || geom || '))', 2100)
;

---

Alternatively, assuming the GEOMETRY(POLYGON, 2100) typed column approach, wrap that field in EWKT (i.e. 'SRID=2100;POLYGON(( <numbers > ))') representation as above, but within the CSV; that way, a COPY would trigger the implicit typecast to GEOMETRY during import.

• destheo: Thank you so much.. The TEXT column approach, worked for me with some changes..... ST_GeomFromText('POLYGON'|| geom ||')', 2100); and I replaced the double parenthesis at the end of my .csv file, with single...