Writting both PEN and LABEL tool parameters when exporting line features to DXF using GDAL/OGR

Question

When transferring data from a PostgreSQL/PostGIS database to a DXF file using ogr2ogr I'm not able to figure out if it's possible to have both a PEN and a LABEL on line features.

This is what I currently run:

ogr2ogr -of "DXF" export.dxf PG:"host=localhost dbname=database port=5432 user='postgres' password='********'" -sql "WITH cte AS
(
SELECT
    ST_INTERSECTION(a.geom, (
        SELECT ST_SETSRID(geom, 2056)
        FROM boundaries.state_boundary WHERE name LIKE 'Zuerich')
    ) AS geom,
    surface_material
FROM av.roads AS a
WHERE ST_INTERSECTS(ST_SETSRID(a.geom, 2056), (SELECT ST_SETSRID(geom, 2056) FROM boundaries.state_boundary WHERE name LIKE 'Zuerich'))
)
SELECT
    surface_material AS TEXT,
    'PEN(c:#00D1E2FF,w:5px); LABEL(f:Arial,s:12pt,dp:5px,m:m,t:\"{surface_material}\")' AS OGR_STYLE,
    'main road' AS LAYER,
    geom
FROM cte"

Notice: the surface_material attribute in the PG database contains text which can have spaces or single quotes in it.

A resulting feature looks like this when I explored the resulting export.dxf file with ogrinfo -al:

OGRFeature(entities):18181
  Layer (String) = main road
  SubClasses (String) = AcDbEntity:AcDbPolyline
  EntityHandle (String) = 2471E
  Style = PEN(c:#00bfff)
  LINESTRING Z (2683912.8564 1247848.2197 0,2683924.3566 1247872.1497 0,2683015.0767 1247881.2798 0,2683016.7865 1247899.5998 0)

Some strange things that I can notice are that

• the PEN's color is not the one that I have actually specified (but it's visually "close to"),
• the PEN width is not taken into account,
• the LABEL is not appearing at all.

I don't know why. How to fix the Style value in order to embed all these values correctly?

The GDAL version I'm playing with is: GDAL 3.10.0dev-082d0e78c54dd544a14947ac216fdcca8080595c, released 2024/06/20

Relevant documentation

https://gdal.org/user/ogr_feature_style.html
https://gdal.org/drivers/vector/dxf.html
https://images.autodesk.com/adsk/files/autocad_2014_pdf_dxf_reference_enu.pdf

Answer 1

@swiss_knight I really like this question, and I'm wondering that no self-proclaimed GIS guru has answered yet: No, this is not possible!. 🤔😊

I wouldn't say "No", but the solution to this issue requires a lot of CAD background knowledge and programming skills to be solved.

First of all, it must be mentioned that the CAD world is simply completely different from the GIS world. The CAD focus is mainly on creating static drawings, whereas dynamic data is usually visualized in GIS. For the visualization process in GIS, we need corresponding object attributes that are usually not used in CAD drawing creation. That's why there is no default mechanism to store attributes in every CAD object (only "block" objects can be provided with attributes out-of-the-box).

But there is a mechanism to add extended data to any CAD object, called Extended Entity Data (XED or EED). Unfortunately GDAL/OGR cannot yet generate XEDs in DXF exports (https://github.com/OSGeo/gdal/issues/9472). With XEDs you could store your Linestring attributes in DXF, but due to the lack of a missing label engine in most CAD programs, there is no ready-made way to display the attributes.

I have solved similar issues by generating the necessary DXF code using SQL, which gives me the possibility to attach XEDs to any drawing object. The labeling then takes place manually in BricCAD/AutoCAD using AutoLISP.

Here is an example code using your Postgis table:

SELECT String_Agg(txt,chr(10)) as txt 
FROM 
(SELECT '  0'||chr(10)||'LWPOLYLINE'||chr(10)||
        '  8'||chr(10)||'Main Road'||chr(10)||
        '100'||chr(10)||'AcDbEntity'||chr(10)||
        '  5'||chr(10)||row_number() over() + 131072||chr(10)||
        '100'||chr(10)||'AcDbPolyline'||chr(10)||
        ' 70'||chr(10)||'0'||chr(10)||
        ' 90'||chr(10)||CAST((length(txt) - length(replace(txt,' 20'||chr(10),'')))/4 as INT) ||txt||chr(10)||
        '1001'||chr(10)||'ACAD'||chr(10)||
        '1000'||chr(10)||surface_material as txt 
 FROM 
 (SELECT regexp_replace(regexp_replace(substring(ST_AsText(ST_SnapToGrid((ST_Dump(ST_FORCE2D(geom))).geom,0.01)),12),'\s*([-]*\d+\.*\d*)\s',chr(10)||' 10'||chr(10)||'\1 ','g'),'\s+(\d+\.*\d*)[,)]',chr(10)||' 20'||chr(10)||'\1','g') as txt,surface_material LIMIT 10) as temp) 
FROM cte) as temp2

To get a loadable DXF file, you have to add the GDAL files header.dxf and trailer.dxf (from GDAL\Share directory).

Another way I would try is with the help of QGIS. Here we can add labels to the linestrings and export everything to DXF. However, this also requires some configuration work and unfortunately only produces loose texts that are not linked to the lines.

BTW: if you have AutoCAD or BricsCAD, you can retrieve the XEDs with the following line of LISP:

(cdr (cadadr (assoc -3 (entget (car (entsel)) '("*")))))

...isn't AutoLISP a beautiful language! 😁