Using triggers to populate the geometry column
In this recipe, we imagine that we have ever increasing data in our database, which needs spatial representation; however, in this case we want a hardcoded geometry column to be updated each time an insertion happens on the database, converting our x and y values to geometry as and when they are inserted into the database.
The advantage of this approach is that the geometry is then registered in the geometry_columns
view, and therefore this approach works reliably with more PostGIS client types than creating a new geospatial view. This also provides the advantage of allowing for a spatial index that can significantly speed up a variety of queries.
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...