2

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!

1
  • Can you show the table schemas for the two tables that you're trying to join?
    – BradHards
    Apr 12, 2013 at 23:56

1 Answer 1

1

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.

7
  • how do you save this query out as a new table?
    – user12711
    Apr 15, 2013 at 16:44
  • @user12711 Answered above.
    – Scro
    Apr 15, 2013 at 17:16
  • I've got a spatial view created using SpatiaLite GUI's query/view composing, with an Inner Join, selecting the Geometry column and view name. But this is just a virtual table isn't it? I need to load it into QGIS or create a spatialite.sqlite db file with this table in there. Is there a way?
    – user12711
    Apr 15, 2013 at 18:02
  • 1
    @user12711 It is indeed a view, but QGIS will load it just fine. If you need to edit the related data, then triggers can handle that. If I remember correctly, the Spatialite GUI version 1.6 or higher View Composer will write the triggers for you. One caveat, you may need to create any new databases with version 1.5 or lower, as QGIS 1.8 chokes on any database I create with the latest SpatiaLite GUI. Hope that helps. If you absolutely need to create a new table, that can be done, but it's a longer answer.
    – Scro
    Apr 15, 2013 at 18:12
  • 1
    QGIS should load the view just like a table if you use the composer or otherwise INSERT the appropriate info into views_geometry_columns. Typically, for materializing views I create the view and make sure it's right, then I create a table with the same schema and use an INSERT statement with SELECT * FROM the_view.
    – Scro
    Apr 16, 2013 at 17:36

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.