Mapping Hacks

Load Your Waypoints into a Spatial Database #88

Chapter 8, Building the Geospatial Web

|

441

HACK

descr instead. The GPX format defines additional elements that can exist in

the format, but since my Garmin GPS doesn’t support those elements, we

can ignore them for now.

You can create a new table in an existing database, or create a new data-

base. Recapping from “Build a Spatially Indexed Data Store”

[Hack #87], you

can create a new database and add the spatial extensions in PostGIS with

these commands:

$ createdb gpswork

$ createlang plpgsql gpswork

$ psql -d gpswork -f /usr/share/pgsql/postgis.sql

$ psql -d gpswork -f /usr/share/pgsql/spatial_ref_sys.sql

Your copies of postgis.sql and spatial_ref_sys.sql might be in another

directory.

These SQL statements will create the table to match the GPX format:

create table waypoint (

waypoint_id serial NOT NULL,

name varchar(32),

cmt varchar(255),

descr varchar(255),

sym varchar(255));

select AddGeometryColumn('gpswork', 'waypoint', 'location', 4326, 'POINT', 2);

The AddGeometryColumn function also inserts our column into the table

geometry_columns. This is crucial to the workings of PostGIS.

If you put these statements into the file create_waypoint.sql, you can create

the table from the command line:

$ psql -d gpswork -f create_waypoint.sql

The -d parameter specifies the database, and -f indicates the file containing

SQL commands to execute.

Importing Waypoints ...