[postgis-users] Fwd: Partitionning using geometry

[Stephen V. Mather]

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

[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);
------

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150401/40292255/attachment.html>