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, which could be real data.

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 me; 
-- 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 the 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);