PostGIS and pgRouting template

PostGIS and pgRouting template

Posted by Daniel on 7 February 2011 | 0 Comments


Tags: , , ,

When creating a new database in PostgreSQL using the standard template, PostGIS and pgRouting functions and tables are missing of course. You can add these functions and tables one by one, but doing this every time for every single database is annoying work.

The solution: make some template(s) containing funtions an tables from PostGIS and pgRouting and create a new database with them.

Template for PostGIS

First we create a template for PostGIS. Thanks to Shoaib from Nomad Labs!

createdb -U postgres -E UTF8 -T template0 template_postgis
createlang -U postgres -d template_postgis plpgsql

psql --quiet -U postgres -d template_postgis -f [path/to/postgresql]/contrib/postgis.sql
psql --quiet -U postgres -d template_postgis -f [path/to/postgresql]/contrib/spatial_ref_sys.sql
psql --quiet -U postgres -d template_postgis -f [path/to/postgresql]/contrib/postgis_comments.sql

psql -U postgres -d template_postgis -c "GRANT ALL ON geometry_columns TO PUBLIC;"
psql -U postgres -d template_postgis -c "GRANT ALL ON geography_columns TO PUBLIC;"
psql -U postgres -d template_postgis -c "GRANT ALL ON spatial_ref_sys TO PUBLIC;"

psql -U postgres -d template_postgis -c "VACUUM FULL;"
psql -U postgres -d template_postgis -c "VACUUM FREEZE;"

psql -U postgres -d postgres -c "UPDATE pg_database SET datistemplate='true' WHERE datname='template_postgis';"
psql -U postgres -d postgres -c "UPDATE pg_database SET datallowconn='false' WHERE datname='template_postgis';"

Template for pgRouting

Taking the PostGIS template we made before, we create a template containing pgRouting functions in the same way:

sudo -u postgres createdb -E UTF8 -T template_postgis template_routing
sudo -u postgres psql --quiet -d template_routing -f $ROUTING_SQL_PATH/routing_core.sql
sudo -u postgres psql --quiet -d template_routing -f $ROUTING_SQL_PATH/routing_core_wrappers.sql
sudo -u postgres psql --quiet -d template_routing -f $ROUTING_SQL_PATH/routing_topology.sql
sudo -u postgres psql --quiet -d template_routing -f $ROUTING_SQL_PATH/routing_tsp.sql
sudo -u postgres psql --quiet -d template_routing -f $ROUTING_SQL_PATH/routing_tsp_wrappers.sql
sudo -u postgres psql --quiet -d template_routing -f $ROUTING_SQL_PATH/routing_dd.sql
sudo -u postgres psql --quiet -d template_routing -f $ROUTING_SQL_PATH/routing_dd_wrappers.sql
sudo -u postgres psql --quiet -d template_routing -f $ROUTING_SQL_PATH/routing_darp.sql
sudo -u postgres psql --quiet -d template_routing -f $ROUTING_SQL_PATH/matching.sql
sudo -u postgres psql -d template_routing -c "VACUUM FULL;"
sudo -u postgres psql -d template_routing -c "VACUUM FREEZE;"
sudo -u postgres psql -d postgres -c "UPDATE pg_database SET datistemplate='true' WHERE datname='template_routing';"
sudo -u postgres psql -d postgres -c "UPDATE pg_database SET datallowconn='false' WHERE datname='template_routing';"

createdb -U postgres -E UTF8 -T template_postgis template_routing

psql -U postgres --quiet -d template_routing -f /usr/share/pgrouting/routing_core.sql
psql -U postgres --quiet -d template_routing -f /usr/share/pgrouting/routing_core_wrappers.sql
psql -U postgres --quiet -d template_routing -f /usr/share/pgrouting/routing_topology.sql
psql -U postgres --quiet -d template_routing -f /usr/share/pgrouting/routing_tsp.sql
psql -U postgres --quiet -d template_routing -f /usr/share/pgrouting/routing_tsp_wrappers.sql
psql -U postgres --quiet -d template_routing -f /usr/share/pgrouting/routing_dd.sql
psql -U postgres --quiet -d template_routing -f /usr/share/pgrouting/routing_dd_wrappers.sql

psql -U postgres -d template_routing -c "VACUUM FULL;"
psql -U postgres -d template_routing -c "VACUUM FREEZE;"

psql -U postgres -d postgres -c "UPDATE pg_database SET datistemplate='true' WHERE datname='template_routing';"
psql -U postgres -d postgres -c "UPDATE pg_database SET datallowconn='false' WHERE datname='template_routing';"

Create pgRouting database

Now we can create a pgRouting database like this and all required functions are already there:

createdb -U postgres -E UTF8 -T template_routing my_routing_db