[R-sig-Geo] readOGR, PostGIS - problem with schemas

[Ben Madin]

Albin,

I too have encountered this problem, and there was a bit of a discussion on this list a little over twelve months ago (search for "error in readogr")

The bottom line in my case was to do with the layers (and the correct schemas) not being properly recorded in the geometry columns table. I think I might have truncated and run populate_geometry_columns() a couple of times to overcome this. 

I think it was also important that the schema you want is in the database search_path... this might have to be done before the populate_geometry_columns function is run.

cheers (good luck, the frustration nearly killed me but I was very generously helped by this list.)

Ben








On 14/11/2011, at 4:03 PM, Albin Blaschka wrote:

> 
> Dear List,
> 
> if I try to open a postgis-layer via readOGR and the layer/table is in a schema (layer = myschema.mytable), I get an error stating that the layer can not be found - but the layer *is* there.
> 
> mylayer <-
> readOGR(dsn="PG:host=127.0.0.1 user=username dbname=mydb password=secret port=5432", layer = "myschema.mylayer", verbose = TRUE)
> 
> if I take a layer in the "public" schema and put in the parameter "layer" just the tablename (layer = mylayer), it works, if I say "layer = public.mylayer", the layer is not found, too
> 
> If I try outside of R on the commandline the ogr* - commands (ogrinfo...), everything works as expected, for example
> 
> > ogrinfo "PG:host=127.0.0.1 user=username dbname=mydb password=secret port=5432" "myschema.mylayer"
> 
> INFO: Open of `PG:host=127.0.0.1 user=username dbname=mydb password=secret port=5432' using driver `PostgreSQL' successful.
> 
> Layer name: myschema.mylayer
> [...snip...]
> 
> So, the problem lies within the schema.table - notation... Is that a known limitation, or did I miss something?
> 
> Session Info: (I am on Ubuntu 11.10 "Onereiric")
> 
> R version 2.14.0 (2011-10-31)
> Platform: x86_64-pc-linux-gnu (64-bit)
> 
> locale:
> [1] LC_CTYPE=de_AT.UTF-8       LC_NUMERIC=C LC_TIME=de_AT.UTF-8        LC_COLLATE=de_AT.UTF-8
> [5] LC_MONETARY=de_AT.UTF-8    LC_MESSAGES=de_AT.UTF-8    LC_PAPER=C                LC_NAME=C
> [9] LC_ADDRESS=C               LC_TELEPHONE=C LC_MEASUREMENT=de_AT.UTF-8 LC_IDENTIFICATION=C
> 
> attached base packages:
> [1] stats     graphics  grDevices utils     datasets  methods   base
> 
> other attached packages:
> [1] rgdal_0.7-3        RPostgreSQL_0.2-0  DBI_0.2-5 RColorBrewer_1.0-5 maptools_0.7-38    lattice_0.20-0
> [7] sp_0.9-81          foreign_0.8-46     spatstat_1.24-2 deldir_0.0-16      mgcv_1.7-10
> 
> loaded via a namespace (and not attached):
> [1] grid_2.14.0  Matrix_1.0-1 nlme_3.1-102 tools_2.14.0
> 
> Thank you,
> Albin
> 
> 
> -- 
> | Albin Blaschka, Mag.rer.nat.
> | Etrichstrasse 26, A-5020 Salzburg
> | * www.albinblaschka.info * www.thinkanimal.info *
> | - It's hard to live in the mountains, hard but not hopeless!
> 
> _______________________________________________
> R-sig-Geo mailing list
> R-sig-Geo at r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo