User:Pov/gpx2postgis

gpx2postgis is a little command-line script written in python, that allows you to load GPX files inside a postgis table. Example usages are:

• display GPS traces on a map, for instance to show your last vacations' trip
• visualize data before they're imported

requirements

This script is written in python and uses the following libraries/binaries

• gpsbabel:

convert gpx to a simpler format, this requirement may be lifted easily if demand is signifiant.

• python mapnik biding:

this is required to reproject the data to the same projection used by mapnik

• pypgsql:

this is required to ensure that python can talk to your postgis instance

usage

./osm2pgsql.py --help

find . -name "*.gpx" -print0 | xargs -0 gpxtopgsql.py -u login -t traces -P

sample results

gpx traces on a map

gpx of city boundaries to check the data before an import

source

#!/usr/bin/env python
#encoding=utf8

from pyPgSQL import libpq
from optparse import OptionParser
from os import popen
from mapnik import Coord, Projection

parser = OptionParser()
parser.add_option('-H', '--host', action='store', dest='host')
parser.add_option('-d', '--database', action='store', dest='db', default='gis')
parser.add_option('-u', '--user', action='store', dest='user')
parser.add_option('-p', '--password', action='store', dest='password')
parser.add_option('-t', '--table', action='store', dest='table', default='traces')
parser.add_option('-P', '--purge', action='store_true', dest='purge', default=False)
parser.add_option('-C', '--create', action='store_true', dest='create', default=False)
parser.add_option('-D', '--drop', action='store_true', dest='drop', default=False)
(options, args) = parser.parse_args()

cnxString = 'dbname=%s' % options.db
if options.host:
    cnxString += ' host=%s' % options.host 
if options.user:
    cnxString += ' user=%s' % options.user 
if options.password:
    cnxString += ' password=%s' % options.password 
c = libpq.PQconnectdb(cnxString)

if options.drop:
    c.query('DROP TABLE %s' % options.table)
if options.create:
    c.query('CREATE TABLE %s (id serial, name varchar(256), simple_way geometry, way geometry)' % options.table)
    c.query('CREATE INDEX %s_way ON %s USING gist(way)' % (options.table, options.table))
    c.query('CREATE INDEX %s_simple_way ON %s USING gist(simple_way)' % (options.table, options.table))
    try:
        c.query('INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, type) VALUES (\'\', \'public\', %s, \'way\', 2, 900913, \'LINESTRING\')' % libpq.PgQuoteString(options.table))
    except:
        pass
c.query('begin')
if options.purge:
    c.query('DELETE FROM %s' % options.table)

prj = Projection("+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +no_defs +over")
for filename in args:
    tabfile = popen('/usr/local/bin/gpsbabel -i gpx -f "%s" -o csv -F -' % filename, 'r')
    lines = []
    for line in tabfile:
        values = line.split(',')
        merc = prj.forward(Coord(float(values[1]), float(values[0])))
        lines.append('%s %s' % (merc.x, merc.y))
    try:
        if len(lines) == 0:
            continue
        sql = "INSERT INTO %s (name, way) VALUES (%s, simplify(ST_GeomFromText('SRID=900913;LINESTRING(%s)'), .2))" % (options.table, libpq.PgQuoteString(filename), ','.join(lines))
        c.query(sql)
    except Exception, e:
        print sql, e
        pass

c.query('update %s set simple_way = simplify(way, 100)' % options.table)
c.query('commit')

sample mapnik style

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE Map>
<Map srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +no_defs +over">
<Style name="gpx-hires">
    <Rule>
      <MaxScaleDenominator>100000</MaxScaleDenominator>
      <LineSymbolizer>
        <CssParameter name="stroke">#0000ff</CssParameter>
        <CssParameter name="stroke-width">4</CssParameter>
        <CssParameter name="stroke-opacity">0.3</CssParameter>
        <CssParameter name="stroke-linecap">round</CssParameter>
        <CssParameter name="stroke-linejoin">round</CssParameter>
      </LineSymbolizer>
    </Rule>
</Style>
<Style name="gpx">
    <Rule>
      <MinScaleDenominator>100000</MinScaleDenominator>
      <LineSymbolizer>
        <CssParameter name="stroke">#0000ff</CssParameter>
        <CssParameter name="stroke-width">2</CssParameter>
        <CssParameter name="stroke-opacity">0.3</CssParameter>
        <CssParameter name="stroke-linecap">round</CssParameter>
        <CssParameter name="stroke-linejoin">round</CssParameter>
      </LineSymbolizer>
    </Rule>
</Style>
<Style name="gpxlabels">
    <Rule>
      <MaxScaleDenominator>500000</MaxScaleDenominator>
      <TextSymbolizer name="name" face_name="DejaVu Sans Book" size="10" fill="#0000ff" halo_fill= "white" halo_radius="1" allow_overlap="false" wrap_width="0"/>
    </Rule>
</Style>
<Layer name="gpx-hires" status="on" srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +no_defs +over">
    <StyleName>gpx-hires</StyleName>
    <Datasource>
      <Parameter name="type">postgis</Parameter>
      <Parameter name="user">postgres</Parameter>
      <Parameter name="dbname">gis</Parameter>
      <Parameter name="table">(select way from traces) as traces</Parameter>
      <Parameter name="estimate_extent">true</Parameter>
    </Datasource>
</Layer>
<Layer name="gpx" status="on" srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +no_defs +over">
    <StyleName>gpx</StyleName>
    <Datasource>
      <Parameter name="type">postgis</Parameter>
      <Parameter name="user">postgres</Parameter>
      <Parameter name="dbname">gis</Parameter>
      <Parameter name="table">(select simple_way as way from traces) as traces</Parameter>
      <Parameter name="estimate_extent">true</Parameter>
    </Datasource>
</Layer>

<Layer name="gpxnames" status="on" srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +no_defs +over">
    <StyleName>gpxlabels</StyleName>
    <Datasource>
      <Parameter name="type">postgis</Parameter>
      <Parameter name="user">postgres</Parameter>
      <Parameter name="dbname">gis</Parameter>
      <Parameter name="table">(select st_centroid(simple_way) as way, name from traces where st_isclosed(simple_way)) as traces</Parameter>
      <Parameter name="estimate_extent">true</Parameter>
    </Datasource>
</Layer>

</Map>