1

I have 13 tables containing MultiPolygons in the same projection (27700). I am trying to write an efficient query to get back a Boolean for:

Is one polygon in table A within X meters of any polygon in table B
OR within Y meters of any polygon in table C
...
OR within Z meters of any polygon in table M

The distance tolerance for each table is different (25m, 50m, or 100m) but doesn't change, so hopefully this is indexable.

I'm beginning by writing the query for only one table, but I don't know how to optimise for this case let alone the case for 11 more tables in the mix.

Query:

SELECT lr.title_no, fz.ogc_fid, fz.layer
FROM land_registry_titles lr
    LEFT JOIN flood_zones fz ON ST_DWithin(lr.geom, fz.geom, 100)
WHERE lr.title_no = 'ON270596'
ORDER BY ST_DWithin(lr.geom, fz.geom, 100)
LIMIT 1;

Explain analyze:

 Limit  (cost=41.19..41.20 rows=1 width=26) (actual time=9189.496..9189.498 rows=1 loops=1)
   ->  Sort  (cost=41.19..41.20 rows=2 width=26) (actual time=9189.495..9189.496 rows=1 loops=1)
         Sort Key: (((lr.geom && st_expand(fz.geom, '100'::double precision)) AND (fz.geom && st_expand(lr.geom, '100'::double precision)) AND _st_dwithin(lr.geom, fz.geom, '100'::double precision)))
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Nested Loop Left Join  (cost=10.32..41.18 rows=2 width=26) (actual time=442.262..9189.457 rows=12 loops=1)
               ->  Index Scan using land_registry_titles_title_no_idx on land_registry_titles lr  (cost=0.56..12.60 rows=2 width=503) (actual time=0.053..0.122 rows=11 loops=1)
                     Index Cond: ((title_no)::text = 'ON270596'::text)
               ->  Bitmap Heap Scan on flood_zones fz  (cost=9.75..14.02 rows=1 width=2829) (actual time=693.324..795.498 rows=0 loops=11)
                     Recheck Cond: ((lr.geom && st_expand(geom, '100'::double precision)) AND (geom && st_expand(lr.geom, '100'::double precision)))
                     Filter: _st_dwithin(lr.geom, geom, '100'::double precision)
                     Rows Removed by Filter: 2
                     Heap Blocks: exact=23
                     ->  BitmapAnd  (cost=9.75..9.75 rows=1 width=0) (actual time=0.141..0.141 rows=0 loops=11)
                           ->  Bitmap Index Scan on flood_zones_st_expand_idx  (cost=0.00..4.75 rows=62 width=0) (actual time=0.070..0.070 rows=2 loops=11)
                                 Index Cond: (lr.geom && st_expand(geom, '100'::double precision))
                           ->  Bitmap Index Scan on flood_zones_geom_geom_idx  (cost=0.00..4.75 rows=62 width=0) (actual time=0.051..0.051 rows=2 loops=11)
                                 Index Cond: (geom && st_expand(lr.geom, '100'::double precision))
 Planning Time: 0.384 ms
 Execution Time: 9189.574 ms
(19 rows)

Ultimately I would like end up with one row of output with 12 columns, containing true or false if the given polygon in table A is within the distance tolerance for any and all of the other 12 tables.

Since all geometry(MultiPolygon,27700), ST_DWithin should be using units of meters, I shouldn't need to cast to Geography right?

I am using a custom definition for 27700 to add the NAD grids for more accurate transformations. Below shows it's definitely in meters:

# select proj4text from spatial_ref_sys where srid = 27700;
                                                                                  proj4text
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 +proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +datum=OSGB36 +ellps=airy +units=m +no_defs +nadgrids=/users/craigsnyders/nad-grids/OSTN02_NTv2.gsb
(1 row)

I have already created some indexes on the tables:

// table A
"land_registry_titles_geom_geom_idx" gist (geom) CLUSTER
"land_registry_titles_st_expand_idx" gist (st_expand(geom, 100::double precision))

// table B
"flood_zones_geom_geom_idx" gist (geom)
"flood_zones_st_expand_idx" gist (st_expand(geom, 100::double precision))
2
  • I've added more detail - because the geom columns are actually in 27700 SRID, do they still need a cast go geography? I thought it would use meters, as the unit of that SRID. – craigsnyders Feb 6 at 11:12
  • Rather than adding an addendum that corrects the SRID, you should probably correct the first sentence. – Vince Feb 6 at 12:47
0

Probably the least favourable option here is a JOIN chain. In general, pivoting individual table comparison into columns is better implemented with correlated sub-queries.

In this case, use an EXISTS expression:

SELECT <A>.*
       EXISTS (SELECT 1 FROM <B> WHERE ST_DWithin(<A>.geom, <B>.geom, <dist>)) AS <B>_within,
       ....
FROM   <A>
;

It is obligatory to have spatial indexes on tables <B> - <Z>; I suggest to take @Vince's comment into account as well.

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.