Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Performance Tests on Distance
-- Large primary geometry
select st_distance(e.geom, v.geom)
from ed_2017 e, va_ply_17 v
where e.ed_abbrev = 'KLA' and v.ed_abbrev != 'KLA';
-- 25s
select _ST_DistanceRectTree(e.geom, v.geom)
from ed_2017 e, va_ply_17 v
where e.ed_abbrev = 'KLA' and v.ed_abbrev != 'KLA';
-- 6s
select _ST_DistanceRectTreeCached(e.geom, v.geom)
from ed_2017 e, va_ply_17 v
where e.ed_abbrev = 'KLA' and v.ed_abbrev != 'KLA';
-- 2.7s
---------------------------------------------------------------------
-- small primary geometry (5pt)
select sum(st_distance(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v
where e.edva_code = 'NEW030' and v.ed_abbrev != 'NEW030';
-- 320ms
select Sum(_ST_DistanceRectTree(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v
where e.edva_code = 'NEW030' and v.ed_abbrev != 'NEW030';
-- 454ms
select Sum(_ST_DistanceRectTreeCached(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v
where e.edva_code = 'NEW030' and v.ed_abbrev != 'NEW030';
-- 432ms
---------------------------------------------------------------------
-- medium primary geometry (21pt)
select sum(st_distance(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v
where e.edva_code = 'VFA064' and v.ed_abbrev != 'VFA064';
-- 480ms
select Sum(_ST_DistanceRectTree(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v
where e.edva_code = 'VFA064' and v.ed_abbrev != 'VFA064';
-- 480ms
select Sum(_ST_DistanceRectTreeCached(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v
where e.edva_code = 'VFA064' and v.ed_abbrev != 'VFA064';
-- 470ms
---------------------------------------------------------------------
-- All pairs between 25 and 30 pts
select sum(st_distance(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v
where (st_npoints(e.geom) between 25 and 30)
and (st_npoints(v.geom) between 25 and 30);
-- 2.5s
select Sum(_ST_DistanceRectTree(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v
where (st_npoints(e.geom) between 25 and 30)
and (st_npoints(v.geom) between 25 and 30);
-- 8.7s
select Sum(_ST_DistanceRectTreeCached(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v
where (st_npoints(e.geom) between 25 and 30)
and (st_npoints(v.geom) between 25 and 30);
-- 7.6s
---------------------------------------------------------------------
-- All pairs between 100 and 110 pts
select sum(st_distance(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v
where (st_npoints(e.geom) between 100 and 120)
and (st_npoints(v.geom) between 100 and 120);
-- 1s
select Sum(_ST_DistanceRectTree(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v
where (st_npoints(e.geom) between 100 and 120)
and (st_npoints(v.geom) between 100 and 120);
-- 3s
select sum(st_distance(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v
where (st_npoints(e.geom) between 100 and 120)
and (st_npoints(v.geom) between 100 and 120);
-- 1s
select Sum(_ST_DistanceRectTree(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v
where (st_npoints(e.geom) between 100 and 120)
and (st_npoints(v.geom) between 100 and 120);
-- 3s
-- All pairs between 1000 and 2000 pts
select sum(st_distance(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v
where (st_npoints(e.geom) between 1000 and 2000)
and (st_npoints(v.geom) between 1000 and 2000);
-- 25s
select Sum(_ST_DistanceRectTree(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v
where (st_npoints(e.geom) between 1000 and 2000)
and (st_npoints(v.geom) between 1000 and 2000);
-- 7s
select Sum(_ST_DistanceRectTreeCached(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v
where (st_npoints(e.geom) between 1000 and 2000)
and (st_npoints(v.geom) between 1000 and 2000);
-- 6s
---------------------------------------------------------------------
-- All pairs between 2500 and 5000 pts
select sum(st_distance(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v
where (st_npoints(e.geom) between 2500 and 5000)
and (st_npoints(v.geom) between 2500 and 5000);
-- 96s
select Sum(_ST_DistanceRectTree(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v
where (st_npoints(e.geom) between 2500 and 5000)
and (st_npoints(v.geom) between 2500 and 5000);
-- 9s
select Sum(_ST_DistanceRectTreeCached(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v
where (st_npoints(e.geom) between 2500 and 5000)
and (st_npoints(v.geom) between 2500 and 5000);
-- 8s
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.