Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I'm not very skilled on using postgis but I face a problem that I must solve, i.e. create tables to load geographical data stored as dxf.

I have a thousand dxf files (3d) with polylines, points and polygons and I want to convert them to a postgis/postgres db.

I'm in windows xp using postgres 9 postgis 2.0.

Gdal/OGR are installed using Osgeo4Win installer. I can write some basic python script to handle data in batch.

Can you please direct me to some useful tutorial/hint on how to face this problem. Thanks in advance

share|improve this question
1  
Commercial FME does the job. I havent got any GDAL/Ogr conversion work with those formats (dgn, dxf,dwg ) –  simplexio Oct 12 '12 at 12:04
    
Do you have to directly import DXF to PostGIS? or can the DXF be converted to Shapefile? - PostGIS has a multi-shapefile import tool in PGAdminIII (uses the PostGIS Shapefile Import/Export Manager) –  Mapperz Oct 12 '12 at 18:54
add comment

2 Answers 2

I'm a little bit late, but hope it helps.

Tuts and books

If you want something learn about ogr, gdal, python and postGIS you can look at first on these pages and book.

Geoprocessing with Python using Open Source GIS

http://www.gis.usu.edu/~chrisg/python/

ogr

http://www.gdal.org/ogr/

ogr2ogr - cheatsheet

http://www.bostongis.com/?content_name=ogr_cheatsheet#41

for an programming example there is an an apitutorial for C++, C and Python

http://www.gdal.org/ogr/ogr_apitut.html

you can also begin here:

http://wiki.osgeo.org/wiki/OSGeo_Python_Library

http://trac.osgeo.org/gdal/wiki/GdalOgrInPython

PostGIS

http://www.bostongis.com/ is allways a good starting point. And also the book "PostGIS in Action" by them is an great guid to PostGIS.

An other nice overview you can find in the following book:

Erik Westra, Python Geospatial Development.

http://www.packtpub.com/python-geospatial-development/book

Now to your specific problem.

First Problem - Geometry

GEOMETRY in dxf vs GEOMETRY in GIS

One problem is the definition of geometries in dxf and in GIS

Geometries in dxf:

Points are allways points, there is no problem here.

Linestrings are linestrings, theres also no problem so far.

But when it comes to polygons there is an problem with in dxf. Polygons will allways represented as LINESTRING!!! Whenever you choose polygon or 3dpoly as option, to create an polygon, it will be an linestring.

If your read the features from an dxf-file with ogr the representation of polygons is ever an representation of an linestring.

Example:

WKT-string: LINESTRING(1 1 1,2 1 1,2 2 1,1 2 1,1 1 1)

But should be:

WKT: POLYGON(1 1 1,2 1 1,2 2 1,1 2 1,1 1 1)

See Wikipedia for WKT-notation

ogr2ogr doesn't change this. You have to do it yourself. We cover this later.

Second Problem - ogr2ogr

The great problem with ogr2ogr is to know what all this options are for and where to learn about. There isn't much help with ogr2ogr --help or ogr2ogr --long-usage on the commandline. So by trial and error I have figured out the following.

ogr2ogr -dsco INIT_WITH_EPSG=yes -lco LAUNDER=yes -lco SPATIAL_INDEX=yes \
    -append -skipfailures -f PostgreSQL \
    PG:"dbname={dbname} user={user} password={password} schemas={schema}" \
    -nln {yourtable} \
    -nlt {geometry}25D \
    -a_srs EPSG:{EPSG-Code}
    {srs-file}.dxf

Here are the options (for Version 1.9):

  • dsco INIT_WITH_EPSG -- don't sure we need this for PostgreSQL, works for Spatialite
  • lco -- layer creation option; LAUNDER=yes all strings to lower and some special charackters will be presented as underbar
  • lco SPATIAL_INDEX=yes; creates an spatial index
  • append (or/and update) -- append to existing table
  • skipfailures -- use this option, otherwise ogr2ogr will stop if it find an geometry that isn't defined in the table
  • f PostgreSQL -- write to PostgreSQL
  • nln -- yourtablename
    • create a table with this name in your db, without! columns, this will ogr2ogr do, it takes the dxf-structure (layer, subclasses, extendedentity, linetype, entityhandle, text)
    • if you you want your own colums, you have to figure out it by yourself
    • without nln it creates an table "entities"
  • nlt -- new layer type; here you can choose between an 2D or an 3D geometry
    • from ogr2ogr --long-usage
    • nlt type: Force a geometry type for new layer. One of NONE, GEOMETRY, POINT, LINESTRING, POLYGON, GEOMETRYCOLLECTION, MULTIPOINT, MULTIPOLYGON, or MULTILINESTRING. Add "25D" for 3D layers Default is type of source layer.
    • default representation for dxf-features is 2D!, but with three coordinates, so ogr2ogr sets Z-values allways to zero
  • a_srs EPSG: -- assign an output EPSG-code

