Description

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

2D	3D	M

Spatial Standards Support

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