PostGIS layer to R using OGR driver imports unwanted raster column as String

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 :)

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 :)