1

I'm trying to convert MapInfo tab file to ESRI shapefile using ogr2ogr either with SQLite dialect or without. When using sqlite dialect,

ogr2ogr -overwrite -f "ESRI Shapefile"  -dialect sqlite -sql "select * from _MigrationIlot where not (geometry is null or IsValid(geometry))" "%Chem%\MigrationIlotErreur.shp" "%Chem%\_MigrationIlot.tab"

the resulting shapefile (the DBF file to be precise) will have a different structure than the original dat file and I will then be unable to open it in my Delphi program (with a TTable component) nor can I open it with my usual database desktop (Borland database desktop).

Whereas without using SQLite dialect,

ogr2ogr -overwrite -f "ESRI Shapefile" "%Chem%\MigrationIlot.shp" "%Chem%\_MigrationIlot.tab"

everything is fine (it's not what I want but the DBF file is readable). After some investigations with a Hexadecimal Editor, I noticed that Numeric field type in the DBF file are defined as N 24,15. Using -lco RESIZE=YES doesn't help: still N 24,15.

According to DBF specifications found here: http://www.clicketyclick.dk/databases/xbase/format/data_types.html or here : http://linux.techass.com/projects/xdb/xbasedocs/xbase_toc.html, it seems that Numeric fields size in DBF files shouldn't be greater than 18 digits.

As I'm unable to read these DBF files in my program, is there a way around this?

1

I do not believe that it is generally possible to maintain the schema with SQL queries because the query itself can alter the schema. See for example this:

ogrinfo migrationparcelle.tab -sql "select RNTYPESOL*0.1 as test  from migrationparcelle" -dialect sqlite
INFO: Open of `migrationparcelle.tab'
      using driver `MapInfo File' successful.

Layer name: SELECT
Geometry: None
Feature Count: 2
Layer SRS WKT:
(unknown)
test: Real (0.0)
OGRFeature(SELECT):0
  test (Real) = 13.2

OGRFeature(SELECT):1
  test (Real) = 8.9

The SQLite dialect is introducing another issue here because when that path is used the source data is actually going through SQLite database and SQLite does not support the concept of defining number fields like SURFEPSPEF: Real (8.2) (http://www.sqlite.org/datatype3.html).

It seems that the OGR SQL dialect can handle your case:

ogr2ogr -f "ESRI Shapefile" -sql "select * from migrationparcelle" schematest.shp MigrationParcelle.tab

ogrinfo -al -so schematest.shp
INFO: Open of `schematest.shp'
      using driver `ESRI Shapefile' successful.

Layer name: schematest
Metadata:
  DBF_DATE_LAST_UPDATE=2015-10-13
Geometry: Polygon
Feature Count: 2
Extent: (523944.654000, 6930912.262000) - (524330.763000, 6931304.045000)
Layer SRS WKT:
PROJCS["Lambert_Conformal_Conic",
    GEOGCS["GCS_Unknown",
        DATUM["North_American_Datum_1983",
            SPHEROID["GRS_80",6378137,298.257222101]],
        PRIMEM["Greenwich",0],
        UNIT["Degree",0.017453292519943295]],
    PROJECTION["Lambert_Conformal_Conic_2SP"],
    PARAMETER["standard_parallel_1",44],
    PARAMETER["standard_parallel_2",49.00000000001],
    PARAMETER["latitude_of_origin",46.5],
    PARAMETER["central_meridian",3],
    PARAMETER["false_easting",700000],
    PARAMETER["false_northing",6600000],
    UNIT["Meter",1.0]]
CAMPAGNE: String (4.0)
NOMPARC: String (35.0)
CODE: String (4.0)
IDEXT: String (35.0)
NOMEXP: String (50.0)
SIRET: String (35.0)
SURFACE: Real (10.4)
INSEE: String (5.0)
ILOT: String (3.0)
RNTYPESOL: Integer (9.0)
ANALSOL: Integer (9.0)
CULTURE_DO: String (25.0)
CULTURE: String (25.0)
BOTAP: String (35.0)
VARIETE_DO: String (25.0)
VARIETE: String (25.0)
DATESEMIS: Date (10.0)
DATESEMISS: Date (10.0)
DATEREC: Date (10.0)
DATERECS: Date (10.0)
GESTRESIDU: Integer (9.0)
GESTREPOUS: Integer (9.0)
DEST: Integer (9.0)
DATEDEST: Date (10.0)
MODEDEST: Integer (9.0)
RDTOBJ: Real (8.2)
UNITERDT: String (50.0)
SURFEPSPEF: Real (8.2)
SURFEPSPEL: Real (8.2)
SURFEPSPEA: Real (8.2)
ST5: Real (8.2)
ST20: Real (8.2)
ST50: Real (8.2)
ST100: Real (8.2)
CIPCULT_DO: String (25.0)
CIPLIBCULT: String (25.0)
CIPDATESEM: Date (10.0)
CIPDATEDES: Date (10.0)
CIPMODEDES: Integer (9.0)
CIPCOMM: String (254.0)
CISCULT_DO: String (25.0)
CISLIBCULT: String (25.0)
CISDATESEM: Date (10.0)
CISDATEDES: Date (10.0)
CISMODEDES: Integer (9.0)

Another option could be to use a workaround by converting your data from tab into shp so that the dbf file gets created and then update the shapefile

ogr2ogr -f "ESRI Shapefile" -update -overwrite -sql "select * from migrationparcelle" schematest.shp MigrationParcelle.tab -dialect SQLite

With my GDAL that command leads to crash of the ogr2ogr program but it seems that the crash happens after the data has been written correctly into the shapefile. With OGR SQL dialect update succeeds without ogr2ogr crash but that dialect works even directly without the workaround.

  • ogr2ogr update works on my setup with or without sqlite dialect (no crash). Unfortunately, although the first conversion produces a valid dbf, the "update" does indeed (only) update with ogr sql dialect whereas it updates AND modifies the fields type with Sqllite dialect, leading to the same result as before. – David HILPIPRE Oct 13 '15 at 12:46
1

The first part of @user30184's answer made me think I should eventually go differently about this. I think I will try to produce 2 datasets : an intermediate first (using sqlite) will contains only an identifier for all valid (or empty) geometries ("CODE" in the sample data).

ogr2ogr -overwrite -f "ESRI Shapefile"  -dialect sqlite -sql "select CODE from MigrationParcelle where geometry is not null or IsValid(geometry)" "%Chem%\Intermediate.shp" "%Chem%\MigrationParcelle.tab"

The second and final dataset (using ogr sql) will be the result of the conversion of the mapinfo table, joined on the identifier of the intermediate dataset. I'm not sure what kind of join ogr sql performs though. I still need to experiment on this.

ogr2ogr -overwrite -f "ESRI Shapefile"  -sql "select * from MigrationParcelle mp left join '%Chem%\Intermediate.shp' mpv on mpv.code = mv.code" "%Chem%\MigrationParcelle.shp" "%Chem%\MigrationParcelle.tab"

The second command still gives me an error : Intermediate does exist in the directory but I get : ERROR 1: SELECT from table X:\TestExport\exe\Dossier_migration\50819703500027\Intermediate.shp failed, no such table/featureclass. I'm pretty sure i'm close, just a little help...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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