Geography Reference
In-Depth Information
Figure 55: Five Largest Counties in the U.K in Descending Order
Another thing that might be useful to know is how long the perimeter of the land mass is.
Finding this is just as easy, as shown in the following code sample and data output:
SELECT name2,ST_Perimeter(ST_Transform(the_geom,27700)) FROM ukcountys LIMIT 5
Figure 56: Perimeter of U.K. Counties
Scenario 2: How many of what are where?
Another typical use of GIS is for gathering information on how one object's location is related
to another object's location. For example, given three U.K. counties—Durham, Tyne and
Wear, and Cumbria—we can easily find out how many principal towns are in each.
SELECT ukcountys.name2,count(uktowns.*)
FROM ukcountys,uktowns
WHERE ST_Within(uktowns.geometry,ST_Transform(ukcountys.the_geom,27700)) AND
ukcountys.name2 IN ('Durham','Tyne and Wear','Cumbria')
GROUP BY ukcountys.name2
This code gives us the following:
