Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
    
I think we need more information about your set up (indexes, query, etc). Also does it matter as you've done the join now, or do you need to do it again? –  iant Aug 16 '12 at 7:47
    
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 –  Stefan Aug 16 '12 at 8:19
    
Not sure if it matters, but you could try swapping the arguments to st_intersects, the rationale being that maybe that way it will iterate over the neighbourhoods instead of addresses, saving lots of comparisons. The internal implementation or the optimiser may render it useless though. –  lynxlynxlynx Aug 16 '12 at 10:24
    
this answer could be useful.... gis.stackexchange.com/a/31562/5850 –  vinayan Aug 16 '12 at 16:20
    
Please post the explain plan for the query (postgresql.org/docs/9.1/static/sql-explain.html) and the postgres version. –  unicoletti Aug 17 '12 at 6:40
add comment

3 Answers

up vote 3 down vote accepted

Maybe try to do this without using an UPDATE. Since you're joining all the records in one table to another just do a create table query to make a brand new up to date table. My suspicion is that the update query could be driving the point/polygon join backwards and not nesting the loop with polygons outside points, thereby missing a big performance optimization.

CREATE TABLE addresses2 AS
SELECT a.*, n.neighborhood
FROM addresses a JOIN neighborhoods n 
ON ST_Contains(n.geom, a.geom);
share|improve this answer
add comment

I assume you have spatial indexes in place already. If you don't then do it. You also want to make sure your spatial indexes are being used with

EXPLAIN yourquery

ST_Intersects is not that costly especially in newer versions (version 1.4+). It is a function of how many points you have in your neighborhood how costly it is. With large number of points, ST_DWithin often performs better than ST_Intersects, so you can try switching your ST_Intersects to ST_Dwithin(geom1,geom2, 0.000001)

You can gain quite a lot of performance with simplification which will reduce the number of points regardless of which relationship function you are using. - check out our slides http://www.postgis.us/downloads/oscon2009_PostGISTips.pdf Slide 18.

So first thing to do is if you are running < PostGIS 1.4 upgrade. Major improvement.

The other thing that makes things slow is how big of an area each of your neighborhood covers as if most of your geometries don't pass the index check, they'd never even hit the more costly _ST_Intersects, _ST_DWithin checks.

share|improve this answer
add comment

St_intersects is costly.

This previous question shows another performance problem with St_intersects. This and this links also report that kind of problem. In a previous work I used St_intersects to eventually check the intersection between 20 and 100 polygons it usually took about 90 seconds (I think that's slow).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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