Database Reference
In-Depth Information
Using geospatial views
ViewsinPostgreSQLallowforadhocrepresentationofdataanddatarelationshipsin
alternateforms.Inthisrecipe,we'llbeusingviewstoallowfortheautomaticcreation
ofpointdatabasedontabularinputs.Wecanimagineacasewheretheinputstream
ofdataisnon-spatial,butincludeslongitudeandlatitudeorsomeothercoordinates.
We would like to automatically show this data as points in space.
Getting ready
Wecancreateaviewasarepresentationofspatialdataprettyeasily.Thesyntaxfor
creating a view is similar to creating a table; for example:
CREATE VIEW viewname AS
SELECT...
Intheprecedingcommandline,our
SELECT
querymanipulatesthedataforus.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
