problems setting up postgis and importing data

Question

Using Postgres 9.2.3.1 on a new Mac. I installed Postgres from the dmg file that I downloaded from the Postgres site. I then installed PostGIS using stackbuilder. This all worked flawlessly so far as I can tell.

I have tweaked the pg_hba.conf file to trust connections from local and localhost and created a database "db". Then I tried::

Mac-mini:pg postgres$ bin/createlang plpgsql db
createlang: language "plpgsql" is already installed in database "db"

so it would appear that postgres knows all about postgis.

When I tried:

Mac-mini:pg postgres$ bin/shp2pgsql -c ~rful011/tmp/weed_boundaries weed_plot_boundaries | psql db
Shapefile type: Arc
Postgis type: MULTILINESTRING[2]
SET
SET
BEGIN
NOTICE:  CREATE TABLE will create implicit sequence "weed_plot_boundaries_gid_seq" for serial column "weed_plot_boundaries.gid"
CREATE TABLE
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "weed_plot_boundaries_pkey" for table "weed_plot_boundaries"
ALTER TABLE
ERROR:  function addgeometrycolumn(unknown, unknown, unknown, unknown, unknown, integer) does not exist
LINE 1: SELECT AddGeometryColumn('','weed_plot_boundaries','geom','0...
               ^

I must have missed something but what?

Thanks, Russell

Answer 1

Creating plpgsql is usually not enough. You also have to create all spatial functions. In Ubuntu, this is how it's done:

createdb postgis_template
createlang plpgsql postgis_template
psql -d postgis_template -f /usr/share/postgresql/8.4/contrib/postgis.sql
psql -d postgis_template -f /usr/share/postgresql/8.4/contrib/spatial_ref_sys.sql