Im trying out ways to create a topology of edges and vertices for a road network. Currently, there are three ways I know of:
1. Using ArcGIS build coverage line.
– This includes the use of ArcGIS. Exporting the feature into tics, arcs and nodes, then afterwards assembling them all together. Its functional but have not fully tested the quality of the road topology. Also, assembling them back together through the spatial objects comparison will take some time. It would be better *If* ArcGIS could create the nodes wrt to the base table.
2. Using PostGres, postlbs functions.
– Use of SELECT ASSIGN_VERTEX_ID(‘table_name’, double_precision_distance. So far this bails on me on my first test on my win-xp laptop. We can test this on Linux if the response is the same.
3. Using Mapinfo Basic Scripts provided by J.
– Haven’t gone indepth with these for now, but looking forward to it.
4. Using Grass
– As documented in pgrouting.postlbs.org site’s Topology Creation
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 | geometryA. 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.
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 0A.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