[gdal-dev] Bug in OGR library

Mon Jan 10 06:19:15 PST 2022

Hi,

I asked you to create a GML with SQL query from one feature in a hope that the huuuge value from your database would be generated as a FID and appear in GML. Did you try what happens?

So if missing primary key is the main problem why not to add a primary key into your table? Or did you already try to run ogr2ogr with -unsetFid?

-Jukka Rahkonen-

Lähettäjä: Kopszak Marta - Partner Hurt <Marta.Kopszak at orange.com>
Lähetetty: maanantai 10. tammikuuta 2022 16.09
Vastaanottaja: Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi>; Even Rouault <even.rouault at spatialys.com>; gdal-dev at lists.osgeo.org
Aihe: RE: [gdal-dev] Bug in OGR library

I think a single record wouldn't help you. If I would copy only one record to another empty database it could be exported to .xlsx. We have a postgres database and a table without any primary key, so QGIS uses a tid. The overall size of the database is huuuge (billions of records), so the tid reaches high values and apparently overflows int4.

Export to CSV goes fine, so it seems OGR’s CSV driver uses int8, but when exporting to XLSX or ODS you get that error. Maybe it’s just as simple as change id datatypes in affected drivers?

From: Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi<mailto:jukka.rahkonen at maanmittauslaitos.fi>>
Sent: Monday, January 10, 2022 2:24 PM
To: Kopszak Marta - Partner Hurt <Marta.Kopszak at orange.com<mailto:Marta.Kopszak at orange.com>>; Even Rouault <even.rouault at spatialys.com<mailto:even.rouault at spatialys.com>>; gdal-dev at lists.osgeo.org<mailto:gdal-dev at lists.osgeo.org>
Subject: Re: [gdal-dev] Bug in OGR library

Hi,

Now it starts to make sense. Could you paste the contents of a single row that you can’t write into xlsx format? You can capture it with ogrinfo “ogrinto [database connection] -sql "select * from my_table limit 1". Or maybe better to convert that row into GML format and attach that to your mail.

-Jukka Rahkonen-

Lähettäjä: Kopszak Marta - Partner Hurt <Marta.Kopszak at orange.com<mailto:Marta.Kopszak at orange.com>>
Lähetetty: maanantai 10. tammikuuta 2022 15.07
Vastaanottaja: Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi<mailto:jukka.rahkonen at maanmittauslaitos.fi>>; Even Rouault <even.rouault at spatialys.com<mailto:even.rouault at spatialys.com>>; gdal-dev at lists.osgeo.org<mailto:gdal-dev at lists.osgeo.org>
Aihe: RE: [gdal-dev] Bug in OGR library

I have 2 bilions of rows in the  whole database and I try to export only one table which has 56 columns and 423 thousands of rows so it’s not the issue with Microsoft Excel limitation. I can’t write even a single row (error Feature creation error (OGR error: negative FID are not supported) occurs immediately),
Marta Kopszak

Hi,

Could you clarify a bit? You have billion rows in the database table but xlsx and ods can support at maximum a bit more than one million rows (1,048,576). You can’t imagine to have success with converting the whole table. Or is your problem that you can’t write even a single row from the source table because the value of the ID key is too big for xlsx/ods formats?

-Jukka Rahkonen-

Lähettäjä: gdal-dev <gdal-dev-bounces at lists.osgeo.org<mailto:gdal-dev-bounces at lists.osgeo.org>> Puolesta Kopszak Marta - Partner Hurt
Lähetetty: maanantai 10. tammikuuta 2022 14.34
Vastaanottaja: Even Rouault <even.rouault at spatialys.com<mailto:even.rouault at spatialys.com>>; gdal-dev at lists.osgeo.org<mailto:gdal-dev at lists.osgeo.org>
Aihe: Re: [gdal-dev] Bug in OGR library

Hi Even,
Thanks for your replay. More info about the problem:

Error:
Save error:
Export to vector file failed.
Error: Feature write errors:
Feature creation error (OGR error: negative FID are not supported)

The database has more than 2 bilons of records (record – I mean record in the database table). The error occurs when I export a single table witout the primary key to .xlsx.

I suppose that when the table doesn’t have the primary key it use tid – the tuple identifier, and the size of tid may exceed the size of a 32-bit integer. It cause the problem to export this table to .xlsx format by OGR library.

Regards,
Marta Kopszak

From: gdal-dev <gdal-dev-bounces at lists.osgeo.org<mailto:gdal-dev-bounces at lists.osgeo.org>> On Behalf Of Even Rouault
Sent: Monday, January 10, 2022 11:42 AM
To: gdal-dev at lists.osgeo.org<mailto:gdal-dev at lists.osgeo.org>
Subject: Re: [gdal-dev] Bug in OGR library

Marta,

What is the bug exactly ?

"2 billion records" : by record, you mean cells, like a sheet with 2048 columns and 1048576 rows ? By quickly looking at the code I can't see an obvious limitation, but that doesn't mean anything. Closer investigation would be needed. Please file an issue at https://github.com/OSGeo/gdal/issues/new with exact details how to reproduce (number of rows/features, and columns/fields in particular).

But even if the writer side would work, the OGR reader side would probably have issues as the whole spreadsheet will be loaded into RAM and for 2 billion cells, I would expect at least 32 or more GB of RAM to be needed. Perhaps spreadsheet software are able to cope with that in a smarter way.

Even
Le 10/01/2022 à 11:27, Kopszak Marta - Partner Hurt a écrit :
Dear Sirs,
I ran into an error when exporting data through the OGR library to the .xlsx and .ods format. The bug occurs when there are more than 2 billion records in the database. I know the problem was solved for other formats, e.g. csv. Will the problem be solved also for other formats (e.g. xlsx, .ods)? If this is not the correct e-mail, please forward this e-mail to the appropriate person.

Kind regards,
Marta Kopszak

Marta Kopszak
Współpracownik<https://www.orange.pl/>

Rozwój Sieci Stacjonarnej, Wydział Rozwoju Planowania
Orange Polska, gen. Romualda Traugutta 55, 50-416 Wrocław | RODO - informacja o danych <https://www.orange.pl/>

 <https://www.orange.pl/>

<https://www.orange.pl/>

_______________________________________________<https://www.orange.pl/>

gdal-dev mailing list<https://www.orange.pl/>

gdal-dev at lists.osgeo.org<https://www.orange.pl/>

https://lists.osgeo.org/mailman/listinfo/gdal-dev<https://www.orange.pl/>

-- <https://www.orange.pl/>

http://www.spatialys.com<https://www.orange.pl/>

My software is free, but my time generally not.<https://www.orange.pl/>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20220110/662e9325/attachment-0001.html>