2

I have a csv file with thes two columns (amongst others): localisation and geo_point_2d.

Here is the content of one cell of localisation :

{"type": "MultiPolygon", "coordinates": [[[[-53.5441768169403, 5.50647948448245], [-53.5467839241028, 5.50461059944752], [-53.5478729009628, 5.5055717410565], [-53.5473901033401, 5.50614308560981], [-53.5477602481842, 5.50646346549284], [-53.5460811853409, 5.50824691035157], [-53.5441768169403, 5.50647948448245]]]]}

Here is the content of the cell on the same line as the previous one in the column 'geo_point_2d':

5.50638356893;-53.5461903708

I migrated the table to postgis and tried to change the types of these columns to geometry with :

ALTER TABLE my_table_name
ALTER COLUMN localisation TYPE geometry

For the column geo_point_2d I get :

ERROR:  parse error - invalid geometry
HINT:  "5.50638356893;" <-- parse error at position 14 within geometry
SQL state: XX000

I tried to change the ; by , but the error was still there.

For the column localisation I get :

ERROR:  parse error - invalid geometry
HINT:  "{"" <-- parse error at position 2 within geometry
SQL state: XX000

In the best case I would like to access to polygons instead of points. When getting the csv file a GeoJson files is also available but I needed to make some changes in the document before visualizing it. The GeoJson displays well on QGis. I guess I am under ESPG:4326

My question is : How can I get to change the type of the columns to geometry ?

6

You will need to tell PostGIS how to coerce the TEXT values into GEOMETRYs; the implicit type cast is able to cast WKT/WKB between TEXT and GEOMETRY (while you provide either GeoJSON or...well, two numbers as TEXT).

Run

ALTER TABLE <table>
  ALTER COLUMN localisation TYPE GEOMETRY(MULTIPOLYGON, <SRID>)
    USING ST_GeomFromGeoJSON(localisation)
;

Udate:

If your geometries are of different type, either use the generic GEOMETRY(GEOMETRY, 4326), or, in this case, force to ST_Multi:

ALTER TABLE <table>
   ALTER COLUMN localisation TYPE GEOMETRY(MULTIPOLYGON, <SRID>)
     USING ST_SetSRID(ST_Multi(ST_GeomFromGeoJSON(localisation)), 4326)
 ;

The expression after USING will be applied to the column value; you can do arbitrary things here as long as they return the correct column type as specified.

  • I changed the SRID to 4326 and got : ERROR: Geometry SRID (0) does not match column SRID (4326), with the sird set to 0 I get : ERROR: Geometry type (Polygon) does not match column type (MultiPolygon), then I changed MULTIPOLYGON to POLYGON and got : eometry type (MultiPolygon) does not match column type (Polygon) – Basile Feb 14 at 15:03
  • 1
    ST_SETSRID and ST_MULTI will solve that – Ian Turton Feb 14 at 15:14
  • As @IanTurton says; it's strange though since ST_GeomFromGeoJSON is supposed to default to EPSG:4326, as it's the GeoJSON standard. I can't pinpoint exactly where, but I experienced some typemod errors where they should not be with the current PG version. May need to investigate.... – ThingumaBob Feb 14 at 15:17
  • @Basile any progress? Did this help? – ThingumaBob 23 hours ago

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.