One of the things that really frustrated me about the KNN GIST distance box box centroid operators that came in PostgreSQL 9.1 and PostGIS 2.0 was the fact that one of the elements needed to be constant to take advantage of the index. In PostGIS speak, this meant you couldn't put it in the FROM clause and could only enjoy it in one of two ways.
SELECT s.pid FROM ch10.land AS l WHERE l.land_type = 'apartment' ORDER BY ST_GeomFromText('POINT(234054 902182)',26986) <-> l.geom LIMIT 10;
This approach forces a query for each row and each subquery in row references a constant geometry which is the current row thus is constant enoughWhat is the closest aparmentment to each educational parcel?
SELECT l.pid AS reference_parcel , (SELECT pid FROM ch10.land As s ORDER BY l.geom <#> s.geom LIMIT 1) As closest_parcel FROM ch10.land AS l WHERE l.land_type = 'education';
And if you needed to get more than one row back, like what is the N closest (N > 1), it was some really ugly code you had to resort to if you wanted to use KNN gist. So ugly you'd be better off just sticking with ST_DWithin and guess an expansion to check.
But in 9.3 we have LATERAL which allows us to put our subquery back in the FROM clause and still utilize a KNN GIST index and adorn with as many additional computed columns as we want. It looks like this:
SELECT l.pid, s.pid As closest_apartment, s.dist_m FROM ch10.land As l CROSS JOIN LATERAL (SELECT pid, ST_Distance(b.geom,l.geom) as dist_m FROM ch10.land As b WHERE b.land_type = 'apartment' ORDER BY b.geom <-> l.geom LIMIT 10 ) As s WHERE l.land_type = 'education' ORDER BY l.pid, dist_m;
The explain plan demonstrates the spatial index is being used
Sort (cost=15074.71..15081.26 rows=2620 width=24) (actual time=129.435..129.495 rows=2620 loops=1) Output: l.pid, b.pid, (st_distance(b.geom, l.geom)) Sort Key: l.pid, (st_distance(b.geom, l.geom)) Sort Method: quicksort Memory: 301kB -> Nested Loop (cost=0.56..14925.95 rows=2620 width=24) (actual time=0.411..119.107 rows=2620 loops=1) Output: l.pid, b.pid, (st_distance(b.geom, l.geom)) -> Index Scan using idx_land_land_type on ch10.land l (cost=0.29..178.63 rows=262 width=277) (actual time=0.040..0.163 rows=262 loops=1) Output: l.pid, l.bldg_val, l.land_val, l.other_val, l.total_val, l.addr_num, l.full_str, l.land_type, l.units, l.geom Index Cond: ((l.land_type)::text = 'education'::text) -> Limit (cost=0.28..56.09 rows=10 width=277) (actual time=0.176..0.452 rows=10 loops=262) Output: b.pid, (st_distance(b.geom, l.geom)), ((b.geom <-> l.geom)) -> Index Scan using idx_land_geom_gist on ch10.land b (cost=0.28..5882.65 rows=1054 width=277) (actual time=0.174..0.449 rows=10 loops=262) Output: b.pid, st_distance(b.geom, l.geom), (b.geom <-> l.geom) Order By: (b.geom <-> l.geom) Filter: ((b.land_type)::text = 'apartment'::text) Rows Removed by Filter: 224 Total runtime: 129.687 ms