[R-sig-Geo] [R] Connecting to PostgreSQL/PostGIS from R (rgdal?)

[Edzer J. Pebesma]

Mike, my experience with linking R with PostGIS are documented here:

http://wiki.intamap.org/index.php/PostGIS

which I may have mentioned before on r-sig-geo.

Indeed, as Tim confirms, besides the (r)gdal link, (R)ODBC is another 
working link for transferring table information. I doubt whether it will 
be easy to transport e.g. line or polygon topology through this link.
--
Edzer


>
>  
>
>>I apologize if I come accross as somewhat naive in this - I certainly am
>>still a novice when it come to the use of R.  Also, I apologize for the
>>longish email that follows, but if you're interested in what I'm trying
>>to do, this is the clearest explanation I have off the top of my head:
>>
>>My needs are that I'm working on a .Net gui which displays map data
>>using the C# MapScript library for MapServer, and manipulates/analyzes
>>spatial data (generally just points for now, but this might change in
>>the future) stored in a PostgreSQL/PostGIS database.  Roger, I think
>>you'll recall asking me about this tool, for which I had a poster up at
>>the OSGeo conference in Lausanne last year.  Lately, I've been trying to
>>port it to Gtk# so that it will work on both Linux and Windows, and so
>>far it seems to be working.  However, if I add any real functionality to
>>it in the future, I'd like to find a better way to link PostgreSQL with
>>the functionality of R.
>>
>>In this tool, I had started to use R as I feel it's better suited to do
>>some of the more complicated processing - though so far this is limited
>>to creating voronoi polygons, for which the deldir library in R worked
>>very well.  To make this happen, I used system process objects in
>>Mono/.Net to control the R commandline.  The issue I faced here is that
>>the only way I could get the data into R from PostgreSQL (without
>>requiring 'a priori' setup on the client machine of something like an
>>ODBC driver with the RODBC librar) was to dump the data to a temporary
>>text file, import it into R, process it in R, dump it back into text
>>formatted for SQL, then execute the SQL to input the data into PostgreSQL.
>>
>>This overall process would be much more efficient if I could find a more
>>integrated way of transferring data between R/PostgreSQL - right now my
>>.Net application primarily processes data within PostgreSQL itself, but
>>integration with R would make a great deal of sense for me, as its
>>primary purpose is to calculate spatial statistics.
>>
>>However, my ideal solution would be to have a package of binaries that
>>are self sufficient, so I can give someone the compiled binaries on a CD
>>for example, and that person could use it on a Windows machine with no
>>initial setup, or he/she could use it on a Linux machine where
>>installation of the necessary prequisites is either already done, or
>>fairly easy (at least from my experience with Fedora so far, using the
>>standard repositories).  At the moment, the only missing piece here is
>>the link between R and PostgreSQL.
>>
>>Maybe there is a way I can accomplish this with RODBC that I'm unaware
>>of...but as as I understand it, this requires a Windows user to install
>>the PostgreSQL ODBC driver first, then create a DSN to each database
>>that is used in PostgreSQL.  If I'm wrong about any of this, I
>>apologize, and I'd be happy to be corrected.  Every once in a while I
>>take a look around online to see if anything new has been released
>>(e.g., a win32 port of pl/r, or some easy-to-use solution for connecting
>>to PgSQL from within R).  It seems like rgdal might be a sort-of
>>solution, at least for spatail data - except that I'm somewhat inept at
>>compiling it cleanly (I did get it working, and it seems okay, but it's
>>not packaged properly - I'm sure of that).  Even so, it doesn't seem to
>>have an option for linking to non-spatial data (at least, from what I've
>>been able to figure out from experimentation and documentation), which
>>would be pretty useful as well.
>>
>>If either of you have any additional suggestions, I'd be glad to hear them.
>>
>>Sorry again for such a long email.
>>
>>Regards,
>>Mike
>>
>>Roger Bivand wrote:
>>    
>>
>>>On Thu, 31 May 2007, Prof Brian Ripley wrote:
>>>
>>>      
>>>
>>>>Roger,
>>>>
>>>>I see that on Linux I have an ODBC driver.  That should not introduce 
>>>>further dependencies on Windows, and might be useful.
>>>>
>>>>OTOH, I do think RODBC is the answer to the underlying question.  It works 
>>>>well with PostgreSQL on Windows, and abstracts out the pesky differences 
>>>>between PostgreSQL clients in different versions (as the ODBC driver is 
>>>>part of PostgreSQL).
>>>>        
>>>>
>>>Brian,
>>>
>>>Thanks, of course there are better routes than rgdal/PostGIS when the data 
>>>are not geometries. But because PostGIS stores its representations in 
>>>specific ways, both the PostgreSQL and GDAL/OGR sides need access to the 
>>>PostGIS library to unpick the parts of the lines and polygons involved. 
>>>Edzer has had some success with this on Linux, as far as I understand to 
>>>insert results from R into a PostGIS/PostgreSQL database, I think being 
>>>read by MapServer for HTTP delivery. 
>>>
>>>I'm not sure what Mike's specific needs are - if aspatial or simple point
>>>coordinates, it ought to be possible to avoid the intermediate data layer,
>>>unless something "on the other side" needs it. With line or polygon 
>>>geometries, some intermediate layer is needed, and that's where the 
>>>dependencies come from. 
>>>
>>>The FWTools Windows binaries for rgdal built against HDF4 seem to work,
>>>against its NetCDF which crashes R (I think this is one of Frank
>>>Warmerdam's DLLs doing stdcall, and another not doing it). The user
>>>workaround in that case was to use your ncdf binary, which was feasible
>>>for raster data. 
>>>
>>>FWTools is advertised as aiming: "to track the latested development
>>>versions of the packages included as opposed to official releases. While
>>>this may mean the packages are less stable, it is intended to give folks a
>>>chance to use the latest and greatest". Having talked to the OSGeo
>>>developers, it seems that their "user" is a system integrator of some
>>>kind, setting up a production system for a larger organisation in-house or
>>>more broadly. This doesn't match too well with the R "user" profile, 
>>>particularly in a one-off research setting.
>>>
>>>Roger
>>>
>>>      
>>>
>>>>Brian
>>>>
>>>>
>>>>On Wed, 30 May 2007, Roger Bivand wrote:
>>>>
>>>>        
>>>>
>>>>>On Wed, 30 May 2007, Mike Leahy wrote:
>>>>>
>>>>>          
>>>>>
>>>>>>Hello,
>>>>>>
>>>>>>I've been trying every now and then to find a cross operating system
>>>>>>solution that would let me access PostgreSQL (and PostGIS) from R, or to
>>>>>>access R from PostgreSQL.  I know of pl/r, which accomplishes the
>>>>>>latter, but has yet to be successfully ported to Windows.  Similarly,
>>>>>>I've tried to use Rdbi and DBI, but I haven't had luck with those on
>>>>>>Windows either for connecting to PostgreSQL from R.  Can anyone suggest
>>>>>>a solution for this?
>>>>>>
>>>>>>It would seem that rgdal could also help me in this case. Unfortunately,
>>>>>>the version of the GDAL library that is included in the rdgal binary
>>>>>>available on CRAN (for windows) doesn't include the PostgreSQL driver
>>>>>>for OGR (i.e., it's not listed by the ogrDrivers() function).
>>>>>>
>>>>>>I compiled rgdal on Windows myself using the GDAL library from
>>>>>>FWTools-1.3.1, but I was unsuccessful at creating a proper binary
>>>>>>package for R.  I was only able to get it to work by substituting the
>>>>>>rgdal.dll that was installed by CRAN with the one that I compiled that
>>>>>>links against the GDAL library from FWTools.  Even though it works (at
>>>>>>first glance with ogrInfo(), and readOGR()), I still get a warning
>>>>>>message when I load the libary: "DLL attempted to change FPU control
>>>>>>word from 8001f to 9001f".
>>>>>>
>>>>>>So my question with respect to rgdal is a) is it likely that an rgdal
>>>>>>package is going to be released in the future with the PostgreSQL driver
>>>>>>included in GDAL/OGR, or b) are there any suggestions/instructions that
>>>>>>might get me through the compilation and packaging process for rgdal
>>>>>>with better success?
>>>>>>            
>>>>>>
>>>>>The warning is harmless - R is just reporting that it has stopped the
>>>>>dynamically linked libraries resetting a flag that they should not change
>>>>>while R is running. If you followed the notes in README.windows in rgdal,
>>>>>you ought to be OK. There are no plans to provide more Windows binary
>>>>>drivers than those present now, because the others involve further
>>>>>external dependencies, which most users would not welcome.
>>>>>
>>>>>So please try to do an ogrinfo at the command line in Windows using
>>>>>FWTools to your PostGIS data, and then the equivalent within R with your
>>>>>locally built rgdal, and see how it goes. Even on Linux, getting all the
>>>>>components lined up isn't easy, according to people who have tried, but
>>>>>can be done if you need to do it.
>>>>>
>>>>>Hope this helps,
>>>>>
>>>>>Roger
>>>>>
>>>>>          
>>>>>
>>>>>>Thanks in advance for any help,
>>>>>>Mike
>>>>>>
>>>>>>_______________________________________________
>>>>>>R-sig-Geo mailing list
>>>>>>R-sig-Geo at stat.math.ethz.ch
>>>>>>https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>>>>>>
>>>>>>            
>>>>>>
>>    
>>
>
>  
>