Export to DXF from PostgreSQL using ogr2ogr

Question

I have a large dataset in a PostGIS database (OS MasterMap). My aim is to export specific areas of this dataset to DXF, while retaining the attributes of the data as layers in the DXF.

My current ogr2ogr command looks like this:

$ ogr2ogr -f "DXF" os_mm_export.dxf PG:"host=11.11.11.11 dbname=db user=user password=xxxxx" -sql "SELECT ST_INTERSECTION(a.the_geom, (SELECT ST_SETSRID(geom, 27700) FROM schema.site_boundary)) AS geom FROM tbl_os_mmap AS a WHERE ST_INTERSECTS(ST_SETSRID(a.the_geom, 27700), (SELECT ST_SETSRID(geom, 27700) FROM schema.site_boundary))"

This works fine to export the data to DXF from the specified area, but the DXF only comes out with a single layer '0'.

What I want is to specify a 'line type' field to be used to create layers in the DXF.

There are also points to be exported, for those tables I would like to be able to specify which field would be used to label the points.

I realise it would be possible to break the process down into multiple steps, but I'm interested to see if it would be possible to do both things in a single ogr2ogr command, as this would obviously be the fastest and most elegant solution.

Answer 1

You need a Layer attribute in your input into ogr2ogr for it to convert into layers in CAD.

I wrote a post about this for a straightforward GML to DXF conversion, using an intermediate step of ShapeFile because or geometry issues with the outputs:

http://gisforthought.com/gis-to-cad-using-ogr2ogr-part-2-gml-to-dxf-with-os-mastermap/

But it works just as well for PostGIS to DXF.

Your command should be along the lines of (replacing "line_type" with the field containing the column containing your line types):

ogr2ogr -f "DXF" os_mm_export.dxf PG:"host=11.11.11.11 dbname=db user=user password=xxxxx" -sql "SELECT ST_INTERSECTION(a.the_geom, (SELECT ST_SETSRID(geom, 27700) FROM schema.site_boundary)) AS geom, line_type as Layer FROM tbl_os_mmap AS a WHERE ST_INTERSECTS(ST_SETSRID(a.the_geom, 27700), (SELECT ST_SETSRID(geom, 27700) FROM schema.site_boundary))"