Connecting to a Postgresql data base from R (Ubuntu Natty)

Just three quick steps in Ubuntu.

1. Install unixodbc and odbc-postgresql

sudo apt-get install  unixodbc

sudo apt-get install odbc-postgresql

2. Edit the blank odbc.ini file. You can just paste in the text below. The first entry is for my own local database. Change the details to match your own if you have one.  If you want access to the online Biotree.net database you need to substitute the real password for the ***** entry.

sudo gedit /etc/odbc.ini

[ODBC Data Sources]
mydb = Database description

[mydb]
Driver = /usr/lib/odbc/psqlodbcw.so
Database = gisdb
Servername = localhost
Username = postgres
Password = postgres
Protocol = 8.2.5
ReadOnly = 0

[ODBC Data Sources]
biotree = Database description

[biotree]
Driver = /usr/lib/odbc/psqlodbcw.so
Database = biotree_development
Servername = apps.iecolab.es
Username = biotree_adv
Password = *******
Protocol = 8.2.5
ReadOnly = 0

[ODBC]
InstallDir = /usr/lib

Step 3. Then in R, assuming that the RODBC library is installed

library(RODBC)

con<-odbcConnect(“biotree”)

You now design a query and run it to get your data.

d<-sqlQuery(con, “select * from gis.biotree_db where genus like ‘Quercus’”)
fix(d)

Note that if you want spatial layers from a PostGIS data base in R you can use rgdal to connect to the data base. The problem is that you can’t run queries from within R using rgdal. You can only import the whole layer. The work around is to use system (ogr2ogr …) with a temp file, but for most purposes it is easier to visualise and edit the layer in Qgis and save as shapefile for importing into R.

What is the equivalent in Windows?

It is a similar process but not quite as simple. You first need to install the odbc driver from http://postgresql.org . The easiest way is to download the msi file that installs itself.

 

Once the driver is installed you just need to set up the connector from the control panel, administrative tools,

You can use ODBC with R in the dame way, and you can also  load Postgresql queries into Excel if you find any reason to do so.

 

Installing PostGIS in Ubuntu Natty

This will install PostGIS and add the R language through PLR in the current version of Ubuntu Natty 11.04

sudo apt-get install qgis
sudo apt-get install postgresql
sudo apt-get install postgresql-8.4-postgis
sudo apt-get install postgresql-8.4-plr
sudo apt-get install pgadmin3

One small step that is necessary is to change the user password for postgres. You can do this with psql. Care needed here. This has to be done correctly. The following line gets you into psql.

sudo -u postgres psql -d template1

Type this (being very careful with quotation marks and the semicolon).

alter user postgres with password 'postgres';

If successul you get a message saying ALTER ROLE (If there is any problem here then retype the single quotation marks to make sure they are simple. WordPress keeps changing them for some reason if they are not in an HTML box).

Now become the postgres user and create a postgistemplate. This is a blank data base into which you will load all the functions and tables needed for POSTGIS. All new data bases created using this template will have these functions loaded.

sudo su postgres
createdb postgistemplate
createlang plpgsql postgistemplate

The PostGIS functions and spatial_ref system are loaded from here on Ubuntu

psql -d postgistemplate -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
psql -d postgistemplate -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql

This cpmmand loads in the R language (to use this you must have R installed)
 psql -d postgistemplate -f /usr/share/postgresql/8.4/plr.sql

To create a database under this template
createdb -T postgistemplate -O postgres gisdb

You will also have to alter the following config file

sudo gedit /etc/postgresql/8.4/main/postgresql.conf

Find the line with listen_addresses and uncomment it. If you want the database to be open to other users use’*’ instead of localhost
listen_addresses = ‘*’        # what IP address(es) to listen on;

Download a small test database with the countries of the word from this site with wget. Again the file  is disguised as a word doc in order to go into the wordpress site.

wget http://duncanjg.files.wordpress.com/2008/09/paises.doc

Restore this database. The database was built using an old version of Postgis, so you will see many errors which you can ignore (See http://blog.cleverelephant.ca/2010/09/postgis-back-up-restore.html

sudo -u postgres psql gisdb<paises.doc

Now run qgis and connect to your new PostGIS data base.

 

The trial database with a single countries of the world table can also be downloaded without wget by clicking here.

paises

Updating to Ubuntu 11.04: Solving the sticking cursor

I now expect Ubuntu to work strait out of the box so I was disappointed by a severe bug in the kernel that the current version of Natty uses.

uname -a
Linux duncan-HP 2.6.39-02063904-generic #201108040905 SMP Thu Aug 4 11:04:48 UTC 2011 i686 i686 i386 GNU/Linux

The issue makes the cursor periodically freeze on the screen for up to a second. The system can still be used, but the experience is extremely frustrating. It turns out that a process called kworker is using 100% of the CPU.  This can be fixed by following the instructions here, http://ubuntuforums.org/showthread.php?t=1796873.

Typing

sudo echo “options drm_kms_helper poll=N”>/etc/modprobe.d/local.conf

and rebooting solved the problem for me.

Notice that if you install this version you get a new “look and feel”. Not to my taste, but it is easy to go back to the old classic at log in.

Lyx and Sweave: Worth climbing the learning curve?

One of the interesting elements of using Linux is the demystification of many *nix concepts that you might have come into contact previously in a lateral manner. One of these is Latex. Any R user in Windows quickly becomes aware that Latex exists. However Latex is fundamentally a *nix thing. It has even been claimed by Windows users that Latex is a legacy typesetting paradigm!

There are certainly some issues with the complexity of Latex. However there is no denying that Latex documents look impressively formal. Personally I think it is worth the effort to look seriously at latex, even if you are a complete newcomer to Linux.

I was curious about Latex. However I readily admit that I really couldn’t be bothered to learn latex as such. Life is far too short. Fortunately I found Lyx. Lyx is Latex for the lazy. No need to learn all the details, it is advertised as a WYSWYM latex typesetting program (What you see is what you mean).

So how do you use Lyx? The first important tip is to make sure that you install ABSOLUTELY, BUT ABSOLUTELY, EVERYTHING remotely relevant to TEX and Latex before starting anything with Lyx. Fonts, languages, the lot. This  avoids later frustrations with missing sty files etc. I can’t remember offhand all the packages I needed, but do go to Synaptics and look for texlive-full plus anything else remotely related with either tex or latex. This will mean quite a long initial download but you certainly won’t regret it. Without all the Tex/Latex stuff installed the Lyx experience can be frustrating. You are likely to get at least a few annoying and incomprehensible error messages when compiling documents. This could give you the feeling that the whole system is more trouble than it is worth. You will add about 500 MB to your install in total, but that shouldn’t be a problem.

The next step before using Lyx seriously for work is to analyse your own ability with Word or Open Office. The idea of using Lyx is to save time in the long run. Experienced Office users can probably replicate everything Lyx or Latex can do. However many of us do have problems writing long documents such as theses or technical reports in Office suites. It becomes very difficult to maintain a consistent style. I now find Lyx easier to use than any office program for long document. The logic of using Lyx is to improve productivity, not make simple tasks much more difficult. Define what was most difficult for you to use in an Office suite and find a consistent way to achieve it in Lyx. My main problems were with positioning figures on the page and producing correctly structured tables of content. Both are very easy in Lyx once you have found out how, but there is an initial learning curve.

Then follow the examples in the Lyx documentation and allow some time for experimentation.

Sweave in Lyx

Most R users will  be very interested in the use of Sweave with Lyx. This has been made possible by the work of Gregor Gojanc. I have only recently realised just how cool this can be. Once all the Latex extras are installed in Ubuntu following the instructions in “INSTALL” here worked well.

http://cran.r-project.org/contrib/extra/lyx/

A faster way of getting to this point would be to download the file below which contains my .lyx directory hidden as a zip file with fake doc extension. Replace the whole of the .lyx directory you have in your home directory by mine. Remember to use control H to see hidden files. You might want to back up your original directory first, but the replacement shouldn’t cause any issues.

lyxzip1

Then do tools/reconfigure in Lyx and you should be able to use Sweave. My directory also has a layout incorporated for making beamer files that I got from here.

http://ggorjan.blogspot.com/2008/09/using-beamer-with-lyx-sweave.html

Here is an example

beamersweave

The source in Lyx that made this is available here (again take of the doc extension and change it to .lyx), You’ll need beamer-latex installed to compile it and you will have to provide your own logo picture.

beamersweavelyx

And here is an early draft of a course for beginners to R that I am writing in Lyx/Sweave. If the documents compile then you have everything installed correctly.

rcoursedraft1lyx

Again there is a bit a sharp curve up to get Lyx and Sweave working. You will need to read the sweave manual first (A Google for Sweave will provide other material)

It is admitedly hard to get Sweave/beamer working as this is still quite an experimental concept. I found that not all the beamer features work, but you can still get some nice looking slides. The easiest way to use beamer for the time being is to adapt my template by cutting and pasting the box of “ERT”  for every new slide, changing the R code within them to what you need. Some styles such as Title and Author stop the document compiling, so don’t change the title page much. Remember to put your own logo in the graphic. The huge payoff in terms of time saved producing slides is made by \begin{frame}[shrink,containsverbatim]. The shrink option automatically adjusts R output to the page. Great as long as you don’t print a huge object. Also notice the use of xtable and <<results=tex>> in code chunks to produce formatted tables.

Inserting a histogram into a normal Lyx/Sweave document like the R course is very simple. In an ERT box you just need to write.

<<fig=T>>=

hist(x)

@

The great thing about this way of working is you know all the R code you are showing actually runs, as they document won’t compile if you’ve made a typo or syntax error.

I will be mentioning Lyx with bibtex in a later post as this is another potentially major productivity booster that may need some help to use at the start.

A presentation for the first class on the introductory R course I am writing is now available here

presentation1

¿Software libre para el UNICH?

La Universidad Intercultural de Chiapas es una institución nueva en San Cristóbal con la misión  de proporcionar mas oportunidades de educación superior de calidad a los jóvenes de la zona. El término “intercultural” viene del reconocimiento que la zona de los Altos de Chiapas esta compuesta de diversos grupos étnicos.

La UNICH recientemente me contactó para ayudar a analizar una propuesta para un laboratorio de información geográfica. El presupuesto inicial fue de 809,000 pesos. Una parte sustancial de este gasto (135,000 pesos) estaba en el rubro de Software. No estaba bien calculado, dado de que la intención fue comprar solamente una licencia para cada elemento. Este corría el riego de uno de dos resultados. 1) El uso muy restingido del laboratorio. 2) La tentación de usar software sin haber pagado correctamente por los derechos. Hoy en día herramientas geográficas deben estar puesto a la disposición de todos, particularmente los jóvenes procedentes del ámbito rural alrededor de San Cristóbal.

