Mixing POINT and MULTIPOINT in PostGIS table

Question

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.

Answer 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.