I have two datasets which I want to spatially join (with an intersect). The first dataset consists of 9 million addresses. The second dataset consists of 12.000 neighbourhoods. I want the add the neighbourhood to the address.
Some information about the system: Microsoft Windows Server 2003 (SP-2) Inter(R) Xeon(R) CPU X5650 @ 2.67 GHz 2.66 GHz, 4,00 GB of RAM
I log in via Remote Desktop
The processing time for this query is about 50 minutes. Quite long I would say. Can someone tell what would be the general processing time?
Both geometry columns have an index. First I add a column
ALTER TABLE temp.test ADD COLUMN buurtnr INTEGER;
Then I want to fill the column by doing a spatial join.
UPDATE temp.adresses a SET buurtnr = n.buurtnr FROM temp.neighb n WHERE st_intersects (a.geometrie, n.the_geom)
I have to run the query monthly, together with similar query. The whole process now takes about a day. I want to make it faster.