[postgis-users] Spatially ordering data

[Burgholzer, Robert (DEQ)]

How about:
1. Take a centroid of each shape,
2. Rounding it’s x and y coordinates to whole lat/lon values
3. Ordering by lat_round, lon_round and these values would proceed in a gridded fashion.  

Ex:  select oid, floor(st_x(the_geom)) as x_index, floor(st_y(the_geom)) as y_index from precip_noaa_daily_grid order by x_index, y_index;
  oid  | x_index | y_index
-------+---------+---------
  2143 |     -84 |      36
  2142 |     -84 |      36
  2141 |     -84 |      36
  2620 |     -84 |      36
  2619 |     -84 |      36
  2618 |     -84 |      36


The only drawback is that if there were data that were added during processing, they may be skipped by your processor, however, if you were to set a flag to indicate that something was processed, you’d be covered.

Hth,
/r/b

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Jonathan Moules
Sent: Thursday, March 02, 2017 12:42 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Spatially ordering data

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