[postgis-users] Is it possible to do constraint exclusion based on spatial extents?

[Stephen Woodbridge]

Hi Remi,

So I got this far, without any success:

-- get the bbox for each table

select st_extent(geom) from rawdata.streets_91;
   -- 'BOX(-87.60196 24.54513,-80.0323 31.00091)'
select st_extent(geom) from rawdata.streets_92;
   -- 'BOX(-88.47265 30.2251,-80.84104 35.00732)'
select st_extent(geom) from rawdata.streets_93;
   -- 'BOX(-84.32147 32.08265,-76.02063 36.58729)'

-- set the CHECK constraint (using diagonal line to define bbox)

ALTER TABLE rawdata.streets_91 ADD CONSTRAINT bbox_chk
   CHECK (geom && st_setsrid('LINESTRING(-87.60196 24.54513,-80.0323 
31.00091)'::text, 4326));
ALTER TABLE rawdata.streets_92 ADD CONSTRAINT bbox_chk
   CHECK (geom && st_setsrid('LINESTRING(-88.47265 30.2251,-80.84104 
35.00732)'::text, 4326));
ALTER TABLE rawdata.streets_93 ADD CONSTRAINT bbox_chk
   CHECK (geom && st_setsrid('LINESTRING(-84.32147 32.08265,-76.02063 
36.58729)'::text, 4326));

-- turn on constraint exclusion for union and inheritance
SET constraint_exclusion = partition;

explain select * from rawdata.streets where st_dwithin(geom, 
st_setsrid('POINT(-84.414 33.883)'::geometry, 4326), 50.0/111120.0);

explain select * from rawdata.streets where st_dwithin(geom, 
st_setsrid('POINT(-67.146 18.476)'::geometry, 4326), 50.0/111120.0);

explain select * from rawdata.streets where st_dwithin(geom, 
st_setsrid('POINT(-81.777 24.558)'::geometry, 4326), 50.0/111120.0);

So only the first and third select should hit and they should only hit 
one table based on constraint exclusion.

But I am not showing any checking of constraints in the explain output.
Maybe it is touchy about the way you set/test the constraints.

Anyone know if this is possible and how to set it up?

Thanks,
   -Steve

On 8/2/2015 3:36 PM, Rémi Cura wrote:
> Hey,
> I asked this question to the postgres mailing list here:
> http://www.postgresql.org/message-id/CAJvUf_szgMLxC4=b6+AgY9HgyPaRes2JVb6bTKXiFF6CXa_epw@mail.gmail.com
>
> I would be very interested if you find a solution (I did not).
>
> Cheers,
> Rémi-C
>
> 2015-08-02 21:04 GMT+02:00 Stephen Woodbridge <woodbri at swoodbridge.com
> <mailto:woodbri at swoodbridge.com>>:
>
>     Hi,
>
>     I know I can setup table inheritance and constraint exclusion based
>     on say the state field. But I would like to do this based on geom
>     and the table extents. How would I do this?
>
>     Say for example, I create a roads table, then load data into
>     roads_area1, roads_area2, etc and these inherit from roads.
>
>     For the state abbrv. I might have:
>
>     ALTER TABLE roads_area1 ADD CONSTRAINT chk
>         CHECK (state IN ('MA', 'ME', 'NH'));
>
>     So doing the same based on geom column might look like:
>
>     ALTER TABLE roads_area1 ADD CONSTRAINT bbox_chk
>     CHECK (geom && <????>);
>
>     So does this work?
>
>     Do I need to do anything special to get postgresql to using
>     constraint exclusion? What?
>
>     How would I set <????>?
>
>     These tables are create once and query often, so the extents are not
>     changing after they are loaded.
>
>     Thanks,
>        -Steve W
>     _______________________________________________
>     postgis-users mailing list
>     postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>     http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>