[SOLVED] Unable to import .geojson Geometry to PostGis Database (null)

Unable to import .geojson Geometry to PostGis Database (null)

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.

Solution

Just specify the geometry name to serve as match to your table's geometry column, since your table has two geometry columns, as:

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

adding additionally the sql dialect, because geometry name varies in different dialects.