Name

AddGeometryColumn — Adds a spatial column to an existing feature table.

Synopsis

varchar AddGeometryColumn(varchar catalogue, varchar schema, varchar table, varchar column, integer srid, varchar type, integer coordinate_dimension);

varchar AddGeometryColumn(varchar schema, varchar table, varchar column, integer srid, varchar type, integer coordinate_dimension);

varchar AddGeometryColumn(varchar table, varchar column, integer srid, varchar type, integer coordinate_dimension);

[Note]

Srid could also be varchar. In that case, in H2 call function AddGeometryColumnEx

Description

The AddGeometryColumn method will

  • add a geometry table to the feaure table.

  • ensure that an entry for the SRID exists in the SPATIAL_REFERENCE_SYSTEMS table.

  • add an entry to the GEOMETRY_COLUMNS table that stores the SRID for the geometry column.

  • add a Spatial Reference Check Constraint to the feature table.

The catalog, schema and table define the name of the table of attributes (catalog now unused).

The SRID must be an integer value that exists in the SPATIAL_REF_SYS table.

The type must be an uppercase string corresponding to the geometry type (eg, MULTIPOLYGON,LINESTRING).

The coordinate_dimension must be an integer (2 for XY coordinates, 3 for XYZ or 4 for XYZM).

Coordinate Dimensions
2D3DM
Spatial Standards Support
OGC SFS for SQL. 1.1 (1999)OGC SFS for SQL. 1.1.0 (2005)OGC SFS for SQL. 1.2.0 (2006)SQL-MM Part 3
2.3.86.2.7--

Examples

PostgreSQL
create table test (id serial PRIMARY KEY);

--Shows Table test properties: 
\d test

                         Table "public.test"                                                 
 Column |  Type   |                     Modifiers                                            
--------+---------+---------------------------------------------------                       
 id     | integer | not null default nextval('test_id_seq'::regclass)                        
Indexes:                                                                                     
    "test_pkey" PRIMARY KEY, btree (id)                                                      

-- Adds a geometry column to the table test (3 versions)
-- <catalogue>, <schema>, <table>, <column>, <srid>, <type>, <dim>
SELECT AddGeometryColumn ('','public','test','geom_point',4326,'POINT',2);

-- <schema>, <table>, <column>, <srid>, <type>, <dim>
SELECT AddGeometryColumn ('public','test','geom_polygon',25830,'MULTIPOLYGON',4);

-- <table>, <column>, <srid>, <type>, <dim>
SELECT AddGeometryColumn ('test','geom_line',-1,'LINESTRING',3);

--Shows Table test properties (Coordinate Dimension, Geometry Type and SRID Constraints have been added) 
\d test

                            Table "public.test"
    Column    |  Type   |                     Modifiers
--------------+---------+---------------------------------------------------
 id           | integer | not null default nextval('test_id_seq'::regclass)
 geom_point   | bytea   |
 geom_polygon | bytea   |
 geom_line    | bytea   |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "enforce_dims_geom_line" CHECK (st_coorddim(geom_line) = 3)
    "enforce_dims_geom_point" CHECK (st_coorddim(geom_point) = 2)
    "enforce_dims_geom_polygon" CHECK (st_coorddim(geom_polygon) = 4)
    "enforce_geotype_geom_line" CHECK (geometrytype(geom_line)::text = 'LINESTRING'::text OR geom_line IS NULL)
    "enforce_geotype_geom_point" CHECK (geometrytype(geom_point)::text = 'POINT'::text OR geom_point IS NULL)
    "enforce_geotype_geom_polygon" CHECK (geometrytype(geom_polygon)::text = 'MULTIPOLYGON'::text OR geom_polygon IS NULL)
    "enforce_srid_geom_line" CHECK (st_srid(geom_line) = (-1))
    "enforce_srid_geom_point" CHECK (st_srid(geom_point) = 4326)
    "enforce_srid_geom_polygon" CHECK (st_srid(geom_polygon) = 25830)

