Getting ready

We can create a view as a representation of spatial data pretty easily. The syntax for creating a view is similar to creating a table; for example:

CREATE VIEW viewname AS
 SELECT...

In the preceding command line, our SELECT query manipulates the data for us. Let's start with a small dataset. In this case, we will start with some random points.

First, we create the table from which the view will be constructed as follows:

–- Drop the table in case it exists
DROP TABLE IF EXISTS chp02.xwhyzed CASCADE; 
CREATE TABLE chp02.xwhyzed
-- This table will contain numeric x, y, and z values
(
 x numeric,
 y numeric,
 z numeric
)
WITH (OIDS=FALSE);
ALTER TABLE chp02.xwhyzed OWNER TO postgres;
-- We will be disciplined and ensure we have a primary key
ALTER TABLE chp02.xwhyzed ADD COLUMN gid serial;
ALTER TABLE chp02.xwhyzed ADD PRIMARY KEY (gid);

Now let's populate this with data for testing using the following query:

INSERT INTO chp02.xwhyzed (x, y, z)
 VALUES (random()*5, random()*7, random()*106);
INSERT INTO chp02.xwhyzed (x, y, z)
 VALUES (random()*5, random()*7, random()*106);
INSERT INTO chp02.xwhyzed (x, y, z)
 VALUES (random()*5, random()*7, random()*106);
INSERT INTO chp02.xwhyzed (x, y, z)
 VALUES (random()*5, random()*7, random()*106);

How to do it...

Now to create the view, we will use the following query:

-- Ensure we don't try to duplicate the view
DROP VIEW IF EXISTS chp02.xbecausezed;
-- Retain original attributes, but also create a point attribute from x and y
CREATE VIEW chp02.xbecausezed AS
 SELECT x, y, z, ST_MakePoint(x,y)
 FROM chp02.xwhyzed;

How it works...

Our view is really a simple transformation of the existing data using PostGIS's ST_MakePoint function. The ST_MakePoint function takes the input of two numbers to create a PostGIS point; in this case, our view simply uses our x and y values to populate the data. Any time there is an update to the table to add a new record with x and y values, the view will populate a point, which is really useful for data that is constantly being updated.

There are two disadvantages to this approach. The first is that we have not declared our spatial reference system in the view, so any software consuming these points will not know the coordinate system we are using—that is, whether it is a geographic (latitude/longitude) or a planar coordinate system. We will address this problem shortly. The second problem is that many software systems accessing these points may not automatically detect and use the spatial information from the table. This problem is addressed in the Using triggers to populate a geometry column recipe.

There's more...

To address the first problem mentioned in the How it works... section, we can simply wrap our existing ST_MakePoint function in another function specifying the SRID using ST_SetSRID, as shown in the following query:

-- Ensure we don't try to duplicate the view
DROP VIEW IF EXISTS chp02.xbecausezed;
-- Retain original attributes, but also create a point attribute from x and y
CREATE VIEW chp02.xbecausezed AS
 SELECT x, y, z, ST_SetSRID(ST_MakePoint(x,y), 3734) -- Add ST_SetSRID
 FROM chp02.xwhyzed;

See also