2

I'm trying to load a bunch of GeoJSON files into one PostGIS table. I get this kind of error:

Warning 1: Geometry to be inserted is of type Point, whereas the layer geometry type is Multi Point.
Insertion is likely to fail
ERROR 1: INSERT command for new feature failed.
ERROR:  Geometry type (Point) does not match column type (MultiPoint)

I've resolved this kind of issue previously with lines and polygons before using the -PROMOTE_TO_MULTI flag, but this doesn't seem to work for POINT/MULTIPOINT.

My first input looks like:

ogr2ogr -f "PostgreSQL" -lco GEOMETRY_NAME=the_geom -lco FID=gid PG:"dbname=trees" melbourne.vrt -nln trees1 -nlt MULTIPOINT

If I remove the -nlt MULTIPOINT, I get this error:

Loading wyndham.geojson
Warning 1: Geometry to be inserted is of type Multi Point, whereas the layer geometry type is Point.
Insertion is likely to fail
ERROR 1: INSERT command for new feature failed.
ERROR:  Geometry type (MultiPoint) does not match column type (Point)

Command: INSERT INTO "trees1" ("the_geom" , "latitude", "longitude") VALUES ('0104000020E61000000100000001010000001E55A5583FAF11416A1DE9D0E8255641'::GEOMETRY, '-37.86935', '144.60962') RETURNING "gid"
ERROR 1: Terminating translation prematurely after failed

Is there any workaround?

UPDATE

It looks like in this instance, the file containing "MultiPoint" geometry actually only contains single points, which are syntactically expressed as MultiPoint:

"geometry":{"type":"MultiPoint","coordinates": [[290431.89426198014,5803919.42858718]]}, ...

Just in case that makes any workarounds easier.

  • What you do is right. Multipoints are so rare compared to (multi)linestings and (multi)polygon mixtures that it is generally better that PROMOTE_TO_MULTI does not cast points to multipoints. – user30184 Mar 25 '15 at 10:06
  • Ok, so how do I get both types of file into the one PostGIS table? Any workaround? – Steve Bennett Mar 25 '15 at 11:23
1

No workaround should be needed. What you did in the first place is the right way.

Take this GML as input:

<?xml version="1.0" encoding="utf-8" ?>
<ogr:FeatureCollection
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="http://ogr.maptools.org/ mixed_points.xsd"
     xmlns:ogr="http://ogr.maptools.org/"
     xmlns:gml="http://www.opengis.net/gml">
  <gml:boundedBy>
    <gml:Box>
      <gml:coord><gml:X>-3.819644919168593</gml:X><gml:Y>-0.932808891454964</gml:Y></gml:coord>
      <gml:coord><gml:X>-3.502092956120094</gml:X><gml:Y>-0.5737586605080816</gml:Y></gml:coord>
    </gml:Box>
  </gml:boundedBy>

  <gml:featureMember>
    <ogr:mixed_points fid="mixed_points.0">
      <ogr:geometryProperty><gml:Point><gml:coordinates>-3.819644919168593,-0.573758660508082</gml:coordinates></gml:Point></ogr:geometryProperty>
    </ogr:mixed_points>
  </gml:featureMember>
  <gml:featureMember>
    <ogr:mixed_points fid="mixed_points.1">
      <ogr:geometryProperty><gml:MultiPoint><gml:pointMember><gml:Point><gml:coordinates>-3.502092956120094,-0.829965357967666</gml:coordinates></gml:Point></gml:pointMember><gml:pointMember><gml:Point><gml:coordinates>-3.691541570438801,-0.932808891454964</gml:coordinates></gml:Point></gml:pointMember></gml:MultiPoint></ogr:geometryProperty>
    </ogr:mixed_points>
  </gml:featureMember>
</ogr:FeatureCollection>

Convert into PostGIS:

ogr2ogr -f "PostgreSQL" PG:"dbname='db' host='localhost' port='5432' user='user' password='password'" -lco GEOMETRY_NAME=the_geom -lco FID=gid mixed_points.gml -nln points -nlt MULTIPOINT

Check what got inserted:

ogrinfo PG:"dbname='db' host='localhost' port='5432' user='user' password='password'" points



INFO: Open of `PG:dbname='db' host='localhost' port='5432' user='user' password='password''
      using driver `PostgreSQL' successful.

Layer name: points
Geometry: Multi Point
Feature Count: 2
Extent: (-3.819645, -0.932809) - (-3.502093, -0.573759)
Layer SRS WKT:
(unknown)
FID Column = gid
Geometry Column = the_geom
OGRFeature(points):1
  MULTIPOINT (-3.819644919168593 -0.573758660508082)

OGRFeature(points):2
  MULTIPOINT (-3.502092956120094 -0.829965357967666,-3.691541570438801 -0.932808
891454964)

If you want to save points as points and multipoints as multipoints into a same table, initialize it to use just general GEOMETRY data type by using -nlt GEOMETRY in the ogr2ogr command. Such tables may be not well supported by some softwares.

However, if all geometries are actually simple points but expressed as multipoints it is also possible to cast them to simple ones.

Compere:

ogrinfo multipoints.gml -al
Had to open data source read-only.
INFO: Open of `multipoints.gml'
      using driver `GML' successful.

Layer name: multipoints
Geometry: Unknown (any)
Feature Count: 5
Extent: (-4.514290, -0.976111) - (-3.751083, -0.730730)
Layer SRS WKT:
(unknown)
Geometry Column = geometryProperty
fid: String (0.0) NOT NULL
OGRFeature(multipoints):0
  fid (String) = multipoints.0
  MULTIPOINT (-4.514289838337184 -0.73073036951501)

With:

ogrinfo multipoints.gml -dialect sqlite -sql "select CastToPoint(geometryProperty) from multipoints"
Had to open data source read-only.
INFO: Open of `multipoints.gml'
      using driver `GML' successful.

Layer name: SELECT
Geometry: Unknown (any)
Feature Count: 5
Extent: (-4.514290, -0.976111) - (-3.751083, -0.730730)
Layer SRS WKT:
(unknown)
Geometry Column = CastToPoint(geometryProperty)
OGRFeature(SELECT):0
  POINT (-4.514289838337184 -0.73073036951501)

I apologize the tricky syntax but I have not invented it.

  • It might have been unclear originally [I've now added the full error message], but I get an insertion error when adding a POINT into a MULTIPOINT table, so yes, it's a problem. (Well, at least with the geoJSON files I'm dealing with). Setting -nlt GEOMETRY seems to be the winning solution! – Steve Bennett Mar 25 '15 at 12:26
  • See the edited answer. If you want you can cast your single part multipoints into simple points with GDAL as well. – user30184 Mar 25 '15 at 12:52
  • Excellent, such a comprehensive answer, thank you. – Steve Bennett Mar 26 '15 at 3:05

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.