OpenGIS metadata¶

Typmods¶

PostGIS 2.0+ also adds support for type modifiers, or typmods, on geometry objects. Typmods provide a method of specifying additional information for a column definition. For example:

CREATE TABLE test (
  description VARCHAR(255)
)

The typmod in this case is the number 255, indicating the maximum length of values in the description field. By applying a typmod to a geometry column, you can specify the geometry type, dimensionality and spatial reference identifier. To create a new spatial table geotbl with a geometry column of type point, dimensionality Z, and a spatial reference identifier of 4326, the following typmod definition is required:

CREATE TABLE geotbl (
  geom GEOMETRY(PointZ, 4326)
)

Updating spatial tables¶

Updating spatial tables in previous versions of PostGIS, such as changing the spatial reference or geometry type, was a multi-step process—dropping constraints, updating the table, updating the geometry_columns table and re-applying the constraints. With version 2.0+, this can be accomplished with a single ALTER TABLE command. The following example will simultaneously alter the dimensionality and projection of the data as well as the associated table constraints:

ALTER TABLE geotabl
  ALTER COLUMN geom
  SET DATA TYPE geometry(Point,26910)
  USING ST_Transform(ST_Force_2D(geom, 26910))

The geometry_columns view is updated automatically to reflect the changes.

Manually registering geometry columns¶

There may still be occasions when you are required to manually register a geometry column, such as when working with SQL views or bulk data inserts. With PostGIS 2.0+ if the definition of a geometry column is typmod-based, the view or table creation process will automatically register the column correctly, so no further action is required. However, if a geometry column in an existing view is constraint-based, it will not be registered correctly.

For example, if a view is defined as follows:

CREATE VIEW public.vw_mytable AS
  SELECT gid, ST_Transform(geom, 3395) AS geom, f_name
  FROM public.mytable;

Querying the geometry_columns view will confirm the incorrect registration of the geometry column:

SELECT f_table_name, f_geometry_column, srid, type
        FROM geometry_columns
  WHERE f_table_name = 'public.vw_mytable';

f_table_name | f_geometry_column | srid | type
-------------+-------------------+------+----------
 vw_mytable  | geom              | 0    | GEOMETRY
-------------+-------------------+------+----------

To register the geometry column with PostGIS 2.0+, you must convert, or cast, the geometry to be typmod-based:

DROP VIEW public.vw_mytable;
CREATE VIEW public.vw_mytable AS
        SELECT gid, ST_Transform(geom,3395)::geometry(Geometry, 3395) AS geom, f_name
        FROM public.mytable;

If the geometry type is known to be a 2D polygon, the view could be redefined as:

DROP VIEW public.vw_mytable;
CREATE VIEW public.vw_mytable AS
  SELECT gid, ST_Transform(geom,3395)::geometry(Polygon, 3395) AS geom, f_name
  FROM public.mytable;

Querying the geometry_columns view again will report the update.

SELECT f_table_name, f_geometry_column, srid, type
  FROM geometry_columns
  WHERE f_table_name = 'public.vw_mytable';

f_table_name | f_geometry_column | srid  | type
-------------+-------------------+-------+----------
 vw_mytable  | geom              | 3395  | GEOMETRY
-------------+-------------------+-------+----------

The geometry column is now correctly registered.

If you wish to add a geometry column to an existing table, the function AddGeometryColumn() now uses a typmod by default to define the column, rather than constraints.

AddGeometryColumn() accepts the following parameters:

AddGeometryColumn(varchar table_name, varchar column_name, integer srid, varchar type, integer dimension, boolean use_typmod=true);

SELECT AddGeometryColumn('my_table', 'geom', 4326, 'POINT', 2);

To add a point using constraint-based behavior, set the use_typemod value to be false.

SELECT AddGeometryColumn('my_table', 'geom', 4326, 'POINT', 2, false);