1

I've created an SQL script that selects a given set of polygons ("v_rag_RenouvellementClaim") that are completeley within a specified buffer ("v_rag_RenouvellementClaimGrtr25000_Buffer") using PostGIS ST_Within. The code works but is very slow to display as a table or as a layer in QGIS 3 (over 3 minutes). The result outputs just under 20000 records.
Is there a more efficient way to get this to display/run?

I'm using QGIS 3, PostgreSQL 9.6 and PostGIS 2.3

CREATE OR REPLACE VIEW "GestionPropriete_Staging_RnvlmntClaims"."v_rag_RecClaim_Within4500mBuffer_25000" as
SELECT 
    "v_rag_RenouvellementClaimGrtr25000_Buffer".tit_no || '_' || "v_rag_RenouvellementClaim".tit_no as uid, --concatenates the Allocation Claim number to the Reipient Claim number as UID
    "v_rag_RenouvellementClaimGrtr25000_Buffer".tit_no as "Alloc_Claim",
    "v_rag_RenouvellementClaimGrtr25000_Buffer".geom,
    "v_rag_RenouvellementClaimGrtr25000_Buffer"."1_renewal_cre_cu" as "Alloc_Credit",
     "v_rag_RenouvellementClaim".tit_no  as "Rec_Claim",
     "v_rag_RenouvellementClaim"."1_renewal_cre_cu",
     ABS("v_rag_RenouvellementClaim"."1_renewal_cre_cu")  as "Rec_abs_Credit_1stRenw" -- returns the absolute value of the 1st renewal credit value.
    FROM "GestionPropriete"."v_rag_RenouvellementClaim", "GestionPropriete"."v_rag_RenouvellementClaimGrtr25000_Buffer"
        WHERE "v_rag_RenouvellementClaim"."1_renewal_cre_cu" < 0 AND -- selects the values where the 1st renewal cost is less than 0
            st_within("v_rag_RenouvellementClaim".geom, "v_rag_RenouvellementClaimGrtr25000_Buffer".geom)

  • do you have spatial indexes? – JGH Apr 30 at 15:45
  • Good point, I've made sure the source data set has a spatial index. Would the fact that this script references two views rather than actual tables have an impact on the load times? – Frederic Apr 30 at 16:32
  • Try running EXPLAIN (analyze, buffers) on this query to see the bottleneck. – JGH Apr 30 at 19:02
  • 1
    You could also use a materialised view rather than a view if this is only processed occasionally. – tomtomnz Apr 30 at 19:12

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.