Creating a Spatial Table in PostGIS

April 5th, 2007 rupert 2 comments

Creating the spatial table from scratch…

[postgres@rupert-linux ~]$ su - postgres
[postgres@rupert-linux ~]$ createdb _E UTF-8 template_postgis
CREATE DATABASE
[postgres@rupert-linux ~]$ createlang plpgsql template_postgis
[postgres@rupert-linux ~]$ psql -f /usr/local/pgsql/share/lwpostgis.sql -d template_postgis
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
COMMIT
postgres@rupert-linux ~]$ psql -f /usr/local/pgsql/share/spatial_ref_sys.sql -d template_postgis
[postgres@rupert-linux ~]$ psql

Alternatively you could also create your spatial table from a template…

     14 postgres=# CREATE DATABASE gistest TEMPLATE=template_postgis;
     15 CREATE DATABASE

I have also added the routing functions using routing.sql and routing_postgis.sql

[postgres@rupert-linux ~]$ psql -f /usr/local/pgsql/share/contrib/routing.sql -d template_postgis
[postgres@rupert-linux ~]$ psql -f /usr/local/pgsql/share/contrib/routing_postgis.sql -d template_postgis

FYI…

The spatial_ref_sys table contains the ESPG codes and projections. Currently it has 3162 records.

gistest=# \d spatial_ref_sys;
 srid      | integer                 | NOT NULL
 auth_name | character varying(256)  |
 auth_srid | integer                 |
 srtext    | character varying(2048) |
 proj4text | character varying(2048) |

The geometry_columns contains the geometry type of the table you just created..

gistest=# \d geometry_columns;
 f_table_catalog   | character varying(256) | NOT NULL
 f_table_schema    | character varying(256) | NOT NULL
 f_table_name      | character varying(256) | NOT NULL
 f_geometry_column | character varying(256) | NOT NULL
 coord_dimension   | integer                | NOT NULL
 srid              | integer                | NOT NULL
 type              | character varying(30)  | NOT NULL
Categories: postgis, postgres Tags: , ,

Installing PostGres, PostGIS, pgRouting in Linux

April 3rd, 2007 rupert 5 comments

A. Installing PostGres

A.1. Download and install the source: postgresql-8.2.3.tar.gz

[root@rupert-linux routing]tar -xvzf postgresql-8.1.2.tar.gz
[root@rupert-linux routing]LDFLAGS=-lstdc++
[root@rupert-linux routing]./configure --prefix=/usr/local/pgsql --with-perl --with-python --with-krb5 --with-openssl
[root@rupert-linux routing]make
[root@rupert-linux routing]make install
 
A.2. Post Compile Operations:
 
[root@rupert-linux routing]/usr/sbin/adduser postgres
 
[root@rupert-linux routing]mkdir /usr/local/pgsql/data
 
[root@rupert-linux routing]chown postgres /usr/local/pgsql/data/
 
[root@rupert-linux routing]su - postgres
 
[root@rupert-linux routing]/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data/

Note: If youre default locale is not EN, say chinese, then you would get:

The files belonging to this database system will be owned by user “postgres”.
This user must also own the server process.

The database cluster will be initialized with locale zh_CN.GB18030.
initdb: could not find suitable encoding for locale “zh_CN.GB18030″

A.3. Edit environment variables. Add the ff to your .bash_profile

[root@rupert-linux routing] vi /root/.bash_profile
PATH=$PATH:$HOME/bin:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/usr/local/mysql/bin:$JAVA_HOME/bin:/usr/local/pgsql/bin
export PGLIB=/usr/local/pgsql/lib
export PGDATA=/usr/local/pgsql/data
export PGPORT=5432
export PGOPTS="-i"

A.4. Start PostGres Automatically on Startup. Postgres Startup Script

[root@rupert-linux routing]touch /etc/init.d/postgresql
[root@rupert-linux routing] vi /etc/init.d/postgresql

PostGres Startup Script

#!/bin/sh
# postgresql    This is the init script for starting up the PostgreSQL
#               server
 
# chkconfig: - 85 15
# description: Starts and stops the PostgreSQL backend daemon that handles all database requests.
# processname: postmaster
# pidfile: /usr/local/pgsql/data/postmaster.pid
#
 
# Source function library.
. /etc/rc.d/init.d/functions
 
