getting dxf files out of PostgreSQL

Question

I've successfully loaded a bunch of shapefiles into a PostGIS database. I would like to be able to retrieve specific geometry and layers as dxf files based on SQL queries.

I'm using PostGIS, GDAL, PostgreSQL, and Python from the OSGeo4W suite on Windows.

I'm imagining that I should be using ogr2ogr a bit like this:

$ ogr2ogr -sql "SELECT * FROM some_table" -f "dxf" outputfile.dxf -overwrite "PG:dbname=mydb user=dbusername password=dbpassword"

but when I do that with the appropriate values, I get a series of errors like this:

ERROR 1: DXF layer does not support arbitrary field creation, field 'objectid' not created. 

Resulting in an error for every attribute column in the layer some_table But I do get a dxf file.

If I've already created a .dxf file, and I rerun the command above (including the -overwrite option), I get this error instead:

Layer sql_statement not found, and CreateLayer not supported by driver. ERROR 1: Terminating translation prematurely after failed translation from sql statement.

I have already checked to make sure that the GDAL_DATA environmental variable is set correctly. (to C:\OSGeo4W\share\gdal)

It's certainly possible that I'm approaching this task incorrectly because I'm new to all these tools, so I'm open to any suggestions and tips about how to make .dxf files from GIS data stored in PostgreSQL based on SQL queries.

Answer 1

Benjamin,

DXF (as supposed by OGR) does not support arbitrary GIS attributes. It has a fixed schema that looks like:

Layer: String (0.0)
SubClasses: String (0.0)
ExtendedEntity: String (0.0)
Linetype: String (0.0)
EntityHandle: String (0.0)

and only a few of these are actually examined on write. The simpliest expedient is just to use the -skipfailures method though it seems like the translation is proceeding despite the errors even without that.

As for -overwrite - that is applicable for formats that support layer deletion which DXF does not. Just pre-delete the existing DXF file before running ogr2ogr.

Answer 2

with the answer from Frank and the hint from the "GIS-Kompetenzzentrum" of the town of Uster (only in german)

http://gis.uster.ch/dokumentation/datenkonvertierung/ogr

I have figured out that PostGIS to dxf can be done like so:

ogr2ogr -f DXF $TITLE$.dxf PG:"dbname='$DBNAME$' host='$HOST$' port='$PORT$' user='$USER$' password='$PASSWORD$'" -sql "select $COLUMN$ as Layer, the_geom from $TABLE$"

The sql-statement, on the end, created for every row in $COLUMN$ an Layer. So it will be best to select a $COLUMN$ like ID or a relevant name.

I have tried it with an 2D Polygon-Table. The dxf-file has not closed polylines. Perhaps it is possible to add something like " $polygon$ as Linetype" to the sql-string.

If you want all geometries from your table on one layer then you can use the code from the "GIS-Kompetenzzentrum" of Uster

ogr2ogr -select '' -f DXF DXF $TITLE$.dxf PG:"dbname='$DBNAME$' host='$HOST$' port='$PORT$' user='$USER$' password='$PASSWORD$' schemas=$SCHEMA$ tables=$TABLE$(the_geom)"

On both statements you can add the following option:

-spat xmin ymin xmax ymax

if you want an spatial restriction.

Answer 3

What Frank said was going to be my suggestion.
Create a dxf with your layer names already in it and use that as template.