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

[Igor Stassiy]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150303/85465b02/attachment.html>