Timeline for Postgis spatial join performance (point on area)
Current License: CC BY-SA 3.0
13 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 10, 2016 at 11:37 | vote | accept | Jorge Vidinha | ||
| May 4, 2016 at 10:30 | comment | added | Jorge Vidinha | Update on actual tests seems that st_within is the winner in my cenario , i havent tried yet the recreation of the full table as @Nicklas Avén proposed. ST_Contains(po.geom, pt.geom) 34.2 secs execution time. ST_Intersects(po.geom, pt.geom) 36.2 secs execution time. ST_Within(po.geom, pt.geom) 21.4 secs execution time. | |
| May 4, 2016 at 7:29 | answer | added | Nicklas Avén | timeline score: 3 | |
| May 3, 2016 at 18:14 | comment | added | John Powell | If you look at the explain, you will discover that there is a full table scan on the points table (by definition, as you are updating every row of it). You could possibly try what Vince suggests and reverse it, possibly using ST_Intersects intstead, as clearly a point can not contain a polygon, but, basically, yes, this is the most efficient you can make it without parallelizing. | |
| May 3, 2016 at 13:16 | history | edited | Vince | CC BY-SA 3.0 |
English usage
|
| May 3, 2016 at 11:21 | comment | added | Vince | Yes, use ST_Within. | |
| May 3, 2016 at 11:15 | comment | added | Jorge Vidinha | @Vince you mean reversing it but using ST_Within() instead of ST_Contains ? | |
| May 3, 2016 at 10:50 | comment | added | Jorge Vidinha | adding a LIMIT 1000 at end of this query ; seems not to work. Any ideia how to limit this query ? | |
| May 3, 2016 at 10:50 | comment | added | Vince | Turn the query inside out and use the polygons to find points. Instead of millions of point-in-poly tests, you'll have thousands of poly-cointains-point. | |
| May 3, 2016 at 10:36 | comment | added | user30184 | PostGIS should be quite well optimized to handle such data. How long does it take to update for example thousand rows with LIMIT 1000? | |
| May 3, 2016 at 10:30 | comment | added | Jorge Vidinha | they are not complex , but they are many and points are millions . Do you belleive this should be the most performant query for this operation ? | |
| May 3, 2016 at 9:29 | comment | added | user30184 | If your polygons are complicated having typically thousands of vertices it might make sense to split them first. If they are simple or if you are not hurry then just go ahead. | |
| May 3, 2016 at 9:20 | history | asked | Jorge Vidinha | CC BY-SA 3.0 |