Is it possible to do constraint exclusion based on spatial extents?

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Is it possible to do constraint exclusion based on spatial extents?

Stephen Woodbridge
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
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to do constraint exclusion based on spatial extents?

Rémi Cura
Hey,
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 <[hidden email]>:
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
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: 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@...
>
> 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 <[hidden email]
> <mailto:[hidden email]>>:
>
>     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
>     [hidden email] <mailto:[hidden email]>
>     http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: 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@...
>>
>>
>> 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 <[hidden email]
>> <mailto:[hidden email]>>:
>>
>>     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
>>     [hidden email] <mailto:[hidden email]>
>>     http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> [hidden email]
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: 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@...
>>>
>>>
>>>
>>> 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 <[hidden email]
>>> <mailto:[hidden email]>>:
>>>
>>>     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
>>>     [hidden email] <mailto:[hidden email]>
>>>     http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>
>>>
>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> [hidden email]
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> [hidden email]
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users