[postgis-users] Fwd: Partitionning using geometry

[Rémi Cura]

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 <svm at clevelandmetroparks.com>:

>  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:* postgis-users-bounces at lists.osgeo.org <
> postgis-users-bounces at lists.osgeo.org> on behalf of Rémi Cura <
> remi.cura at gmail.com>
> *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
> 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/20150402/0958df2c/attachment.html>