Geography Reference
In-Depth Information
The only thing different is the
<=
clause in the last part of the
WHERE
statement, so what
changes? Not much, really. However, if you're using bounding boxes and buffers, you'll often
get better results using
ST_Distance
with a
<=
operator.
Again, we used the cast operator to make sure our data output a normal numeric type and
rounded it to one decimal place, divided it by 1609.344 to convert it to miles, and finally
filtered things so that only towns around Durham were included.
It's easy enough to exchange the Durham city geometry for a GPS point from a GPS device,
for example, and list the towns around that point.
SELECT t.name,t.admin_name,round((ST_Distance(ST_Point(428110 542709),t.geometry)
/ 1609.344)::numeric, 1) as distanceinmiles
FROM ukcitys AS c, uktowns as t
WHERE c.name = 'DURHAM' AND ST_Dwithin(ST_Point(428110 542709),t.geometry,
16093.44)
Or if your GPS is WGS84 (SRID 4326), you'll need to transform it to meters and OSGB36
(SRID 27700).
SELECT t.name,t.admin_name,round((ST_Distance(ST_Transform(ST_Point(-1.56450
54.77851),27700),t.geometry) / 1609.344)::numeric, 1) as distanceinmiles
FROM ukcitys AS c, uktowns as t
WHERE c.name = 'DURHAM' AND ST_Dwithin(ST_Transform(ST_Point(-1.56450
54.77851),27700),t.geometry, 16093.44)
Scenario 4: What is my geometry made of?
In some cases you may need to take your geometry apart and reassemble it in a different
way, or make a new geometry based on the original one.
First off, let's find out how many points make up the border around County Durham.
SELECT name2,ST_NPoints(the_geom) FROM ukcountys WHERE name2 = 'Durham'
Figure 60: Number of Border Points