# Get config.
. /etc/sysconfig/network
 
# Check that networking is up.
# Pretty much need it for postmaster.
[ ${NETWORKING} = "no" ] && exit 0
 
[ -f /usr/local/pgsql/bin/postmaster ] || exit 0
 
# See how we were called.
case "$1" in
start)
pid=`pidof postmaster`
if [ $pid ]
then
echo "Postmaster already running."
else
echo -n "Starting postgresql service: "
su -l postgres -c '/usr/local/pgsql/bin/pg_ctl -o -i -D /usr/local/pgsql/data/ -l /usr/local/pgsql/data/logfile start'
sleep 1
echo
exit
fi
;;
 
stop)
echo -n "Stopping postgresql service: "
killproc postmaster
sleep 2
rm -f /usr/local/pgsql/data/postmaster.pid
echo
;;
 
restart)
$0 stop
$0 start
;;
 
*)
echo "Usage: postgresql {start|stop|restart}"
exit 1
esac
 
exit 0

A.5. PostGres Testing

[root@rupert-linux routing]# su - postgres
[postgres@rupert-linux ~]$ /usr/local/pgsql/bin/createdb test
[postgres@rupert-linux ~]$ /usr/local/pgsql/bin/psql test

B. Installing PostGIS.

B.1. Download Geos

[root@rupert-linux /]# bzip2 -d geos-3.0.0rc4.tar.bz2
[root@rupert-linux /]# tar -xvf geos-3.0.0rc4.tar
[root@rupert-linux /]# ./configure
[root@rupert-linux /]# make
[root@rupert-linux /]# make install

B.2. Download PostGIS.

[root@rupert-linux /]# cp postgis-1.2.1.tar.gz /home/installers/postgresql-8.2.3/contrib/
[root@rupert-linux /]# tar -zxvf postgis-1.2.1.tar.gz
[root@rupert-linux /]# cd /home/installers/postgresql-8.2.3/contrib/postgis-1.2.1/
[root@rupert-linux /]# ./configure --with-pgsql=/usr/local/pgsql/bin/pg_config
[root@rupert-linux /]# make
[root@rupert-linux /]# make install

B.3 Reload ldconfig

[root@rupert-linux /]# vi /etc/ld.so.conf
/usr/local/include
/usr/local/lib
[root@rupert-linux /]# ldconfig

B.4. Testing PostGIS

$ su - postgres
$ createlang plpgsql test
$ /usr/local/pgsql/bin/psql -d test -f /usr/local/pgsql/share/lwpostgis.sql
BEGIN
psql:/usr/local/pgsql/share/postgresql/contrib/lwpostgis.sql:39 NOTICE:  type "histogram2d" is not yet defined
DETAIL:  Creating a shell type definition.
.
.
.
COMMIT
$ /usr/local/pgsql/bin/psql -d test -f /usr/local/pgsql/share/spatial_ref_sys.sql
BEGIN
INSERT 0 1
INSERT 0 1
.
.
.
VACUUM

C. Installing pgRouting.

C.1 Install Boost Library if you don’t have it. Download boost

[root@rupert-linux /]# tar -jxvf boost_1_33_1.tar.bz2
[root@rupert-linux /]# ./configure
[root@rupert-linux /]# make
[root@rupert-linux /]# make install
[root@rupert-linux /]# cd /usr/local/include/boost1.3.3
[root@rupert-linux /]# cp -Rf boost /usr/local/include/

C.2 Download and install CGAL.

[root@rupert-linux installers]# tar zxvf CGAL-3.2.1.tar.gz
[root@rupert-linux installers]# cd CGAL-3.2.1
[root@rupert-linux CGAL-3.2.1]# ./install_cgal --prefix=/usr/local/cgal --with-boost=n --without-autofind -ni /usr/bin/g++
Copy libCGAL.so to the pgsql/lib directory...
[root@rupert-linux CGAL-3.2.1]#  cp -Rf /usr/local/cgal/lib/i686_Linux-2.6_g++-3.4.3/libCGAL.so /usr/local/pgsql/lib/

C.3 Download and install GAUL.

