Use pgRouting without executing sql files in PostgreSQL every time

Question

I have installed pgRouting and whenever I create a new database in PostgreSQL, and in order to use the pgRouting function, I have to execute 3 sql files of pgRouting,

routing_core
routing_core_wrappers
routing_core_wrappers

Can I use the pgRouting function just after I create a database without executing its sql files every time？ How？

I am using PostgreSQL 8.4 under Windows 8.1 x64, thank you.

UPDATE#1

I have installed PostgreSQL 9.2 and create a database named "test", loading a shapefile in it, but as I execute the sql line below, the error message shows that the python can't find the 9.2 server,

import sys
import psycopg2

conn = psycopg2.connect("dbname = 'test' user = 'postgres' host = 'localhost' password = 'ntubse40'")
cur = conn.cursor()

query = """
    ALTER TABLE tc_2000_w_area ADD COLUMN source integer;
    ALTER TABLE tc_2000_w_area ADD COLUMN target integer;
    SELECT assign_vertex_id('tc_2000_w_area', 0.0001, 'the_geom', 'gid')
;"""
cur.execute(query)

#print out table and check its change
cur.copy_to(sys.stdout, 'tc_2000_w_area', sep = '|')

cur.close()

>>> 

Traceback (most recent call last):
  File "C:/Users/Heinz/Desktop/python_test/any_test.py", line 4, in <module>
    conn = psycopg2.connect("dbname = 'test' user = 'postgres' host = 'localhost' password = 'ntubse40'")
  File "C:\Python27\lib\site-packages\psycopg2\__init__.py", line 164, in connect
    conn = _connect(dsn, connection_factory=connection_factory, async=async)
OperationalError: 嚴重錯誤:  資料庫"test"不存在

UPDATE#2 I finally solve the change server problem (just a stupid one, thanks Craig Ringer!), and I try the following code, successfully connect to 9.2 server,

import sys
import psycopg2

conn = psycopg2.connect("port = '5433' dbname = 'test' user = 'postgres' host = 'localhost' password = 'xxxx'")
cur = conn.cursor()

query = """
    ALTER TABLE tc_2000_w_area ADD COLUMN source integer;
    ALTER TABLE tc_2000_w_area ADD COLUMN target integer;
    SELECT assign_vertex_id('tc_2000_w_area', 0.0001, 'the_geom', 'gid')
;"""
cur.execute(query)

#print out table and check its change
cur.copy_to(sys.stdout, 'tc_2000_w_area', sep = '|')

cur.close()

If you were using a currrent PostgreSQL version you could just CREATE EXTENSION pgrouting; instead. Consider upgrading. — Craig Ringer
@CraigRinger Do you mean I can just type "CREATE EXTENSION pgrouting;" in the sql window? Because I tried, but still can't use features of pgRouting. — Heinz
You're using PostgreSQL 8.4. The command would've just shown an error. Extensions were added in 9.0. So upgrade, or live with what you're doing already. — Craig Ringer
Multiple servers on the same computer listen on different ports. So port=5433 for example. See the psycopg2 and PostgreSQL manuals. — Craig Ringer
You could also create those tables and functions in the template1 database. Then they will be available in every database created after that. — a_horse_with_no_name

Stephen Woodbridge Stephen Woodbridge · Accepted Answer · 2014-04-18T00:32:39

There are two ways to do this as have been mentioned above:

for postgres 9.0+ you can use create extension like:

createdb mynewdb

psql -c "create extension postgis" mynewdb

psql -c "create extension pgrouting" mynewdb
for postgres 8.4 create a template database

createdb template_pgrouting

psql -c "create language plpgsql" template_pgrouting

psql -f /path/to/postgis.sql template_pgrouting

psql -f /path/to/pgrouting.sql template_pgrouting
create a new database using the template

createdb -T template_pgrouting mynewdb

createdb -T template_pgrouting anotherdb

In step to I will leave the exact files and paths as an exercise for the reader.

Use pgRouting without executing sql files in PostgreSQL every time

1 Answers