3

I've loaded a v.large shapefile (~2GB) into Spatialite in QGIS and a non-spatial table (from a csv, 8 MB) in there as well (via the QSpatialite GUI).

They have successfully been joined in QGIS. They also display as a relation (because there is a one to many relationship between a polygon and 1 or 5 records in the table).

As far as I know there is an index for the polygons.

Whats the most efficient way to create a new joined layer? To pass on to another user.

(Does the Layer|SaveAs dialog work for adding a Spatialite layer to an existing db? It doesn't time out for me as there is too much data)

Improve this question
1
  • after attempting SaveAs, no evidence of progress for an hour, no change in file size of *.splite file. Standard windows install of QGIS 2.18.2
    – BillW
    Commented Dec 22, 2016 at 1:52

2 Answers 2

Reset to default
2

You can't save a layer with a one to many join, you can only export a one to one join.

My advice would be to place your data and QGIS project (with the two tables joined) in a folder, then zip that up to send to someone else.

The reason behind not being able to save a one to many join is that a layer is essentially one table. So if you saved a one to many join, you would either lose rows on the non-spatial table or duplicate rows on the spatial table.

Improve this answer
5
  • OK, thanks. I can reduce the table to a priority match, so that the join becomes 1:1. What is the most efficient way to export this? I haven't been able to create a new file of any joined data. I'd rather create a standalone data layer. Plus I'd like to do more analysis on the data and the presence of the join looks like it will be too slow.
    – BillW
    Commented Dec 22, 2016 at 3:40
  • What sort of analysis are you trying to do? If you have it in Spatialite why not keep in in there and do all your analysis via SQL? If you want to maintain the information available in the data you will have to maintain the two datasets. It's like you're trying to fit a 3 prong plug into a 2 hole socket.
    – Liam G
    Commented Dec 22, 2016 at 3:47
  • Well two purposes, provide something like the simplest and complete dataset to others, plus do analysis myself. It is pretty clear just living with the joined table is pretty heavy on processing power, need to make it into one table. Sounds like I'll have to get hands dirty with SQL. Whats the best interface to apply sql in QGIS to the db?
    – BillW
    Commented Dec 22, 2016 at 5:03
  • There is the spatialite plugin and the db manager (see database menu at the top of qgis window). How big is the dataset?
    – Liam G
    Commented Dec 22, 2016 at 7:31
  • 2GB shp with 8 MB csv table
    – BillW
    Commented Dec 22, 2016 at 8:28
0

I've got into the Processing Toolbox and used the Join Attributes to Table tool in there. It is still not foolproof, the only success I've had is to create a virtual layer with this tool first then import that to Spatialite with the DB Manager. This tool won't save direct to a file.

I've only attempted to join a version of the non-spatial table that has unique records only.

I've imported two versions of the non-spatial table into the spatialite db, if the end user wants to see the 1 to many data they will have to be satisfied with creating a Relation on their machine.

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.