[postgis-users] Initial SRS and geometry columms table creation

[Gregory S. Williamson]

Ivan --

> I am just starting out with PostgreSQL and PostGIS.  It appears that it is 
> necessary to create the SRS and geometry_columns tables before importing 
> ESRI shapefile data.  Is this correct?

Yes -- this is done by the sql script (lwpostgis.sql in postGIS 1.x) that creates the function interfaces that postGIS uses.

>                                         One thing that seems odd though is 
> that in order to create the geometry_columns table one would have to know 
> what tables are going to be produced by importing GIS data into the database 
> and the resulting name of the geometry_column.

The table is created when the postGIS functions are created, but it is not populated until later. Basically, you create a table, then add a geometry column to the table with AddGeometryColumn and the new reference gets added to the geometry_columns table itself.

> I am also wondering if it is necessary to create a spatial table first for 
> each shapefile that one is adding to the database or if one may merely use 
> the loader to create the feature table then modify it by adding a geometry 
> column to it.

I think the shp2pgsql program can be told to create a new table:

USAGE: shp2pgsql [<options>] <shapefile name> <table name> <database>

OPTIONS:
  -s <srid>  Set the SRID field. If not specified it defaults to -1.

  (-d|a|c) These are mutually exclusive options:
      -d  Drops the table , then recreates it and populates
          it with current shape file data.
      -a  Appends shape file into current table, must be
          exactly the same table schema.
      -c  Creates a new table and populates it, this is the
          default if you do not specify any options.

  -D  Use postgresql dump format (defaults to sql insert
      statments.

  -k  Keep postgresql identifiers case.


> Lastly, I have a spatial reference system that is not defined in EPSG.  It 
> is a system for the entire state of California in NAD83 defined thusly:
<...>
Not having done this myself I will let someone with more experience guide you. You would want to create the new SRID entry before loading data, or else load the data as SRID -1 and then create the new srid and run a postGIS function to recast it. Of the two I'd opt for the first.

HTH.

Greg Williamson
DBA
GlobeXplorer LLC