Hoy escribí una nueva propuesta para el laboratorio basado en el uso de Software libre. Esta disponible haciendo clic abajo.

unich

¿Por qué uso Linux?

Hay razones ideológicas de apoyar el movimiento de software libre. Tengo simpatía con ellos. Pero uso Linux (Ubuntu) en mi laptop por razones puramente prácticas.
Un sistema operativo no es más que una plataforma para correr programas. Como tal, mientras todos los programas corren bien, uno no debe pensar sobre el asunto. Este es especialmente importante cuando se trata de la maquina que usas todo el tiempo. El problema con Windows es que simplemente no hace lo que prometa hacer.
1. No quiero arriesgar la perdida de tiempo asociado con infecciones de virus.
2. No quiero spyware.
3. No quiero estar constantemente checando todos los archivos que recibo por virus
4. No quiero tener que reiniciar la maquina varios veces al día si abro mas de tres programas para recuperar la memoria (Windows XP).
5. No quiero tener que reinstalar el sistema operativa cada seis meses para mantenerlo funcionando a la velocidad inicial (Windows XP)
6. No quiero tener un sistema instalada que no puedo fácilmente reinstalar (Windows Vista)
7. No quiero que el sistema operativa decide cuales programas puedo instalar (Windows Vista)
8. No quiero comprar mas de 1GB de memoria para correr el sistema operativa (Windows Vista)
9. No quiero esperar constantemente para abrir programas.
10. No quiero pensar que mi laptop ha costado mil pesos mas que vale por haber sido forzado a comprar el sistema operativa al mismo tiempo (gracias a Dell este no es siempre cierto)
11. Quiero un diseño con interface moderno , atractivo y fácil de usar (Ubuntu)
12. Quiero tener la confianza que siempre puedo reinstalar el sistema rápidamente si pasa algo.
13. Quiero correr programas gratis y abiertos como Open Office, GRASS, Qgis, Gimp en una forma eficiente. (Nota: Los versiones de los mismos para Windows son disponibles pero no corren tan rápidamente)
14. Quiero instalar programas en línea en vez de buscar discos de instalación que se puede perder.
15. Quiero tener disponible herramientas practicas para procesar datos y imágenes usando “scripts” (Image Magick, Awk, Python, wget etc)
16. Quiero configurar mi laptop como servidor (Apache2, MySQL etc)
17. Quiero usar bases de datos potentes y geográficamente explicitas (Postgresql)
18. Quiero usar la memoria eficientemente para que puedo correr otros sistemas operativas como maquinas virtuales si un programa que tengo que usar no es disponible para el sistema nativa (e.g Word, Excel etc).
19. Quiero tener varios espacios de trabajo (Vista si tiene, XP no)
20. Quiero divertirme de vez en cuando haciendo el sistema visualmente atractiva (Beryl o Compiz )
21. Quiero encontrar información para arreglar mis propios problemas si el sistema no funciona.

Quiero que la pantalla que esta en frente de mis ojos tantas horas por dia se ve asi …….