Table management functions: help define tables with geometry fields

AddGeometryColumn

Add a field of geometry type to an existing table
Function type: text

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

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

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

give an example

select addgeometrycolumn('river','geom_test',4326,'POINT',2,true);
# Using the first usage, add the geometry field 'geometry' of two-dimensional points to the river table_ test' 

Parameter information to be entered

• Table positioning (table name or schema name + table name or directory name + schema name + table name)
• The name of the new column
• SRID value for the new column
• geometry attribute of the new column (point, line, face, etc.)
• Dimension of new column
• Whether to use a type modifier when creating a new column

Possible error reporting reasons

• SQL error [42883]: error: function addgeometrycolumn(unknown, unknown, integer, unknown, boolean) does not exist - incomplete parameter input / sequence error / type error
• Table / schema does not exist (current search path cannot be found)
• The entered SRID value, geometry attribute, dimension, etc. are invalid

DropGeometryColumn

Delete a geometry type field in a table
Function type: text

DropGeometryColumn(varchar table_name, 
varchar column_name);

DropGeometryColumn(varchar schema_name, varchar table_name, 
varchar column_name);

DropGeometryColumn(varchar catalog_name, varchar schema_name, varchar table_name, 
varchar column_name);

give an example

select dropgeometrycolumn('river', 'geom_test');
# Delete the 'geom' you just created_ 'test 'field

Parameter information to be entered

• Column positioning
• Field name to be deleted

DropGeometryTable

Delete a table and all its references in the geometry type field
Function type: boolean

DropGeometryTable(varchar table_name);

DropGeometryTable(varchar schema_name, varchar table_name);

DropGeometryTable(varchar catalog_name, varchar schema_name, varchar table_name);

give an example

create table lake();
# Create test table 'lake'
select addgeometrycolumn('lake','geom',4326,'POLYGON',2,true); 
# Add the geometry field 'geometry' to the table
select dropgeometrytable('lake');
# Delete test table

Parameter information to be entered

• Table positioning

be careful

This function still exists for backward compatibility. You can now use the DROP TABLE function directly to delete a table with a geometry field, just as you can delete other tables

Find_SRID

Returns the SRID value of the found geometry field
Function type: integer

Find_SRID(varchar a_schema_name, varchar a_table_name, varchar a_geomfield_name);

give an example

SELECT find_srid('public', 'river', 'geom');

Parameter information to be entered

• Column positioning
• Field name

Populate_Geometry_Columns

Ensure that fields of each geometry type have appropriate type modifiers or spatial constraints to ensure that they are properly registered in geometry_columns view.
By default, all geometry fields without type modifier are converted to those with type modifier.
In order to meet the needs of backward compatibility and spatial information (for example, the geometry type of the child table may be different from that of the parent table), the previous check constraint behavior is still supported. If you want to use the previous, you should use the new optional parameter when creating a new geometry field_ Set typmod to false; In this way, the new field created will not have a type modifier, but will be defined with three constraints:

• enforce_ dims_ the_ Geometry: make sure that each geometry field has the same dimension
• enforce_ geotype_ the_ Geometry: make sure that each geometry field has the same geometry type
• enforce_ srid_ the_ Geometry: make sure that each geometry field has the same projection
  See official explanation for details Populate_Geometry_Columns
  Function type: text / int

Populate_Geometry_Columns(boolean use_typmod=true);

Populate_Geometry_Columns(oid relation_oid, boolean use_typmod=true);

give an example

create table public.table_test(gid serial, geom geometry);
# Create test table_test, including a column of 'gid' fields of serial type and a column of 'geom' fields of geometry type
INSERT INTO table_test (geom) VALUES(
	ST_GeomFromText('LINESTRING(1 2, 3 4)',4326) 
	);
# Insert data, default use_typmod=true
SELECT Populate_Geometry_Columns('public.table_test'::regclass);
# I can't understand it. I can't find relevant Chinese information on the Internet. See the official website for details

UpdateGeometrySRID

Update the srid values of all elements in a geometry column; Update geometry_ Constraints and references in columns. If the column is enforced by the type definition, the type definition will be changed.
Function type: text

UpdateGeometrySRID(varchar table_name, varchar column_name, 
integer srid);

UpdateGeometrySRID(varchar schema_name, varchar table_name, varchar column_name, 
integer srid);

UpdateGeometrySRID(varchar catalog_name, varchar schema_name, varchar table_name, varchar column_name, 
integer srid);

give an example

select UpdateGeometrySRID('table_test', 'geom', 
3857);
# Modify srid value
select find_srid('public','table_test','geom');
# Check the modified srid value

Parameter information to be entered

• Column positioning
• Target srid value

reference resources: Official document of PostGIS 3.2

These functions assist in defining tables containing geometry columns.

AddGeometryColumn — Adds a geometry column to an existing table.
DropGeometryColumn — Removes a geometry column from a spatial table.
DropGeometryTable — Drops a table and all its references in geometry_columns.
Find_SRID — Returns the SRID defined for a geometry column.
Populate_Geometry_Columns — Ensures geometry columns are defined with type modifiers or have appropriate spatial constraints.
UpdateGeometrySRID — Updates the SRID of all features in a geometry column, and the table metadata.