2

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.

  • You can try adding indexes on the IDs too. Make sure you have selected the unique identifier column to avoid computing a new key – JGH Apr 23 '18 at 11:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.