3

For reasons, I need to create a new column in a vector shapefile with a name longer than 10 characters, and export to mapinfo TAB format. I know .shp's don't support >10 character column names, so I'm looking for an intermediate format that's both editable in QGIS, allows addition of columns after features have been added, and supports >10 character UPPERCASE column names. All of the vector formats seem to support some combination of these, but not all three.

Anybody have this problem in the past, and/or know of a format that supports this use case?

  • I don't use QGIS regularly, so I'll leave this to someone else to confirm, but I know in ESRI software the FileGeodatabase (FGDB) Feature Class vector format can meet all of those requirements and more, and I know I've heard people say that the FGDB is editable in QGIS. I just don't know if it requires an additional plug-in/add-on or if all those same functionality are present when that format is used in QGIS. But from a mainly ESRI user, that would be my recommendation to check into first as from what I know of QGIS it may work for you. – John Jun 10 '15 at 21:37
  • SQLite? (87654321...) – DPSSpatial Jun 10 '15 at 21:43
  • Unfortunately SQLite doesn't seem to allow for uppercase column names, which the tool I'm feeding this into requires. Thanks though, closest I've gotten so far. As for ESRI FGDB, writing to FGDB from QGIS seems to be murky/uncharted territory, but thanks for the suggestion - might be a last-ditch solution. – willidiots Jun 10 '15 at 21:52
  • I don't see that limitation in spatialite. – AndreJ Jun 11 '15 at 5:33
4

Any SQL-compliant spatial database should work, including PostGIS (if you happen to be running a PostGIS server) or SpatiaLite (but note quirks about behavior mentioned below).

Either one will satisfy your criteria:

  1. Identifiers (including column names) can be up to 63 characters in a default PostGIS installation (and this can be increased by changing the server's NAMEDATALEN constant). I can't find any limit to column name length in SQLite docs, and blog posts seem to confirm that there aren't any.
  2. Fields are editable in QGIS.
  3. New columns can be added at any time.
  4. Uppercase column names (UGGH!) can be used, but the behavior is different and complex. See following.

In general, SQL developers will try to avoid using case-sensitive identifiers. However, case-sensitive identifiers can be created using quoting. Without quoting, some databases (Oracle) will fold identifiers to UPPERCASE, some (PostgreSQL/PostGIS) will fold identifiers to lowercase, and some (SQLite/SpatiaLite, SQL Server) are case-preserving but not case-sensitive.

In PostGIS you can force uppercase by using quoted identifiers:

CREATE TABLE foo (
  "COLUMN_NAME_1" int
);

Note that since I never do this kind of table creation in QGIS DB Manager, I don't know whether you can force uppercase column names that way, but you can do so using other management tools or writing the SQL yourself (which you can submit using DB Manager's SQL Editor) as demonstrated above.

This will require you to use quoting in any SQL that you write, as SELECT COLUMN_NAME_1... will be folded to SELECT column_name_1... internally, and column_name_1 does not equal COLUMN_NAME_1.

For SpatiaLite, the behavior is a little strange. SQLite will preserve the case of the column as created (whether or not you quote the identifier), and will also preserve it on export, but in any SQL that you write the column name will be treated in a case-insensitive manner. Any combination of upper and lower case letters will be accepted regardless of quoting, and SQLite will treat COLUMN_NAME_1 and column_name_1 as a conflicting names and will not let you create both columns in the same table. (PostGIS, OTH, will allow names that differ only in case.)

For either PostGIS or SpatiaLite, the case will be preserved if you load the layer in QGIS and export to TAB using the Save As dialog.

More info on identifier case-sensitivity is available in this extremely informative blog post: http://www.alberton.info/dbms_identifiers_and_case_sensitivity.html

NOTE: Original answer only discussed PostGIS. Based on answer by @user30184, I have significantly changed answer to include information about SpatiaLite.

  • This worked, thank you so much for the detailed writeup! I ended up importing the .shp into a Spatialite DB via DB manager, editing the DB offline via sqlitebrowser, then re-importing the table to QGIS and saving to TAB. Looking at the .tab file confirms everything has the proper column name. – willidiots Jun 13 '15 at 0:37
2

I made a test with SQLite / SpatiaLite and for me QGIS does show UPPERCASE or whatever I want to.

enter image description here

  • I got an error attempting this. While QGIS showed the appropriate capitalization in the editor immediately after I created the column, attempting to save it generated an error. It appears that GDAL/whatever QGIS uses under the hood forces everything to lower case before saving, which then generates an error message as the fields don't match QGIS's expectations. – willidiots Jun 13 '15 at 0:38
  • Actually I created my test database also outside QGIS with Spatialite-gui. Generally I think that QGIS or GDAL is doing the right thing with casting everything to lowercase but there are use cases like yours when it can make trouble. – user30184 Jun 13 '15 at 19:43

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.