Timeline for QGIS point sample tool faster than in PostGIS - how to improve query?
Current License: CC BY-SA 4.0
12 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 18, 2018 at 19:04 | comment | added | pdavis | @NateWanner your second query is the one that provides incorrect values. The first one resulted in correct values being assigned to the points. | |
| May 16, 2018 at 20:39 | comment | added | Nate Wanner | @pdavis which query gives the incorrect match? Also, can you try with pgAdmin III just out of curiosity? | |
| May 16, 2018 at 14:58 | comment | added | pdavis | @NateWanner, I had a chance yesterday afternoon to run the new query. It did finish faster, this time in a couple hours. Still not up to QGIS speed. But the polygon rate value picked up by the points was not the right value, unfortunately. Must be something to do with the placement of the JOIN statement? I've also asked a related question, having to do with the speed of queries in pgAdmin4, and maybe that is the ultimate issue here? gis.stackexchange.com/questions/282952/… | |
| May 16, 2018 at 0:20 | comment | added | Nate Wanner | @pdavis see if the edits made above work any better. | |
| May 16, 2018 at 0:20 | history | edited | Nate Wanner | CC BY-SA 4.0 |
Added where clause based on Thingamabub's comment and primary key assumption.
|
| May 15, 2018 at 14:56 | comment | added | pdavis | @ThingumaBob and Nate, you both have good points. First, the 'fert_rate' is in the points file and 'rate' is the polygon value being assigned to the points. There are many points per polygon and each point needs the rate from the poly. Nate, in regards to your edit: Overlaps are not a concern because after point sampling I actually eliminate points from overlapping areas, having extracted and isolating those locations in a separate table. But I want to start with the full point set before filtering based on other criteria. I'm going to run the query in your edit and see what happens. | |
| May 12, 2018 at 23:19 | comment | added | Nate Wanner | @ThingumaBob. Good point. I had just grabbed one of the queries and added a join. I put the join on the other query since I'm not sure which tables fert_rate and rate are in. | |
| May 12, 2018 at 23:16 | history | edited | Nate Wanner | CC BY-SA 4.0 |
Discussed issues raised by Thingamabub
|
| May 12, 2018 at 8:11 | comment | added | geozelot |
this query will try to set the fert_rate column to each entry in pnt_sample; you need to add a filter to get only one row as the SET parameter. can you be sure ST_Intersects does always return one point per polygon in your Query 1?
|
|
| May 11, 2018 at 23:02 | comment | added | pdavis | New update - it ran for 4 or 5 hours and finished correctly. What's strange is that it took this long, but the message when it finished said 'query returned successfully in 12 min.'. So I wonder why the discrepancy, but that's a whole different question which I'll ask the SE experts on Monday. | |
| May 11, 2018 at 20:57 | comment | added | pdavis | Nate, your suggestion is still running after 2+ hours; however I noticed if I simply 'select' instead of 'update' using your query or either of mine, it returns the expected values in 11 seconds in all cases. So it seems the intersection itself is not the issue, but something to do with the update process? | |
| May 11, 2018 at 17:43 | history | answered | Nate Wanner | CC BY-SA 4.0 |