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;
[
{
"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