pg-promise wrong insertion of multipolygons into postgis database

Question

So I am using pg-promise to insert multiple geojson MultiPolygons into a postgis database. The insertion into the database work fine, but for some of the row in the database I get a strange behaviour, that is the cell is filled with two lines. The first line some load message and the second line is the actual geom object which more strangely is converted right from geojson to postgis geom.

function createBorder(pathToJSON, table) {
  fs.readFile(pathToJSON,
    {encoding: 'UTF-8'},
    (err, data) => {

    let geoJSON = JSON.parse(data);
    geoJSON.features.forEach(f => {
      f.geometry.crs = {
        type: 'name',
        properties: {
          name: 'EPSG:4326'
        }
      }
      db.none('INSERT INTO nyc_borders(geom)\
        VALUES (ST_GeomFromGeoJSON(${geoJSON}))', {
        geoJSON: f.geometry
      })
      .then((d) => {
        console.log(f.geometry);
      })
      .catch(error => {
        console.log("ERROR: ", error);
      })
    });
  });
}
createBorder('./data/community_districts.geojson');

I shortend the geoJSON output, it is basically the community district borders from nyc downloaded from the opendata portal Geojson:

{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "properties": {
        "shape_leng": "51549.5578986",
        "boro_cd": "311",
        "shape_area": "103177785.347"
      },
      "geometry": {
        "type": "MultiPolygon",
        "coordinates": [
          [
            [
              [
                -73.97348373564797,
                40.61137106069874
              ],
              [
                -73.97303089190211,
                40.6090051063008
              ],
              [
                -73.97299433938896,
                40.60881414180224
              ]
            ]
          ]
        ]
      }
    },
    {
      "type": "Feature",
      "properties": {
        "shape_leng": "65821.875617",
        "boro_cd": "313",
        "shape_area": "88195686.2688"
      },
      "geometry": {
        "type": "MultiPolygon",
        "coordinates": [
          [
            [
              [
                -73.96720294103956,
                40.573326317397424
              ],
              [
                -73.96738975478877,
                40.573258999904446
              ],
              [
                -73.9674356779313,
                40.57320896967127
              ],
              [
                -73.96736390080571,
                40.57304456895217
              ],
              [
                -73.98372152615246,
                40.59582107821707
              ]
            ]
          ]
        ]
      }
    }
  ]
}

Some pictures from my database:

database table with rows that have two lines inside one cell

one cell expanded to see the actual tow lines better

So I am really stuck because I do not have an idea how to start debuging, singe the insertion does work some how and also the conversion of the geojson object looks fine. I actually can not figure out who is causing this wrong behaviour.

vitaly-t · Answer 1 · 2018-07-01T20:09:04.687

You can have full control over how pg-promise formats data, by using Custom Type Formatting.

For example, if you have an array[][2] (points as shown), you can convert them like this:

const toGeometry = g => ({ /* g = array[][2] (points) */
    rawType: true,
    toPostgres: a => {
        const points = a.map(p => pgp.as.format('$1 $2', p));
        return 'ST_GeomFromText(\'LINESTRING(' + points.join() + ')\')';
    }
});

And then you can pass in toGeometry(f.geometry) to apply your custom formatting.

pg-promise wrong insertion of multipolygons into postgis database

2 Answers2