CODEWITHSUNDEEP
geospatialpostgis
Nguyen Ngoc Tuan
Nguyen Ngoc Tuan

Reputation: 351

ST_contains does not work correctly when filterin

I have following table and data.

create table test ( id bigserial not null,
geo geometry not null );

insert
    into
    test(geo)
values ('MULTIPOLYGON (((0 0, 0 0, 0 7, 0 7, 0 0)), ((0 0, 0 7, 7 7, 7 0, 0 0)), ((0 0, 7 0, 7 0, 0 0, 0 0)), ((7 7, 7 7, 7 0, 7 0, 7 7)), ((0 7, 0 7, 7 7, 7 7, 0 7)), ((0 0, 7 0, 7 7, 0 7, 0 0)))'),
('POLYGON ((0 0, 5 0, 5 5, 0 5, 0 0))'),
('POLYGON ((2 2, 5 2, 5 5, 2 5, 2 2))');

select * from test;

id|geo                                                                                                                                                                                        |
--|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
 5|MULTIPOLYGON (((0 0, 0 0, 0 7, 0 7, 0 0)), ((0 0, 0 7, 7 7, 7 0, 0 0)), ((0 0, 7 0, 7 0, 0 0, 0 0)), ((7 7, 7 7, 7 0, 7 0, 7 7)), ((0 7, 0 7, 7 7, 7 7, 0 7)), ((0 0, 7 0, 7 7, 0 7, 0 0)))|
 6|POLYGON ((0 0, 5 0, 5 5, 0 5, 0 0))                                                                                                                                                        |
 7|POLYGON ((2 2, 5 2, 5 5, 2 5, 2 2))                                                                                                                                                        |

following query (Q) should return all rows

select
    *
from
    test t
where
    st_contains('MULTIPOLYGON (((0 0, 0 0, 0 7, 0 7, 0 0)), ((0 0, 0 7, 7 7, 7 0, 0 0)), ((0 0, 7 0, 7 0, 0 0, 0 0)), ((7 7, 7 7, 7 0, 7 0, 7 7)), ((0 7, 0 7, 7 7, 7 7, 0 7)), ((0 0, 7 0, 7 7, 0 7, 0 0)))',
    geo);

id|geo                                                                                                                                                                                        |
--|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
 5|MULTIPOLYGON (((0 0, 0 0, 0 7, 0 7, 0 0)), ((0 0, 0 7, 7 7, 7 0, 0 0)), ((0 0, 7 0, 7 0, 0 0, 0 0)), ((7 7, 7 7, 7 0, 7 0, 7 7)), ((0 7, 0 7, 7 7, 7 7, 0 7)), ((0 0, 7 0, 7 7, 0 7, 0 0)))|
 6|POLYGON ((0 0, 5 0, 5 5, 0 5, 0 0))                                                                                                                                                        |

Because following constraint returns true.

select
    st_contains('MULTIPOLYGON (((0 0, 0 0, 0 7, 0 7, 0 0)), ((0 0, 0 7, 7 7, 7 0, 0 0)), ((0 0, 7 0, 7 0, 0 0, 0 0)), ((7 7, 7 7, 7 0, 7 0, 7 7)), ((0 7, 0 7, 7 7, 7 7, 0 7)), ((0 0, 7 0, 7 7, 0 7, 0 0)))',
    'POLYGON ((2 2, 5 2, 5 5, 2 5, 2 2))');

What is wrong here with the query Q above?

Upvotes: 1

Views: 602

Answers (1)

JGH
JGH

Reputation: 17906

The input geometry is invalid, and so is the result as per the doc:

So ST_Contains(A,B) implies ST_Within(B,A) except in the case of invalid geometries where the result is always false regardless or not defined.

WITH test(geo) as (
   values ('MULTIPOLYGON (((0 0, 0 0, 0 7, 0 7, 0 0)), ((0 0, 0 7, 7 7, 7 0, 0 0)), ((0 0, 7 0, 7 0, 0 0, 0 0)), ((7 7, 7 7, 7 0, 7 0, 7 7)), ((0 7, 0 7, 7 7, 7 7, 0 7)), ((0 0, 7 0, 7 7, 0 7, 0 0)))'),
  ('POLYGON ((0 0, 5 0, 5 5, 0 5, 0 0))'),
  ('POLYGON ((2 2, 5 2, 5 5, 2 5, 2 2))'))
select st_isvalid(geo), st_isvalidreason(geo) from test;

 st_isvalid |             st_isvalidreason
------------+-------------------------------------------
 f          | Too few points in geometry component[0 7]
 t          | Valid Geometry
 t          | Valid Geometry

That being said, you may want to read carefully the doc on st_contains and st_covers as there are subtleties when the geometries share an edge.

Upvotes: 1

Related Questions