Geography Reference
In-Depth Information
As you can see in our SQL and Figure 78, we are using
ST_Transform
to transform our
points from OSGB36 (SRID 27700) to WGS84 (SRID 4326). The result is that when a
SELECT
from ukcitys_wgs is performed, the table layout will be identical to ukcitys, but the
geometry will be in the new coordinate system.
Views solve lots of problems like this in a GIS, and you'll tend to find that GIS databases
make very extensive use of them.
Repeat these steps and create
uktowns_wgs
using the following SQL:
SELECT uktowns.gid, uktowns.number, uktowns.name, uktowns.admin_name,
st_transform(uktowns.geometry, 4326) AS geometry FROM uktowns;
One Small Problem...
For all that views do, they do have one small problem when it comes to using them in this
way: the spatial metadata.
If you recall the beginning of the topic, we discussed the
geometry_columns
table and its
importance in the GIS database. When we create tables in the regular table space, we
generally use the spatial function
AddGeometryColumn
to add the column that will contain
the actual geometry object. You should have seen this happen when you used GeoKettle to
add the county data—the SQL that was generated to create the table should have contained
the
AddGeometryColumn
spatial function. This not only adds the column and modifies the
table as needed, but it also registers the field with the required metadata tables and sets up
some triggers to enforce the correct data types and SRIDs.
The problem is because a view is built from existing columns, there is no way of creating an
actual geometric column on a view. This means that we have to add it manually. Fortunately,
it's not a particularly difficult process; it only involves an insert.
Open up an SQL editor window and enter the following SQL:
INSERT INTO
geometry_columns(f_table_catalog,f_table_schema,f_table_name,f_geometry_column,coo
rd_dimension,srid,type)
VALUES('','public','uktowns_wgs','geometry',2,4326,'MULTIPOINT')
If you examine the rows already in
geometry_columns
, you'll notice that the data being
inserted is identical to the row for
uktowns
; the only difference is the SRID.
To complete this task, perform the following to update for the city view:
