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.
with panden as (select * from source.houses where pandstatus = 'Pand in gebruik' OR pandstatus = 'Pand in gebruik (niet ingemeten)') select pand1.identificatie, count(pand2.geovlak) from panden as pand1, panden as pand2 where st_touches(pand1.geovlak, pand2.geovlak) AND pand1.identificatie <> pand2.identificatie group by pand1.identificatie;