3

I've downloaded some polygons to import them into a SQL Server geography instance for some testing. My issue is that every time I export them to a .csv file using QGIS 3.36.3, the Polygons are output using the Right hand rule and SQL Server requires polygons to use the Left hand rule.

I have downloaded every data set available from https://www.data.gov.uk/dataset/8e3a4564-8081-42ec-8772-03ade11d4acf/local-authority-boundaries-scotland and https://boundaries.scot/boundary-maps/data-files but every dataset that I download outputs in the same way. I have looked on every online resource I can find and have found that I'm able to force the right hand rule using the processing toolbox in QGIS, and have also found what appear to be many other different ways to force the output to RHR, but I haven't found anything to force the LHR.

SQL Server has the facility to reorient polygons to use the LHR, but this doesn't work on Multipolygons - and there are too many sub-polygons in the dataset for me to be able to pull out each individual polygon to correct them.

Is there a (reasonably) simple way within QGIS of forcing the LHR, or reorienting the polygons so that SQL is happy with the data I'm trying to import into it?

3

2 Answers 2

5

There is a way to reverse the polygons directly without turning them into linestrings and back. QGIS has a function "polygon_force_ccw" https://docs.qgis.org/3.34/en/docs/user_manual/expressions/functions_list.html#force-polygon-ccw for that purpose.

Forces a geometry to respect the convention where exterior rings are counter-clockwise, interior rings are clockwise.

Shapefiles use the clockwise orientation for the outer rings. That the source data follows that convention can be checked with ogrinfo

ogrinfo -dialect SQLite -sql "select ST_IsPolygonCCW(geometry) from SP_regions_1999_2011" SP_regions_1999_2011.shp
INFO: Open of `SP_regions_1999_2011.shp'
      using driver `ESRI Shapefile' successful.

Layer name: SELECT
Geometry: None
Feature Count: 8
Layer SRS WKT:
(unknown)
ST_IsPolygonCCW(geometry): Integer (0.0)
OGRFeature(SELECT):0
  ST_IsPolygonCCW(geometry) (Integer) = 0
...

Confirmed that the polygons are not counter-clockwise.

Then open the data with QGIS and create a new layer with the "Geometry by expression" tool. Use expression "force_polygon_ccw($geometry)".

enter image description here

Save the new layer into CSV and check the result with ogrinfo.

ogrinfo -dialect SQLite -sql "select ST_IsPolygonCCW(geom_WKT) from ccw" ccw.csv
INFO: Open of `ccw.csv'
      using driver `CSV' successful.

Layer name: SELECT
Geometry: None
Feature Count: 8
Layer SRS WKT:
(unknown)
ST_IsPolygonCCW(geom_WKT): Integer (0.0)
OGRFeature(SELECT):0
  ST_IsPolygonCCW(geom_WKT) (Integer) = 1

OGRFeature(SELECT):1
  ST_IsPolygonCCW(geom_WKT) (Integer) = 1
...

The polygons in the CSV file are now counter-clockwise.

I did not test a Python way, but with a small modification to this answer https://gis.stackexchange.com/a/412670/30184 it should be easy.

1
  • That's amazing! Thank you so much for your help! Commented Jun 25 at 21:24
2

Huge thanks to Babel for suggesting reverse line direction.

Converting the polygons to lines, reversing direction and then converting those lines back into Polygons worked perfectly and even a luddite like me was able to do it without causing any chaos.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.