[postgis-users] Spatially ordering data

Lars Aksel Opsahl Lars.Opsahl at nibio.no
Wed Mar 1 04:40:31 PST 2017


Hi


Here https://github.com/larsop/content_balanced_grid is some generic code that you can use for this.



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.



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;

Lars

________________________________
Fra: postgis-users <postgis-users-bounces at lists.osgeo.org> på vegne av Rémi Cura <remi.cura at gmail.com>
Sendt: 1. mars 2017 13:11
Til: PostGIS Users Discussion
Emne: Re: [postgis-users] Spatially ordering data

hey,
the most classical approach would be to construct a quad tree<https://en.wikipedia.org/wiki/Quadtree>.
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<mailto: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<mailto: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<mailto:postgis-users at lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org<mailto: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/20170301/26b9e450/attachment.html>


More information about the postgis-users mailing list