December 2012 - Page 3 of 3

Let’s say you have a little accident while messing with the schema of your PostGIS-enabled database, and the spatial_ref_sys and geometry_columns tables get wiped out. When you go to install a GeoDjango-enabled app such as the example “world” app, Python will yell at you, and you will be very sad:

$ python manage.py syncdb
Failed to install index for world.WorldBorder model: relation "spatial_ref_sys" does not exist
LINE 1: SELECT SRID         FROM spatial_ref_sys WHERE SRID = new_sr...
                                 ^
QUERY:  SELECT SRID         FROM spatial_ref_sys WHERE SRID = new_srid
CONTEXT:  PL/pgSQL function "addgeometrycolumn" line 75 at SQL statement
SQL statement "SELECT AddGeometryColumn('','',$1,$2,$3,$4,$5)"
PL/pgSQL function "addgeometrycolumn" line 5 at SQL statement

$ python manage.py syncdb

Failed to install index for world.WorldBorder model: relation "spatial_ref_sys" does not exist

LINE 1: SELECT SRID FROM spatial_ref_sys WHERE SRID = new_sr...

QUERY: SELECT SRID FROM spatial_ref_sys WHERE SRID = new_srid

CONTEXT: PL/pgSQL function "addgeometrycolumn" line 75 at SQL statement

SQL statement "SELECT AddGeometryColumn('','',$1,$2,$3,$4,$5)"

PL/pgSQL function "addgeometrycolumn" line 5 at SQL statement

It fails, because you accidentally dropped some very important PostGIS tables from the database. So you google a bit and find this: http://postgis.refractions.net/documentation/manual-1.5/ch02.html#id418654 which tells you what you need to do to set up a clean PostGIS installation.

So you dig around in the shared host, until you find where the PostGIS files are, and in particular spatial_ref_sys.sql:

$ find . -name *.sql
./share/contrib/postgis-1.5/postgis_upgrade_14_to_15.sql
./share/contrib/postgis-1.5/postgis_upgrade_13_to_15.sql
./share/contrib/postgis-1.5/postgis_upgrade_15_minor.sql
./share/contrib/postgis-1.5/spatial_ref_sys.sql
./share/contrib/postgis-1.5/uninstall_postgis.sql
./share/contrib/postgis-1.5/postgis.sql

$ find . -name *.sql

./share/contrib/postgis-1.5/postgis_upgrade_14_to_15.sql

./share/contrib/postgis-1.5/postgis_upgrade_13_to_15.sql

./share/contrib/postgis-1.5/postgis_upgrade_15_minor.sql

./share/contrib/postgis-1.5/spatial_ref_sys.sql

./share/contrib/postgis-1.5/uninstall_postgis.sql

./share/contrib/postgis-1.5/postgis.sql

So you try to run spatial_ref_sys.sql, and it fails, and you are sad:

$ psql -h localhost -U user -d database -f spatial_ref_sys.sql
[...]
psql:spatial_ref_sys.sql:5: ERROR:  relation "spatial_ref_sys" does not exist
LINE 1: INSERT INTO "spatial_ref_sys" ("srid","auth_name","auth_srid...
                    ^
psql:spatial_ref_sys.sql:9: ERROR:  current transaction is aborted, commands ignored until end of transaction block
[...]

$ psql -h localhost -U user -d database -f spatial_ref_sys.sql

[...]

psql:spatial_ref_sys.sql:5: ERROR: relation "spatial_ref_sys" does not exist

LINE 1: INSERT INTO "spatial_ref_sys" ("srid","auth_name","auth_srid...

psql:spatial_ref_sys.sql:9: ERROR: current transaction is aborted, commands ignored until end of transaction block

[...]

Normally to create the missing table, you would run the postgis.sql script, and it would create these tables alongside the various GIS stored procedures. On a shared host, this probably won’t be possible, because you won’t have the permissions:

$ psql -h localhost -U user -d database -f postgis.sql
[...]
psql:postgis.sql:59: ERROR:  permission denied for language c
psql:postgis.sql:65: ERROR:  current transaction is aborted, commands ignored until end of transaction block
[...]

$ psql -h localhost -U user -d database -f postgis.sql

[...]

psql:postgis.sql:59: ERROR: permission denied for language c

psql:postgis.sql:65: ERROR: current transaction is aborted, commands ignored until end of transaction block

[...]

If you dig through postgis.sql for the proper CREATE TABLE statements, you’ll see something like:

-------------------------------------------------------------------
-- SPATIAL_REF_SYS
-------------------------------------------------------------------
CREATE TABLE spatial_ref_sys (
         srid integer not null primary key,
         auth_name varchar(256),
         auth_srid integer,
         srtext varchar(2048),
         proj4text varchar(2048)
);

-------------------------------------------------------------------
-- GEOMETRY_COLUMNS
-------------------------------------------------------------------
CREATE TABLE geometry_columns (
        f_table_catalog varchar(256) not null,
        f_table_schema varchar(256) not null,
        f_table_name varchar(256) not null,
        f_geometry_column varchar(256) not null,
        coord_dimension integer not null,
        srid integer not null,
        type varchar(30) not null,
        CONSTRAINT geometry_columns_pk primary key (
                f_table_catalog,
                f_table_schema,
                f_table_name,
                f_geometry_column )
) WITH OIDS;

-------------------------------------------------------------------

-- SPATIAL_REF_SYS

-------------------------------------------------------------------

CREATE TABLE spatial_ref_sys (

srid integer not null primary key,

auth_name varchar(256),

auth_srid integer,

srtext varchar(2048),

proj4text varchar(2048)

);

-------------------------------------------------------------------

-- GEOMETRY_COLUMNS

-------------------------------------------------------------------

CREATE TABLE geometry_columns (

f_table_catalog varchar(256) not null,

f_table_schema varchar(256) not null,

f_table_name varchar(256) not null,

f_geometry_column varchar(256) not null,

coord_dimension integer not null,

srid integer not null,

type varchar(30) not null,

CONSTRAINT geometry_columns_pk primary key (

f_table_catalog,

f_table_schema,

f_table_name,

f_geometry_column )

) WITH OIDS;

You can pop these into a PostgreSQL admin shell and recreate these tables. Then you just have to rerun the spatial_ref_sys.sql file, and you should see:

$ psql -h localhost -U user -d database -f spatial_ref_sys.sql
BEGIN
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1

$ psql -h localhost -U user -d database -f spatial_ref_sys.sql

BEGIN

INSERT 0 1

Then, when you run the Django management script again, it should succeed.

Month: December 2012

H0H 0H0

spatial_ref_sys and geometry_columns

Eliminate Bash History