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

[Paul Ramsey]

Oh yeah, actually that's an important enhancement if a lot of the
lines are going to be 100% inside their target polygons, to avoid
running the full Intersection() test...

ST_Within() and ST_Contains() are the same, (code actually just gates
one into the other, with reversed arguments) and as long as you have
gist indexes on teh geoms of both the planner will do the optimal
thing automatically

P.


On Tue, Mar 3, 2015 at 5:56 AM, Igor Stassiy <istassiy at gmail.com> wrote:
> 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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users