My points table has 246,338 features and the polygon table has 43,957 features. I am relating the values of one attribute from the polygons to update a column in the points table.
This question is similar to Speed up point sampling with ST_Value function in PostGIS, raster/vector overlay but that question specifically addresses point sampling of raster, not polygons, using ST_Value, and the optimum raster tile storage size.
Both tables have had maintenance performed and have spatial indexes that are updated. The polygons are records of fertilizer application rates applied to a field and are fairly messy with plenty of overlaps:
The polygons table has been checked for invalid geometries and those have been fixed. Running ST_NPoints tells me there are an average of only 5 vertices per polygon so ST_Simplify is not necessary. The average polygon size is 112 m2 and range from 0 (only 60 of them) to 826 m2. I cannot dissolve (ST_Union) polygons on anything because I need the unique numeric fertilizer values from those individual polygons.
I've tried two queries in PostGIS, but have abandoned them after running them for a few hours:
update points set fert_rate = rate from points a, polys b where st_intersects(a.geom, b.geom);
with pnt_sample as ( select rate from points a, polys b where st_intersects(a.geom, b.geom) ) update points set fert_rate = pnt_sample.rate from pnt_sample;
Having QGIS read the tables from the DB and run the 'point sampling' tool takes only a couple of minutes. However this is not the optimal solution for many reasons, particularly because the output must be saved as a shapefile, headers are truncated to 10 characters, and then must be imported back into the DB, header names fixed, etc.
My questions are:
- What is QGIS doing in its algorithm that I don't have in my query?
- How can I incorporate that advantage into my approach so I don't have to go outside of my database?
I would like to keep any suggestions/solutions within PostGIS/QGIS.
I'm using PostGIS 2.4.1 and QGIS 2.18.14 on a Windows 10 machine.
EDIT: In response to John Powell and Thingamubob's comments, I ran EXPLAIN ANALYZE on the two queries above. Both tables had vacuum/analyze performed just prior to running EXPLAIN ANALYZE. Here is the output for the result of the first query:
And here is the output from the second query, the one using the CTE:
Each of these EXPLAIN ANALYZE queries took about an hour to run.