[postgis-users] Fwd: Partitionning using geometry

[David Haynes]

Actually we are in the process of doing something similar

On Wed, Apr 1, 2015 at 11:03 AM, Rémi Cura <remi.cura at 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 at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150401/abeaee13/attachment.html>