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
