[postgis-users] Spatially ordering data

[Jonathan Moules]

Thanks for the suggestions. Some thoughts after testing:


Darafei: (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.
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.

Lars: (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.

Remi: 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.

Are there any other options? I may consider trying to glue together the two approaches, but that's going to take some thought.

Thanks,
Jonathan

---- On Wed, 01 Mar 2017 12:56:58 +0000 Travis Kirstine <traviskirstine at gmail.com> wrote ---- 

if you go with Remi suggestion you then can cluster the quad tree index to reorder records  


http://postgis.net/docs/performance_tips.html






On 1 March 2017 at 07:11, Rémi Cura <remi.cura at gmail.com> wrote:
hey,

the most classical approach would be to construct a quad tree.

You can think of it as a grid that adapts its resolution locally according to the data.

This is also easy to code.



Cheers,

Rémi-C



2017-03-01 12:46 GMT+01:00 Darafei "Komяpa" Praliaskouski <me at komzpa.net>:
Have a look here:

http://www.paulnorman.ca/blog/2016/05/improve-your-st-geohash-sorting-with-these-three-simple-tricks/ 

for simplest case, you can just order by your geom field.

ср, 1 мар. 2017 г. в 14:44, Jonathan Moules <jonathan-lists at lightpear.com>:

Hi List,
I've got a database of about 60 million spatial features that I need to run through a process.

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.

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.

Non-spatially this would be simple:

    SELECT * FROM my_table ORDER BY some_key ASC LIMIT 500000 OFFSET 0
    
Does anyone have any suggestions for how to spatially order data with PostGIS?

Thanks,
Jonathan




_______________________________________________
 postgis-users mailing list
 postgis-users at lists.osgeo.org
 https://lists.osgeo.org/mailman/listinfo/postgis-users
 
_______________________________________________
 postgis-users mailing list
 postgis-users at lists.osgeo.org
 https://lists.osgeo.org/mailman/listinfo/postgis-users



 


_______________________________________________
 postgis-users mailing list
 postgis-users at lists.osgeo.org
 https://lists.osgeo.org/mailman/listinfo/postgis-users



 _______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170302/056a03ec/attachment.html>