Database Reference
In-Depth Information
Using triggers to populate a geometry
column
In this recipe, we imagine that we have ongoing updates to our database, which
needsspatialrepresentation;however,inthiscase,wewantahard-codedgeometry
columntobeupdatedeachtimean INSERT operationtakesplaceonthedatabase,
converting our x and y values to geometryas they are inserted in the database.
The advantage of this approach is that the geometry is then registered in the geo-
metry_columns view,andthereforethisapproachworksreliablywithmorePostGIS
client types than creating a geospatial view. This also provides the advantage of al-
lowingforaspatialindexthatcansignificantlyspeedupavarietyofqueries.Thedis-
advantageforusersusingPostgreSQLversionslowerthanVersion9.0isthat,without
a WHERE clause within the trigger, every time an insert takes place, the trigger will
becalculatedonallpointstocreategeometry.Thismethodcouldbeveryexpensive
on large datasets. However, for users of PostgreSQL 9.0 and later, a WHERE clause
makesthistriggerperformquickly,aswecanconstrainthetriggertoonlythoserows
that have no geometry yet populated.
Getting ready
We will start by creating another table of random points with x , y , and z values, as
shown in the following query:
DROP TABLE IF EXISTS chp02.xwhyzed1 CASCADE;
CREATE TABLE chp02.xwhyzed1
(
x numeric,
y numeric,
z numeric
)
WITH (OIDS=FALSE);
ALTER TABLE chp02.xwhyzed1 OWNER TO postgres;
ALTER TABLE chp02.xwhyzed1 ADD COLUMN gid serial;
ALTER TABLE chp02.xwhyzed1 ADD PRIMARY KEY (gid);
Search WWH ::




Custom Search