By Rupert
Posts tagged postgis
Transform 900913 to 4326
Sep 4th
Stumbled upon an error in reprojecting data from 900913 (Google Speherical Mercator) to 4326 (WGS84). I’m recalling this from my head now, the error was something related to “NAD sth”. Workaround was to convert it as follows:
1. 900913
2. 32650 (UTM for your area)
3. 4326
I’ll get the SQL from my ofcmate and paste it here…
Postgres PostGIS CheatSheet v2
Aug 29th
This is a quick-command list of Postres. If you want detailed instructions, please visit the Postgres Manual.
How do I Show all databases?
1. Using “psql -l”
2. Using
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.
How do I run a script from the prompt?
psql -d cybersoftbj -u user -f myfile.sql
Its very useful in reloading user-defined functions.
How do I create a user/role?
CREATE ROLE lbs WITH LOGIN PASSWORD 'mypassword' SUPERUSER INHERIT CREATEDB CREATEROLE;
How do I change the password for a user/role?
ALTER ROLE lbs PASSWORD 'mynewpassword';
How to provide/restrict access privileges to tables?
GRANT SELECT ON TABLE table TO user;
REVOKE SELECT ON TABLE table FROM user;
How to dump database in a text file?
pg_dump -U lbs -d cybersoftbjv1 -h 127.0.0.1 -W > cybersoftbjv1.sql
How to dump database cleanly?
pg_dump -c -d -E UTF8 -h 127.0.0.1 -U lbs -W platform_v1 > platform_v1.sql
How to rename a database?
ALTER DATABASE beijing_app RENAME TO beijing_app_20080801;
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;
How to change a column type with Cast?
ALTER TABLE roads ALTER COLUMN class_new TYPE integer USING class_new::integer;
How to add a geometry column to a table?
EXAMPLE: SELECT AddGeometryColumn(‘public’, ‘poi’, ‘the_geom’, 4326, ‘POINT’, 2)
Changing column names with spaces?
ALTER TABLE class_aroundme RENAME "level 1" TO level_1;
Setting kernel shmmax for postgres
sysctl -w kernel.shmmax=134217728
Note: For permanent changes see /etc/sysctl.cfg11. How to backup table(s) from pg_dump?
pg_dump poi_beijing -t class -t poi_class -f $BACKUPDIR/test_$MYDATE.sql
Postgres PostGis PostInstall
Jun 7th
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
Installing Postgresql, Postgis, pgRouting on Debian
May 25th
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
Installing Postgres8.3,8.4, Postgis1.3.3,1.4.1, pgRouting on MacOSX Leopard
May 1st
References:
http://developer.apple.com/internet/opensource/postgres.html
http://www2.russbrooks.com:8080/2007/11/4/install-postgresql-on-mac-os-x-10-5-leopard
Warning: Most of the packages listed below is installed by source.
1. Download the current postgres source.
8.3: $./configure --with-prefix=/usr/local/pgsql --with-python 8.4: $./configure --prefix=/usr/local/pgsql --with-python $make $sudo make install
2. Don’t delete the postgres folder. You might need this later on for future compilations. See pgadmin3 admin pack below.
3. Add a postgres user from System Preferences > Accounts
4. Initialize the data directory
$mkdir /usr/local/pgsql/data $chown postgres /usr/local/pgsql/data/ $su - postgres $/usr/local/pgsql/bin/initdb -E utf8 -D /usr/local/pgsql/data
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 C. The default text search configuration will be set to "english". fixing permissions on existing directory /usr/local/pgsql/data ... ok creating subdirectories ... ok selecting default max_connections ... 20 selecting default shared_buffers/max_fsm_pages ... 2400kB/20000 creating configuration files ... ok creating template1 database in /usr/local/pgsql/data/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... ok creating dictionaries ... ok setting privileges on built-in objects ... ok creating information schema ... ok vacuuming database template1 ... ok copying template1 to template0 ... ok copying template1 to postgres ... ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the -A option the next time you run initdb. Success. You can now start the database server using: /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data or /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
4. Install Postgis
- install geos with the standard ./configure -> make -> make install
- then install postgis
8.3: $./configure --with-pgsql=/usr/local/pgsql/bin/pg_config --with-geos=/usr/local/bin/geos-config 8.4 using Kyngchaos GEOS Framework: $./configure --with-pgsql=/usr/local/pgsql/bin/pg_config --with-geosconfig=/Library/Frameworks/GEOS.framework/unix/bin/geos-config $make $sudo make install
5. Starting postgres on boot. Download postgresstartup.tar.gz then extract to your /Library/StartupItems
6. Creating the database
createdb -E utf8 template_postgis createlang plpgsql template_postgis 8.3: psql -d template_postgis -f /usr/local/pgsql/share/lwpostgis.sql psql -d template_postgis -f /usr/local/pgsql/share/spatial_ref_sys.sql 8.4: psql -d template_postgis -f /usr/local/pgsql/share/contrib/postgis.sql psql -d template_postgis -f /usr/local/pgsql/share/contrib/spatial_ref_sys.sql
7. Install and download pgAdmin3 for MacOS X
http://www.postgresql.org/ftp/pgadmin3/release/
8. Startup pgadmin3. You will notice there is a window stating…
Server instrumentation
The server lacks instrumentation functions.
pgAdmin III uses some support functions that are not available by default in all PostgreSQL versions. These enable some tasks that make life easier when dealing with log files and configuration files.
9. Compile the adminpack. Go to $postgresql_install_directory/contrib/adminpack
$ make gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -bundle -multiply_defined suppress adminpack.o -L../../src/port -bundle_loader ../../src/backend/postgres -o libadminpack.0.0.so rm -f libadminpack.0.so ln -s libadminpack.0.0.so libadminpack.0.so rm -f libadminpack.so ln -s libadminpack.0.0.so libadminpack.so rupert:adminpack rupert$ sudo make install Password: mkdir /usr/local/pgsql/share/contrib /bin/sh ../../config/install-sh -c -m 644 ./uninstall_adminpack.sql '/usr/local/pgsql/share/contrib' /bin/sh ../../config/install-sh -c -m 644 adminpack.sql '/usr/local/pgsql/share/contrib' /bin/sh ../../config/install-sh -c -m 755 libadminpack.0.0.so '/usr/local/pgsql/lib/adminpack.so'
10. Load the adminpack.sql into your maintenance and template database
$ psql -U postgres -d postgres -f /usr/local/pgsql/share/contrib/adminpack.sql CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION ...
11. Disconnect and Reconnect from pgAdmin3. You shouldn’t see the window again.
Continue only if you want pgRouting
12. Essentially we would need Boost Graph Library (BGL) a.k.a boost, Genetic Algorithm Utility Library (or, GAUL for short), and GEOS (which we installed earlier when we installed postgis).
13. The easiest way to install boost is using MacPorts.
$ sudo port install boost-jam ---> Fetching boost-jam ---> Attempting to fetch boost-jam-3.1.16.tgz from http://downloads.sourceforge.net/boost ---> Verifying checksum(s) for boost-jam ---> Extracting boost-jam ---> Configuring boost-jam ---> Building boost-jam ---> Staging boost-jam into destroot ---> Installing boost-jam 3.1.16_0 ---> Activating boost-jam 3.1.16_0 ---> Cleaning boost-jam ---> Fetching gmake ---> Attempting to fetch make-3.81.tar.bz2 from http://ftp.gnu.org/gnu/make ---> Verifying checksum(s) for gmake ---> Extracting gmake ---> Configuring gmake ---> Building gmake with target all ---> Staging gmake into destroot ---> Installing gmake 3.81_0 ---> Activating gmake 3.81_0 ---> Cleaning gmake ---> Fetching boost ---> Attempting to fetch boost_1_34_1.tar.bz2 from http://downloads.sourceforge.net/boost ... $ sudo port install boost
Note: I encountered an error when I directly installed “sudo port install boost” on my first run. A quick workaround is to install boost-jam, then install boost afterwards. For more details:
- http://trac.macosforge.org/projects/macports/ticket/13714
- http://trac.macosforge.org/projects/macports/ticket/14043
Error: Target org.macports.build returned: shell command ” cd “/opt/local/var/macports/build/_opt_local_var_macports_sources_rsync.macports.org_release_ports_devel_boost/work/boost_1_34_1″ && gmake all ” returned error 2 Command output: Makefile:2: *** missing separator. Stop.
14. You can check if boost was successfully installed by…
$ sudo port -v installed boost boost-jam The following ports are currently installed: boost @1.34.1_3+darwin_9 (active) boost-jam @3.1.16_0 (active)
15. For GAUL, we don’t need slang base on http://pgrouting.postlbs.org/wiki/1.x/InstallationManual.
$ ./configure --enable-slang=no $ make $ make install
16. Ok, so this is the heartbreaker. I was able to get pass cmake on pgRouting on version 1.02 however, I received “undefined symbols” when linking the librouting.dylib
Linking CXX shared library ../../lib/librouting.dylib
Undefined symbols:
"_errcode", referenced from:
_shortest_path in dijkstra.o
_shortest_path_astar in astar.o
_shortest_path_shooting_star in shooting_star.oFor the complete error details, see pgrouting_problem.txt
17. Thanks to www.kyngchaos.com, we can install http://www.kyngchaos.com/files/software/unixport/pgRouting-1.0.1-4.dmg binary from http://www.kyngchaos.com/wiki/software:postgres
18. Now we can load the pgRouting functions to our template database
psql -U postgres -d template_postgis -f /usr/local/pgsql/share/contrib/routing_core.sql psql -U postgres -d template_postgis -f /usr/local/pgsql/share/contrib/routing_core_wrappers.sql psql -U postgres -d template_postgis -f /usr/local/pgsql/share/contrib/routing_dd.sql psql -U postgres -d template_postgis -f /usr/local/pgsql/share/contrib/routing_dd_wrappers.sql psql -U postgres -d template_postgis -f /usr/local/pgsql/share/contrib/routing_dd_tsp.sql psql -U postgres -d template_postgis -f /usr/local/pgsql/share/contrib/routing_tsp.sql psql -U postgres -d template_postgis -f /usr/local/pgsql/share/contrib/routing_tsp_wrappers.sql
Comments