I have a Postgres/PostGIS DB with two geometries in a single table. I want to export them as a GeoJSON GeometryCollection (see here) but I can't seem to get the GEOMETRY_AS_COLLECTION layer create option to work properly in ogr2ogr.
ogr2ogr -skipfailures -progress -s_srs EPSG:4326 -t_srs EPSG:4326 -f "GeoJSON" .\myFile.json PG:"host=server user=postgres dbname=name password=password" -sql "select * from table" -nln test -nlt GeometryCollection -lco GEOMETRY_AS_COLLECTION=YES
How do I get this working?
Best Answer
GEOMETRY_AS_COLLECTION does not work exactly as you thought. What is does is to label all geometries that ogr2ogr writes out to be of geometry type GeometryCollection that can wrap any kind of geometries inside it. By doing that it is possible to convert geojson data that contains a mixture of geometrytypes (points, lines, polygons, multi-variants of those, geometry collections) into formats like ESRI Shapefile as a single homogenous layer without a need to sort the dataset by the geometrytype into separate shapefiles. It does not process the incoming data by building geometrycollections on-the-fly if features have several geometries. GDAL documents http://trac.osgeo.org/gdal/wiki/rfc41_multiple_geometry_fields and http://www.gdal.org/drv_geojson.html are worth reading.
For doing what you want I think it would be easiest to make a temporary table into PostGIS and populate it with the GeometryCollections. However, it should be possible to make it also on-the-fly with ogr2ogr by using a clever -sql parameter. ST_Collect is the function to use and there is an usage example about GeometryCollections in http://postgis.net/docs/manual-2.0/ST_Collect.html.
Another perhaps usable alternative could be to write your features two times into geojson. First time with geometry_1 and all the other attributes, second time with geometry_2 and all the attributes. Alternatively it might be enough to write the second set with only feature_id and geometry_2 fields, that way you can join the second geometry to the attributes of the first dataset by using feature_id as a key.