OGR in Python with PostGIS

ogr_postgis.py

from glob import glob
from os.path import dirname, basename, join

print 'psql -U username -c "TRUNCATE roads_skeletron"'

all_filenames = glob('tmp/*/???????-roads-z12.shp')

for (overall_index, filename) in enumerate(sorted(all_filenames)):

    quad = basename(filename)[:7]
    
    print 'echo', '-' * 20, overall_index, 'of', len(all_filenames), quad

    lat = int(quad[1:3]) if quad[0] == 'n' else -int(quad[1:3])
    lon = int(quad[4:7]) if quad[3] == 'e' else -int(quad[4:7])
    
    filenames = glob(join(dirname(filename), 'roads-z??.json'))
    
    for (index, filename) in enumerate(sorted(filenames)):
    
        print 'ogr2ogr -t_srs EPSG:900913 -f PostgreSQL \\'
        print '  -clipsrc %d %d %d %d \\' % (lon, lat, lon+5, lat+5)
        
        if index == 0:
            print '  -overwrite \\'
            print '  -lco GEOMETRY_NAME=geometry -lco ENCODING=UTF-8 \\'
            print '  -nlt MULTILINESTRING \\'
        else:
            print '  -append -update \\'
            print '  -nlt MULTILINESTRING \\'
        
        print '  -nln roads_%(quad)s \\' % locals()
        print '  PG:"dbname=\'dbname\' user=\'username\'"', filename
        print ''
        
    print "psql -U username -c \"INSERT INTO roads_skeletron (zoomlevel, shortname, name, pixelwidth, the_geom, source) SELECT zoomlevel, shortname, name, pixelwidth, SetSRID(Multi(geometry), 900913), '%(quad)s' FROM roads_%(quad)s\"" % locals()
    print "psql -U username -c 'DROP TABLE roads_%(quad)s'" % locals()