<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><meta content="text/html;charset=UTF-8" http-equiv="Content-Type"></head><body ><div style='font-size:10pt;font-family:Verdana,Arial,Helvetica,sans-serif;'>Thanks for the suggestions. Some thoughts after testing:<br><br><br><b>Darafei</b>: (order by ST_Geohash) Interesting notion, but when implemented the grouping is tenuous at best. If I return 5,000 features they can be spread over 200km (bad) or 6km (good). It'd definitely better clustered than without this, but I was surprised at how dispersed some of the groups were, and that may be too much for my purposes.<br>Although I see Paul's answer to this SO question (https://gis.stackexchange.com/questions/188879/clustering-markers-using-quadtree/189296) may help. I've not tested it yet.<br><br><b>Lars</b>: (content_balanced_grid) The issue here is what happens to features that overlap the grid edges, as I only want to process any given polygon once, and I can't clip them? I'd have to use a ST_Within to only get polygons inside the CBG (which is fine), but the remaining features would need an ST_Overlaps against the entire grid. I've been running that 30 mins so far on a small sample of my full database, and it's still running, so it definitely won't scale.<br><br><b>Remi</b>: I was looking for something higher level. Implementing a Quad-Tree in SQL isn't something I'd be comfortable doing. Although if it is simple to do, it may be worth implementing as a feature within PostGIS; I'm sure others would also benefit from being able to easily spatially sort. I tried googling PostGIS quadtree but didn't find anything so I'm guessing PG doesn't implement this type of index by default.<br><br>Are there any other options? I may consider trying to glue together the two approaches, but that's going to take some thought.<br><br>Thanks,<br>Jonathan<br><div class="zmail_extra"><div id="1"><br>---- On Wed, 01 Mar 2017 12:56:58 +0000 <b>Travis Kirstine <traviskirstine@gmail.com></b> wrote ---- <br></div><blockquote style="border-left: 1px solid #0000FF;padding-left: 6px; margin: 0 0 0 5px"><div><div dir="ltr"><div>if you go with Remi suggestion you then can cluster the quad tree index to reorder records </div><div><br></div><a href="http://postgis.net/docs/performance_tips.html" target="_blank">http://postgis.net/docs/performance_tips.html</a><br><div><br></div><div><br></div></div><div><br><div>On 1 March 2017 at 07:11, Rémi Cura <span><<a href="mailto:remi.cura@gmail.com" target="_blank">remi.cura@gmail.com</a>></span> wrote:<br><blockquote style="margin: 0 0 0 0.8ex;border-left: 1.0px rgb(204,204,204) solid;padding-left: 1.0ex;"><div dir="ltr"><div style="font-family: monospace , monospace;">hey,<br></div><div style="font-family: monospace , monospace;">the most classical approach would be to construct a <a href="https://en.wikipedia.org/wiki/Quadtree" target="_blank">quad tree</a>.<br></div><div style="font-family: monospace , monospace;">You can think of it as a grid that adapts its resolution locally according to the data.<br></div><div style="font-family: monospace , monospace;">This is also easy to code.<br></div><div style="font-family: monospace , monospace;"><br></div><div style="font-family: monospace , monospace;">Cheers,<br></div><div style="font-family: monospace , monospace;">Rémi-C<br></div></div><div><div><div><br><div>2017-03-01 12:46 GMT+01:00 Darafei "Komяpa" Praliaskouski <span><<a href="mailto:me@komzpa.net" target="_blank">me@komzpa.net</a>></span>:<br><blockquote style="margin: 0 0 0 0.8ex;border-left: 1.0px rgb(204,204,204) solid;padding-left: 1.0ex;"><div dir="ltr">Have a look here:<br><br><a href="http://www.paulnorman.ca/blog/2016/05/improve-your-st-geohash-sorting-with-these-three-simple-tricks/" target="_blank">http://www.paulnorman.ca/blog/2016/05/improve-your-st-geohash-sorting-with-these-three-simple-tricks/</a> <br><br>for simplest case, you can just order by your geom field.</div><br><div><div dir="ltr">ср, 1 мар. 2017 г. в 14:44, Jonathan Moules <<a href="mailto:jonathan-lists@lightpear.com" target="_blank">jonathan-lists@lightpear.com</a>>:<br></div><blockquote style="margin: 0 0 0 0.8ex;border-left: 1.0px rgb(204,204,204) solid;padding-left: 1.0ex;"><div><div><u></u><div><div style="font-size: 10.0pt;font-family: Verdana , Arial , Helvetica , sans-serif;">Hi List,<br>I've got a database of about 60 million spatial features that I need to run through a process.<br><br>The process can't do all of them at once, so I want to get the data from postgis in spatial chunks of up to say 500,000 features per query.<br><br>The data itself isn't evenly spatially distributed, so gridding won't work either, and I don't see any prospectively useful functions in the reference.<br><br>Non-spatially this would be simple:<br><br> <i>SELECT * FROM my_table ORDER BY some_key ASC LIMIT 500000 OFFSET 0</i><br> <br>Does anyone have any suggestions for how to spatially order data with PostGIS?<br><br>Thanks,<br>Jonathan<br></div></div></div></div>_______________________________________________<br> postgis-users mailing list<br> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div> <br>_______________________________________________<br> postgis-users mailing list<br> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div> </div></div><br>_______________________________________________<br> postgis-users mailing list<br> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div> _______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></div></blockquote><br></div><br></div></body></html>