Sunday, 11 November 2018

postgis - Improve the speed for ST_DWithin


I need to get the outer polygon of the LSOAs within 10 miles of each postcode centroid. There are 180k LSOA polygons in table1 and 2.6 million postcode centroid points in table2.


I have created 2 geography columns (polygon.geog, pois.geog) and one geometry column polygon.coordinates. All of them are indexed using GIST.


I tried running for 10 using the code below which took 1 minute.


SELECT pois.objectid, ST_Multi(ST_Union(polygon.coordinates)) as coords   
into table3
FROM table1 as polygon

INNER JOIN table2 as pois
ON ST_DWithin(polygon.geog,pois.geog, 10* 1609.34,false)
group by pois.objectid
limit 10;

Query plan, enter image description here


[
{
"Plan": {
"Startup Cost": 0.84,

"Plans": [
{
"Partial Mode": "Simple",
"Startup Cost": 0.84,
"Plans": [
{
"Startup Cost": 0.84,
"Plans": [
{
"Startup Cost": 0.43,

"Scan Direction": "Forward",
"Plan Width": 39,
"Node Type": "Index Scan",
"Plan Rows": 3011500,
"Relation Name": "table2",
"Alias": "pois",
"Parallel Aware": false,
"Parent Relationship": "Outer",
"Total Cost": 11352214.75,
"Index Name": "postcode_pkey"

},
{
"Filter": "(((pois.geog)::geography && _st_expand(geog, '16093.4'::double precision)) AND _st_dwithin(geog, (pois.geog)::geography, '16093.4'::double precision, false))",
"Startup Cost": 0.41,
"Scan Direction": "NoMovement",
"Plan Width": 11560,
"Node Type": "Index Scan",
"Index Cond": "(geog && _st_expand((pois.geog)::geography, '16093.4'::double precision))",
"Plan Rows": 1,
"Relation Name": "table1",

"Alias": "polygon",
"Parallel Aware": false,
"Parent Relationship": "Inner",
"Total Cost": 78.31,
"Index Name": "gidgeo"
}
],
"Node Type": "Nested Loop",
"Plan Rows": 181100,
"Join Type": "Inner",

"Parallel Aware": false,
"Parent Relationship": "Outer",
"Plan Width": 5783,
"Total Cost": 247219664.75
}
],
"Node Type": "Aggregate",
"Strategy": "Sorted",
"Plan Rows": 181100,
"Parallel Aware": false,

"Group Key": [
"pois.objectid"
],
"Parent Relationship": "Outer",
"Plan Width": 39,
"Total Cost": 247223286.75
}
],
"Node Type": "Limit",
"Plan Rows": 1,

"Parallel Aware": false,
"Plan Width": 39,
"Total Cost": 1365.96
}
}
]

Is it possible to improve the speed of this code?




No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...