2

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?

  • 1
    where is your polygon stored? can you add a single (small) example of what it looks like – Ian Turton Mar 6 '20 at 15:02
  • sorry but i am new to postgis and i don't undrestand your question. What do you mean where is my polygon stored? – destheo Mar 6 '20 at 15:12
  • i edited my post with my .csv first line attached – destheo Mar 6 '20 at 16:46
  • 1
    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 ... – Cyril Mikhalchenko Mar 6 '20 at 17:51
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.

  • 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... – destheo Mar 12 '20 at 13:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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