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

[Stephen Woodbridge]

And I also found this from 2008:

http://comments.gmane.org/gmane.comp.gis.postgis/16787

Ok, I will move on to other stuff until I forget this and bring it up 
again in a few years :)

-Steve

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