2

Working in R, I tried to write a simple feature file with 402 rows and 1170 columns (only one geometry column) and got the following error:

> dim(sf_object)
## [1]  402 1170

> sf::write_sf(sf_object, "sf-object.gpkg" , driver = "GPKG")

## Failed to create feature 0 in sf-object
## Failed to create feature 0 in sf-object
## Error in st_write.sf(..., quiet = quiet, delete_layer = delete_layer) : 
##  failed writing to temporary file     
##  C:\Users\URBAND~1\AppData\Local\Temp\RtmpukYmED\filee82cdc6ada.gpkg

When I split the simple feature object into two objects (each with half the columns) the code runs without error.

Does the GeoPackage standard impose a maximum number of columns per feature that I am unaware of?

  • Best practice for database design considers 100 columns is too many for one table. More than a thousand isn't likely to be compatible with most database applications. – Vince Mar 6 '19 at 1:51
  • @Vince with ML applications generating thousands of columns (which they confusingly call "features") this limit is looking rather archaic. But then RDBMS are probably not the right place for ML algorithms to operate... – Spacedman Mar 6 '19 at 8:45
4

GeoPackage is a SQLite database and the maximun number of columns is documented in https://sqlite.org/limits.html

The default setting for SQLITE_MAX_COLUMN is 2000. You can change it at compile time to values as large as 32767. On the other hand, many experienced database designers will argue that a well-normalized database will never need more than 100 columns in a table.

You can check if non-default compile option was used when your SQLite was compile with a PRAGMA command

PRAGMA compile_options

If you really need more columnd you must recompile SQLite from sources with an option like

-DSQLITE_MAX_COLUMN=32000

See https://stackoverflow.com/questions/22964033/how-to-actually-change-the-maximum-number-of-columns-in-sqlite

3

If there is a column limit then its above where you are. I can write a GPKG with ten rows and 1692 column spatial point object:

> dim(d)
[1]   10 1692

Its an sf object:

> class(d)
[1] "sf"         "data.frame"

With points. Here's the first five columns:

> d[,1:5]
Simple feature collection with 10 features and 4 fields
geometry type:  POINT
dimension:      XY
bbox:           xmin: 1 ymin: 1 xmax: 10 ymax: 10
epsg (SRID):    NA
proj4string:    NA
   z      geometry col1 col2 col3
1  a   POINT (1 1)    1    2    3
2  b   POINT (2 2)    1    2    3
3  c   POINT (3 3)    1    2    3
4  d   POINT (4 4)    1    2    3
5  e   POINT (5 5)    1    2    3
6  f   POINT (6 6)    1    2    3
7  g   POINT (7 7)    1    2    3
8  h   POINT (8 8)    1    2    3
9  i   POINT (9 9)    1    2    3
10 j POINT (10 10)    1    2    3
> 

Write it out, no errors:

> write_sf(d, "bigd.gpkg",driver="GPKG")
> 

Check it at the command line - a summary output gives 1702 lines of text (one for each of the columns plus some extra data):

$ ogrinfo -so -al bigd.gpkg | wc -l
1702

To check in case its a limit on rows x columns, I just saved a 1000x1692 version of the above, no errors.

The documentation of Sqlite, which underlies Geopackage, lists its limits here: https://sqlite.org/limits.html , including a possible 2000 column limit on table size, but that can be set by compilation options.

This seems to be the limit on my system. (writec is a function I wrote that writes a spatial object to disk)

> writec(d[,1:1998])
> writec(d[,1:1999])
Failed to create feature 0 in ncol-2000
Failed to create feature 0 in ncol-2000
Error in st_write.sf(..., quiet = quiet, delete_layer = delete_layer) : 
  failed writing to temporary file /tmp/RtmpOsvA9W/file13b843d8ed51.gpkg
In addition: Warning messages:

I'm using sf 0.7.3 on Linux which links to:

> library(sf)
Linking to GEOS 3.6.2, GDAL 2.2.3, PROJ 4.9.3

Maybe you have another problem...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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