0

Original Post

I imported this Multi Polygon

./apps/ogrinfo -so -al  ~/Downloads/osm-boundaries/germany/OSMB-5cf5ed195895ffb3c65abc341dc8138bb5c6cedf.geojson/OSMB-5cf5ed195895ffb3c65abc341dc8138bb5c6cedf.geojson
INFO: Open of `/home/begerad/Downloads/osm-boundaries/germany/OSMB-5cf5ed195895ffb3c65abc341dc8138bb5c6cedf.geojson/OSMB-5cf5ed195895ffb3c65abc341dc8138bb5c6cedf.geojson'
      using driver `GeoJSON' successful.

Layer name: OSMB-5cf5ed195895ffb3c65abc341dc8138bb5c6cedf
Geometry: Multi Polygon
Feature Count: 1
Extent: (6.166035, 47.555796) - (14.979315, 54.838962)
Layer SRS WKT:
GEOGCRS["WGS 84",
    DATUM["World Geodetic System 1984",
        ELLIPSOID["WGS 84",6378137,298.257223563,
            LENGTHUNIT["metre",1]]],
    PRIMEM["Greenwich",0,
        ANGLEUNIT["degree",0.0174532925199433]],
    CS[ellipsoidal,2],
        AXIS["geodetic latitude (Lat)",north,
            ORDER[1],
            ANGLEUNIT["degree",0.0174532925199433]],
        AXIS["geodetic longitude (Lon)",east,
            ORDER[2],
            ANGLEUNIT["degree",0.0174532925199433]],
    ID["EPSG",4326]]
Data axis to CRS axis mapping: 2,1
osm_id: Integer (0.0)
boundary: String (0.0)
admin_level: Integer (0.0)
parents: String (0.0)
name: String (0.0)
local_name: String (0.0)
name_en: String (0.0)

into PostgreSQL last year using this instruction.

~/git/github/gdal/build$ #./apps/ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 dbname=<enter db> user=<enter user> password=<enter key>" ~/Downloads/osm-boundaries/germany/OSMB-5cf5ed195895ffb3c65abc341dc8138bb5c6cedf.geojson/OSMB-5cf5ed195895ffb3c65abc341dc8138bb5c6cedf.geojson

I compiled gdal from the repository like this.

mkdir -p ~/git/github && cd ~/git/github
git clone [email protected]:OSGeo/gdal.git
cd gdal/
rm -r build
mkdir -p build && cd build
cmake ..
cmake --build .

This is my current version.

~/git/github/gdal/build$ ./apps/ogr2ogr --version
GDAL 3.9.0dev-64cf9b4e88, released 2024/02/26 (debug build)

I do not know the version I used last year.

Anyhow, the import instruction above that worked last year is not working anymore this year. This is a part of the reply.

...
ERROR 1: Could not fetch SRS: ERROR:  current transaction is aborted, commands ignored until end of transaction block

ERROR 1: ERROR:  current transaction is aborted, commands ignored until end of transaction block

ERROR 1: ERROR:  current transaction is aborted, commands ignored until end of transaction block

ERROR 1: ERROR:  current transaction is aborted, commands ignored until end of transaction block
no COPY in progress

ERROR 1: Unable to write feature 0 from layer OSMB-5cf5ed195895ffb3c65abc341dc8138bb5c6cedf.
ERROR 1: Terminating translation prematurely after failed
translation of layer OSMB-5cf5ed195895ffb3c65abc341dc8138bb5c6cedf (use -skipfailures to skip errors)
ERROR 1: ERROR:  permission denied to create event trigger "ogr_system_tables_event_trigger_for_metadata"
HINT:  Must be superuser to create an event trigger.

I am sure I was no superuser last year either. That is not possible on the managed server I am using.

Do you know if something changed in the meantime?

Is it possible to provide a schema as part of the import instruction so that I can switch to a schema with other privileges?

Do you know a way to import the Multi Polygon GeoJSON file at hand into a PostgreSQL database without superuser privileges?

Update 1

I can confirm that the above instruction leads to a different reply when I switch to a database where the user has superuser privileges.

~/git/github/gdal/build$ ./apps/ogr2ogr -f "PostgreSQL" PG:"host='localhost' port='5432' dbname='<enter db>' user='<enter user>' password='<enter key with escaping>'" ~/Downloads/osm-boundaries/germany/OSMB-5cf5ed195895ffb3c65abc341dc8138bb5c6cedf.geojson/OSMB-5cf5ed195895ffb3c65abc341dc8138bb5c6cedf.geojson
Segmentation fault

Anyhow, my mission is not completed. I am not satisfied with the Segmentation fault reply not telling me the actual case.

Also, I did not accomplish the task of importing the respective GeoJSON file into PostgreSQL. Any ideas?

Update 2

When you download GeoJSON from the osm-boundaries website and extract the archive you get a file system like this.

~/Downloads/osm-boundaries/germany$ find
.
./OSMB-5cf5ed195895ffb3c65abc341dc8138bb5c6cedf.geojson
./OSMB-5cf5ed195895ffb3c65abc341dc8138bb5c6cedf.geojson/OSMB-5cf5ed195895ffb3c65abc341dc8138bb5c6cedf.geojson
./OSMB-5cf5ed195895ffb3c65abc341dc8138bb5c6cedf.geojson.gz

Of course you can rename it to what ever you want.

Here is the GeoJSON we are talking about.

