[postgis-users] Wondering why I can't use the geometry type in a CREATE statement.

[Stephen Woodbridge]

Greg Militello wrote:
> I am just wondering why PostGIS does not create nee datatypes in a way that allows a CREATE statement to make them.  There is an example in the doc that looks like this:
> 
> CREATE TABLE parks (
>   park_id    INTEGER,
>   park_name  VARCHAR,
>   park_date  DATE,
>   park_type  VARCHAR
> );
> SELECT AddGeometryColumn('parks', 'park_geom', 128, 'MULTIPOLYGON', 2 );
> 
> Why isn't this possible?
> 
> CREATE TABLE parks (
>   park_id    INTEGER,
>   park_name  VARCHAR,
>   park_date  DATE,
>   park_type  VARCHAR,
>   parks   GEOMETRY(128, 'MULTIPOLYGON', 2)
> );
> Or something similar.   Having to create the column as an after effect makes it difficult to use PostGIS with database abstraction layers.  Most of the good DBALs allow the use of custom column types, however they do not usually support post create statement column generation (maybe with an exception here or there with ALTER TABLE commands).
> 
> 
> Specifically I am looking into extending doctrine (a PHP DBAL/ORM) to utilize spacial DBs.  

  CREATE TABLE parks (
    park_id    INTEGER,
    park_name  VARCHAR,
    park_date  DATE,
    park_type  VARCHAR,
    park_geom  geomtery,
    CONSTRAINT enforce_dims_park_geom CHECK (st_ndims(park_geom) = 2),
    CONSTRAINT enforce_geotype_park_geom CHECK (geometrytype(park_geom) 
= 'MULTIPOLYGON'::text OR the_geom IS NULL),
    CONSTRAINT enforce_srid_park_geom CHECK (st_srid(park_geom) = 4326)
  );

Should do the trick.

Later you can update the metadata tables with:

select probe_geometery_columns();

-Steve W