You have to do this for points, linestrings and geometrycollections.

Then you can write an shell script like this one (created by underdark)

for f in `ls *dxf` do
ogr2ogr -dsco INIT_WITH_EPSG=yes -lco LAUNDER=yes -lco SPATIAL_INDEX=yes \
  -append -skipfailures -f PostgreSQL \
  PG:"dbname={dbname} user={user} password={password} schemas={schema}" \
  -nln $f \
  -nlt {geometry}25D \
  -a_srs EPSG:{EPSG-Code}
  $f
done

So then you have an table with points, an table with linestrings and an table with geometrycollections. Now you have to figure out where your polygons are. You can find them with the following SQL-statement. It returns all linestrings where the first point and the last point are equal. You can put this in an SELECT INTO-statement and create an new table. After that you can also clean your linestring table with this statement. It is also possible to create an trigger so it will be a little more comfortable ;-).

SELECT ogc_fid, layer,
       ST_Polygon(ST_GeomFromEWKT(ST_asText(wkb_geometry)), {EPSG-Code}) as the_geom
INTO {your-polygon-table} 
FROM {your-linestring-table}
WHERE ST_StartPoint(wkb_geometry) = ST_EndPoint(wkb_geometry);
share|improve this answer
    
Hello I don't know why but I did't receive any notification for your super-kind answers I have seen them just now. –  Pieri70 May 6 '13 at 10:42
add comment

Hello I don't know why but I did't receive any notification for your super-kind answers I have seen them just now. By the way I made a Python script, I'm not a Python guru, but it works. It asks all the parameters to connect to postgis and a folder to process. It lists all dxf files in a folder (and subfolders) and creates a postgis table with the first, then append all the geometries of the others dxf. It logs all files and processing strings into a txt file. Here it is:

#
    # Script che processa tutti i dxf in una cartella e nelle sottocartelle         #
    # da lanciare da una shell python con il comando execfile('dxftopostgis.py')    #
    # al lancio chiede la cartella da processare, i parametri per connettersi al db #
    # ed il numero del sistema di ccordinate secondo EPSG.                          #
    # Made by Pietro Rossin                                                         #
    #################################################################################
    import os, glob

    try:
        from osgeo import ogr
    except:
        import ogr

    import gdal
    from gdalconst import *

    in_workspace = str(raw_input("Dammi il percorso per la cartella coi dxf: "))
    host = str(raw_input("Server con postgis: "))
    dbname = str(raw_input("Nome db: "))
    user = str(raw_input("Username: "))
    pword = str(raw_input("Password: "))
    epsg = str(raw_input("Numero EPSG?: "))

    #entra nella cartella da processare e crea una lista di folders
    os.chdir(in_workspace)
    listafolders= os.listdir(in_workspace)

    #apre un file su cui loggare le stringhe di processamento
    outfile = open(in_workspace + '\Log_processamento.txt','w')

    #ciclo per processare tutti i file delle sottodirectory
    primo = 1
    for folder in listafolders:
        currentfolder = in_workspace + folder
        #modulo glob per espandere i percorsi con caratteri jolly
        filelist = glob.glob(currentfolder + '\*.dxf')
        for file in filelist:
            #print file
            if primo == 1:
                stringaprocessare = 'ogr2ogr --config DXF_INLINE_BLOCKS FALSE --config DXF_MERGE_BLOCK_GEOMETRIES FALSE -f "PostgreSQL" PG:"host= '\
                + host + " user=" + user + " dbname=" + dbname + " password="+ pword + "\" " +" -lco DIM=3 " + file + " -where \"Layer NOT LIKE 'X%'\"" +" -overwrite -a_srs EPSG:" + epsg + " entities"

            else:
                    stringaprocessare = 'ogr2ogr --config DXF_INLINE_BLOCKS FALSE --config DXF_MERGE_BLOCK_GEOMETRIES FALSE -f "PostgreSQL" PG:"host= '\
                + host + " user=" + user + " dbname=" + dbname + " password="+ pword + "\" " + file + " -where \"Layer NOT LIKE 'X%'\"" +" -append " +  "entities"

            primo = -1
            print 'Sto processando il file '+ file
            os.system(stringaprocessare)
            outfile.write(stringaprocessare + "\n")

    else: 
        outfile.close()
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.