[R-sig-Geo] PostGIS layer to R using OGR driver imports unwanted raster column as String

Thu Dec 1 13:11:10 CET 2016

>Hey Vil,

>I know it doesn't answer the problem of using rgdal to load PostGIS layers
>(especially rasters), but have you tried to use rpostgis::pgGetRast to load
>rasters in R? Might not be the most computer efficient, but should get you
>there...
>
>Mathieu. 

Thanks for ideas. I have already managed to load PostGIS layers with postGIStools::get_postgis_query() which gives me more flexibility.  But I would really expect that rgdal can do it too.

>OK, what does ogr2ogr do in GDAL2? I do not have any experience of the driver you are using, so others will have to try to repicate - do you have a >reproducible case? What does rgdal::ogrListLayers() say?
>
>Roger

Ok, sorry, I will try to make it more reproducible.

PostgreSQL database: version psql (9.6.1, server 9.5.5)
Postgis full version gave me:
POSTGIS="2.2.2 r14797" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1" LIBJSON="0.11.99" (core procs from "2.2.1 r14555" need upgrade) RASTER (raster procs from "2.2.1 r14555" need upgrade)

I have noticed that there is GDAL version 1.10.1 and RASTER need upgrade.
Thus I have upgraded postgis from source.
now Postgis full version gives me:
POSTGIS="2.3.1 r15264" GEOS="3.6.0-CAPI-1.10.0 r0" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 2.1.2, released 2016/10/24" LIBXML="2.9.1" RASTER

SQL command \d table gives:

                   Table "macfish.laketable"
         Column          |          Type           | Modifiers 
-------------------------+-------------------------+-----------
 lt_lake                 | character varying(40)   | not null
 lt_elevation            | double precision        | 
 lt_depthrast            | raster                  | 
 lt_mac_exclude          | geometry(Polygon,32633) | 
 lt_shoreline_pol_smooth | geometry(Polygon,32633) | 
 lt_shoreline_pol_simple | geometry(Polygon,32633) | 
 lt_shoreline_pol        | geometry(Polygon,32633) |

Spatial columns are listed in list got by sql command  "select * from geometry_columns;"
raster has also SRID 32633  (checked with ST_SRID()) and raster column is listed in list got by sql command "select * from raster_columns ;"  as :
 r_table_catalog | r_table_schema |  r_table_name  | r_raster_column | srid  | scale_x | scale_y | blocksize_x | blocksize_y | same_alignment | regular_blocking | num_bands | pixel_types | nodata_values | out_db |  extent  | spatial_index 
 macfishdb       | macfish        | laketable      | lt_depthrast    |     0 |         |         |             |             | f              | f                |           |             |               |        |         | f

command
ogr2ogr -f "ESRI Shapefile" abc.shp PG:"dbname=DB host=HOST user=USER password=PSSWD port=5432" "SHEMA.TABLE(SPATIAL_COLUMN)"
gives me (as before) lines:

 Warning 6: Normalized/laundered field name: 'lt_elevation' to 'lt_elevati'
 Warning 6: Normalized/laundered field name: 'lt_elevation' to 'lt_elevati'
Warning 1: Value '01000001004CB551378E00F03F4CB551378E00F..... loooong string of hexadecimal values ' 

After that, abc.shp contains the proper shp file. But there is still column named 'lt_depthra' of length 256 chars. These are the same hexadecimal characters as in Warning 1 of ogr2ogr commad.

In R:
library(rgdal) gives me :
Loading required package: sp
rgdal: version: 1.2-4, (SVN revision 643)
 Geospatial Data Abstraction Library extensions to R successfully loaded
 Loaded GDAL runtime: GDAL 2.1.2, released 2016/10/24
 Path to GDAL shared files: /usr/local/share/gdal
 Loaded PROJ.4 runtime: Rel. 4.8.0, 6 March 2012, [PJ_VERSION: 480]
 Path to PROJ.4 shared files: (autodetected)
 Linking to sp version: 1.2-3 

