<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"><!-- P {margin-top:0;margin-bottom:0;} --></style>
</head>
<body dir="ltr">
<div id="divtagdefaultwrapper" 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 id="Signature">
<div id="divtagdefaultwrapper" 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 class="WordSection1">
<p class="MsoNormal" style="margin-top: 0px; margin-bottom: 0px;"><img alt="http://sig.cmparks.net/cmp-ms-90x122.png" align="left" height="122" hspace="12" width="90" style="-webkit-user-select: none;" src="http://sig.cmparks.net/cmp-ms-90x122.png">
<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 target="_blank" href="http://www.clemetparks.com" tabindex="0" id="LPNoLP"><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 tabindex="-1" style="display:inline-block; width:98%">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" color="#000000" style="font-size:11pt"><b>From:</b> postgis-users-bounces@lists.osgeo.org <postgis-users-bounces@lists.osgeo.org> on behalf of Rémi Cura <remi.cura@gmail.com><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 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>
</body>
</html>