I am developing a webmapping application in which I used PosgreSQL with PostGIS plugin as my DB.

I have this two tables namely; evidensapp_polystructures and evidensapp_seniangcbr. Both tables contain geometry(multipolygons).

-- Table: evidensapp_seniangcbr

-- DROP TABLE evidensapp_seniangcbr;

CREATE TABLE evidensapp_seniangcbr
  id serial NOT NULL,
  hazard character varying(16) NOT NULL,
  geom geometry(MultiPolygon,32651),
  CONSTRAINT evidensapp_seniangcbr_pkey PRIMARY KEY (id)
ALTER TABLE evidensapp_seniangcbr
  OWNER TO postgres;

-- Index: evidensapp_seniangcbr_geom_id

-- DROP INDEX evidensapp_seniangcbr_geom_id;

CREATE INDEX evidensapp_seniangcbr_geom_id
  ON evidensapp_seniangcbr
  USING gist
ALTER TABLE evidensapp_seniangcbr CLUSTER ON evidensapp_seniangcbr_geom_id;

-- Table: evidensapp_polystructures

-- DROP TABLE evidensapp_polystructures;

CREATE TABLE evidensapp_polystructures
  id serial NOT NULL,
  bldg_name character varying(100) NOT NULL,
  bldg_type character varying(50) NOT NULL,
  brgy_locat character varying(50) NOT NULL,
  municipali character varying(50) NOT NULL,
  province character varying(50) NOT NULL,
  geom geometry(MultiPolygon,32651),
  CONSTRAINT evidensapp_polystructures_pkey PRIMARY KEY (id)
ALTER TABLE evidensapp_polystructures
  OWNER TO postgres;

-- Index: evidensapp_polystructures_geom_id

-- DROP INDEX evidensapp_polystructures_geom_id;

CREATE INDEX evidensapp_polystructures_geom_id
  ON evidensapp_polystructures
  USING gist
ALTER TABLE evidensapp_polystructures CLUSTER ON evidensapp_polystructures_geom_id;

With my query below it takes almost 15mins. to get the result.

-- Executing query:
SELECT  COUNT(evidensapp_polystructures.brgy_locat ) as High,evidensapp_polystructures.brgy_locat as Barangay, evidensapp_polystructures.municipali as Municipality
FROM  evidensapp_seniangcbr  INNER JOIN  evidensapp_polystructures  ON ST_Intersects( evidensapp_seniangcbr.geom ,  evidensapp_polystructures.geom )  WHERE  evidensapp_seniangcbr.hazard  = 'Low' GROUP BY  evidensapp_polystructures.brgy_locat , evidensapp_polystructures.municipali
Total query runtime: 896688 ms.
23 rows retrieved.

It is not acceptable. So, how do I optimize it? I've done spatial indexing and clustering based on this site.


I suggest to calculate the intersection of the geometries beforehand only once and store it in an additional table evidensapp_seniangcbr_polystructures_intersection containing an id, evidensapp_seniangcbr_id pointing to evidensapp_seniangcbr.id and evidensapp_polystructures_id pointing to evidensapp_polystructures.id.

Then you can get rid of the geometry intersection in

INNER JOIN  evidensapp_polystructures ON ST_Intersects( evidensapp_seniangcbr.geom ,  evidensapp_polystructures.geom )````

and instead use a join using the ids

INNER JOIN evidensapp_seniangcbr_polystructures_intersection intersec ON evidensapp_seniangcbr.id = intersec.evidensapp_seniangcbr_id 
INNER JOIN evidensapp_polystructures ON evidensapp_polystructures.id = intersec.evidensapp_polystructures_id

Off course this only works, if your data does not change. Otherwise you need a trigger doing the intersection and filling the evidensapp_seniangcbr_polystructures_intersection table.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.