[postgis-users] Finding Geometries that overlap with the given ones

[Paul Ramsey]

You want to express this as a JOIN,

SELECT A.id, B.id
FROM A JOIN B
ON (
  ST_Intersects(A.shape, B.shape) AND
  ST_Length(ST_Intersection(A.shape, B.shape)) / ST_Length(A.shape) >= 0.5
)
WHERE B.kind != 1

As a general "join rule" I put any clause where both tables appear
into the JOIN clause and any clause where only one table appears into
the WHERE clause. The planner ends up rearranging things anyway it
likes, but I find my rule brings some sense of order into my head.

Order of operations is generally evaluated left-to-right, but the
planner will rearrange things, particularly things with operators
(like the && that hides inside ST_Intersects), so that the most
selective clause evaluates first. In this case, since the
ST_Intersects is hiding an && operator it doesn't matter where you put
it, it will be promoted to wherever it is most effective. The only
time the left-to-right thing is a place your fiddling will matter is
when the functions are all scored as the same cost, and the planner
has no idea what the most effective clause will be; in that case it'll
apply them left to right.

P.


On Tue, Mar 3, 2015 at 2:14 AM, Igor Stassiy <istassiy at gmail.com> wrote:
> Hello,
>
> Lets say I have two tables
>
> CREATE EXTENSION postgis;
> DROP TABLE A;
> DROP TABLE B;
> CREATE TABLE A(shape Geometry, id INT);
> CREATE TABLE B(shape Geometry, id INT, kind INT);
> CREATE INDEX ON A USING GIST (shape);
>
> I would like to select for each object A, the an object of B of a particular
> kind, such
> that the relative overlap between A and B is at least 0.5:
>
> SELECT A.id, B.id FROM A, B
> WHERE B.id = (SELECT B.id FROM B WHERE
>  ST_Intersects(A.shape, B.shape)
>  AND ST_Length(ST_Intersection(A.shape, B.shape)) / ST_Length(A.shape) >=
> 0.5
>  AND B.kind != 1 LIMIT 1)
>
> The number of rows in table A orders of magnitude larger than that of table
> B.
>
> What are the indexes that should give optimal performance and what should be
> the order of arguments
> in the ST_Intersection? From the following page it seems that the order or
> arguments can have dramatic effects:
> http://support.esri.com/EN/knowledgebase/techarticles/detail/35498
>
> Thanks,
> Igor
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users