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

[Igor Stassiy]

Thanks Paul.

What if I want to say ST_Contains(A, B)? And will it matter if I say
ST_Within(B, A) instead? I have a feeling it will, since
most likely the query planner will decide to process A row by row and find
a "suitable B" each time. In this case, we need a GiST index on B, so that
we could find the B that is contained in A right?

Thanks again,
Igor

On Tue, Mar 3, 2015 at 5:47 PM Paul Ramsey <pramsey at cleverelephant.ca>
wrote:

> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150303/09edf5ca/attachment.html>