[root@rupert-linux installers]# tar zxvf gaul-devel-0.1849.tar.gz
[root@rupert-linux gaul-devel-0.1849-0]# ./configure -prefix=/usr/local/gaul
[root@rupert-linux gaul-devel-0.1849-0]# make
[root@rupert-linux gaul-devel-0.1849-0]# make install
[root@rupert-linux gaul-devel-0.1849-0]# cd /usr/local/gaul (Note: Copy gaul includes and libraries to standard directories...)
[root@rupert-linux gaul]# cp -Rf include/* /usr/include/
[root@rupert-linux gaul]# cp -Rf lib/* /usr/local/lib/

C.4 Download and install pgRouting.

[root@rupert-linux installers]# tar -jxvf pgRouting-0.9.9.tgz
[root@rupert-linux routing]# vi configure
##########################################
Edit line 3036...
CGAL_MKF='find /usr/local/cgal/include/CGAL.....'
##########################################
[root@rupert-linux routing]# ./configure --with-cgal=/usr/local/cgal --with-gaul=/usr/local/gaul --with boost=/usr/local --pg-sql=/usr/local/pgsql
[root@rupert-linux routing]# make
[root@rupert-linux routing]# make install

Transparent Overlays in TileCache

March 31st, 2007 rupert Comments off

From the tilecache mailing list….

Hi Everyone,

Really appreciate all the replies…

1. Installed Imaging-1.1.6.tar.gz (PIL).

[root@rupert-linux ~]# python
Python 2.3.4 (#1, Mar 10 2006, 06:12:09) [GCC 3.4.5 20051201 (Red Hat 3.4.5-2)] on linux2 Type "help", "copyright", "credits" or "license" for more information.
>>> import sys
>>> print sys.path
['', '/usr/lib/python23.zip', '/usr/lib/python2.3', '/usr/lib/python2.3/plat-linux2', '/usr/lib/python2.3/lib-tk', '/usr/lib/python2.3/lib-dynload', '/usr/lib/python2.3/site-packages',
'/usr/lib/python2.3/site-packages/PIL',
'/usr/lib/python2.3/site-packages/gtk-2.0']
>>>

2. Modified test1.cfm

     41         var options = {
     42                         controls: [new OpenLayers.Control.MouseDefaults()]
     43                       };
     44
     45         map = new OpenLayers.Map( $('map'), options);
     46
     47         var layer_base = new OpenLayers.Layer.WMS(
     48                     "Base Layer",
     49                     "#request.mapserv_tile#",
     50                     {
     51                         map: '/home/map/beijing/new/wms.map',
     52                         layers: '#request.basemap_tile#',
     53                         format: 'image/png', 'transparent': 'false'
     54                     }
     55                     );
     56
     57         var layer_road = new OpenLayers.Layer.WMS(
     58                     "Road Layer",
     59                     "#request.mapserv_tile#",
     60                     {
     61                         map: '/home/map/beijing/new/wms.map',
     62                         layers: '#request.roads_tile#',
     63                         format: 'image/png', 'transparent': 'true'
     64                     },
     65                     {
     66                         reproject: false
     67                     }
     68                     );
     69
     70         map.addLayer(layer_base);
     71         layer_base.setIsBaseLayer(true);
     72
     73         layer_road.setIsBaseLayer(false);
     74         map.addLayer(layer_road);

3. tilecache.cfg. Commented metaTile=true http://222.128.19.19/tilecache/tilecache.cfg

     47 [basemap]
     48 type=WMSLayer
     49 url=http://127.0.0.1/cgi-bin/mapserv?map=/home/map/beijing/new/wms.map
     50 layers=district,greens,major_river,minor_river
     51 #bbox=-180,-90,180,90
     52 #metaTile=true
     53 extension=png
     54
     55 [roads]
     56 type=WMSLayer
     57 url=http://127.0.0.1/cgi-bin/mapserv?map=/home/map/beijing/new/wms.map
     58 #layers=road4,road4label,road3,road3label,road2,road2label,road1,road1label,road11,road11label
     59 #bbox=116.1737,39.8211,116.5640,40.0799
     60 #maxresolution=1.40625
     61 #bbox=-180,-90,180,90
     62 layers=road1,road1label,road11,road11label
     63 extension=png
     64 #metaTile=true

4. Modified wms.map http://222.128.19.19/tilecache/wms.map

OUTPUTFORMAT
     NAME png
     DRIVER "GD/PNG"
     MIMETYPE "image/png"
     IMAGEMODE RGB
     EXTENSION "png"
     FORMATOPTION "INTERLACE=OFF"
END

5. Checked access_log. “transparent=true” exists…

192.168.1.150 - - [30/Mar/2007:13:46:26 +0800] "GET /tilecache/tilecache.py?MAP=%2Fhome%2Fmap%2Fbeijing%2Fnew%2Fwms.map&LAYERS=r
oads&FORMAT=image%2Fpng&TRANSPARENT=true&SERVICE=WMS&VERSION=1.1.1&REQUEST=G
etMap&STYLES=&EXCEPTIONS=application%2Fvnd.ogc.se_inimage&SRS=EPSG%3A4326&BB
OX=116.334229%2C39.891357%
2C116.345215%2C39.902344&WIDTH=256&HEIGHT=256 HTTP/1.1" 200 14580

Now, this is really weird. I have “transparent”: true in test1.cfm all the time. That didn’t worked.

For every test iteration i made, I always tried clearing the cache, removing the python compiled scripts, then restarting Apache just to get a clean state..


rm -Rf /usr/local/apache2/htdocs/tmp/*
rm /wwwroot/tilecache/TileCache/*.pyc
/etc/init.d/httpd restart

Then I tried appending “transparent=true” to tilecache.cfg based on Eric’s suggestion…
That worked. Now I wonder why… Nevertheless, its working now. Again many thanks to everyone…

Rupert

On Fri, Mar 30, 2007 at 01:57:43PM +0800, Rupert de Guzman Jr wrote:
> Hi Everyone,
>
> Really appreciate all the replies…
> Then I tried appending “transparent=true” to tilecache.cfg based on
> Eric’s suggestion…
> That worked. Now I wonder why… Nevertheless, its working now. Again
> many thanks to everyone…

TileCache pays almost no attention to the URL: Only the BBOX and the layername matter. Anything else is simply ignored: so your transparency being set in OpenLayers actually has 0 affect.

The reason for this is that TileCache can only store one copy of an image. If the URL parameters modified the content, you could get an inconsistent cache.

This is ‘by design’, insofar as there is no obvious solution (other than to complain more loudly when TC Gets parameters it isn’t expecting, which is an outstanding FIXME in the code). The lack of error message is not by design, and I’m sorry you got bit by the poorly documented behavior.

Regards,

Christopher Schmidt
MetaCarta

Setting python_path on Unix

March 29th, 2007 rupert Comments off

At the time of this writing.. download mod_python-3.3.1.tgz

./configure –with-apxs=/usr/local/apache2/bin/apxs
make
make install

You need to pass the PYTHONPATH to apache..

AddHandler python-program .py
PythonHandler TileCache.Service
PythonOption TileCacheConfig /usr/local/apache2/htdocs/tilecache/tilecache.cfg
PythonDebug On
PythonPath “sys.path + ['/usr/local/apache2/htdocs/tilecache/']”

In your bash shells, you could also check the PYTHONPATH…

[root@rupert-linux views]# python
Python 2.3.4 (#1, Mar 10 2006, 06:12:09)
[GCC 3.4.5 20051201 (Red Hat 3.4.5-2)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sys
>>> print sys.path
['', '/usr/lib/python23.zip', '/usr/lib/python2.3', '/usr/lib/python2.3/plat-linux2', '/usr/lib/python2.3/lib-tk', '/usr/lib/python2.3/lib-dynload', '/usr/lib/python2.3/site-packages', '/usr/lib/python2.3/site-packages/gtk-2.0']
>>>
Categories: mapserver, tilecache Tags: ,

Unattended Linux Installation using KickStart

March 6th, 2007 rupert Comments off

I have found this very handy in installing the same Linux OS on similar machines.

1. Download the isos from CentOS. I’m using CentOS4.4

2. Make available via http the CentOS installers. The directory should be as follows:


wwwroot/
/CentOS
/headers
/images
/isolinux
/NOTES
/repodata
.discinfo
centosdocs-man.css
GPS
RELEASE-NOTES-en
RELEASE-NOTES-en.html
RPM-GPG-KEY
RPM-GPG-KEY-centos4
yumgroups.xml

3. Create your kickstart files as follow:

#Generated by Kickstart Configurator
#platform=x86, AMD64, or Intel EM64T
 
#System  language
lang en_US.UTF-8
#Language modules to install
langsupport --default=en_US.UTF-8 en_US.UTF-8
#System keyboard
keyboard us
#System mouse
mouse
#Sytem timezone
timezone Asia/Manila
#Root password
rootpw --iscrypted $1$U0Buucop$JgH/lyirJj2Pb36OGvA521
#Reboot after installation
reboot
#Install OS instead of upgrade
install
#Use Web installation
url --url http://192.168.224.1/centos/
#System bootloader configuration
bootloader --location=mbr 
 
xconfig --card "VMWare" --videoram 16384 --hsync 31.5-37.9 --vsync 50-70 --resolution 800x600 --depth 16
#network --device eth0 --bootproto dhcp --hostname centos
#network --bootproto=static --ip=192.168.224.10 --netmask=255.255.255.0 --gateway=192.168.1.1 --hostname centos
network --bootproto=static --ip=192.168.224.10 --netmask=255.255.255.0 --hostname centos 
 
#Clear the Master Boot Record
zerombr yes
#Partition clearing information
clearpart --all --initlabel
#Disk partitioning information
part /boot --fstype ext3 --size 100 --asprimary
part swap --recommended
part / --fstype ext3 --size 2048 --grow
#System authorization infomation
auth  --useshadow  --enablemd5
#Firewall configuration
firewall --enabled --port=22:tcp --port=80:tcp --port=3306:tcp
selinux --disabled
#Do not configure XWindows
skipx
#Package install information
%packages
grub
kernel-smp
e2fsprogs
 
%post
rpm -i http://192.168.224.1/centos/CentOS/RPMS/glibc-kernheaders-2.4-9.1.98.EL.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/glibc-headers-2.3.4-2.25.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/glibc-devel-2.3.4-2.25.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/cpp-3.4.6-3.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/vim-common-6.3.046-0.40E.7.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/vim-enhanced-6.3.046-0.40E.7.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/gcc-3.4.6-3.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/compat-libstdc++-33-3.2.3-47.3.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/autoconf-2.59-5.noarch.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/automake14-1.4p6-12.noarch.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/automake15-1.5-13.noarch.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/automake16-1.6.3-5.noarch.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/automake17-1.7.9-5.noarch.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/automake-1.9.2-3.noarch.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/bzip2-devel-1.0.2-13.EL4.3.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/libidn-0.5.6-1.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/curl-7.12.1-8.rhel4.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/pkgconfig-0.15.0-3.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/libidn-devel-0.5.6-1.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/e2fsprogs-devel-1.35-12.4.EL4.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/krb5-devel-1.3.4-33.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/zlib-devel-1.2.1.2-1.2.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/openssl-devel-0.9.7a-43.10.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/flex-2.5.4a-33.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/libstdc++-devel-3.4.6-3.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/libxml2-devel-2.6.16-6.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/libxslt-devel-1.1.11-1.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/nmap-3.70-1.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/umb-scheme-3.2-36.EL4.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/guile-1.6.4-14.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/swig-1.3.21-6.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/apr-0.9.4-24.5.c4.2.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/apr-util-0.9.4-21.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/neon-0.24.7-4.i386.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/perl-URI-1.30-4.noarch.rpm
rpm -i http://192.168.224.1/centos/CentOS/RPMS/subversion-1.1.4-2.ent.i386.rpm
 
/sbin/chkconfig cups-config-daemon off
/sbin/chkconfig cups off
/sbin/chkconfig gpm off
/sbin/chkconfig isdn off
/sbin/chkconfig kudzu off
/sbin/chkconfig mysql off
/sbin/chkconfig netfs off
/sbin/chkconfig nfslock off
/sbin/chkconfig pcmcia off
/sbin/chkconfig portmap off
/sbin/chkconfig rawdevices off
/sbin/chkconfig rpcgssd off
/sbin/chkconfig rpcidmapd off
/sbin/chkconfig rpcsvcgssd off
/sbin/chkconfig sendmail off
/sbin/chkconfig smartd off
/sbin/chkconfig xfs off
/sbin/chkconfig xinetd off

4. Insert the CentOS4.4 CD1 and boot the machine. On prompt type:

linux ks=http://path-to/centos/ks.cfg

Categories: linux Tags: , ,