On Sat, 2 Jun 2007, Tim Keitt wrote: > My Rdbi (+Rdbi.PgSQL) package was written to be much simpler and > easier to use than either DBI or RODBC. Unfortunately, I do not have > time to maintain it, so I'm not sure what state it is in. Either DBI > or RODBC should work fine for pushing tables back and forth. RODBC is > probably the most up-to-date and might be the best long term solution. > (DBI is the most complete I think in terms of a full blown db API.)
There are quite a few 'db API' features in RODBC that are not in DBI. > For pushing geometries into PostGIS, I invariably use the shp2pgsql > utility as it seems to give the best results. You could easily dump > our Spatial*DataFrame to a file and shell out to run shp2pgsql. Not > elegant but would work. (Wish I had time to finish low-level OGR > coverage in rgdal...) > > Edzer's approach is one I've suggested on many occasions -- that we > subclass the sp classes to act as a proxy to a PostGIS table. I first > developed that approach in rpgsql -- you could create proxy data > frames that simply forwarded access requests to PostgreSQL (I think > the idea has now been applied to SQLite tables). The problem of course > is that R does not support polymorphism "below the surface" in the C > level implementation, so once a proxy object is passed to R's > internals, it is nothing more than a pointer with no call interface. I really don't think that is true, and indeed polymorphism is almost entirely implemented at C level. A visible R object and a SEXP are one and the same thing. > > THK > > On 6/2/07, Edzer J. Pebesma <[EMAIL PROTECTED]> wrote: >> 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@stat.math.ethz.ch >>>>>>>> https://stat.ethz.ch/mailman/listinfo/r-sig-geo >>>>>>>> >>>>>>>> >>>>>>>> >>>> >>>> >>> >>> >>> >> >> _______________________________________________ >> R-sig-Geo mailing list >> R-sig-Geo@stat.math.ethz.ch >> https://stat.ethz.ch/mailman/listinfo/r-sig-geo >> > > > -- Brian D. Ripley, [EMAIL PROTECTED] Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ University of Oxford, Tel: +44 1865 272861 (self) 1 South Parks Road, +44 1865 272866 (PA) Oxford OX1 3TG, UK Fax: +44 1865 272595 _______________________________________________ R-sig-Geo mailing list R-sig-Geo@stat.math.ethz.ch https://stat.ethz.ch/mailman/listinfo/r-sig-geo
