19

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.

  • 1
    Which version of PostGIS are you using? – R.K. Dec 6 '12 at 9:55
16

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.

  • 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
  • For the newest method, is 'SCHEMA' needed? What's it for? (Yours is the only example I've seen so far that do that.) – matt wilkie Apr 26 '18 at 22:41
14

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.

  • 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
  • you probably also want to add CREATE EXTENSION postgis_topology; – matt wilkie Sep 11 '18 at 21:07
  • 1
    @monkut in pgAdmin4 this is available via right-click on 'Extensions' in the navigation tree – matt wilkie Sep 11 '18 at 21:09
3

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

  • 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 '14 at 19:52
2

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.

2
#!/bin/sh
instance_name=$1
psql << EOF
create database $instance_name;
\connect $instance_name
-- Enable PostGIS (includes raster)
CREATE EXTENSION postgis;
-- Enable Topology
CREATE EXTENSION postgis_topology;
-- fuzzy matching needed for Tiger
CREATE EXTENSION fuzzystrmatch;
-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;
EOF

I use this bash script to execute creation of new database with postgis enabled and name given on stdin.

Postgres 9.3

PostGIS 2.1.3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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