Programmatically creating exports from PostGIS database into different file formats (e.g. shapefile, dxf, dgn,…)?

Question

Is there a kind of wrapper/library written in python which would enable me to programatically create exports from postgis database into different file formats (most common being, shapefile, dxf, dgn,...). If not i ll probably build one myself using psycopg2, gdal and ogr, these are probably components to start with, yeah?

I need this because i ll be making some kind of web service for data exports.

Answer 1

i think you dont need lots of search for your work about exporting from postgis. everything is clear gdal, psycopg2 and python can make lots of things for your request.

i have been using them with geodjango for same request from user for a long time. you can get more info about Using psycopg2 with PostgreSQL here and a document which name is Reading and Writing Vector Data with OGR highly recommended for you to read...

the basic connection with postgis:

res = []
con = psycopg2.connect("dbname='mydb' user='reid' host='127.0.0.1' password='reid'")
cur = con.cursor()
cur.execute("SELECT ST_Area(the_geom) FROM mypoly")
rows = cur.fetchall()

for row in rows:
    respre = row[2] # find your result.
    res.append(respre)     

con.commit()
cur.close()
con.close()

i hope it helps you....

Answer 2

Tidiest approach that I have seen is to use python to trigger the pgsql2shp command line utility which is part of the postgis package.

Not very pythonic, but very simple and robust.

e.g. See How can I get a shapefile from a postgis query?

Once you have a shapefile it is straight forward to convert to most common formats using ogr2ogr, also over the command line. http://www.gdal.org/ogr2ogr.html

Both DGN and DXF seem to be supported: http://www.gdal.org/ogr_formats.html

Example:

import os #of course you should use subprocess instead...

os.system("/path/to/postgis/bin/pgsql2shp -f ./output_shapefile.shp ... database_name \"SELECT * FROM schema.tablename LIMIT 5;\"")

os.system("/path/to/ogr2ogr -f DXF ./output_converted.dxf ./output_shapefile.shp")

os.system("/path/to/ogr2ogr -f DGN ./output_converted.dgn ./output_shapefile.shp")

Answer 3

You mention dgn and dxf and open source code then answer is probably No. To my knowledge there is no good free or open source solutions to work with dgn (v8, v7 may even work) (not sure about dxf) files

If you dont have to touch dgn (v8, v7 may even work) then GDAL supports kml (use st_ASKML and XML to get full kml). QGIS has option to "Save as..." for vector layers and probably has somekind python library for it.

I use FME for my dgn to shp/db/dgn/csv/raster conversion. Also FME has support for PostGIS and Postgresql databases (and with some strange lisence you get support for Esri etc..). Works great and costs a lot, isn't open source.