Command
rgdal::ogrListLayers(dns = "dbname=DB host=HOST user=USER password=PSSWD port=5432" ) 
gives me:
 [1] "macfish.shoreline"         "macfish.position_old"      "macfish.laketable"         "macfish.firstpos"         
 [5] "macfish.loggerspos"           
attr(,"driver")
[1] "PostgreSQL"
attr(,"nlayers")
[1] 5

which is list of all tables (SHEMA.TABLE) in my database which contain one or more spatial columns.

R version 3.3.2 (2016-10-31)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Debian GNU/Linux 8 (jessie)

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C               LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8    
 [5] LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8    LC_PAPER=en_US.UTF-8       LC_NAME=C                 
 [9] LC_ADDRESS=C               LC_TELEPHONE=C             LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] rgdal_1.2-4 sp_1.2-3   

loaded via a namespace (and not attached):
[1] tools_3.3.2     grid_3.3.2      lattice_0.20-33

command 
gdal-config --version 
gives me: 2.1.2

I hope its better description. I really dont know what to provide next. I will let it be for some time (too much work). As Mathieu suggested, I can solve that by other packages than rgdal. 
Thank you for everything :)

________________________________________
Fra: Vilem Ded [Ded.V at seznam.cz]
Sendt: 30. november 2016 2:56
Til: Roger Bivand
Kopi: R-Sig_Geo"
Emne: Re: [R-sig-Geo] PostGIS layer to R using OGR driver imports  unwanted raster column as String

Hi,
thx for idea. When rgdal is attached:

Loading required package: sp
rgdal: version: 1.1-10, (SVN revision 622)
 Geospatial Data Abstraction Library extensions to R successfully loaded
 Loaded GDAL runtime: GDAL 1.10.1, released 2013/08/26
 Path to GDAL shared files: /usr/share/gdal/1.10
 Loaded PROJ.4 runtime: Rel. 4.8.0, 6 March 2012, [PJ_VERSION: 480]
 Path to PROJ.4 shared files: (autodetected)
 Linking to sp version: 1.2-3

gdal-config --version  really showed version 1, so I have installed gdal2 from source.

Now the rgdal attaching gives me:

Loading required package: sp
rgdal: version: 1.2-4, (SVN revision 643)
 Geospatial Data Abstraction Library extensions to R successfully loaded
 Loaded GDAL runtime: GDAL 2.1.2, released 2016/10/24
 Path to GDAL shared files: /usr/local/share/gdal
 Loaded PROJ.4 runtime: Rel. 4.8.0, 6 March 2012, [PJ_VERSION: 480]
 Path to PROJ.4 shared files: (autodetected)
 Linking to sp version: 1.2-3

readOGR throws an error when running the same command as before:

obj <- rgdal::readOGR(dsn="PG:dbname=DB host=HOST user=USER password=PSSWD port=5432", layer = "SCHEMA.TABLE(LAYER)")
Error in ogrInfo(dsn = dsn, layer = layer, encoding = encoding, use_iconv = use_iconv,  :
  Layer not found

And now ,as expected from error, function ogrInfo() works differently. It throws the same error and work only without specification of LAYER (e.g. layer = "SCHEMA.TABLE")

Some more ideas? Thank you.

---------- Původní zpráva ----------
Od: Roger Bivand
Komu: Roger Bivand , Vilem Ded , Help
 R-Sig_Geo
Datum: 30. 11. 2016 12:53:32
Předmět: Re: [R-sig-Geo] PostGIS layer to R using OGR driver imports
 unwanted raster column as String

Briefly, could you add a copy of the startup messages when rgdal is attached, and the version of gdal shown by gdal-config --version? Maybe gdal2 would do better if you are on gdal1?

Roger

Roger Bivand

Norwegian School of Economics

Bergen, Norway

Fra: Vilem Ded

Sendt: onsdag 30. november, 11.11

Emne: [R-sig-Geo] PostGIS layer to R using OGR driver imports unwanted raster column as String

Til: Help R-Sig_Geo

Hi, right to the point. I  am trying to load spatial layer from postgis database to R: obj