mobile and gis dev notes

1. Edit pg_hba.conf

# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
host    all         all         192.168.1.0 255.255.255.0       md5
# IPv6 local connections:
#host    all         all         ::1/128               trust

2. Edit postgres.conf

55
56 listen_addresses = '*' # what IP address(es) to listen on;
57 # comma-separated list of addresses;
58 # defaults to 'localhost', '*' = all
59 # (change requires restart)

4. For CentOS5.1, create symbolic links:
ln -s /usr/local/lib/libproj.so.0 /usr/lib/libproj.so.0
ln -s /usr/local/lib/libgeos_c.so.1 /usr/lib/libgeos_c.so.1
ldconfig
/etc/init.d/postgresql stop
/etc/init.d/postgresql start

3. Postgis Post Install

createdb -E utf8 template_routing
createlang plpgsql template_routing
psql -d template_routing -f /usr/share/postgresql-8.3-postgis/lwpostgis.sql 
psql -d template_routing -f /usr/share/postgresql-8.3-postgis/spatial_ref_sys.sql 
psql -d template_routing -f /usr/share/postlbs/routing_core.sql 
psql -d template_routing -f /usr/share/postlbs/routing_core_wrappers.sql

Operating System: Debian sid

Versions:

• postgres 8.3.1
• postgis 1.3.3
• pgRouting1.02

1. Install base system and ssh

#vi /etc/apt/sources.list to include
deb http://debian.cn99.com/debian etch main
deb-src http://debian.cn99.com/debian etch main
#apt-get update
#apt-get upgrade libc6

2. Install the required packages for postgres8.3 and postgis1.3.3

#apt-get install sudo nmap telnet
#apt-get install python2.5 python2.5-dev python-setuptools
#apt-get install g++
#apt-get install build-essential cmake ibboost-graph-dev
#apt-get install libreadline5 libreadline5-dev
#apt-get install zlib-bin zlib1g-dev
#apt-get install libkrb5-dev
#apt-get install libcurl3
#apt-get install libssl-dev
#apt-get install postgresql-8.3
#apt-get install postgresql-8.3-postgis
#apt-get install postgresql-server-dev-8.3

3. Installing pgRouting

# tar -zxvf pgRouting-1.02.tgz
# cmake .
# make 
# make install

One of my hurdles recently is migrating data from my Debian Lenny Desktop Box (Postgres8.2.7/Postgis1.3.1) to my new MacBookPro Leopard(Postgres8.3.1/Postgis1.3.3). I found it out the hard way by inspecting the dump files manually. Here are the results:

1. I strongly suggest if you have a big dump file (mine is 500MB) to split the schema from the data.

pg_dump -C -s -h 127.0.0.1 -U lbs -W beijing > beijing_20080507_schema.sql
pg_dump -a -d -h 127.0.0.1 -U lbs -W beijing > beijing_20080507_schema.sql

2. pg_dump from an 8.2 would have statically linked liblwgeom to /usr/lib/postgresql/8.2/liblwgeom. You should change that to whereever your liblwgeom resides, mine is on /usr/local/pgsql/lib/liblwgeom. Just do a simple search and replace using vim on your file_schema.sql

3. After editing the schema, we can now restore the structure of the database. Check for errors and manually update the schema if needs be.

psql -h 127.0.0.1 -U beijing_4326 < beijing_20080507_schema.sql

4. Ok, so now we have the structure ready, we can also check this from pgAdmin3. Have a good look on the functions and table structures if they are fully restored.

5. Let’s load the data.

psql -h 127.0.0.1 -U beijing_4326 < beijing_20080507_data.sql

It seems very nice for the new PostGres8.3 installer to bundle up third party libraries installation with the new admin pack feature. Using this feature, you can install PostGis, nPgsql, etc. The installation was painless and smooth this time. I have to backup all my data though using pg_dump.

To lessen the learning curve on installing PostGres + PostGis together, I made a short flash movie here. There is no audio in the tutorial. The idea was to introduce PostGres to my Chinese staff. I have to remove PostGreSQL 8.2 first before installing 8.3.

‘R’ is a statistical package. For an overview, please go to www.r-project.org
My intention was to remove the point outliers from a given set of point geometries.

I just recently installed R both on my Windows XP and Debian. Regina’s www.bostongis.com is an excellent tutorial in getting involved with R. I do suggest you head first to PLR Part 1: Up and Running with PL/R (PLR) in PostgreSQL: An almost Idiot’s Guide to get you started.

The install instructions for Windows works flawlessly. I have to hold back to R-2.5 though as I plan to use RPy (Python for R), see details below. To install ‘R’ in Debian, there’s a couple of settings that we need to take care of…

1. Install r-base
sudo apt-get install r-base

2. Install plr on postgres
sudo apt-get install postgresql-8.2-plr

3. Using R in a database
psql -d beijing -U lbs -h 127.0.0.1 < /usr/share/postgresql/8.2/plr.sql

4. Set the R_HOME environment variable
/etc/postgresql/8.2/main/environment
R_HOME='/usr/lib/R'

5. Restart Debian.

RPy, R for Python, is another alternative to use R in Python. I installed it both in Windows and Debian. Note that I reverted to R-2.5 on Windows to be compatible with RPy. For Debian, Im currently using R-2.6.

For the Windows Binary Installation,

1. Read the RPy Main Site

2. Install prerequisites:

- NumPy
- Win32 Extensions Download

3. Afterwards, install the main package, RPy Download

In Debian, its a straight forward…sudo apt-get install python-rpy