2

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.

  • I think you need to use the && operations to boost performance for bounding box. revenant.ca/www/postgis/workshop/indexing.html plus VACUUM your data. – Mapperz Mar 6 '18 at 15:15
  • the st_makevalid functions in the st_intersects is probably what is making the query either slow or not use the spatial index. is the make valid function necessary? – ziggy Mar 6 '18 at 15:18
  • 2
    Do NOT use ST_MakeValid on each feature -- This forces a full table scan. If the features are not valid, you should fix that first (once, not with the use of every row). – Vince Mar 6 '18 at 15:19
  • Yes, putting functions in joins makes the query non-sargable, ie, no index can be used. Which data set is this, as a matter of interest. It sounds like one of the address ones. – John Powell Mar 6 '18 at 16:32
  • Thanks everyone. It was the ST_MakeValid function made it much slower. But this query would run for about 2 days to get the results. – Shana Mar 6 '18 at 23:27
3

The index cannot be used, because you're intersecting with modified fields, but the index only knows of the unmodified fields.

As @Vince said, your best option would be to fix the polygons of the tables before intersecting or at insert.

UPDATE titlenumberpolygon SET geom = ST_MakeValid(geom);

or

INSERT INTO titlenumberpolygon (geom) VALUES (ST_MakeValid(geom));

If that is not a possibility, you could create the indices including ST_Make_Valid():

CREATE INDEX valid_geoid ON titlenumberpolygon USING GIST(ST_MakeValid(geom));
  • The technical term is non-sargable, see, for example, this post. I love the word, and wish it were more commonly understood. – John Powell Mar 6 '18 at 16:34
  • Thanks this is it and it is about sargable queries. But still this query would run for about 2 days to get the results. – Shana Mar 6 '18 at 23:25

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.