4

I have a set of points in a csv file like this (ie. a square):

-77.63333333;-11.75
-77.63333333;-12.25
-77.3;-12.25
-77.3;-11.75
-77.63333333;-11.75
-77.63333333;-11.75

After getting rid of the semi-colon (e.g., with the nice awk), I tried to make a closed polygon with the exact shape of the six pairs of coordinates in the csv file (ie. a square) with the following call:

ogr2ogr -f "ESRI Shapefile" tmp.shp tmp.gmt -a_srs EPSG:4326 -dialect sqlite -sql "select st_concavehull(st_collect(geometry)) from tmp"

It is obvious that st_concavehull is not st_makepolygon (so, it does not produce the desired square), and there should be an extra step to first connect all points with lines for this to work (e.g., st_makeline).

Is there a way to accomplish the desired squared polygon with ogr2ogr? Any hints are welcomed.

Improve this question
3
  • Are the points in the csv ordered correctly? Since you're editing it already can you add a sequential id and order by that in your query (use it for ordering in st_makeline)? Commented Mar 8, 2023 at 23:57
  • I haven't tried that one, thanks for suggesting it. Now, I am not sure whether st_makeline is or is not supported by ogr/sqlite (I think it is not). Commented Mar 9, 2023 at 21:45
  • looks like it's called makeline gaia-gis.it/gaia-sins/spatialite-sql-latest.html#p0 and I'm not sure if it's supported in ogr Commented Mar 9, 2023 at 22:10

1 Answer 1

Reset to default
1

You can also read your .csv file with GDAL directly without editing it. Just read the documentation https://gdal.org/drivers/vector/csv.html and use the syntax like

ogrinfo concave.csv -al -oo X_POSSIBLE_NAMES=field_1 -oo Y_POSSIBLE_NAMES=field_2

As you can read from the documentation of SpatiaLite https://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html, ST_ConcaveHull returns polygons.

The returned Geometry will always be of the Polygon or MultiPolygon type.

The command that you wrote looks good to me. However, I noticed that the ST_ConcaveHull function returns NULL that means an error if the number of points in the input is low. Six points were not enough but I did not try to find what is the minimum. You can test the query with your data with ogrinfo first.

ogrinfo -dialect sqlite -sql "select ST_ConcaveHull(ST_Collect(geometry)) from source" source.csv -oo X_POSSIBLE_NAMES=field_1 -oo Y_POSSIBLE_NAMES=field_2

I tested the query with OpenStreenMap data of Liechtenstein and it worked fine against a point layer (15286 points). The exact command was

ogr2ogr -f GeoJSON tmp.json liechtenstein.sqlite -dialect SQLite -sql "select st_concavehull(st_collect(geometry)) from points"
Improve this answer
4
  • 1
    Thanks, I know the command works well (I have used it before), that's not the issue, I just want to get the shape (ie. closed polygon) from the CSV file (ie. six pairs of coordinates in the example above), thus, a square. I updated this issue in the post. Commented Mar 5, 2023 at 21:48
  • With those points ST_ConvexHull makes a square ogrinfo concave.csv -al -oo X_POSSIBLE_NAMES=field_1 -oo Y_POSSIBLE_NAMES=field_2 -sql "select st_convexhull(st_collect(geometry)) from concave" -dialect sqlite. I do not understand what you wrote about ST_ConcaveHull "there should be an extra step to first connect all points with lines for this to work (e.g., st_makeline)". SpatiaLite builds a multipoint feature for ST_ConcaveHull in any case, and using linestring (-77.63333333 -11.75, -77.63333333 -12.25, -77.3 -12.25, -77.3 -11.75, -77.63333333 -11.75, -77.63333333 -11.75) fails as well. Commented Mar 6, 2023 at 7:17
  • 1
    you are right, st_convexhull did the trick, thanks for pointing that out. When I referred to the "extra" step, I assumed that it was necessary to connect all points (or create segments from these points) before applying, for instance, st_makepolygon. Now I will play with the st_convexhull (e.g., at 99%) to create non-squared polygons. In case you have another idea for such irregular polygons please do not hesitate to let me know, thanks again. Commented Mar 6, 2023 at 13:15
  • 1
    Remember the native documentation of the SpatiaLite functions that GDAL supports with the SQLite dialect gaia-gis.it/gaia-sins/spatialite-sql-latest.html. I am not sure what kind of shape you want to achieve. Concave hull leaves less empty areas within the polygon but the algorithm seems to require more points than the convex hull algorithm. If you want north-up rectangles, use ST_Envelope. Commented Mar 6, 2023 at 15:08

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.