In theory, they use check for partitionning. It is possible (but inneficient) that check will overlaps (they give one such example in doc). Thus you can partition into tables that may have some common space.
This is the theory. I don't understand why it doesn't work, and I got no answers from postgres mailing list. Cheers, Rémi-C 2015-04-01 23:01 GMT+02:00 Stephen V. Mather <[email protected]>: > Hi, > > > Can you reliably partition with anything other than points? I thought > Postgres hadn't implemented ranges appropriately for the inevitable > linestring or polygon that crosses a partition boundary. (Fuzzy 2-year-old > memories, so perhaps something has changed...). > > > Cheers, > > Best, > > Steve > > > [image: http://sig.cmparks.net/cmp-ms-90x122.png] *Stephen V. Mather* > GIS Manager > (216) 635-3243 (Work) > clevelandmetroparks.com <http://www.clemetparks.com> > > > > > ------------------------------ > *From:* [email protected] < > [email protected]> on behalf of Rémi Cura < > [email protected]> > *Sent:* Wednesday, April 1, 2015 12:03 PM > *To:* PostGIS Users Discussion > *Subject:* [postgis-users] Fwd: Partitionning using geometry > > (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 > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
