Re: [postgis-users] Is it possible to do constraint exclusion based on spatial extents?
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/20.0);
explain select * from rawdata.streets where st_dwithin(geom,
st_setsrid('POINT(-67.146 18.476)'::geometry, 4326), 50.0/20.0);
explain select * from rawdata.streets where st_dwithin(geom,
st_setsrid('POINT(-81.777 24.558)'::geometry, 4326), 50.0/20.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_...@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 mailto:wood...@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@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Is it possible to do constraint exclusion based on spatial extents?
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/20.0);
explain select * from rawdata.streets where st_dwithin(geom,
st_setsrid('POINT(-67.146 18.476)'::geometry, 4326), 50.0/20.0);
explain select * from rawdata.streets where st_dwithin(geom,
st_setsrid('POINT(-81.777 24.558)'::geometry, 4326), 50.0/20.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_...@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 mailto:wood...@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@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Is it possible to do constraint exclusion based on spatial extents?
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/20.0);
explain select * from rawdata.streets where st_dwithin(geom,
st_setsrid('POINT(-67.146 18.476)'::geometry, 4326), 50.0/20.0);
explain select * from rawdata.streets where st_dwithin(geom,
st_setsrid('POINT(-81.777 24.558)'::geometry, 4326), 50.0/20.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_...@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 mailto:wood...@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@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Is it possible to do constraint exclusion based on spatial extents?
Hey,
I asked this question to the postgres mailing list here:
http://www.postgresql.org/message-id/CAJvUf_szgMLxC4=b6+agy9hgypares2jvb6btkxiff6cxa_...@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 :
> 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@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
[postgis-users] Is it possible to do constraint exclusion based on spatial extents?
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@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
