Skip to content

readOGR, PostGIS - problem with schemas

3 messages · Albin Blaschka, Ben Madin

Original
Albin Blaschka
# 
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
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:

            

      
      
      
    

        

      
      
    

  
  


  


      

    

    

      
      

        


  

        

      Albin Blaschka
    

    

      
      #
    

  


  

      
Am 14.11.2011 13:50, schrieb Ben Madin:

            

      
      
      
    

        
Hello Ben, hello list!

a truncate on the table geometry_columns followed by a select 
Populate_Geometry_columns() did the trick - readOGR now works as 
expected! Thank you very much!

kind regards,
Albin