[R-sig-Geo] install rgdal to query postgis database

[Edzer Pebesma]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Duncan, maybe you need to add

host="localhost"

to the arguments. Does user postgres need a password?

If it worked, please share with the list.

On 07/27/2013 12:13 PM, Duncan McPherson wrote:
> I have installed postgres, postgis, gdal 'complete' all from
> kyngchaos. Then I loaded a map of England, and have been able to do
> manipulations of that with SQL statements. I can also create maps
> with QGis by loading Postgis layers within QGis. And, I can use the
> utilities in terminal, e.g.:
> 
> iMac:~ duncan$ ogrinfo -ro "PG:dbname=health_england
> user=postgres" INFO: Open of `PG:dbname=health_england
> user=postgres' using driver `PostgreSQL' successful. …[list of
> layers]
> 
> Further, I have installed R64 and RStudio, the packages DBI and
> RPostgreSQL and am able to query the non-map parts of the
> database:
> 
>> library(DBI) library(RPostgreSQL) 
>> con=dbConnect(dbDriver('PostgreSQL'), dbname="health_england",
>> user="postgres") std<-data.frame(dbGetQuery(con, 'SELECT * FROM
>> case_list LIMIT 10'), row.names=1)
> 
> Now the problem:
> 
> I need to be able to produce many maps from this database within R,
> so that I can, for example, use the non-map analyses above to
> colour the map areas. I want to do this programmatically because of
> the number of maps (also the reason for not wanting to save out a
> shapefile of every map), rather than produce each one in QGis. From
> reading around, including this:
> http://gis.stackexchange.com/questions/64950/which-is-the-best-way-of-working-with-postgis-data-in-r,
> that rgdal would be able to handle the database connection and
> correctly handle the geometry columns, which RPostgreSQL cannot.
> But I keep getting this:
> 
>> dsn="PG:dbname=england_health user=postgres" ogrListLayers(dsn)
> Error in ogrListLayers(dsn) : Cannot open data source
> 
> Information that may be useful if you were to be able to help me
> might be:
> 
>> sessionInfo()
> R version 3.0.1 (2013-05-16) Platform: x86_64-apple-darwin10.8.0
> (64-bit)
> 
> locale: [1]
> en_GB.UTF-8/en_GB.UTF-8/en_GB.UTF-8/C/en_GB.UTF-8/en_GB.UTF-8
> 
> attached base packages: [1] stats     graphics  grDevices utils
> datasets  methods   base
> 
> other attached packages: [1] RPostgreSQL_0.4 DBI_0.2-7
> rgdal_0.8-8     sp_1.0-9
> 
> loaded via a namespace (and not attached): [1] grid_3.0.1
> lattice_0.20-15 tools_3.0.1
> 
>> ogrDrivers()
> name write ... 42         PGDump  TRUE 43           PGeo FALSE 44
> PostgreSQL  TRUE ...
> 
> 
>> library(rgdal)
> Loading required package: sp rgdal: version: 0.8-8, (SVN revision
> 463) Geospatial Data Abstraction Library extensions to R
> successfully loaded Loaded GDAL runtime: GDAL 1.10.0, released
> 2013/04/24 Path to GDAL shared files:
> /Library/Frameworks/GDAL.framework/Versions/1.10/Resources/gdal 
> Loaded PROJ.4 runtime: Rel. 4.8.0, 6 March 2012, [PJ_VERSION: 480] 
> Path to PROJ.4 shared files: (autodetected)
> 
> Many thanks in anticipation,
> 
> Duncan
> 
> _______________________________________________ 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
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQEcBAEBAgAGBQJR8/caAAoJEM1OCHCtOnfxsV4IAIBr+718m1zWYG13rEdvkqkM
cJia8/yHaBXTPZsLRfiuU7KvVOgvSFAhqi6AespJv0MXIPeDK2TG/OE1Kod+bMWU
gIUKOBKQzwXkAqI8MYyeQ8l4Wya5R1x68qGrtTWC63o31sE/VgBELGoLO9QLvzpo
wtDdRKWqsspSy7lGmYpEcaFhOx37s17WuIqJfLnhPkove7cgkc7OE1pHMVwqXgpL
0S3MhgRlI6Vlc52kZ3oQkd188f2atZ2ZPq3qvMF3PKPNMzX9yKn8576uCNkUFHgm
dfxZlYgXVws6BT2Qr+M8GJQhxZGa/H4JiekfOQSbK7zUaF2n68p9GOQWdc97d3o=
=mRIx
-----END PGP SIGNATURE-----