Using geospatial views

Views in PostgreSQL allow for ad hoc representation of data and data relationships in alternate forms. In this recipe, we'll be using views to allow for the automatic creation of point data based on tabular inputs. We can imagine a case where the input stream of data is non-spatial, but includes longitude and latitude or some other coordinates. We would like to automatically show this data as points in space.

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...