Database Reference
In-Depth Information
POINT(21.89...
(5 rows)
8. Finally, create a spatial index for the geometric column of the table:
postgis_cookbook=> CREATE INDEX
idx_firenews_geom ON chp01.firenews USING
GIST (the_geom);
How it works...
This recipe showed you how to load nonspatial tabular data (in CSV format) in
PostGIS using the
COPY
PostgreSQL command.
After creating the table and copying the CSV file rows to the PostgreSQL table,
youupdatedthegeometriccolumnusingoneofthegeometryconstructorfunctions
thatPostGISprovides(
ST_MakePoint
and
ST_PointFromText
forbi-dimension-
al points).
These geometry constructors (in this case,
ST_MakePoint
and
ST_PointFromText
)mustalwaysprovidethe
spatial reference system identifier
(
SRID
)together with the point coordinates to define the point geometry.
Eachgeometricfieldaddedinanytableinthedatabaseistrackedwitharecordin
the
geometry_columns
PostGIS metadata view.InthepreviousPostGIS version
(< 2.0), the
geometry_fields
view was a table and needed to be manually up-
dated, possibly with the convenient
AddGeometryColumn
function.
For the same reason, to maintain the updated
geometry_columns
view, when
dropping a geometry column or removing a spatial table in the previous PostGIS
versions,therewerethe
DropGeometryColumn
and
DropGeometryTable
func-
tions. With PostGIS 2.0, you don't need to use these functions any more, but you
cansafelyremovethecolumnorthetablewiththestandard
ALTER TABLE DROP
COLUMN
and
DROP TABLE
SQL commands.
Inthelaststepoftherecipe,youhavecreatedaspatialindexonthetabletoimprove
performances. Please be aware that as in the case of alphanumerical database