{"type" : "FeatureCollection", "features" : [{"type": "Feature", "geometry": {"type":"MultiPolygon","coordinates":[[[[10.4544399,47.5557964],[7.5217995,47.6632658],[6.3459138,53.7245358],[13.649826,54.8389616],[14.9793151,51.0770378],[10.4544399,47.5557964]],[[10.4544399,47.5557964],[10.4707128,47.5797253],[10.4820072,47.584485],[10.428999,47.5776114],[10.4544399,47.5557964]],[[8.6585911,47.6913488],[8.6699501,47.6848539],[8.7178729,47.6906896],[8.7012698,47.7151988],[8.6585911,47.6913488]]],[[[7.4088353,54.1365048],[7.4485573,54.1387271],[7.5499457,54.0276137],[7.4088354,54.0076133],[7.4088353,54.1365048]]],[[[6.1871642,50.5261652],[6.1963868,50.5310648],[6.1989309,50.5274114],[6.2059619,50.5240556],[6.2067025,50.5218755],[6.1922624,50.5210957],[6.1871642,50.5261652]]],[[[6.1740677,50.5563539],[6.2405745,50.5870618],[6.2398546,50.5860403],[6.2390991,50.5826364],[6.2391072,50.5772147],[6.2361908,50.5724539],[6.2352864,50.5661026],[6.2105531,50.5547469],[6.1979838,50.5402999],[6.1974378,50.5362633],[6.1923744,50.5375939],[6.188508,50.5405166],[6.1780977,50.5415201],[6.1740677,50.5563539]]],[[[6.2461937,50.5973695],[6.2467439,50.5982266],[6.248485,50.5978889],[6.24821,50.5968229],[6.2461937,50.5973695]]],[[[6.1666886,50.6436204],[6.1833778,50.646983],[6.1836394,50.6447134],[6.1847308,50.6417053],[6.1862926,50.6405061],[6.1890337,50.639692],[6.1916644,50.6395669],[6.1969988,50.6408351],[6.2029532,50.6393167],[6.2207441,50.640281],[6.2234505,50.6410158],[6.2258095,50.6429625],[6.2272478,50.6479545],[6.2316044,50.6489328],[6.2372953,50.6445546],[6.2433249,50.6411281],[6.2455217,50.6404753],[6.2495462,50.6404874],[6.2569258,50.6417158],[6.2610832,50.642937],[6.2649697,50.642391],[6.2738114,50.6297549],[6.1817998,50.6237964],[6.1666886,50.6436204]]],[[[6.166035,50.6618618],[6.1961617,50.6587908],[6.1862985,50.6515434],[6.183861,50.6479421],[6.166035,50.6618618]]]]}, "properties": {"osm_id": -51477, "boundary": "administrative", "admin_level": 2, "parents": null, "name": "Germany", "local_name": "Deutschland", "name_en": "Germany"}}]}
7
  • 1
    Do you have PostGIS installed in your PostgreSQL database?
    – user30184
    Mar 5 at 11:50
  • @user30184 Yup! PostGIS is installed! Again, it worked last year on the same database? I just wanted to add another Multi Polygon from a GeoJSON file. Mar 5 at 11:57
  • Can you share the geojson data? Have you tried with some not-self-compiled GDAL version?
    – user30184
    Mar 5 at 15:31
  • why is your file stored like this? OSMB-5cf5ed195895ffb3c65abc341dc8138bb5c6cedf.geojson/OSMB-5cf5ed195895ffb3c65abc341dc8138bb5c6cedf.geojson
    – ziggy
    Mar 5 at 16:18
  • @user30184 Is there a way to get the GDAL ogr2ogr app from a Debian package? Mar 5 at 18:34

2 Answers 2

1

Cheers everyone for supporting me on the way to the solution!

It looks like we have the following cause:

  • Something wired happens when you build and use GDAL utility ogr2ogr like I did in my question post.

With your support I solved this issue this way.

  • Install the GDAL utility ogr2ogr using the Debian package.
sudo apt install gdal-bin --no-install-recommends
  • Import the GeoJSON file like this.
ogr2ogr -f "PostgreSQL" PG:"host='localhost' port='5432' dbname='<enter db name>' user='<enter user name>' password='<enter key and mind escaping>'" ~/Downloads/osm-boundaries/germany-2023-08-18.geojson
  • Optionally you can rename the imported polygone. The GeoJSON file name is used as the default table name.
psql -h localhost -p 5432 -U <enter user name> -d <enter db name>  -c"ALTER TABLE germany_2023_08_18 RENAME TO de_al2_w_ntags_smplcty10_20230818;"

Cheers!

0

The error comes from this source code file https://github.com/OSGeo/gdal/blob/fe3cf27139f56067b31b2e200ffd4cfa8407760b/ogr/ogrsf_frmts/pg/ogrpgtablelayer.cpp#L354

 "CREATE FUNCTION "
            "ogr_system_tables.event_trigger_function_for_metadata()\n"
            "RETURNS event_trigger LANGUAGE plpgsql AS $$\n"
            "DECLARE\n"
            "    obj record;\n"
            "BEGIN\n"
            "    FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()\n"
            "    LOOP\n"
            "        IF obj.object_type = 'table' THEN\n"
            "            DELETE FROM ogr_system_tables.metadata m WHERE "
            "m.schema_name = obj.schema_name AND m.table_name = "
            "obj.object_name;\n"
            "        END IF;\n"
            "    END LOOP;\n"
            "END;\n"
            "$$;");

By the error message it really seems to be so that the first user to insert data into PosgreSQL with GDAL must have superuser rights because otherwise the rights do not allow creating the required event trigger https://www.postgresql.org/docs/current/event-triggers.html. https://dba.stackexchange.com/questions/182501/create-event-trigger-as-non-superuser-in-postgres

Once the event trigger is created then it should be possible to use GDAL as non-superuser again.

1
  • Please refer to Update 1 of my original question post. Mar 5 at 18:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.