Re: [postgis-users] Fwd: Partitionning using geometry
Hey Nicolas, great answer. At least it gives some hope, because it is possible to compute value in plpgsql function and create on the fly a querry with those values hard written. I still don't know if it would work with geometry tough Thanks, Rémi-C 2015-04-02 10:59 GMT+02:00 Nicolas Ribot nicolas.ri...@gmail.com: Hi Remy, As far as I understood table partitionning has one limiting caveat for some usages (from: http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html): Constraint exclusion only works when the query's WHERE clause contains constants (or externally supplied parameters). For example, a comparison against a non-immutable function such as CURRENT_TIMESTAMP cannot be optimized, since the planner cannot know which partition the function value might fall into at run time. Here are the plans of your query using function for check constraint, then using constant values: The second plan shows partionning is used, not in the first: -- fonctions as check parameters: explain 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); Nested Loop (cost=0.00..1206.78 rows=219 width=68) Join Filter: ((st_x(test_father.geom) = st_xmin((buf.buf)::box3d)) AND (st_x(test_father.geom) = st_xmax((buf.buf)::box3d)) AND (st_y(test_father.geom) = st_ymin((buf.buf)::box3d)) AND (st_y(test_father.geom) = st_ymax((buf.buf)::box3d))) - Function Scan on buf (cost=0.00..0.01 rows=1 width=32) - Append (cost=0.00..321.12 rows=17713 width=36) - Seq Scan on test_father (cost=0.00..0.00 rows=1 width=36) - Seq Scan on test_child_1 (cost=0.00..151.64 rows=8364 width=36) - Seq Scan on test_child_2 (cost=0.00..169.48 rows=9348 width=36) -- constants as check parameters: explain SELECT * FROM test_father , ST_Buffer(ST_MakePoint(5,5),1) as buf WHERE -- hard coded values ST_X(geom) BETWEEN 4 and 6 AND ST_Y(geom) BETWEEN 4 and 6 ; Nested Loop (cost=0.00..318.95 rows=2 width=68) - Function Scan on buf (cost=0.00..0.01 rows=1 width=32) - Append (cost=0.00..318.92 rows=2 width=36) - Seq Scan on test_father (cost=0.00..0.00 rows=1 width=36) Filter: ((st_x(geom) = 4::double precision) AND (st_x(geom) = 6::double precision) AND (st_y(geom) = 4::double precision) AND (st_y(geom) = 6::double precision)) - Seq Scan on test_child_1 (cost=0.00..318.92 rows=1 width=36) Filter: ((st_x(geom) = 4::double precision) AND (st_x(geom) = 6::double precision) AND (st_y(geom) = 4::double precision) AND (st_y(geom) = 6::double precision)) The test_child2 table is excluded in this plan. Nicolas On 2 April 2015 at 10:12, Rémi Cura remi.c...@gmail.com wrote: 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 s...@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-boun...@lists.osgeo.org postgis-users-boun...@lists.osgeo.org on behalf of Rémi Cura remi.c...@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 doesnt 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 ( geomST_Expand(ST_MakePoint(10,10),10 ) ) , CHECK (ST_X(geom) BETWEEN 0 AND 20) , CHECK (ST_Y(geom) BETWEEN 0 AND 20
Re: [postgis-users] Fwd: Partitionning using geometry
Hi Remy, As far as I understood table partitionning has one limiting caveat for some usages (from: http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html): Constraint exclusion only works when the query's WHERE clause contains constants (or externally supplied parameters). For example, a comparison against a non-immutable function such as CURRENT_TIMESTAMP cannot be optimized, since the planner cannot know which partition the function value might fall into at run time. Here are the plans of your query using function for check constraint, then using constant values: The second plan shows partionning is used, not in the first: -- fonctions as check parameters: explain 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); Nested Loop (cost=0.00..1206.78 rows=219 width=68) Join Filter: ((st_x(test_father.geom) = st_xmin((buf.buf)::box3d)) AND (st_x(test_father.geom) = st_xmax((buf.buf)::box3d)) AND (st_y(test_father.geom) = st_ymin((buf.buf)::box3d)) AND (st_y(test_father.geom) = st_ymax((buf.buf)::box3d))) - Function Scan on buf (cost=0.00..0.01 rows=1 width=32) - Append (cost=0.00..321.12 rows=17713 width=36) - Seq Scan on test_father (cost=0.00..0.00 rows=1 width=36) - Seq Scan on test_child_1 (cost=0.00..151.64 rows=8364 width=36) - Seq Scan on test_child_2 (cost=0.00..169.48 rows=9348 width=36) -- constants as check parameters: explain SELECT * FROM test_father , ST_Buffer(ST_MakePoint(5,5),1) as buf WHERE -- hard coded values ST_X(geom) BETWEEN 4 and 6 AND ST_Y(geom) BETWEEN 4 and 6 ; Nested Loop (cost=0.00..318.95 rows=2 width=68) - Function Scan on buf (cost=0.00..0.01 rows=1 width=32) - Append (cost=0.00..318.92 rows=2 width=36) - Seq Scan on test_father (cost=0.00..0.00 rows=1 width=36) Filter: ((st_x(geom) = 4::double precision) AND (st_x(geom) = 6::double precision) AND (st_y(geom) = 4::double precision) AND (st_y(geom) = 6::double precision)) - Seq Scan on test_child_1 (cost=0.00..318.92 rows=1 width=36) Filter: ((st_x(geom) = 4::double precision) AND (st_x(geom) = 6::double precision) AND (st_y(geom) = 4::double precision) AND (st_y(geom) = 6::double precision)) The test_child2 table is excluded in this plan. Nicolas On 2 April 2015 at 10:12, Rémi Cura remi.c...@gmail.com wrote: 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 s...@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-boun...@lists.osgeo.org postgis-users-boun...@lists.osgeo.org on behalf of Rémi Cura remi.c...@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 doesnt 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 ( geomST_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 ( geomST_Expand(ST_MakePoint(30,10),10 ) ) , CHECK (ST_X(geom) BETWEEN 20 AND 40) , CHECK (ST_Y
Re: [postgis-users] Fwd: Partitionning using geometry
Yes, hope I only understand now, by using anonymous code block to provide WHERE clauses with constant values... Some months ago I found this constant-value limitation too strong and did not use partitionning... ;) Nicolas On 2 April 2015 at 11:09, Rémi Cura remi.c...@gmail.com wrote: Hey Nicolas, great answer. At least it gives some hope, because it is possible to compute value in plpgsql function and create on the fly a querry with those values hard written. I still don't know if it would work with geometry tough Thanks, Rémi-C 2015-04-02 10:59 GMT+02:00 Nicolas Ribot nicolas.ri...@gmail.com: Hi Remy, As far as I understood table partitionning has one limiting caveat for some usages (from: http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html): Constraint exclusion only works when the query's WHERE clause contains constants (or externally supplied parameters). For example, a comparison against a non-immutable function such as CURRENT_TIMESTAMP cannot be optimized, since the planner cannot know which partition the function value might fall into at run time. Here are the plans of your query using function for check constraint, then using constant values: The second plan shows partionning is used, not in the first: -- fonctions as check parameters: explain 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); Nested Loop (cost=0.00..1206.78 rows=219 width=68) Join Filter: ((st_x(test_father.geom) = st_xmin((buf.buf)::box3d)) AND (st_x(test_father.geom) = st_xmax((buf.buf)::box3d)) AND (st_y(test_father.geom) = st_ymin((buf.buf)::box3d)) AND (st_y(test_father.geom) = st_ymax((buf.buf)::box3d))) - Function Scan on buf (cost=0.00..0.01 rows=1 width=32) - Append (cost=0.00..321.12 rows=17713 width=36) - Seq Scan on test_father (cost=0.00..0.00 rows=1 width=36) - Seq Scan on test_child_1 (cost=0.00..151.64 rows=8364 width=36) - Seq Scan on test_child_2 (cost=0.00..169.48 rows=9348 width=36) -- constants as check parameters: explain SELECT * FROM test_father , ST_Buffer(ST_MakePoint(5,5),1) as buf WHERE -- hard coded values ST_X(geom) BETWEEN 4 and 6 AND ST_Y(geom) BETWEEN 4 and 6 ; Nested Loop (cost=0.00..318.95 rows=2 width=68) - Function Scan on buf (cost=0.00..0.01 rows=1 width=32) - Append (cost=0.00..318.92 rows=2 width=36) - Seq Scan on test_father (cost=0.00..0.00 rows=1 width=36) Filter: ((st_x(geom) = 4::double precision) AND (st_x(geom) = 6::double precision) AND (st_y(geom) = 4::double precision) AND (st_y(geom) = 6::double precision)) - Seq Scan on test_child_1 (cost=0.00..318.92 rows=1 width=36) Filter: ((st_x(geom) = 4::double precision) AND (st_x(geom) = 6::double precision) AND (st_y(geom) = 4::double precision) AND (st_y(geom) = 6::double precision)) The test_child2 table is excluded in this plan. Nicolas On 2 April 2015 at 10:12, Rémi Cura remi.c...@gmail.com wrote: 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 s...@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-boun...@lists.osgeo.org postgis-users-boun...@lists.osgeo.org on behalf of Rémi Cura remi.c...@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 doesnt 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
[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 doesnt 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 ( geomST_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 ( geomST_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@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Fwd: Partitionning using geometry
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.comhttp://www.clemetparks.com From: postgis-users-boun...@lists.osgeo.org postgis-users-boun...@lists.osgeo.org on behalf of Rémi Cura remi.c...@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 doesnt 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 ( geomST_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 ( geomST_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@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Fwd: Partitionning using geometry
Actually we are in the process of doing something similar On Wed, Apr 1, 2015 at 11:03 AM, Rémi Cura remi.c...@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 doesnt 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 ( geomST_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 ( geomST_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@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users