[GIS] How to save a joined spatialite table when each table contains PKUID fields

qgisqspatialitespatialitesqlite

QGIS 1.8.0 —
QSpatialite 6.0.7 —
Windows XP

I have spatialite tables joined together that I need to save as a single table, but an error-message says that QGIS can't save the table because there are two PKUID field columns.

Spatialite_GUI can delete most spatialite table columns, when I need to do that to manually adjust sqlite column names, but my Qspatialite or QGIS always generates PKUID columns for every table it makes, and that column is always locked (I suppose as a primary key column) The PKUID column can't be deleted by Spatialite_GUI since it's locked, and so I get stuck and have to export everything to CSV files. That's the only workaround I know of, to save the table out as CSV file, manually delete the column from CSV and reload it, then join and "save as".

Is there an easier way to save joined tables in QGIS (that have duplicate columns) or locked columns, rather than manually deleting or renaming identically named columns beforehand with CSV files. This is a chore since sqlite db aren't designed to have their field columns renamed or deleted, and QGIS doesn't automatically rename duplicated column names.

What's the easiest way to save a joined table with duplicate column names in QGIS? Thanks in advance!

Best Answer

If you do your join from the SpatiaLite GUI you can assign aliases to the column names. The easiest way is to use the query composer, as it will handle the spatial meta tables for you.

Edit: The SpatiaLite GUI query/view composer can be invoked by menu File-->Advanced-->Query/View Composer or by right clicking on the table tree. Select your table and join conditions on the 'Main' tab. The SQL statement will update above. The 'Filter' and 'Order' tabs will handle your WHERE and ORDER BY statements. Finally, select 'Create Spatial View' from the 'View' tab, enter the view name, and select the table and column that contain the geometry.

Related Question