[GIS] column is of type polygon but expression is of type geometry

geojsongoogle mapspostgis

I am trying to save the coordinates of polygon drawn in google map into postgis as polygon datatype.

currently coordinates are JSON type.

But drying manager is not giving me the coordinates of last point and this is correct because in polygon the coordinates of last point is same as first point. For ex- the coordinates are-

{"type":"polygon","coordinates":[[40.48873742102281,-94.471435546875],[39.66491373749128,-94.7021484375],[40.17047886718109,-93.548583984375]]}

Here drawing manager is not giving the coordinates of last point which is same as first point, so while saving them in postgis, the error is-

column "column_name" is of type polygon but expression is of type geometry

I am using-

"INSERT into table_name (column_name) VALUES (ST_GeomFromGeoJSON('".($coordinates) ."'))";

column_name is of polygon type.

Edit- Code used to create polygons-

drawingManager.setMap(map);
    google.maps.event.addListener(drawingManager, 'overlaycomplete', function(polygon) {
var coordinates_poly = polygon.overlay.getPath().getArray();

    var lat_poly = [];
    var lng_poly = [];
    var Coordinates = [];
    for(var i = 0; i <coordinates_poly.length; i++){
    lat_poly = coordinates_poly[i].lat();
    lng_poly = coordinates_poly[i].lng();
    Data = [lat_poly,lng_poly];
    Coordinates.push(Data);
    }
    var JSON_Coordinates = JSON.stringify(Coordinates);
    document.getElementById("data").value= "{\"type\":\"polygon\",\"coordinates\":" + JSON_Coordinates +"}";
    });
    }

Where "data" is form element.

Someone please help me how to save coordinates as polygon type.

Best Answer

ST_GeomFromGeoJSON is a PostGIS function that returns a PostGIS geometry, which in turn can hold a polygon. Therefore, it's ouptut cannot be used to populate a Postgres Polygon column.

Since you are using Postgis, the solution would be to update your table to use the proper geometry type.

That being said, you are swapping coordinates in your code. They must be set as Longitude first, then Latitude. Then you are missing a set of [] around the coordinates. I would recommend reading this guide.

Note that ST_GeomFromGeoJSON will add the missing coordinate (the repetition of the 1st coordinate) for you, though you could do it in your code

google.maps.event.addListener(drawingManager, 'overlaycomplete', function(polygon) {
    var coordinates_poly = polygon.overlay.getPath().getArray();

    var lat_poly = [];
    var lng_poly = [];
    var Coordinates = [];
    for(var i = 0; i <coordinates_poly.length; i++){
      lat_poly = coordinates_poly[i].lat();
      lng_poly = coordinates_poly[i].lng();
    
      //Long - Lat
      Data = [lng_poly,lat_poly];
      Coordinates.push(Data);
    }

    //Add first coordinate 
    Data = [coordinates_poly[0].lng(),coordinates_poly[0].lat()];
    Coordinates.push(Data);
    
    var JSON_Coordinates = JSON.stringify(Coordinates);

    //Add missing []
    document.getElementById("data").value= "{\"type\":\"polygon\",\"coordinates\":[" + JSON_Coordinates +"]}";
    });
 }
Related Question