4

I have a table with the below structure:

geom name ownership
01031... foo landlord
01032... foo landlord
01033... baz landlord
01034... bar tenant
01035... bar tenant
01036... bar tenant

Landlords own a wide area and tenants own a small section within.

So, the geom of the tenant will at least intersect with the geom of the landlord.

I want to be able to search for a landlord by name, (select * from table where name = 'foo' and ownership = 'landlord';) and get all of the geoms that the landlord owns and get the tenants with geoms inside those geoms.

Likewise, I want to search for a tenant, get all of the buildings they rent and find all of the enclosing landlord geoms.

Conceptually can't get my head around it. Is ST_Intersects the right method?

1 Answer 1

6

Yes, ST_Intersects is the right message. You need to join a subset of the table (with the landlord of interest) to another subset (with the tenants), using ST_Intersects. Give the first subset the alias l and the second one the alias t.

SELECT t.name
FROM   table l 
JOIN   table t ON ST_Intersects(t.geom, l.geom)
WHERE  l.name = 'foo' AND l.ownership = 'landlord'
       AND t.ownership = 'tenant'

You might also want ST_Within or ST_Contains rather than ST_Intersects. See the examples here.

3
  • 1
    This syntax hasn't been used since ANSI 92 was released. Always use JOIN in anything released after Oracle 7
    – Vince
    Commented Sep 5, 2023 at 23:31
  • fair enough, updated the syntax.
    – amball
    Commented Sep 6, 2023 at 0:08
  • 1
    "This syntax hasn't been used since ANSI 92 was released" citation needed, I see it all the time. I understand why people prefer explicit joins but being hyperbolic is unnecessary.
    – GammaGames
    Commented Sep 6, 2023 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.