[postgis-users] Fwd: Partitionning using geometry

Rémi Cura remi.cura at gmail.com
Wed Apr 1 09:03:00 PDT 2015


(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);
------
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150401/af0483e1/attachment.html>


More information about the postgis-users mailing list