By Rupert
Posts tagged postgres
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
Installing PostGres8.3 and Postgis1.3.2 on Windows
Mar 4th
It seems very nice for the new PostGres8.3 installer to bundle up third party libraries installation with the new admin pack feature. Using this feature, you can install PostGis, nPgsql, etc. The installation was painless and smooth this time. I have to backup all my data though using pg_dump.
To lessen the learning curve on installing PostGres + PostGis together, I made a short flash movie here. There is no audio in the tutorial. The idea was to introduce PostGres to my Chinese staff. I have to remove PostGreSQL 8.2 first before installing 8.3.
GIS 2.0
Dec 21st
It’s been a long time since my previous blog post because I was caught up with numerous meetings and presentations with the recent development of “The Interface“. Developing the new beast during a 2.5 week code sprint is both enjoyable and a personal satisfaction. I have to give credit to Yahoo UI and ExtJS for a wonderful javascript library that they opened to the public. Without ExtJS, I don’t think it would be possible to create such an interactive, compelling and impressive interface for my demos.
Web 2.0 has given me a chance to wrap our traditional GIS processes/solutions into a high impact presentation for our marketing/business developments. Thus, it was not a suprise to see that CamptoCamp’s Mapfish is also using extjs. It has aided me very well in data analysis and visualisation. It may just be a meaningless term to others, however I believe we just entered the start of GIS 2.0…
Reflecting… I believe it is worth investing in learning PostGIS in-depth. Though it may appear fascinating to others that a group of small applications (which I called the core platform utilising Nearest Neighbor Searching and Geometric Unions) bundled together in a Web2.0 interface is impressive, I believe I only scratched the surface of what and how PostGIS should be used. I wanted to do more, but my busy work schedule begs to finish other components of our product.
To be continued…
Exporting from Postgres to Mapinfo
Aug 27th
I had a problem when using ogr2ogr and converting a postgres table to a road table. My postgres table containa a utf-8 road name which is all in chinese. The mapinfo road table created by ogr2ogr seems to contain the correct geometry and other fields that is in utf-8. However, all my chinese characters is all messed up. So, I have to export the file and open it to mapinfo.
1. In Postgres, to export to a file..
cybersoftbjv1=# set client_encoding = gbk;
SET
cybersoftbjv1=# \o road.txt;
cybersoftbjv1=# select rd_id, cn_name from roads where cn_name <> '';
cybersoftbjv1=# \q
2. Open the file in vim, and do a “%s/ //g”. This would replace all ” ” to “”.
Note: This is reasonable for chinese since chinese dont have spaces. However english prases and sentences differ.
3. Open the file in mapinfo and replace the other columns using Table -> Update.
If anybody has any other way to specify the client encoding in ogr2ogr that would be perfect…
Comments