0

I have to keep going back to ArcGIS to sort a dataset spatially using the PEANO method that's described here - http://desktop.arcgis.com/en/arcmap/latest/tools/data-management-toolbox/how-sort-works.htm#ESRI_SECTION1_9C199CD3D68A41029943D4FF79500A73

I want to try to achieve the same using SQL - I have asked previously trying to do it using QGIS and haven't got a workable response.

For my data it's critical that the sort doesn't result in a multipart dataset unless two or more road segments with the same name are not connected. If they don't connect there's an issue with the segment.

The solution should work in the QGIS implementation of SQL that only allows the following - https://sqlite.org/lang.html

The data has roads that are segmented and sometimes the segments aren't sequential but join other segments on the same road. This causes issues when calculating the start/end/midpoints of the road once the data is joined by road name and so I need to sort it/dissolve it spatially.

|improve this question
  • SQLite lacks of window functions and I don't know how to rank these data with it. But with Postgis, it's more simple. I think you can create a PyQGIS script for sort X, Y coordinates as you want and update a sort field. – J. Monticolo Aug 8 at 14:09
  • Ok - If I brought the data into a geopackage or spatialite DB would I be able to do this in SQL? – GeorgeC Aug 8 at 14:25
  • QGIS Virtual Layers is based on Spatialite and geopackage is a Spatialite db. The lack is the same and I don't know how to have a rank() over(... on these db. But it's only my knowledge, maybe it's a solution I don't know. – J. Monticolo Aug 8 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.