mobile and gis dev notes

Installing Postgres8.3,8.4,9.0, Postgis1.3.3,1.4.1,2.0, pgRouting on OSX (updated)

February 15th, 2011 rupert No comments

(Updated):Postgres9.0.3 Postgis2.0svn

Before you go any further. Dump data before migrating and installing. Warning: Most of the packages listed below is installed by source.

1. Download the current postgres source. http://www.postgresql.org/ftp/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 Geos , PROJ4, and Postgis

GEOS:

- install geos with the Kyngchaos installer

- installing by source

svn checkout http://svn.osgeo.org/geos/trunk trunk
cd trunk/
sh autogen.sh
./configure --prefix=/usr/local/geos --enable-python
make
make install
sudo make install

PROJ4:

svn co http://svn.osgeo.org/metacrs/proj/trunk/proj/ proj
cd proj
sh autogen.sh
./configure --prefix=/usr/local/proj4
make
sudo make 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
 
9.0:using Kyngchaos GEOS + PROJ framework:(Update Feb 15, 2011)
./configure --with-pgsql=/usr/local/pgsql/bin/pg_config --with-geosconfig=/Library/Frameworks/GEOS.framework/unix/bin/geos-config --with-projdir=/Library/Frameworks/PROJ.framework/unix/ --with-gettext=/usr/local/share/gettext/
 
Note: libintl.h error. You need gettext
gcc -g -O2  -fno-common -DPIC  -Wall -Wmissing-prototypes  -DUSE_NLS -DLOCALEDIR=\"/usr/local/pgsql/share/locale\" -Iyes/include  -c shp2pgsql-core.c
In file included from shp2pgsql-core.c:15:
shp2pgsql-core.h:17:21: error: libintl.h: No such file or di
 
9.0.4: using src from GEOS + PROJ
./configure --with-pgconfig=/usr/local/pgsql/bin/pg_config --with-geosconfig=/usr/local/geos/bin/geos-config --with-projdir=/usr/local/proj4
 
$make
$sudo make install

If everthing is successful, you should see files in /usr/local/pgsql/share/contrib/.

cd /usr/local/pgsql/share/contrib/postgis-2.0
- legacy.sql
- postgis.sql
- postgis_upgrade_20_minor.sql
- spatial_ref_sys.sql
- uninstall_legacy.sql
- uninstall_postgis.sql

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
 
9.0 + postgis2.0:
psql -d template_postgis -f /usr/local/pgsql/share/contrib/postgis-2.0/postgis.sql 
psql -d template_postgis -f /usr/local/pgsql/share/contrib/postgis-2.0/spatial_ref_sys.sql

7. Install and download pgAdmin3 for MacOS X

http://www.postgresql.org/ftp/pgadmin3/release/

Update Feb 15, 2011: Use pgadmin1.12 to connect with postgres9. Other versions (1.8.4, 1.6.3 don’t work)

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:

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.o

For 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

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

Categories: mac, postgis, postgres Tags: leopard, mac, osx, pgRouting, postgis, postgres

Migrating postgres/postgis data (updated)

February 15th, 2011 rupert Comments off

One of my hurdles recently is migrating data from my Debian Lenny Desktop Box (Postgres8.2.7/Postgis1.3.1) to my new MacBookPro Leopard(Postgres8.3.1/Postgis1.3.3). I found it out the hard way by inspecting the dump files manually.

1. pg_dump is your friend.

pg_dump --help

2. I strongly suggest if you have a big dump file (mine is 500MB) to split the schema from the data.

Add “-s” to create the schema:

pg_dump -C -s -h 127.0.0.1 -U lbs -W beijing > beijing_20080507_schema.sql

Add “-a” to dump the data only:

pg_dump -a -h 127.0.0.1 -U lbs -W beijing > beijing_20080507_schema.sql

3. pg_dump from an 8.2 would have statically linked liblwgeom to /usr/lib/postgresql/8.2/liblwgeom. You should change that to whereever your liblwgeom resides, mine is on /usr/local/pgsql/lib/liblwgeom. Just do a simple search and replace using vim on your file_schema.sql

4. After editing the schema, we can now restore the structure of the database. Check for errors and manually update the schema if needs be.

psql -h 127.0.0.1 -U beijing_4326 < beijing_20080507_schema.sql

5. Ok, so now we have the structure ready, we can also check this from pgAdmin3. Have a good look on the functions and table structures if they are fully restored.

6. Let’s load the data.

psql -h 127.0.0.1 -U beijing_4326 < beijing_20080507_data.sql

Categories: postgis, postgres Tags: postgis, postgres

Postgres PostGIS CheatSheet v2

November 11th, 2010 rupert No comments

This is a quick-command list of Postres. If you want detailed instructions, please visit the Postgres Manual.

How do I know the version of Postgis?

SELECT POSTGIS_FULL_VERSION();

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 &gt; cybersoftbjv1.sql

How to dump database cleanly?

