0

On MSSQL tables/views that have field names with spaces or starting with a number we get the warning 502 re: replacing invalid characters with a _

As we re-injest some of these files into MSSQL and use them in other software we would like to keep the (poorly formatted) field names as otherwise we have to change every reference to the replaced fields. It could also affect AUTHORITY and Confirm etc that's used for asset management.

Is there a switch that allows us to do this?

Here's what I use

ogr2ogr -skipfailures -f "MapInfo File" -lco overwrite=yes -lco launder=no -lco relaxedFieldNameMatch=yes -lco unsetDefault=yes "D:\QG\vw_CONFIRM_PavementCondition.tab" "MSSQL:Server=xxxx;Database=GIS_Test;Trusted_Connection=True" -sql "SELECT * FROM dbo.vw_CONFIRM_PavementCondition" -a_srs EPSG:28354 -t_srs EPSG:28354 -s_srs EPSG:28354

Warning 6: dataset D:\QG\vw_CONFIRM_PavementCondition.tab does not support layer creation option launder

Warning 502: Field name 'Road Pavement Maintenance Grade' contains invalid characters. 'Road_Pavement_Maintenance_Grade' will be used instead.

Warning 502: Field name 'Road Pavement Roughness' contains invalid characters. 'Road_Pavement_Roughness' will be used instead.

Warning 502: Field name 'Road Pavement Profile' contains invalid characters. 'Road_Pavement_Profile' will be used instead.

Warning 502: Field name 'Road Pavement Rutting' contains invalid characters. 'Road_Pavement_Rutting' will be used instead.

Warning 502: Field name 'Road Pavement Overall Condition' contains invalid characters. 'Road_Pavement_Overall_Condition' will be used instead.

Does ogr2ogr.main used within python have some additional functionality not available in the .exe? if so a solution in this would be fine as well.

Improve this question
4
  • 3
    It's bad practice to use column names with spaces, even though MSSQL (and Excel and CSV) will let you your output (-f "mapinfo file") probably will not (shapefiles definitely wont) so I would think that OGR2OGR replaces the spaces with underscores regardless knowing that to keep the spaces is to perpetuate a time bomb in your table.
    – Michael Stimson
    Commented Mar 7, 2019 at 5:21
  • At least field names starting with a number are forbidden in TAB files Column names in MapInfo TAB files cannot start with a number. See page 262 pitneybowes.com/content/dam/support/software/…. Do you definitely need MapInfo format? Some other formats do not set limits to column names.
    – user30184
    Commented Mar 7, 2019 at 6:27
  • 1
    Totally agree @MichaelStimson - I didn't create these tables or the MSSQL...unfortunately it's what I've inherited and while I would like to get it compliant, I was hoping there was a way to force this into the tab but as user30184 says this is impossible so we will have to just change the tables and deal with the consequences in other apps using the current structure. This is what happens when DB creators don't follow convensions...
    – GeorgeC
    Commented Mar 7, 2019 at 10:14
  • @user30184 yes we need mapinfo format for this as otherwise it's to many external references to change, but we will have to change the field mapping in those now and at least the DB will become standards compliant.
    – GeorgeC
    Commented Mar 7, 2019 at 10:16

1 Answer 1

Reset to default
0

There is no 'solution' to this as it is ensuring that the standards are being followed in the new tables. We will however have to change the way that OGR2OGR converts the names as

"80L" becomes "_0L" instead of simply adding a underscore at the start to make it "_80L"

ATM we have to map the field names to make sure that this is done correctly.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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