Database Reference
In-Depth Information
Therearetwodisadvantagestothisapproach.Thefirstisthatwehavenotdeclared
ourspatialreferencesystemintheview,soanysoftwareconsumingthesepointswill
notknowthecoordinatesystemweareusing—thatis,whetheritisageographic(lat-
itude/longitude)oraplanarcoordinatesystem.Wewilladdressthisproblemshortly.
Thesecondproblemisthatmanysoftwaresystemsaccessingthesepointsmaynot
automaticallydetectandusethespatialinformationfromthetable.Thisproblemis
addressed in the
Using triggers to populate a geometry column
recipe.
Note
Spatial Reference ID
(
SRID
) allows us to specify the coordinate system for a
givendataset.Thenumberingsystemisasimpleintegervaluetospecifyagiv-
encoordinatesystem.SRIDisderivedoriginallyfromthe
European Petroleum
Survey Group
(
EPSG
) and now maintained by the Surveying & Positioning
Committee of the International Association of
Oil & Gas Producers
(
OGP
).
UsefultoolsforSRIDsareSpatialReference(
http://spatialreference.org
)
andPr-
j2EPSG (
http://prj2epsg.org/search
).
There's more...
Toaddressthefirstproblemmentionedinthe
How it works...
section,wecansimply
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
• The
Using triggers to populate a geometry column
recipe