% pg_dump -c  -d -E UTF8 -h 127.0.0.1 -U lbs -W platform_v1 &gt; 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;

DROP TABLE

DROP TABLE IF EXISTS "my_table";

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?

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.cfg

11. How to backup table(s) from pg_dump?

% pg_dump poi_beijing -t class -t poi_class -f $BACKUPDIR/test_$MYDATE.sql

Change integer primary key to serial

CREATE SEQUENCE seq_job_id INCREMENT 1 MINVALUE 1000 MAXVALUE 2147483648 START 1000 CACHE 1;
 
ALTER TABLE job ALTER COLUMN job_id SET DEFAULT NEXTVAL('seq_job_id'::regclass);
 
SELECT * FROM job
 
SELECT NEXTVAL('seq_job_id')

Date and Time Function Helper: date_add

CREATE OR REPLACE FUNCTION date_add(diffType Character Varying(15), incrementValue bigint, inputDateTime timestamp without time zone) RETURNS timestamp AS $$
DECLARE
   YEAR_CONST Char(15) := 'year';
   MONTH_CONST Char(15) := 'month';
   DAY_CONST Char(15) := 'day';
   HOUR_CONST Char(15) := 'hour';
   MIN_CONST Char(15) := 'minute';
   SEC_CONST Char(15) := 'second';
 
   dateTemp timestamp without time zone;
   intervals interval;
BEGIN
   IF lower($1) = lower(YEAR_CONST) THEN
       SELECT cast(cast(incrementvalue AS character varying) || ' year' AS interval) INTO intervals;
   ELSEIF lower($1) = lower(MONTH_CONST) THEN
       SELECT cast(cast(incrementvalue AS character varying) || ' months' AS interval) INTO intervals;
   ELSEIF lower($1) = lower(DAY_CONST) THEN
       SELECT cast(cast(incrementvalue AS character varying) || ' day' AS interval) INTO intervals;
   ELSEIF lower($1) = lower(HOUR_CONST) THEN
       SELECT cast(cast(incrementvalue AS character varying) || ' hours' AS interval) INTO intervals;
   ELSEIF lower($1) = lower(MIN_CONST) THEN
       SELECT cast(cast(incrementvalue AS character varying) || ' minutes' AS interval) INTO intervals;
   ELSEIF lower($1) = lower(SEC_CONST) THEN
       SELECT cast(cast(incrementvalue AS character varying) || ' seconds' AS interval) INTO intervals;            
   END IF;
 
   dateTemp:= inputDateTime + intervals;
 
   RETURN dateTemp;
END;
$$ LANGUAGE plpgsql;

How to set the current timezone in postgres?

# Session based only
SET time zone 'utc';
SELECT  current_setting('TIMEZONE');
# Permanent
# Edit /usr/local/var/postgres/postgresql.conf (#postgres installed via homebrew)
timezone = 'UTC'

Date/Time Functions

SELECT current_setting('TIMEZONE'); 
--"Australia/Victoria"
 
SELECT Now(), 
timezone('UTC', now()), 
EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)),
to_timestamp(EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))),
to_timestamp(EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)))::TIMESTAMP
 
--"2011-11-14 09:29:14.249427+11" 
--"2011-11-13 22:29:14.249427"
--1321223354
--"2011-11-14 09:29:14+11"
--"2011-11-14 09:29:14"

How to specify the id of a sequence to prevent SQL Error: PGRES_FATAL_ERROR:ERROR: duplicate key value violates unique constraint “gps_histories_pkey”?
This happens when the maximum number of records in gps_histories is not in sync with the sequence id.

SELECT SETVAL('dfms_4000.gps_histories_id_seq', (SELECT MAX(id) FROM dfms_4000.gps_histories)+1)

Categories: postgis, postgres Tags: postgis, postgres

Postgres PostGIS CheatSheet v2

August 29th, 2008 rupert No comments

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

Categories: postgis, postgres Tags: postgis, postgres

Postgres Clients Update: Navicat and EMS

July 28th, 2008 rupert Comments off

Picture 4.png

Navicat already produced some lite (free) versions for Postgres both for Windows and Mac. Browse over the feature matrix to find out which version works for you. Note that all versions allow you to edit the record which I can’t do in pgadmin3.

Picture 3.png

A quick look at EMS on Windows (VMWare) is also a note worthy client. Though the MDI interface is a bit complex, one good feature is to able to see your spatial geometry as text and the corresponding SRID immediately…

Picture 1.png

Picture 2.png

Categories: osx, postgres Tags: leopard, osx, postgres

Newer Entries Older Entries

mobile and gis dev notes

Archive

Installing Postgres8.3,8.4,9.0, Postgis1.3.3,1.4.1,2.0, pgRouting on OSX (updated)

Migrating postgres/postgis data (updated)

Postgres PostGIS CheatSheet v2

Postgres PostGIS CheatSheet v2

Postgres Clients Update: Navicat and EMS

Recent Posts