[postgis-users] SRS Table Upgrade documentation

[Mark Cave-Ayland]

Ben Madin wrote:

> G'day all,
> 
> having just (I think) upgraded PostGIS from 1.3.6 to  1.4.0 (still in 
> PostgreSQL 8.3.7) I moved on in the documentation to :
> 
> spatial_ref_sys table is restore from the dump, to ensure your custom 
> additions are kept, but the distributed one might contain modification 
> so you should backup your entries, drop the table and source the new 
> one. If you did make additions we assume you know how to backup them 
> before upgrading the table. Replace of it with the new one is done like 
> this:
> 
> $ psql newdb
> newdb=> drop spatial_ref_sys;
> DROP
> newdb=> \i spatial_ref_sys.sql
> 
> 
> 
> but got the following message :
> 
> australia=# \i /usr/local/pgsql/share/contrib/spatial_ref_sys.sql
> BEGIN
> psql:/usr/local/pgsql/share/contrib/spatial_ref_sys.sql:5: ERROR: 
>  relation "spatial_ref_sys" does not exist
> 
> Lines 1 to 5 of spatial_ref_sys.sql are :
> 
> BEGIN;
> ---
> --- EPSG 3819 : HD1909
> ---
> INSERT INTO "spatial_ref_sys" 
> ("srid","auth_name","auth_srid","srtext","proj4text") VALUES 
> (3819,'EPSG',3819,'GEOGCS["HD1909",DATUM["Hungarian_Datum_1909",SPHEROID["Bessel 
> 1841",6377397.155,299.1528128,AUTHORITY["EPSG","7004"]],TOWGS84[595.48,121.69,515.35,4.115,-2.9383,0.853,-3.408],AUTHORITY["EPSG","1024"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","3819"]]','+proj=longlat 
> +ellps=bessel +towgs84=595.48,121.69,515.35,4.115,-2.9383,0.853,-3.408 
> +no_defs ');
> 
> so there is no table definition in the spatial_ref_sys.sql file. 
> 
> I would wonder if maybe truncate spatial_ref_sys; might be a better 
> option...
> 
> In the meantime, does anyone have the table definition for 
> spatial_ref_sys.sql...?
> 
> cheers
> 
> Ben

Hmmm yeah. The definition for spatial_ref_sys can be found in 
/usr/local/pgsql/share/contrib/postgis.sql (which is the file used for a 
new installation), while the table content is in spatial_ref_sys.sql. So 
the short answer is to copy/paste the spatial_ref_sys table definition 
from postgis.sql into psql/pgAdmin and everything should work fine.

I'm not necessarily sure that this is a bug, however it doesn't seem to 
be the most intuitive of behaviours...


ATB,

Mark.

-- 
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs