[R-sig-Geo] PostGIS layer to R using OGR driver imports unwanted raster column as String
Edzer Pebesma
edzer.pebesma at uni-muenster.de
Thu Dec 1 13:34:30 CET 2016
Your table (layer) contains multiple geometry columns. This is a rather
new feature (afaik) in PostGIS, and in GDAL. I'm not sure whether
readOGR has adapted to it, and why it should cause troubles when it
occurs rather than simply returning you the table with the first
geometry column (sp objects in R can have only one geometry column).
You could try to use sf::st_read, which has similar syntax as readOGR,
see https://edzer.github.io/sfr/articles/sf2.html , and if that is
successful, convert to sp with as(x, "Spatial"). sf::st_read has an
argument, iGeomField, which can be used to specify (by integer) which
geometry column you want to import.
On 01/12/16 13:11, 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.
>
>
> 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
> _______________________________________________
> R-sig-Geo mailing list
> R-sig-Geo at r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>
--
Edzer Pebesma
Institute for Geoinformatics (ifgi), University of Münster
Heisenbergstraße 2, 48149 Münster, Germany; +49 251 83 33081
Journal of Statistical Software: http://www.jstatsoft.org/
Computers & Geosciences: http://elsevier.com/locate/cageo/
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: <https://stat.ethz.ch/pipermail/r-sig-geo/attachments/20161201/3f72c4e4/attachment.bin>
More information about the R-sig-Geo
mailing list