I`m trying to import geodata and the associated names from various regions within one .geojson file. Some geometries are Polygons, other Multipolygons.

Here is the start of the .geojson file:

{
"type": "FeatureCollection",
"crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:OGC:1.3:CRS84" } },
"features": [
{ "type": "Feature", 
"properties": { "shapeName": "Stuttgart", "shapeISO": "", "shapeID": "9070358B86745718691241", "shapeGroup": "DEU", "shapeType": "ADM2" }, "geometry": { "type": "MultiPolygon", "coordinates": [ [ [ [ 8.768902291000074, 48.521841109000036 ], [ 8.769527448000076, 48.523685716000045 ], [ 8.771349093000026, 48.523480333000066 ], [ 8.771833428000036, 48.524333358000035 ], [ 8.777268014000072, 48.522360651000042 ], [ 8.781038882000075, 48.521310749000065 ], [ 8.784831049000047, 48.520892894000042 ], [...]

Full File: https://drive.google.com/file/d/12sPpPsv_aQZFeD9qImgUmgPYZ4s9kUEn/view?usp=sharing

The Database Table is structured this way:

                                             Table "public.polygons_level_1"
   Column    |            Type             | Collation | Nullable |         Default
-------------+-----------------------------+-----------+----------+----------------------------
 polygon_id  | bigint                      |           | not null | nextval('polygons_level_1_polygon_id_seq'::regclass)
 region_name | character varying(150)      |           |          |
 geometry    | geometry(MultiPolygon,4326) |           |          |
 created_at  | timestamp without time zone |           |          | CURRENT_TIMESTAMP
 updated_at  | timestamp without time zone |           |          | CURRENT_TIMESTAMP
 centroid    | geometry(Point,4326)        |           |          | 

The issue:
The geometries are not imported. There are entries created for all 38 features, yet the “geometry” content is always “null”.

My command is:

ogr2ogr -append -f "PostgreSQL" PG:"dbname=XXX user=XXX password=XXX port=XXX" 
-nln polygons_level_1 
-nlt PROMOTE_TO_MULTI 
-sql "SELECT shapeName AS region_name FROM level1_admin2" 
./level1_admin2.geojson

Note:

• I`ve tried it both with and without the sql command for the region names – no difference to the main issue
• The geodata is supposed to be stored in the database to be transmitted to MapBox when requested, as polygons.
  -lco GEOMETRY_NAME=geometry does not fix the issue

My Setup:

• MacBook Pro M1 Pro
• PostgreSQL 16.4

TLDR: Solution

• Change SQL Prompt:

-sql "SELECT shapeName as region_name, geometry FROM level1_admin2" -dialect SQLite

• Delete Index and create it again in database with USING GIST(geometry)clause, as before the byte size of the geodata would have been too large.