This is a quick-command list of Postres. If you want detailed instructions, please visit the Postgres Manual.
1. How do I Show all databases?
postgres=# \l
List of databases
Name | Owner | Encoding
------------------+----------+----------
postgis | postgres | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
template_postgis | postgres | UTF8
(5 rows)
Note: Do not drop template databases if not necessary.
2. How do I run a script from the prompt?
\i
OR
psql -d cybersoftbj -u user < myfile.sql
Its very usuful in reloading user-defined functions.
3. How do I create a user?
CREATE ROLE lbs WITH LOGIN PASSWORD 'tracking' SUPERUSER INHERIT CREATEDB CREATEROLE;
4. How to dump database in a text file?
pg_dump -U lbs -d cybersoftbjv1 -h 127.0.0.1 -W > cybersoftbjv1.sql
4. How to dump database cleanly?
pg_dump -c -d -E UTF8 -h 127.0.0.1 -U lbs -W platform_v1 > platform_v1.sql
5. How to update using two tables?
UPDATE road_for_update u
SET the_geom = r.the_geom
FROM roads r
WHERE r.rd_id = u.rd_id;
6. How to change a column type with Cast?
ALTER TABLE roads ALTER COLUMN class_new TYPE integer USING class_new::integer;
7. How to provide/restrict access privileges to tables?
GRANT SELECT ON TABLE table TO user;
REVOKE SELECT ON TABLE table FROM user;
8. How to add a geometry column to a table?
SYNTAX: AddGeometryColumn(
,
, , , )
EXAMPLE: SELECT AddGeometryColumn(‘public’, ‘poi’, ‘the_geom’, 4326, ‘POINT’, 2)
9. Changing column names with spaces?
ALTER TABLE class_aroundme RENAME "level 1" TO level_1; 10. Setting kernel shmmax for postgres
sysctl -w kernel.shmmax=134217728
Note: For permanent changes see /etc/sysctl.cfg
11. How to backup table(s) from pg_dump?
pg_dump poi_beijing -t class -t poi_class -f $BACKUPDIR/test_$MYDATE.sql
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.
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
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 DATABASEI 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
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