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

[Stephen Woodbridge]

OK, I found this:

http://blog.cleverelephant.ca/2011/02/spatial-partitioning-in-postgis.html

Which leads me to believe that it is not possible, which is too bad, 
because this seems like a natural thing we WOULD want to do somehow.

I wonder if it would be possible doing something like:

check((st_x(geom) between xmin and xmax) and (st_y(geom) between ymin 
and ymax))

but, I'm not sure how I would expose that in the query to get the check 
to kick in.

Regina - Any thoughts on this?

-Steve

On 8/2/2015 4:19 PM, Stephen Woodbridge wrote:
> 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
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users