Creating the spatial table from scratch…

[postgres@rupert-linux ~]$ su - postgres
[postgres@rupert-linux ~]$ createdb _E UTF-8 template_postgis
CREATE DATABASE
[postgres@rupert-linux ~]$ createlang plpgsql template_postgis
[postgres@rupert-linux ~]$ psql -f /usr/local/pgsql/share/lwpostgis.sql -d template_postgis
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
COMMIT
postgres@rupert-linux ~]$ psql -f /usr/local/pgsql/share/spatial_ref_sys.sql -d template_postgis
[postgres@rupert-linux ~]$ psql

Alternatively you could also create your spatial table from a template…

     14 postgres=# CREATE DATABASE gistest TEMPLATE=template_postgis;
     15 CREATE DATABASE

I have also added the routing functions using routing.sql and routing_postgis.sql

[postgres@rupert-linux ~]$ psql -f /usr/local/pgsql/share/contrib/routing.sql -d template_postgis
[postgres@rupert-linux ~]$ psql -f /usr/local/pgsql/share/contrib/routing_postgis.sql -d template_postgis

FYI…

The spatial_ref_sys table contains the ESPG codes and projections. Currently it has 3162 records.

gistest=# \d spatial_ref_sys;
 srid      | INTEGER                 | NOT NULL
 auth_name | CHARACTER VARYING(256)  |
 auth_srid | INTEGER                 |
 srtext    | CHARACTER VARYING(2048) |
 proj4text | CHARACTER VARYING(2048) |

The geometry_columns contains the geometry type of the table you just created..

gistest=# \d geometry_columns;
 f_table_catalog   | CHARACTER VARYING(256) | NOT NULL
 f_table_schema    | CHARACTER VARYING(256) | NOT NULL
 f_table_name      | CHARACTER VARYING(256) | NOT NULL
 f_geometry_column | CHARACTER VARYING(256) | NOT NULL
 coord_dimension   | INTEGER                | NOT NULL
 srid              | INTEGER                | NOT NULL
 TYPE              | CHARACTER VARYING(30)  | NOT NULL