0

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()
Heinz
  • 2,415
  • 6
  • 26
  • 34
  • 1
    If you were using a currrent PostgreSQL version you could just `CREATE EXTENSION pgrouting;` instead. Consider upgrading. – Craig Ringer Apr 17 '14 at 02:16
  • 1
    @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 Apr 17 '14 at 03:48
  • 1
    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 Apr 17 '14 at 04:00
  • Re update, I'd say you're still actually connected to 8.4 (if you have both installed), not 9.2. Or you created the DB on 8.4 then connected to 9.2. Make sure you're using the correct port. – Craig Ringer Apr 17 '14 at 04:29
  • If I create the DB on 8.4 then I still can't use "CREATE EXTENSION pgrouting;", right? Can psycopg2 choose the server? – Heinz Apr 17 '14 at 04:40
  • Yes, psycopg2 can choose the server. See the documentation for connection strings. – Craig Ringer Apr 17 '14 at 05:14
  • No, lets not; open ended followup on questions that aren't likely to be informative to others in future aren't really why I'm here. Wish SO would stop showing that prompt. (Wont be responding further on this q). – Craig Ringer Apr 17 '14 at 06:55
  • OK, sorry for bothering you, but I can't find the way to change servers in the documentation. I tried to use 'connection', 'server' as keywords, but can't find. – Heinz Apr 17 '14 at 07:00
  • 1
    Multiple servers on the same computer listen on *different ports*. So `port=5433` for example. See the psycopg2 and PostgreSQL manuals. – Craig Ringer Apr 17 '14 at 07:02
  • 3
    You could also create those tables and functions in the `template1` database. Then they will be available in every database created after that. –  Apr 17 '14 at 08:06
  • @Craig Ringer I have edited my post! – Heinz Apr 17 '14 at 08:38

1 Answers1

0

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

  1. for postgres 9.0+ you can use create extension like:

    createdb mynewdb

    psql -c "create extension postgis" mynewdb

    psql -c "create extension pgrouting" mynewdb

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

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

Stephen Woodbridge
  • 1,100
  • 1
  • 8
  • 16