The query I am running is taking a considerable amount of time. I was wondering whether I am missing an opportunity to drastically improve performance.

My goal is to find out how many neighboring houses a house has. I based my code on this example: How to use st_touches to get all nearby states .

ST_Touches seems to be the right function. The query seems to be correct. I just have a very large amount of rows, not like in the example where they use states. Any ideas? It has a spatial index.

panden as (select * from source.houses where pandstatus = 'Pand in gebruik' OR  pandstatus = 'Pand in gebruik (niet ingemeten)')
    pand1.identificatie, count(pand2.geovlak)
    panden as pand1, panden as pand2
    st_touches(pand1.geovlak, pand2.geovlak) AND
    pand1.identificatie <> pand2.identificatie
group by
  • 1
    do you have a spatial index on your tables? please add the output of EXPLAIN on your query
    – Ian Turton
    May 13 '20 at 10:12
  • Yes it has a spatial index. The solution underneath worked.
    – Zuenie
    May 13 '20 at 17:56

Using ST_Touches here is not only tricky, since it identifies a match only

[...] if the only points in common between g1 and g2 lie in the union of the boundaries of g1 and g2.
- docs

(meaning that two geometries can only ever have their exact boundaries in common, which is a delicate precondition for coordinates with floating point precision)

but also less performant due to the amount of operations it has to perform to generate and check against said union of boundaries.

Better run a proximity check using ST_DWithin, with a distance of either 0, or, maybe even better, a very tiny threshold that would include geometries that are not precisely digitized:

SELECT  a.identificatie,
FROM    panden AS a
JOIN    panden AS b
  ON    ST_DWithin(a.geovlak, b.geovlak, 0)  -- or e.g. ST_DWithin(a.geovlak, b.geovlak, 0.001)
WHERE   a.identificatie <> b.identificatie

Note that this will find overlapping geometries as well.

Additionally, consider using pandstatus IN ('Pand in gebruik', 'Pand in gebruik (niet ingemeten)') in the CTE.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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