Archive

Archive for the ‘GIS’ Category

Preparing routing data for pgRouting

April 10th, 2007 rupert No comments

1. It is important that you already have postgres, postgis, pgRouting installed in your machine.

A. The schema. Below is the sample schema that is a derivative of the kanagawa sample data from pgRouting. Take note of the source and target nodes, as well as the length and the node coordinates (x1,y1; x2,y2) of the line.

cybersoftbj=# \dt
List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 public | geometry_columns | TABLE | postgres
 public | roads            | TABLE | postgres
 public | spatial_ref_sys  | TABLE | postgres
(3 rows)
cybersoftbj=# \d roads
TABLE "public.roads"
COLUMN   |          Type          |                      Modifiers
------------+------------------------+-----------------------------------------------------
gid        | integer                | NOT NULL DEFAULT NEXTVAL('roads_gid_seq'::regclass)
rd_id      | bigint
yutu_id    | integer
block_id   | bigint
heirarchy  | character varying(5)
cn_name    | character varying(75)
py_name    | character varying(100)
source     | bigint
target     | bigint
x1         | numeric
y1         | numeric
x2         | numeric
y2         | numeric
costlength | numeric
the_geom   | geometry

A. Extracting the coordinates of the line segments from Mapinfo.

1. I have to format the data structure as follows…

…here is the roads table after weeding out some unnecessary columns…

… adding the source,target,x1,y1,x2,y2,costlength…

2. Using ObjectGeography. Download the MapBasic Reference

ObjectGeography( object, attribute )

ObjectGeography( object, “1″ ) <– gives you the beginning x coord of the point

3. Export the tab file to a shape file for ArchMap.

Categories: postgis, postgres Tags: , , , ,

Loading Mapinfo table to PostGis

April 5th, 2007 rupert 2 comments

AFAIK, there are only two ways to load data to PostGIS:
1. Using Insert statements
2. Using Utilities.

Utilities include shp2pgsql which is found in “C:\Program Files\PostgreSQL\8.2\bin\” or “/usr/local/pgsql/bin”. To load Mapinfo table, I have used the OGR utilities from FWTools for Windows. and used gdal1.3.2 (since it contains ogr) for Unix.

OGR2OGR CheatSheet should be a good kickstart for basic understanding. For the impatient..

ogr2ogr -f "PostGreSQL" -nlt LINESTRING -a_srs "EPSG:4326" PG:"host=localhost user=username password=mypassword dbname=mydb mytab.TAB -select columnName

or

ogr2ogr -f "PostgreSQL" PG:"host=myhost user=myloginname dbname=mydbname password=mypassword" mytabfile.tab -nln newtablename -select columnName

Bear in mind that you should select out the columns from your mapfile (mine is chinese) especially if you have a diffent encoding in your column which matches your database (postgres). You might get a “Terminating translation prematurely after failed translation of layer [layername]” error. Since Mapinfo stores the text to ASCII, my workaround is to export the tabfile to a UTF-8 textfile then upload it to PostGres. Hoping the primary ids would match to make the necessary updates…

An alternative to load Chinese text from Mapinfo to PostGIS is the ff:
1. In Mapinfo use the Universal Translator to export the table into a shape file.
2. Once you have the shape file, you can directly use the shp2pgsql.

shp2pgsql -W “gbk” -s 4326 lbjrdnt_small_polyline roads > roads.sql

Categories: GDAL/OGR, postgis, postgres Tags: , , ,

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" ] &amp;&amp; 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

Categories: postgis, postgres Tags: , , , , ,

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.
&gt;&gt;&gt; import sys
&gt;&gt;&gt; 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']
&gt;&gt;&gt;

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

Categories: mapserver, tilecache Tags: , , ,