Unregister an ST_Geometry column

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>'
);

1. Open a command prompt or shell prompt.
2. 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

3. 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.

1. Open an SQL editor and connect to your database.
2. If you have not already loaded the ST_Geometry library, do so now.
3. 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'
   );