Spatial Databases - Desktop GIS: Mapping the Planet with Open Source Tools - page 94

and also collect the information needed to load, project, and display the
features in a table. How does the geometry_columns table get populated?
There are several ways:
• When loading data using shp2pgsql , QGIS, or ogr2ogr , a record is
inserted into the geometry_columns table.
• Using the AddGeometryColumn function on a table that does not
already have a spatial column. This creates the column in the
table and also inserts a record into geometry_columns .
• Manually inserting a record using a SQL insert statement.
Typically you use the AddGeometryColumn function when you create a
new table and want to add a geometry column. In this case, you cre-
ate the table using SQL without the geometry column and then use the
function to both add the column and create an entry in the geome-
try_columns table.
desktop_data= # CREATE TABLE lakes(LAKE_ID int4, LAKE_NAME varchar(32),
LAKE_DEPTH float);
CREATE TABLE
desktop_data= # SELECT AddGeometryColumn('public', 'lakes', 'the_geom', 4326,
'POLYGON', 2 );
addgeometrycolumn
-----------------------------------------------------
public.lakes.the_geom SRID:4326 TYPE:POLYGON DIMS:2
(1 row)
A final note on the geometry_columns table: Some software such as QGIS
can search your PostgreSQL database and determine which tables are
spatially enabled. To maintain maximum flexibility in your database,
you should probably ensure that each spatial table has an entry in the
geometry_columns table.
Spatial Index
When working with PostGIS, it's important to make sure you have a
spatial index for each layer. Having an index improves both spatial
query and rendering performance.
PostGIS provides a Generalized Search Tree (GiST) index for spatial
features. Depending on how you created your layers, the index may
already exist. You can easily check to see whether an index exists for a
layer using psql to examine the properties of a layer.