I have two datasets in PostGIS 1.5 (on Postgres 9.1) one of polygons and one of points.
I am hoping to create a SQL command that will select all of the points in a dataset that are not within of the polygon features and assign a value of -9999 to one of the columns. In this case the boundary is a continent so -9999 defines a feature off the mainland.
I have several hundred thousand features and have tried to achieve this more or less manually in QGIS. However once the features are selected I cannot interact with QGIS without it crashing.
Having a couple of problems with performance that has this function taking 96+ hours and counting. I tried stopping previously and set a LIMIT of 100 on the SELECT and it was still taking a few hours.
This is with the GiST and BTree indexing.
I tried running SELECT on point_id only without the UPDATE function and got similar results.
Previously in QGIS it was taking about 16 hours to select all points within the polygons.
Will look at some performance tuning and retry a bit later
I am 99% sure Index are on but there is always a little room for doubt =D
Result of the first query is
Nested Loop Left Join (cost=0.00..12955070.02 rows=1 width =4)
Join Filter _st_intersects(point_table.the_geom, polygon_table.the_geom)
Filter: (polygon_table.polygon_id IS NULL)
-> Seq Scan on point_table (cost=0.00..209494.58 rows=1562658 width=104)
-> Index Scan using poly_table_geom_idx on polygon_table (cost=0.00..7.89 rows=1 width=22520) Index Cond: (point_table.the_geom && the_geom)
Result of second query is
Result of comparing the two queries with LIMIT 1000 is that the first takes about 15 minutes and the second is still running after an hour.
I have split the features using ST_Dump and at present I am setting up the Indexing, Keys etc for that.
Have complete ST_Dump
Nested Loop cost increased to 13142577, Seq Scan stayed the same, Index Scan width increased to 1162940
Point layer contains 1728921 points