[postgis-users] Re: PostGIS table structure

[Brent Wood]

Eli Dylan Lorimer wrote:
> Hi,
> I'm going to be tracking myself with a gps for the next few months and 
> will be storing the collection of waypoints in a PostgreSQL/PostGIS db 
> for subsequent visualization. I'm just now trying to figure out the best 
> table structure to use. Although I'm pretty unfamiliar with spatial 
> databases in general, I believe I'll want to store the lat/lon as point 
> objects, correct? As the primary purpose of storing all this info is so 
> that I may examine spatio-temporal patterns in it, the time stamp for 
> each waypoint is very important too. Can anyone recommend a general way 
> to go about storing this data? I'm just looking for a few general ideas 
> and/or pitfalls to avoid so as not to screw myself in the end. Much thanks.

It does sort of depend on exactly what you want to do with the data and how you
will capture it. A good starting point is a table of point geometries, with
each point having a time attribute along with other relevant attributes.

There are different packages for different GPS manufacturers, or you can log
directly from NMEA data into a Postgis database, capturing time, speed, heading
& location. Or just capture the NMEA stream & load it in later.

If you want to load data logged by the GPS, there is a gpx2shp package, as well
as gpsdrive and gpsbabel. (google should find these for you) Also see what is
available for your GPS from the manufacturer.

www.gps.org.nz & www.freegis.org may have something to help.

It can also be useful to turn the points into lines, perhaps a single line for
the trip, or a table with lines, where each line comprises 24hrs of points, but
not necessarily for your purposes. Once you have the point data, various sorts
of postprocessing/analysis can be done in Postgis/GRASS/R. If you just want to
look at the data, overlaid on other map layers, packages like QGIS & JUMP might
be useful.


Cheers,

  Brent Wood