TransWikia.com

PostGIS - st_intersects: WHERE vs INNER JOIN

Geographic Information Systems Asked on July 31, 2021

In PostgreSQL/PostGIS, when running an intersection between 2 geometries, what the difference between these two syntaxes ?

-- Method 1
SELECT
a.id
b.id
FROM a, b
WHERE st_intersects(a.geom, b.geom)

-- Method 2
SELECT
a.id,
b.id
FROM a
INNER JOIN b ON st_intersects(a.geom, b.geom)

In real conditions, first method is longer to execute than second for the same result. Instinctively I would have believed the opposite because the first had one condition, how to explain this ?

An EXPLAIN ANALYZE SELECT with the unrolled detail did not enlighten me more (here is an example with real tables, the names do not match the example above).

Method 1
where

Method 2
inner_join

I’m using PostgreSQL 13.2 and PostGIS 3.1.1.

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP