Mapping Hacks

444

|

Chapter 8, Building the Geospatial Web

#88 Load Your Waypoints into a Spatial Database

HACK

This says that we want to use the spheroid called WGS_1984, which assumes

an Earth radius of 6,378,137 meters (3,963 miles) and a flattening factor of

about 298 meters.

Here is a query to return the distance between any two waypoints.

ORA2 is

the waypoint of the new O’Reilly campus, and

TOLPLZ is the toll plaza for the

Golden Gate Bridge. The query returns the distance in meters, so we divide

the answer by 1609.344, which is the number of meters in a mile, to return

the result in miles:

gpswork-# select distance_spheroid(

w1.location,

w2.location,

'SPHEROID["WGS_1984",6378137,298.257223563]'

) / 1609.344

from waypoint w1, waypoint w2

where w1.name = 'ORA2'

and w2.name = 'TOLPLZ';

?column?

------------------

46.1867561160993

(1 row)

Here’s a brief taste of what we can do with waypoints in PostGIS. This query

calculates the distance from my house to all of my waypoints:

select distinct w1.name, w2.name, round(distance_spheroid(

w1.location,

w2.location,

'SPHEROID["WGS_1984",6378137,298.257223563]'

) / 1609.344) as my_dist

from waypoint w1, waypoint w2

where w1.name = 'HOME'

order by w2.name

The results should look like this (with 496 waypoints you probably don’t

care about omitted):

name | name | my_dist

------+--------+---------

HOME | 100 | 1032

HOME | 588 | 178

HOME | ABSPOT | 80

Not Quite a Cross Tab Query