I have a polygon shapefile layer, Property, with property ownership parcels and a SQLite database with projects. My goal is to highlight the parcels where an entry in the Projects database and show a label or map tip with the list of project ids for the parcel.
I have created a virtual layer using an SQL query:
SELECT p.objectid, p.pid, GROUP_CONCAT(j.ProjectID) AS ProjectIDs FROM Property p, Projects j WHERE j.PID IS NOT NULL AND j.PID = p.pid GROUP BY p.objectid;
I tested with a small region from the Property layer (including the geometry), and it appears to work well, however, when I use the full Property shapefile it does not. It takes several minutes to add the virtual layer with the listed query and every operation with it requires similar or more time.
The Property layer contains approx 210000 polygons and 11 fields. The Projects database has approx 7500 records with 14 fields. The query will result in about 1640 records. I have a spatial index for the Property layer.
I have tried the above with the Property layer as a spatialite database with similar results.
With sqlite I am able to attach the spatialite Property database to the Projects one and create a table in a fraction of a second using:
CREATE TABLE proj_by_parcel AS SELECT p.objectid, p.pid, GROUP_CONCAT(j.ProjectID) AS ProjectIDs FROM nsprd.Property p, Projects j WHERE j.PID IS NOT NULL AND j.PID = p.pid GROUP BY p.objectid;
I have general database and QGIS knowledge.
Is there a different approach that I should be taking in QGIS?
Maybe it will just be best to recreate a static data set periodically.