1

I am using the ogr2ogr command to upload a geopackage layer to my PostGIS database. The layer has 130'870 objects/rows. I am using this code:

ogr2ogr --debug ON -progress -f "PostgreSQL" PG:"host=<hostname> port=<port> user=<dbuser> dbname=<dbname> password=<password>" "<path>/brd_bahnstrecken.gpkg" -clipsrclayer bundesfernstrassen_sektoren -geomfield geom -nln bundesstrassen

The command concludes successfully but the resulting PostGIS-Layer only has 99'999 rows.

What could be the problem?

PS: I also get the follwoing warning while running the command:

"Warning 1: Geometry to be inserted is of type Multi Line String, whereas the layer geometry type is Line String. Insertion is likely to fail"

3
  • 4
    Try first with -nlt PROMOTE_TO_MULTI gdal.org/programs/ogr2ogr.html#cmdoption-ogr2ogr-nlt. I guess that only one transaction with 100000 features (see gdal.org/programs/ogr2ogr.html#cmdoption-ogr2ogr-gt) is successful but some row in the second transaction fails and the whole transaction gets a rollback.
    – user30184
    Commented Oct 23, 2023 at 15:47
  • @user30184 Thanks. Adding the PROMOTE_TO_MULTI solved the issue. Is also helpful to know why it stopped at 100000, so thank you.
    – geo_theo
    Commented Oct 23, 2023 at 16:18
  • It must have been a linestring for the first 99999 rows. You can also use -nlt MULTILINESTRING. You can actually use all the geometry types (LINESTRING, LINESTRINGM, LINESTRINGZM, MULTILINESTRING, MULTILINESTRINGM, etc.). If you use a multi type, all the records will be converted to multi type (linestrings will become multilinestrings with 1 line).
    – jbalk
    Commented Oct 26, 2023 at 17:18

1 Answer 1

0

A few things are having an effect in this case.

  • In PostGIS LINESTRING is a different geometry type thatn MULTILINESTRING. It is not possible to store multilinestrings into a table that has been created to contain linestrings.
  • If ogr2ogr cannot get the exact geometrytype of a vector layer it is reading a smallish sample from the beginning of the data and make a decision based on that. In this case the sample obviously contained only linestrings
  • The ogr2ogr program is saving data into PostGIS within transactions. Transaction is a group of database commands which are performed together. If one command in a transaction fails then the whole transaction fails. Transactions are used because it is much faster to insert many features within one transaction than feature by feature. The default size for transactions in ogr2ogr is 100000 https://gdal.org/programs/ogr2ogr.html#cmdoption-ogr2ogr-gt.
  • What happened originally that ogr2ogr made one succesful transction with linestring data. Ogr2ogr seems to count wrong because it inserted only 99999 features instead of 100000 that is documented. Maybe it is using formula id < 100000. Anyway, the first transaction was succesful and there are features in the PostGIS table
  • When ogr2ogr tries to do the next, and in this case last transaction, it meets a multilinestring in the data. It knows that the transaction is likely to fail and informs user about that. PostGIS really denies the transaction and no new features are inserted. The number of features in the database remains 99999.

There are two ways for making the conversion of the whole dataset to success.

  • Use the -promote_to_multi option. That will convert all the linestrings into single-part multilinestrings. Multipart multilinestrings remain as they are. This way it is possible to store all features into a new multilinestring table.
  • Use option -nlt geometry. With that option the new PostGIS table will be created so that the geometry field accepts all kind of geometries. Linestrings and multilinestrings can be inserted into the same table without conversion. But it will be possible to save also (multi)points and (multi)polygons into this generic geometry table and that is not always desired.

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.