PostGIS - OpenLayers - ST_GeomFromGeoJSON() Inserting wrong coordinates into database

Question

I have a project where I have Polygons drawn in a Openlayers map. These objects get turned into GeojSON using format.writeGeometry(plot.geometry.getGeometry()). This is my first project using a PostGIS database in combination with OpenLayers and GeoJSON.

Then I send them to a NodeJS backend who then sends this data to a PostGIS database. 'INSERT INTO plots (user_id, geog) VALUES ($1, ST_GeomFromGeoJSON($2))'. Where the geog is the Geojson that I receive from the frontend.

The problem that I am facing is that the coordinates get distorted inside the Database. This is an example of how the coordinates are represented inside the database:

Here is the Geojson from the above example that is being inserted into the Database:

{
   "type":"Polygon",
   "coordinates":[
      [
         [
            352847.7681741,
            6591493.68325289
         ],
         [
            352853.14118403,
            6591495.88702873
         ],
         [
            352865.86122686,
            6591501.10405905
         ],
         [
            352866.13573636,
            6591501.21660883
         ],
         [
            352866.14658836,
            6591501.22104384
         ],
         [
            352856.32964058,
            6591526.19583173
         ],
         [
            352854.06390643,
            6591531.95998212
         ],
         [
            352853.92222987,
            6591531.90191122
         ],
         [
            352766.24950317,
            6591495.94284857
         ],
         [
            352766.18921573,
            6591495.91814133
         ],
         [
            352778.56476466,
            6591465.29940885
         ],
         [
            352847.7681741,
            6591493.68325289
         ]
      ]
   ]
}

And the polygon that is being generated in the PostGIS database:

SRID=4326;POLYGON((47.76817410002695 -73.68325288966298,53.14118402998429 -75.88702872954309,65.86122686002636 -81.10405904985964,66.13573635998182 -81.21660882979631,66.14658836001763 -81.2210438400507,56.32964057999197 -73.80416826996952,54.06390642997576 -68.04001787956804,53.92222986998968 -68.09808878041804,-33.75049682997633 -75.94284856971353,-33.81078426999738 -75.91814132966101,-21.43523533997359 -45.29940885026008,47.76817410002695 -73.68325288966298))

Use format.writeGeometry(plot.geometry.getGeometry(), {dataProjection: 'EPSG:4326', 'featureProjection': 'EPSG:3857'}) or format.writeGeometry(plot.geometry.getGeometry(), {dataProjection: 'EPSG:3857', 'featureProjection': 'EPSG:4326'}) Never able to sort out between input and output depending if write or read... — ThomasG77, Jul 01 '21 at 15:29
To go from those projected coordinates (front end) to the geographic coordinates (database result), you have to be projecting it somewhere ..but that step isn't described in your question. — elrobis, Jul 01 '21 at 15:30
@ThomasG77 format.writeGeometry(plot.geometry.getGeometry(), { dataProjection: 'EPSG:4326', featureProjection: 'EPSG:3857', }); Was the fix! Thanks a lot! — Stephen, Jul 01 '21 at 15:34
Looking at this answer to a related question, what happens if you add a node to your GeoJSON specifying the coordinate system, like this: "crs":{"type":"name","properties":{"name":"EPSG:3857"}} ? — elrobis, Jul 01 '21 at 15:35
@elrobis Where should I project them? They get projected in the frontend on a web application, then they are send to a nodejs where they are stored inside the postgis db. — Stephen, Jul 01 '21 at 15:35
@ThomasG77 For .writeGeometry method input CRS is featureProjection and output CRS is dataProjection, see https://openlayers.org/en/latest/apidoc/module-ol_format_GeoJSON-GeoJSON.html#writeGeometry — TomazicM, Jul 01 '21 at 15:35
It looks like you've found a working solution in @ThomasG77's suggestion, as a matter of curiosity—if you're feeling experimental—I'd be interested to know if going back to your original approach and adding the coordinate system node to your GeoJSON also works. My expectations is that these would be two different solutions to the problem, where the lesson is that you needed to sync your coordinate systems somewhere before the geoms get pushed into the DB. — elrobis, Jul 01 '21 at 15:38
@elrobis How do I add the node to my GeoJSON. The GeoJSON is being generated by the formatter. Should I insert the coordinate system node manually to string that is being generated? — Stephen, Jul 01 '21 at 15:41
If you've got the GeoJson in a variable already (for example geoJsonFeature), then it might work to add the CRS node using Array Notation, like this: geoJsonFeature['crs']={"type":"name","properties":{"name":"EPSG:3857"}}; — elrobis, Jul 01 '21 at 15:48

ThomasG77 · Accepted Answer · 2021-07-01T15:56:04.883

Made my comment an answer

Solution 1: reproject coordinates to client side and ingest them as is in server side

You need to reproject your coordinates from your frontend to backend. Your backend expects EPSG:4326. Your frontend use EPSG:3857. You need to transform the coordinates to EPSG:4326 before sending them

So

format.writeGeometry(plot.geometry.getGeometry())

should become

format.writeGeometry(plot.geometry.getGeometry(), {
  dataProjection: 'EPSG:4326',
  featureProjection: 'EPSG:3857'
});

Solution 2: Send coordinates as they are and transform them on server side

You could also do the opposite e.g keep EPSG:3857 in your frontend. Send the geometry to the server

format.writeGeometry(plot.geometry.getGeometry())

and change before inserting your database the coordinates by reprojecting on PostGIS side

So, the following

INSERT INTO plots (user_id, geog) VALUES ($1, ST_GeomFromGeoJSON($2));

would become

INSERT INTO plots (user_id, geog) VALUES ($1, ST_Transform(ST_GeomFromGeoJSON($2), 3857, 4326);

PostGIS - OpenLayers - ST_GeomFromGeoJSON() Inserting wrong coordinates into database

1 Answers1