Splitting SHP file based on property starting with an underscore

Question

I found a rather annoying "feature", when trying to split a SHP file based on values of a property, which has a name starting with and underscore (like _property). The problem occurred when I tried to process data on field bird backdrop in Baden-Württemberg. My goal was to split the data into separate files based on the value of a property called _Prio. To provide a minimum (not) working example, I used geopandas to generate a simple shp file:

import geopandas as gpd
gdf = gpd.GeoDataFrame.from_features(
    [
        {
            "type": "Feature",
            "properties": {"_property": "a"},
            "geometry": {
                "type": "Polygon",
                "coordinates": [
                    [
                        [0, 0],
                        [1, 0],
                        [1, 1],
                        [0, 1],
                        [0, 0],
                    ]
                ],
            },
        },
        {
            "type": "Feature",
            "properties": {"_property": "b"},
            "geometry": {
                "type": "Polygon",
                "coordinates": [
                    [
                        [1, 1],
                        [2, 1],
                        [2, 2],
                        [1, 2],
                        [1, 1],
                    ]
                ],
            },
        },
        {
            "type": "Feature",
            "properties": {"_property": "c"},
            "geometry": {
                "type": "Polygon",
                "coordinates": [
                    [
                        [2, 2],
                        [3, 2],
                        [3, 3],
                        [2, 3],
                        [2, 2],
                    ]
                ],
            },
        },
    ],
    crs=4326,
)
gdf.to_file("test.shp")

Next, I try to split the file into separate geojsons based on _property values:

#!/bin/bash
values=("a" "b" "c")
for item in "${values[@]}"; do
    echo "Processing $item"
    ogr2ogr -f GeoJSON -where "_property='$item'" output_"$item".geojson test.shp
done

This leads to a series of errors and empty geojson files:

Processing a

ERROR 1: SQL Expression Parsing Error: syntax error, unexpected invalid token. Occurred around :
_property='a'
^
ERROR 1: SetAttributeFilter(_property='a') on layer 'test' failed.
Processing b
ERROR 1: SQL Expression Parsing Error: syntax error, unexpected invalid token. Occurred around :
_property='b'
^
ERROR 1: SetAttributeFilter(_property='b') on layer 'test' failed.
Processing c
ERROR 1: SQL Expression Parsing Error: syntax error, unexpected invalid token. Occurred around :
_property='c'
^
ERROR 1: SetAttributeFilter(_property='c') on layer 'test' failed.

• Trying to put single quotes around property name (like '_property') creates valid geosjon files with no features.
• Using -sql with -dialect sqlite and a valid query gives the same results.
• Replacing = operator with LIKE changes nothing - the issue really seams to be the underscore at the beginning of the property name.

I know I can just read this into a GeoDataFrame and deal with it in Python, but I feel like this is something that should work with ogr2ogr. Am I missing anything here?

I'm working on a MBP with Sonoma 14.5, running GDAL 3.9.0 installed via Homebrew. Python code was run using Python 3.12.2 and geopandas 1.0.0.

Answer 1

It should be valid in SQL to have field names beginning with an underscore https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS but I have seen that sometimes they are not accepted if not enclosed with double quotation marks (delimited identifiers).

From the command line this works for me. Notice that the inner double quotes must be escaped with \-

ogr2ogr -f GeoJSON -where "\"_property\"='a'" output_a.geojson test.shp

This is wrong: "single quotes around property name (like '_property')"

In SQL single quotes enclose strings, and you made a comparison if string _property equals string a.