1

About the SessionInfo() I work with:

  • R version 3.4.2
  • rgdal version: rgdal_1.2-13
  • sf version: sf_0.5-4

The problem:

Recently I started to work with Geopackages .gpkg in R. The goal is to read a layer into R, do simple selection and write it to PostgreSQL.

I found two options to load the data from .gpkg into R:

Example, which works well:

The data can be downloaded from this GIS-Browser -> Select "Naturgefahrenkarten" and choose format .gpkg

natrualHazards1 <- readOGR(dsn= "pathto.gpkg", "WB_SYN_GK_F") # readOGR
natrualHazards2 <- st_read(dsn= "pathto.gpkg", layer = "WB_SYN_GK_F") # st_read

To write it into PostgreSQL I chose the pgInsert from the rpostgis Library

pgInsert(con, name=c("public",paste(c("tablename"),collapse="")), 
     naturalHazards1, 
     geom = "geom", df.mode = FALSE,
     overwrite = TRUE,
     df.geom = c("geom",paste(c("(MultiPolygon,2056)"),collapse="")), geog = FALSE)

Example, which causes error:

The data can be downloaded from this GIS-Browser -> Select "Amtliche Vermessung in Farbe" and choose format .gpkg

landuse1 <- readOGR(dsn= fgdb, "Bodenbedeckung_BoFlaeche_Area") # readOGR

Error in ogrInfo(dsn = dsn, layer = layer, encoding = encoding, use_iconv = use_iconv, : Multiple incompatible geometries: wkbPolygon: 1549; NA: 1268

landuse2 <- st_read(dsn= "pathto.gpkg", layer = "Bodenbedeckung_BoFlaeche_Area) # st_read

conn = dbConnect(PostgreSQL(), dbname = "dbname", user= "postgres", password = "password", host = "localhost", port = 5432) 
st_write_db(conn, landuse2 , drop=TRUE)

The error is caused while writing to the PostgreSQL db.

Error in postgresqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not Retrieve the result : FEHLER: Spalte »geometrie« existiert nicht LINE 1: ...bedeckung set wkb_geometry = ST_GeomFromEWKB(cast(Geometrie ... ^ HINT: Vielleicht wurde beabsichtigt, auf die Spalte »bodenbedeckung.Geometrie« zu verweisen. )


Hint:

In order to test the object of class sf "landuse" I tried to make a simple conversion to a SpatialDataFrame using this code:

landuse_sp <- as(landuse2, "Spatial")

Error in as_Spatial(geom, IDs = row.names(from)) : conversion from feature type sfc_GEOMETRY to sp is not supported


Questions:

  1. What's the problem of readOGR in the second example, how can I learn more about the problem in order to resolve it.
  2. What do I have to consider writing objects of class sf to PostgreSQL?
  3. Could the errors be caused from a storing problem?
  4. Somebody has an idea how to read the second example into R and save it from there to PostgreSQL?
  • why use R? just using ogr2ogr would seem easier to me – Ian Turton Jan 26 '18 at 10:20
  • Thanks for the hint. In the end I loaded the data as follows in the command line: cd \"to the folder containing the data" ls -file | % { ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 dbname=db user=user password=pw" $_.fullname Bodenbedeckung_BoFlaeche_Area -a_srs EPSG:2056 -nlt MULTIPOLYGON -nln public.tablename -append } – Li Ku Jan 29 '18 at 10:16
  • But the described errors are not solved yet – Li Ku Jan 29 '18 at 10:21

Your Answer

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

Browse other questions tagged or ask your own question.