[R-sig-Geo] PostGIS layer to R using OGR driver imports^J unwanted raster column as String
Roger Bivand
Roger.Bivand at nhh.no
Thu Dec 1 13:38:52 CET 2016
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 [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
--
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.Bivand at nhh.no
http://orcid.org/0000-0003-2392-6140
https://scholar.google.no/citations?user=AWeghB0AAAAJ&hl=en
http://depsy.org/person/434412
More information about the R-sig-Geo
mailing list