I need to get the intersection of two polygons and both sides use the same dataset (
titlenumberpolygon - UK National polygons). PostGIS is used in the analysis.
First a spatial index was created which took 27 mins. There are 30 million polygons.
CREATE INDEX geoid ON titlenumberpolygon USING GIST(geom)
The following code is used and for both the tables the polygon data is coming from titlenumberpolygon table.
SELECT tb2.UPRN,tb1.titleno INTO AB_VOA_Onwer1 FROM ( SELECT t1.titleno, tp.* FROM Ownership as t1 INNER JOIN titlenumberpolygon as tp ON t1.titleno=tp.title_no) as tb1 INNER JOIN( SELECT AU.UPRN,AU.Postcode,tp.* FROM AB_VOA_UPRN as AU INNER JOIN titlenumberpolygon as tp ON AU.inspiredid=tp.poly_id) as tb2 ON ST_Intersects(ST_MakeValid(tb1.geom),ST_MakeValid(tb2.geom));
It takes over 2 hours to run for one postcode and there are 500,000 postcodes in the table.
How can I optimise this code? Maybe a different spatial indexing method as I am using the same table in the intersection.