0

I have a table of geometries (mixed MultiPolygons and Polygons), I want to check if a point is contained by any of the geometries in the table. I don't care about how many, nor I want to retrieve any information, all I care about is a boolean. Is there a way to speed up this query, given the circumstances?

SELECT N>0 as on_land FROM(
SELECT COUNT(*) N FROM lands l WHERE 
ST_Contains(l.geom,ST_SetSRid(ST_Makepoint(47.3,19.4), 4326))) B

(the union of the 'lands' table is all landmass on earth)

3
  • 1
    do you have a spatial index on l.geom?
    – JGH
    Commented May 29, 2018 at 14:49
  • yes, i'm not even sure what i'm asking to be honest. This query runs under 16ms, sometimes 1ms. Commented May 29, 2018 at 15:00
  • 1
    16 ms?! There is nothing to improve here
    – JGH
    Commented May 29, 2018 at 15:22

1 Answer 1

2

I think the count(*) is not necessary if you are interested in the existence of a container polygon. My solution would be:

select 1 from lands 
  where st_contains(geom, st_setsrid(st_makepoint(47.3,19.4), 4326)) 
  LIMIT 1;

This returns nothing if no polygon contains point and "1" otherwise. I tested the queries on a table having ~40000 polygons. Compare the query plans, yours:

"Subquery Scan on b  (cost=149.07..149.09 rows=1 width=8)"
"  ->  Aggregate  (cost=149.07..149.08 rows=1 width=0)"
"        ->  Bitmap Heap Scan on planet_osm_polygon l  (cost=4.57..149.04 rows=13 width=0)"
"              Recheck Cond: (way ~ '0101000020E6100000CB08B99C5CC651C0271CD5447A284540'::geometry)"
"              Filter: _st_contains(way, '0101000020E6100000CB08B99C5CC651C0271CD5447A284540'::geometry)"
"              ->  Bitmap Index Scan on planet_osm_polygon_index  (cost=0.00..4.57 rows=39 width=0)"
"                    Index Cond: (way ~ '0101000020E6100000CB08B99C5CC651C0271CD5447A284540'::geometry)"

and mine:

"Limit  (cost=0.28..13.39 rows=1 width=0)"
"  ->  Index Scan using planet_osm_polygon_index on planet_osm_polygon  (cost=0.28..170.71 rows=13 width=0)"
"        Index Cond: (way ~ '0101000020E6100000CB08B99C5CC651C0271CD5447A284540'::geometry)"
"        Filter: _st_contains(way, '0101000020E6100000CB08B99C5CC651C0271CD5447A284540'::geometry)"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.