<div dir="ltr"><div><div><div><div><div><br>In theory, they use check for partitionning. <br></div>It is possible (but inneficient) that check will overlaps (they give one such example in doc).<br></div><div>Thus you can partition into tables that may have some common space.<br><br></div><div><br></div>This is the theory.<br></div>I don't understand why it doesn't work, and I got no answers from postgres mailing list.<br><br></div>Cheers,<br></div>Rémi-C<br></div><div class="gmail_extra"><br><div class="gmail_quote">2015-04-01 23:01 GMT+02:00 Stephen V. Mather <span dir="ltr"><<a href="mailto:svm@clevelandmetroparks.com" target="_blank">svm@clevelandmetroparks.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">
<div style="font-size:12pt;color:#000000;background-color:#ffffff;font-family:Calibri,Arial,Helvetica,sans-serif">
<p style="margin-top:0px;margin-bottom:0px">Hi,<br>
</p>
<p style="margin-top:0px;margin-bottom:0px"><br>
</p>
<p style="margin-top:0px;margin-bottom:0px">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...).<br>
</p>
<p style="margin-top:0px;margin-bottom:0px"><br>
</p>
<p style="margin-top:0px;margin-bottom:0px">Cheers,<br>
</p>
<p style="margin-top:0px;margin-bottom:0px">Best,<br>
</p>
<p style="margin-top:0px;margin-bottom:0px">Steve<br>
</p>
<p style="margin-top:0px;margin-bottom:0px"><br>
</p>
<div>
<div style="font-size:12pt;color:#000000;background-color:#ffffff;font-family:Calibri,Arial,Helvetica,sans-serif">
<div style="font-family:Tahoma;font-size:13px">
<div style="font-family:Tahoma;font-size:13px">
<div style="font-family:Tahoma;font-size:13px">
<div>
<p class="MsoNormal" style="margin-top:0px;margin-bottom:0px"><img alt="http://sig.cmparks.net/cmp-ms-90x122.png" src="http://sig.cmparks.net/cmp-ms-90x122.png" align="left" height="122" hspace="12" width="90">
<span style="font-size:14pt;color:rgb(0,108,86);font-family:Helvetica,sans-serif">
<span style="font-family:Helvetica,sans-serif"><strong>Stephen V. Mather</strong></span><br style="font-family:Helvetica,sans-serif">
<span style="font-family:Helvetica,sans-serif"></span></span><span style="font-size:11pt;color:rgb(0,108,86);font-family:Helvetica,sans-serif"><span style="font-family:Helvetica,sans-serif">GIS Manager</span><br style="font-family:Helvetica,sans-serif">
<span style="font-family:Helvetica,sans-serif"></span></span><span style="font-size:9pt;color:rgb(0,108,86);font-family:Helvetica,sans-serif"><span style="font-family:Helvetica,sans-serif">(216) 635-3243 (Work)
</span></span><span style="font-size:11pt;color:rgb(31,73,125);font-family:Helvetica,sans-serif"><a href="http://www.clemetparks.com" target="_blank"><span style="font-family:Helvetica,sans-serif"><br style="font-family:Helvetica,sans-serif">
<span style="font-family:Helvetica,sans-serif">clevelandmetroparks.com</span></span></a></span></p>
<p class="MsoNormal" style="margin-top:0px;margin-bottom:0px"></p>
<br>
<br>
<br>
</div>
</div>
<div></div>
</div>
</div>
</div>
</div>
<div style="color:rgb(33,33,33)">
<hr style="display:inline-block;width:98%">
<div dir="ltr"><font style="font-size:11pt" color="#000000" face="Calibri, sans-serif"><b>From:</b> <a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a> <<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>> on behalf of Rémi Cura <<a href="mailto:remi.cura@gmail.com" target="_blank">remi.cura@gmail.com</a>><br>
<b>Sent:</b> Wednesday, April 1, 2015 12:03 PM<br>
<b>To:</b> PostGIS Users Discussion<br>
<b>Subject:</b> [postgis-users] Fwd: Partitionning using geometry</font>
<div> </div>
</div><div><div class="h5">
<div>
<div dir="ltr">(cross-post from postgres list)<br>
<div>
<div class="gmail_quote">
<div dir="ltr">
<div>
<div>
<div>
<div>
<div>
<div>Hey dear list,<br>
<br>
</div>
I'd like to partition geographical (geometry) data with postgres mechanism.<br>
</div>
<div>(my usage is in fact related to pointcloud, but I use geometry as a work around)<br>
</div>
>From example I read on constraint, nothing should prevent it from working<br>
</div>
Here is a self contained example, the planner doesn"t seems to use the constraint_exclusion mechanism, whatever the constraint<br>
<br>
</div>
Thanks, <br>
</div>
Cheers,<br>
</div>
Rémi-C<br>
<div>
<div>
<div>
<div>
<div>
<div><br>
------<br>
<br>
CREATE SCHEMA IF NOT EXISTS test_partitionning;<br>
SET search_path TO test_partitionning, public ;<br>
<br>
DROP TABLE IF EXISTS test_father CASCADE;<br>
CREATE TABLE test_father (<br>
gid SERIAL PRIMARY KEY<br>
, geom geometry<br>
); <br>
<br>
create table test_child_1 (<br>
check (geometry_overlaps(geom,ST_Expand(ST_MakePoint(10,10),10 ) ) )<br>
,check ( geom&&ST_Expand(ST_MakePoint(10,10),10 ) ) <br>
, CHECK (ST_X(geom) BETWEEN 0 AND 20)<br>
, CHECK (ST_Y(geom) BETWEEN 0 AND 20)<br>
, CHECK ( ST_Intersects(geom, ST_Expand(ST_MakePoint(10,10),10 )) )<br>
) inherits (test_father);<br>
--CREATE INDEX ON test_child_1 USING GIST(geom);<br>
<br>
create table test_child_2 (<br>
check (geometry_overlaps(geom,ST_Expand(ST_MakePoint(30,10),10 ) ) )<br>
,check ( geom&&ST_Expand(ST_MakePoint(30,10),10 ) ) <br>
, CHECK (ST_X(geom) BETWEEN 20 AND 40)<br>
, CHECK (ST_Y(geom) BETWEEN 0 AND 20)<br>
, CHECK ( ST_Intersects(geom, ST_Expand(ST_MakePoint(30,10),10 )) )<br>
) inherits (test_father);<br>
--CREATE INDEX ON test_child_2 USING GIST(geom);<br>
<br>
<br>
INSERT INTO test_child_1 (geom)<br>
SELECT ST_MakePoint(s1/10.0+random(),s2/10.0+random())<br>
FROM generate_series(1,90) AS s1, generate_series(1,90) AS s2;<br>
<br>
INSERT INTO test_child_2 (geom)<br>
SELECT ST_MakePoint(s1/10.0+random(),s2/10.0+random())<br>
FROM generate_series(200,300) AS s1, generate_series(1,90) AS s2;<br>
<br>
<br>
SHOW constraint_exclusion;<br>
SET constraint_exclusion TO partition;<br>
<br>
<br>
WITH area_of_interest AS (<br>
SELECT ST_Buffer(ST_MakePoint(5,5),1) as buf<br>
)<br>
SELECT *<br>
FROM area_of_interest, test_father <br>
WHERE -- geom && buf <br>
ST_X(geom) BETWEEN ST_XMin(buf) AND ST_Xmax(buf)<br>
AND ST_Y(geom) BETWEEN ST_YMin(buf) AND ST_Ymax(buf) ;<br>
<br>
<br>
SELECT *<br>
FROM test_father , ST_Buffer(ST_MakePoint(5,5),1) as buf<br>
WHERE <br>
ST_X(geom) BETWEEN ST_XMin(buf) AND ST_Xmax(buf)<br>
AND ST_Y(geom) BETWEEN ST_YMin(buf) AND ST_Ymax(buf);<br>
------<br>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<br>
</div>
</div>
</div>
</div></div></div>
</div>
</div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>