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:
|
1
2
3
4
5
6
7
8
|
$ 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:
|
1
2
3
4
5
6
7
|
$ 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:
|
1
2
3
4
5
6
7
|
$ 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:
|
1
2
3
4
5
|
$ 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:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
-------------------------------------------------------------------
-- 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:
|
1
2
3
4
5
6
7
8
|
$ 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
|
Then, when you run the Django management script again, it should succeed.