--Displays data from the metadata table GEOMETRY_COLUMNS
SELECT * FROM geometry_columns;

 f_table_catalog | f_table_schema | f_table_name | f_geometry_column | coord_dimension | srid  |     type
-----------------+----------------+--------------+-------------------+-----------------+-------+--------------
                 | public         | test         | geom_point        |               2 |  4326 | POINT
                 | public         | test         | geom_polygon      |               4 | 25830 | MULTIPOLYGON
                 | public         | test         | geom_line         |               3 |    -1 | LINESTRING
(3 rows)
H2
create table test (id serial PRIMARY KEY);

-- Adds a geometry column to the table test (3 versions)
--(1) <catalogue>, <schema>, <table>, <column>, <srid>, <type>, <dim>
SELECT AddGeometryColumn ('','PUBLIC','TEST','geom_point',4326,'POINT',2);

--(2) <schema>, <table>, <column>, <srid>, <type>, <dim>
SELECT AddGeometryColumn ('PUBLIC','TEST','geom_polygon',25830,'MULTIPOLYGON',4);

--(3) <table>, <column>, <srid>, <type>, <dim>
SELECT AddGeometryColumn ('TEST','geom_line',-1,'LINESTRING',3);


--Displays data from the metadata table GEOMETRY_COLUMNS
SELECT * FROM geometry_columns;
F_TABLE_CATALOG  F_TABLE_SCHEMA  F_TABLE_NAME  F_GEOMETRY_COLUMN  COORD_DIMENSION  SRID  TYPE  
                 PUBLIC          TEST          geom_point         2                4326  POINT
                 PUBLIC          TEST          geom_polygon       4                25830 MULTIPOLYGON
                 PUBLIC          TEST          geom_line          3                -1    LINESTRING
(3 rows, 3 ms)

--Displays the fields of the table TEST 
SHOW COLUMNS FROM test;

FIELD        TYPE                  NULL KEY DEFAULT  
ID           BIGINT(19)            NO   PRI NEXT VALUE FOR PUBLIC.SYSTEM_SEQUENCE_877207D5_53E8_43BC_AAF8_3D14ECD94D4F)
geom_point   VARBINARY(2147483647) YES      NULL
geom_polygon VARBINARY(2147483647) YES      NULL
geom_line    VARBINARY(2147483647) YES      NULL
(4 rows, 17 ms)

-- Displays the contraints of the table TEST
SELECT CONSTRAINT_TYPE ,  CONSTRAINT_NAME, CHECK_EXPRESSION    FROM INFORMATION_SCHEMA.CONSTRAINTS WHERE TABLE_NAME='TEST';

CONSTRAINT_TYPE  	CONSTRAINT_NAME  			CHECK_EXPRESSION  
CHECK		ENFORCE_GEOTYPE_PUBLIC_TEST_geom_polygon (("geom_polygon" IS NULL) OR (GEOMETRYTYPE("geom_polygon") = 'MULTIPOLYGON'))
PRIMARY KEY	CONSTRAINT_2				null
CHECK		ENFORCE_GEOTYPE_PUBLIC_TEST_geom_line	(("geom_line" IS NULL) OR (GEOMETRYTYPE("geom_line") = 'LINESTRING'))
CHECK		ENFORCE_DIMS_PUBLIC_TEST_geom_polygon	(ST_COORDDIM("geom_polygon") = 4)
CHECK		ENFORCE_GEOTYPE_PUBLIC_TEST_geom_point	(("geom_point" IS NULL) OR (GEOMETRYTYPE("geom_point") = 'POINT'))
CHECK		ENFORCE_DIMS_PUBLIC_TEST_geom_point	(ST_COORDDIM("geom_point") = 2)
CHECK		ENFORCE_DIMS_PUBLIC_TEST_geom_line	(ST_COORDDIM("geom_line") = 3)

Related functions

DropGeometryColumn,DropGeometryTable,Probe_Geometry_Columns