If you are using SQL exclusively to interact with the spatial tables in PostgreSQL or SQLite, you can unregister the ST_Geometry column before deleting a spatial table. If you do not, a record will be orphaned in the ST_Geometry system table.
Unregister an ST_Geometry column in PostgreSQL
Before you use SQL to delete a table that contains a registered ST_Geometry column, use the st_unregister_spatial_column function to unregister it.
The following is the syntax for the st_unregister_spatial_column function:
st_unregister_spatial_column( '<database_name>', '<schema_name>', '<table_name>', '<spatial_column_name>' );
- Open a command prompt or shell prompt.
- Log in to an SQL editor as the owner of the table, and connect to the database that contains the table for which you want to unregister the ST_Geometry column.
In this example, user cleo is connecting to database spatdat.
psql spatdat cleo
- At the SQL prompt, call the st_unregister_spatial_column function.
Here, the geo column of the waypoints table in schema cleo is unregistered:
SELECT sde.st_register_spatial_column( 'spatdat', 'cleo', 'waypoints', 'geo' );
Only the owner of the table can unregister the spatial column.
Unregister an ST_Geometry column in SQLite
Before you use SQL to delete a table that contains a registered ST_Geometry column, use DropGeometryMetadata to unregister it.
The following is the syntax for DropGeometryMetadata:
DropGeometryMetadata( <'main'|NULL>, <table_name> );
When you connect to SQLite through a SQL client, you are connecting to main. You can specify main or use NULL, which assumes you are connecting to main.
- Open an SQL editor and connect to your database.
- If you have not already loaded the ST_Geometry library, do so now.
- Use DropGeometryMetadata to unregister the ST_Geometry column.
In this example, the ST_Geometry column in the hazardous_sites table is unregistered.
SELECT DropGeometryMetadata( 'main', 'hazardous_sites' );