Actually we are in the process of doing something similar On Wed, Apr 1, 2015 at 11:03 AM, Rémi Cura <remi.c...@gmail.com> wrote:
> (cross-post from postgres list) > Hey dear list, > > I'd like to partition geographical (geometry) data with postgres mechanism. > (my usage is in fact related to pointcloud, but I use geometry as a work > around) > From example I read on constraint, nothing should prevent it from working > Here is a self contained example, the planner doesn"t seems to use the > constraint_exclusion mechanism, whatever the constraint > > Thanks, > Cheers, > Rémi-C > > ------ > > CREATE SCHEMA IF NOT EXISTS test_partitionning; > SET search_path TO test_partitionning, public ; > > DROP TABLE IF EXISTS test_father CASCADE; > CREATE TABLE test_father ( > gid SERIAL PRIMARY KEY > , geom geometry > ); > > create table test_child_1 ( > check (geometry_overlaps(geom,ST_Expand(ST_MakePoint(10,10),10 ) ) ) > ,check ( geom&&ST_Expand(ST_MakePoint(10,10),10 ) ) > , CHECK (ST_X(geom) BETWEEN 0 AND 20) > , CHECK (ST_Y(geom) BETWEEN 0 AND 20) > , CHECK ( ST_Intersects(geom, ST_Expand(ST_MakePoint(10,10),10 )) ) > ) inherits (test_father); > --CREATE INDEX ON test_child_1 USING GIST(geom); > > create table test_child_2 ( > check (geometry_overlaps(geom,ST_Expand(ST_MakePoint(30,10),10 ) ) ) > ,check ( geom&&ST_Expand(ST_MakePoint(30,10),10 ) ) > , CHECK (ST_X(geom) BETWEEN 20 AND 40) > , CHECK (ST_Y(geom) BETWEEN 0 AND 20) > , CHECK ( ST_Intersects(geom, ST_Expand(ST_MakePoint(30,10),10 )) ) > ) inherits (test_father); > --CREATE INDEX ON test_child_2 USING GIST(geom); > > > INSERT INTO test_child_1 (geom) > SELECT ST_MakePoint(s1/10.0+random(),s2/10.0+random()) > FROM generate_series(1,90) AS s1, generate_series(1,90) AS s2; > > INSERT INTO test_child_2 (geom) > SELECT ST_MakePoint(s1/10.0+random(),s2/10.0+random()) > FROM generate_series(200,300) AS s1, generate_series(1,90) AS s2; > > > SHOW constraint_exclusion; > SET constraint_exclusion TO partition; > > > WITH area_of_interest AS ( > SELECT ST_Buffer(ST_MakePoint(5,5),1) as buf > ) > SELECT * > FROM area_of_interest, test_father > WHERE -- geom && buf > ST_X(geom) BETWEEN ST_XMin(buf) AND ST_Xmax(buf) > AND ST_Y(geom) BETWEEN ST_YMin(buf) AND ST_Ymax(buf) ; > > > SELECT * > FROM test_father , ST_Buffer(ST_MakePoint(5,5),1) as buf > WHERE > ST_X(geom) BETWEEN ST_XMin(buf) AND ST_Xmax(buf) > AND ST_Y(geom) BETWEEN ST_YMin(buf) AND ST_Ymax(buf); > ------ > > > _______________________________________________ > 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