<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"><!-- P {margin-top:0;margin-bottom:0;} --></style>
</head>
<body dir="ltr">
<div id="divtagdefaultwrapper" style="font-size:12pt; color:#000000; background-color:#FFFFFF; font-family:Calibri,Arial,Helvetica,sans-serif">
<p>Hi</p>
<p><br>
</p>
<p>Here <a href="https://github.com/larsop/content_balanced_grid" id="LPlnk36918" title="https://github.com/larsop/content_balanced_grid
Cmd+ Klikk eller trykk for å følge koblingen">https://github.com/larsop/content_balanced_grid</a> is some generic code that
you can use for this. </p>
<br>
<p><br>
</p>
<p>The sql below will return a grid that contains max 4000 touching objects pr cell. If there is low density of objects in a area, the cells for that area will be bigger than the cells in a area with high density of objects. This means that the cells will we
have varying sizes in the grid. This code only counts number of objects and not the number points in each object.</p>
<p><br>
</p>
<p><br>
</p>
<pre style="font-family:SFMono-Regular,Consolas,'Liberation Mono',Menlo,Courier,monospace; font-size:14px; margin-top:0px; line-height:1.45; word-wrap:normal; padding:16px; overflow:auto; background-color:rgb(247,247,247); border-top-left-radius:3px; border-top-right-radius:3px; border-bottom-right-radius:3px; border-bottom-left-radius:3px; color:rgb(51,51,51); margin-bottom:0px!important"><code style="font-family:SFMono-Regular,Consolas,'Liberation Mono',Menlo,Courier,monospace; padding:0px; margin:0px; background-color:transparent; border-top-left-radius:3px; border-top-right-radius:3px; border-bottom-right-radius:3px; border-bottom-left-radius:3px; word-break:normal; border:0px; display:inline; overflow:visible; line-height:inherit; word-wrap:normal">SELECT q_grid.cell::geometry(geometry,4258) as geo
FROM (
SELECT(ST_Dump(
cbg_content_based_balanced_grid(ARRAY['org_ar5.ar5_flate geo'],4000))
).geom AS cell) AS q_grid;</code></pre>
<div><br>
</div>
Lars<br>
<br>
<div style="color:rgb(0,0,0)">
<hr tabindex="-1" style="display:inline-block; width:98%">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" color="#000000" style="font-size:11pt"><b>Fra:</b> postgis-users <postgis-users-bounces@lists.osgeo.org> på vegne av Rémi Cura <remi.cura@gmail.com><br>
<b>Sendt:</b> 1. mars 2017 13:11<br>
<b>Til:</b> PostGIS Users Discussion<br>
<b>Emne:</b> Re: [postgis-users] Spatially ordering data</font>
<div> </div>
</div>
<div>
<div dir="ltr">
<div class="gmail_default" style="font-family:monospace,monospace">hey,<br>
</div>
<div class="gmail_default" style="font-family:monospace,monospace">the most classical approach would be to construct a
<a href="https://en.wikipedia.org/wiki/Quadtree">quad tree</a>.<br>
</div>
<div class="gmail_default" 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 class="gmail_default" style="font-family:monospace,monospace">This is also easy to code.<br>
</div>
<div class="gmail_default" style="font-family:monospace,monospace"><br>
</div>
<div class="gmail_default" style="font-family:monospace,monospace">Cheers,<br>
</div>
<div class="gmail_default" style="font-family:monospace,monospace">Rémi-C<br>
</div>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">2017-03-01 12:46 GMT+01:00 Darafei "Komяpa" Praliaskouski
<span dir="ltr"><<a href="mailto:me@komzpa.net" target="_blank">me@komzpa.net</a>></span>:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex; border-left:1px #ccc solid; padding-left:1ex">
<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/<wbr>2016/05/improve-your-st-<wbr>geohash-sorting-with-these-<wbr>three-simple-tricks/</a> <br>
<br>
for simplest case, you can just order by your geom field.</div>
<br>
<div class="gmail_quote">
<div dir="ltr">ср, 1 мар. 2017 г. в 14:44, Jonathan Moules <<a href="mailto:jonathan-lists@lightpear.com" target="_blank">jonathan-lists@lightpear.com</a>><wbr>:<br>
</div>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex; border-left:1px #ccc solid; padding-left:1ex">
<div>
<div class="h5"><u class="m_1092944026455885632gmail_msg"></u>
<div class="m_1092944026455885632gmail_msg">
<div class="m_1092944026455885632gmail_msg" style="font-size:10pt; font-family:Verdana,Arial,Helvetica,sans-serif">
Hi List,<br class="m_1092944026455885632gmail_msg">
I've got a database of about 60 million spatial features that I need to run through a process.<br class="m_1092944026455885632gmail_msg">
<br class="m_1092944026455885632gmail_msg">
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 class="m_1092944026455885632gmail_msg">
<br class="m_1092944026455885632gmail_msg">
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 class="m_1092944026455885632gmail_msg">
<br class="m_1092944026455885632gmail_msg">
Non-spatially this would be simple:<br class="m_1092944026455885632gmail_msg">
<br class="m_1092944026455885632gmail_msg">
<i class="m_1092944026455885632gmail_msg">SELECT * FROM my_table ORDER BY some_key ASC LIMIT 500000 OFFSET 0</i><br class="m_1092944026455885632gmail_msg">
<br class="m_1092944026455885632gmail_msg">
Does anyone have any suggestions for how to spatially order data with PostGIS?<br class="m_1092944026455885632gmail_msg">
<br class="m_1092944026455885632gmail_msg">
Thanks,<br class="m_1092944026455885632gmail_msg">
Jonathan<br class="m_1092944026455885632gmail_msg">
</div>
</div>
</div>
</div>
______________________________<wbr>_________________<br class="m_1092944026455885632gmail_msg">
postgis-users mailing list<br class="m_1092944026455885632gmail_msg">
<a href="mailto:postgis-users@lists.osgeo.org" class="m_1092944026455885632gmail_msg" target="_blank">postgis-users@lists.osgeo.org</a><br class="m_1092944026455885632gmail_msg">
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" class="m_1092944026455885632gmail_msg" target="_blank">https://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a></blockquote>
</div>
<br>
______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a><br>
</blockquote>
</div>
<br>
</div>
</div>
</div>
</div>
</body>
</html>