topic Re: Help with Feature Layer where query in Python Questions

Help with Feature Layer where query

LindsayRaabe_FPCWA — Mon, 24 Feb 2025 06:40:20 GMT

HI Brains Trust. I'm having some trouble with a feature layer query. I can't seem to find answers anywhere.

Simply put, I have a feature service URL (from ArcGIS Online) and I'm trying to get a list of features filtered to exclude rows with a particular value in the "inspection_number" field.

Below is my code - what am I doing wrong? I keep getting Error Code: 400 - 'where' parameter is invalid

fs1_features = fs1_layer.query(where='inspection_number != "Ad-hoc Inspection"', out_fields=['objectid', 'globalid', 'date_of_inspection', 'plantation', 'years_planted', 'username', 'inspection_number'], return_geometry=False).features

I've successfully made it work using an objectid value e.g. fs1_features = fs1_layer.query(where='inspection_number > 1000') but can't make it work with a text value.

Re: Help with Feature Layer where query

nafizpervez — Tue, 25 Feb 2025 07:38:52 GMT

To ensure clarity and consistency, it's better to use the <> operator for "not equal" in SQL, as it is the standard for many SQL dialects. Therefore, the WHERE condition should be written as:

WHERE inspection_number <> 'Ad-hoc Inspection'

This approach avoids ambiguity and aligns with best practices in SQL queries. Let me know if you need further clarification!

Re: Help with Feature Layer where query

CodyPatterson — Mon, 24 Feb 2025 12:04:51 GMT

Hey @LindsayRaabe_FPCWA

In addition to what nafizpervez mentioned, I like to use the <> as well, but I believe the issue comes in where you're using the double quotes instead of single quotes, here's what I would try:

fs1_features = fs1_layer.query(where="inspection_number <> 'Ad-hoc Inspection'", out_fields=['objectid', 'globalid', 'date_of_inspection', 'plantation', 'years_planted', 'username', 'inspection_number'], return_geometry=False).features

SQL is pretty particular about using the single quotes when referencing string values.

Cody

Re: Help with Feature Layer where query

AlfredBaldenweck — Mon, 24 Feb 2025 14:01:54 GMT

This is probably it. SQL reads double quotes as field names and single quotes as strings. (This is one of the ways you can call a field name with a space in it, by encasing it in double quotes, iirc)

Re: Help with Feature Layer where query

HaydenWelch — Mon, 24 Feb 2025 19:47:13 GMT

This is absolutely overkill, but I've been on a class kick lately and have actually been working on a SQLQuery class that can help out with this:

from __future__ import annotations class SQLQuery: def __init__(self, field: str, comparisons: set=None): self.field = field if ' ' in field: self.field = f'"{field}"' # Wrap spaced field in "" self.comparisons = comparisons if not comparisons: self.comparisons = set() # initialize empty set def _guard(self, value: str | int | float) -> str | int | float | None: """Reject any non standard inputs, add single quotes to strings NOTE: This does not sanitize the input! Someone could still inject SQL queries into a string value. """ if isinstance(value, str): value = f"'{value}'" elif not isinstance(value, int | float): raise ValueError(f"Invalid Comparison {value}, must be str | int | float") if value in self.comparisons: value = None return value def __add__(self, value: str | int | float) -> SQLQuery: comps = set(self.comparisons) comps.add(self._guard(value)) return SQLQuery(self.field, comps) def __sub__(self, value: str | int | float) -> SQLQuery: comps = set(self.comparisons) comps.remove(self._guard(value)) return SQLQuery(self.field, comps) def append(self, value: str | int | float) -> None: self.comparisons.add(self._guard(value)) def extend(self, comparisons: list) -> None: for value in comparisons: self.comparisons.add(self._guard(value)) def __repr__(self) -> str: return f"SQLQuery({self.field=}, {self.comparisons=})" def _compare(self, inclusive: bool): # No comparison returns all if not self.comparisons: return "1 = 1" # Use single op for one comparison if len(self.comparisons) == 1: return f"{self.field} {'=' if inclusive else '<>'} {list(self.comparisons)[0]}" # Use IN operator for multiple comparisons if len(self.comparisons) > 1: return f"{self.field} {'IN' if inclusive else 'NOT IN'} ({','.join(map(str, self.comparisons))})" @property def inclusive(self) -> str: return self._compare(inclusive=True) @property def exclusive(self) -> str: return self._compare(inclusive=False)

It's pretty basic, but it lets you write queries like this:

>>> query = SQLQuery('inspection_number') >>> out_fields = ['objectid', 'globalid', 'date_of_inspection', ...] >>> fs1_features = fs1_layer.query( ... where=(query + 'Ad-hoc Inspection').exclusive, ... out_fields=out_fields, ... return_geometry=False).features >>> query SQLQuery(field='inspection_number', comparisons=set()) >>> (query + 'Ad-hoc Inspection').exclusive "inspection_number <> 'Ad-hoc Inspection'" >>> (query + 'Ad-hoc Inspection' + 'Other').exclusive "inspection_number NOT IN ('Ad-hoc Inspection','Other')" >>> (query + 'Ad-hoc Inspection' + 'Other').inclusive "inspection_number IN ('Ad-hoc Inspection','Other')" >>> # Joining Queries >>> query1 = SQLQuery('field') >>> query2 = SQLQuery('field2') >>> ' AND '.join( ... [ ... (query1 + 'val1').inclusive, ... (query2 + 'val2').exclusive ... ] ... ) "field = 'val1' AND field2 <> 'val2'"

I hate having to keep track of a ton of hardcoded SQL strings in my projects, so using a little interface like this can sometimes make it easier. Plus the query construction is handled by the inclusive and exclusive properties, so you'll always be sending a query with that format.

Re: Help with Feature Layer where query

LindsayRaabe_FPCWA — Tue, 25 Feb 2025 01:02:32 GMT

I swear I tried this and every other combination of single and double commas possible, yet it continuously failed. Copy and pasted your code straight over mine and it worked first time. 🤦

Thanks for helping! Now to move on to the next issue!

Re: Help with Feature Layer where query

LindsayRaabe_FPCWA — Tue, 25 Feb 2025 01:43:53 GMT

Yeah - this is next level and I'm definitely not requiring something that functional. Thanks for sharing though. Somone else may find it suitable for their project!

Re: Help with Feature Layer where query

LindsayRaabe_FPCWA — Tue, 25 Feb 2025 01:45:04 GMT

Thanks - I suspect it ended up being both the <> and the incorrect "" '' usage as discussed below.

Re: Help with Feature Layer where query

HaydenWelch — Tue, 25 Feb 2025 04:13:04 GMT

Oh course! I'm just trying to get as much code onto this platform as possible honestly. There's a general lack of extensive solutions and I'm hoping that answers like this will help guide the next generation of people trying to write GIS code.