On Thu, 1 Dec 2016, Vilem Ded wrote:

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.

What does ogrinfo say? Does it (for the GDAL version you are using, now 2.1.2?) say anything other than string? Does it say what the SQL query says? What driver does it say that it is using (rgdal::ogrListLayers() below says PostgreSQL)? This must be an issue with the OGR drivers, and possibly competition between vector and raster drivers. This probably cannot be fixed at the rgdal level, probably only in GDAL or its drivers. Using the OGR/GDAL command line tools makes it possible to isolate the issue.

Roger



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 [de...@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

--
Roger Bivand
Department of Economics, Norwegian School of Economics,
Helleveien 30, N-5045 Bergen, Norway.
voice: +47 55 95 93 55; fax +47 55 95 91 00
e-mail: roger.biv...@nhh.no
http://orcid.org/0000-0003-2392-6140
https://scholar.google.no/citations?user=AWeghB0AAAAJ&hl=en
http://depsy.org/person/434412
_______________________________________________
R-sig-Geo mailing list
R-sig-Geo@r-project.org
https://stat.ethz.ch/mailman/listinfo/r-sig-geo

Reply via email to