# Checking if point is contained by any geometry

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)

• 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
• 16 ms?! There is nothing to improve here
– JGH
Commented May 29, 2018 at 15:22

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)"
``````