PostGIS
Name
Populate_Geometry_Columns — Ensures geometry columns are defined with type modifiers or have appropriate spatial constraints.
Synopsis
text +`*`+Populate_Geometry_Columns*(`boolean
`use_typmod=true`)`;
int +`*`+Populate_Geometry_Columns*(`oid `relation_oid,
boolean use_typmod=true`)`;
Description
Ensures geometry columns have appropriate type modifiers or spatial
constraints to ensure they are registered correctly in the
geometry_columns view. By default will convert all geometry columns
with no type modifier to ones with type modifiers.
For backwards compatibility and for spatial needs such as table
inheritance where each child table may have different geometry type, the
old check constraint behavior is still supported. If you need the old
behavior, you need to pass in the new optional argument as false
use_typmod=false. When this is done geometry columns will be created
with no type modifiers but will have 3 constraints defined. In
particular, this means that every geometry column belonging to a table
has at least three constraints:
-
enforce_dims_geom- ensures every geometry has the same dimension (see ST_NDims) -
enforce_geotype_geom- ensures every geometry is of the same type (see GeometryType) -
enforce_srid_geom- ensures every geometry is in the same projection (see ST_SRID)
If a table oid is provided, this function tries to determine the
srid, dimension, and geometry type of all geometry columns in the table,
adding constraints as necessary. If successful, an appropriate row is
inserted into the geometry_columns table, otherwise, the exception is
caught and an error notice is raised describing the problem.
If the oid of a view is provided, as with a table oid, this function
tries to determine the srid, dimension, and type of all the geometries
in the view, inserting appropriate entries into the geometry_columns
table, but nothing is done to enforce constraints.
The parameterless variant is a simple wrapper for the parameterized
variant that first truncates and repopulates the geometry_columns table
for every spatial table and view in the database, adding spatial
constraints to tables where appropriate. It returns a summary of the
number of geometry columns detected in the database and the number that
were inserted into the geometry_columns table. The parameterized
version simply returns the number of rows inserted into the
geometry_columns table.
Availability: 1.4.0
Changed: 2.0.0 By default, now uses type modifiers instead of check
constraints to constrain geometry types. You can still use check
constraint behavior instead by using the new use_typmod and setting
it to false.
Enhanced: 2.0.0 use_typmod optional argument was introduced that
allows controlling if columns are created with typmodifiers or with
check constraints.
Examples
CREATE TABLE public.myspatial_table(gid serial, geom geometry);
INSERT INTO myspatial_table(geom) VALUES(ST_GeomFromText('LINESTRING(1 2, 3 4)',4326) );
-- This will now use typ modifiers. For this to work, there must exist data
SELECT Populate_Geometry_Columns('public.myspatial_table'::regclass);
populate_geometry_columns
--------------------------
1
\d myspatial_table
Table "public.myspatial_table"
Column | Type | Modifiers
--------+---------------------------+---------------------------------------------------------------
gid | integer | not null default nextval('myspatial_table_gid_seq'::regclass)
geom | geometry(LineString,4326) |-- This will change the geometry columns to use constraints if they are not typmod or have constraints already.
--For this to work, there must exist data
CREATE TABLE public.myspatial_table_cs(gid serial, geom geometry);
INSERT INTO myspatial_table_cs(geom) VALUES(ST_GeomFromText('LINESTRING(1 2, 3 4)',4326) );
SELECT Populate_Geometry_Columns('public.myspatial_table_cs'::regclass, false);
populate_geometry_columns
--------------------------
1
\d myspatial_table_cs
Table "public.myspatial_table_cs"
Column | Type | Modifiers
--------+----------+------------------------------------------------------------------
gid | integer | not null default nextval('myspatial_table_cs_gid_seq'::regclass)
geom | geometry |
Check constraints:
"enforce_dims_geom" CHECK (st_ndims(geom) = 2)
"enforce_geotype_geom" CHECK (geometrytype(geom) = 'LINESTRING'::text OR geom IS NULL)
"enforce_srid_geom" CHECK (st_srid(geom) = 4326)Find_SRID |
UpdateGeometrySRID |