Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I am following a tutorial to install pgrouting. However, I didn't get the part when it says

Start pgAdmin and create a new database based on your PostGIS template

Where can I get this postgis template? I know I have installed PostGIS using homebrew. But I couldn't find the template. I am running macos now.

share|improve this question
1  
Which version of PostGIS are you using? –  R.K. Dec 6 '12 at 9:55

4 Answers 4

New Method With PostgreSQL 9.1

Thanks to the advice of R.K. below, I took a look at this tutorial and found that for PostgreSQL 9.1 all you need to do is add the extensions postgis and postgis_topology to an existing database using the pgAdmin context menus. To create a postgis template, I created a new database called template-postgis and then added these extensions. I then created my other database using this template. When using pg_dump I found the size of the export was much smaller, as it seems to just include these lines and not dump the extension functions:

CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS postgis_topology WITH SCHEMA topology;

Old Redundant Method:

I ended up using the .sql files here:

/Applications/Postgres.app/Contents/MacOS/share/contrib/postgis-2.0/postgis.sql
/Applications/Postgres.app/Contents/MacOS/share/contrib/postgis-2.0/spatial_ref_sys.sql
/Applications/Postgres.app/Contents/MacOS/share/contrib/postgis-2.0/topology.sql

Also, I got this error when I imported an existing PostGIS database into a new database made from this template:

ERROR:  type "spheroid" already exists

So I followed the instructions here and used ON_ERROR_ROLLBACK=on to set up the template, after creating a blank database called "template_postgis":

psql -U postgres -d template_postgis -1 -f /Applications/Postgres.app/Contents/MacOS/share/contrib/postgis-2.0/postgis.sql -v ON_ERROR_ROLLBACK=on
psql -U postgres -d template_postgis -1 -f /Applications/Postgres.app/Contents/MacOS/share/contrib/postgis-2.0/spatial_ref_sys.sql -v ON_ERROR_ROLLBACK=on
psql -U postgres -d template_postgis -1 -f /Applications/Postgres.app/Contents/MacOS/share/contrib/postgis-2.0/topology.sql -v ON_ERROR_ROLLBACK=on

And then imported my backup db, e.g.:

psql -U someuser -d somedb -1 -f somefile.sql -v ON_ERROR_ROLLBACK=on

Even Older Method:

I did this:

createdb -E UTF8 -T template0 template_postgis
createlang -d template_postgis plpgsql
psql --quiet -d template_postgis -f /Applications/Postgres.app/Contents/MacOS/share/extension/postgis--2.0.1.sql

The path to postgis--2.0.1.sql will be different depending on your setup.

share|improve this answer
    
To complete your answer (which works on my Mac): on a Mac, if you installed postgis from Kyngchaos ports, the postgis installation file is: /usr/local/pgsql/share/extension/postgis--2.0.1.sql –  Bruno von Paris Nov 19 '12 at 23:20

If you are running PostgreSQL 9.1+, you don't need to bother with the template database. Just create a database and then on the SQL view in pgAdmin, type and run

CREATE EXTENSION postgis;

That will spatially enable your database.

share|improve this answer
    
Can the template method still be used? Or, is there a way that to add this info to the "new database" dialog in pgAdminIII? –  monkut May 28 '13 at 4:14

in fact this is the foundation of postgis installation.

if you have postgis template you can create a new database from it as :

 createdb -T postgistemplate -O gis gisdb

if you not have it, you can do this with this way:

sudo su postgres
createdb postgistemplate
createlang plpgsql postgistemplate
psql -d postgistemplate -f /usr/share/postgresql-8.2-postgis/lwpostgis.sql
psql -d postgistemplate -f /usr/share/postgresql-8.2-postgis/spatial_ref_sys.sql

you can use bash scripts Debian/Ubuntu - create_template_postgis-debian.sh, here

i hope it helps you...

share|improve this answer
    
It is probably the case that the user postgres does not have permissions to use a terminal, in which case the sudo su postgres command will silently fail. To get past this you can precede all these commands with sudo -u postgres to run the command as the postgres user without switching to the postgres user. –  Ring May 5 at 19:52

As far as I remember there is some script available for the workshop to create those template databases. It's also available here: https://github.com/pgRouting/pgrouting-contrib/blob/master/scripts/create_templates.sh

Eventually you need to change a few things here and there, because it was written to work for Debian/Ubuntu. But you can just pick what you need.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.