nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Help with Feature Layer where query

LindsayRaabe_FPCWA

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.

Encontre mais publicações marcadas com

Accepted answers

CodyPatterson

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

All comments

Hayden Welch

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.

LindsayRaabe_FPCWA

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

LindsayRaabe_FPCWA

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!

LindsayRaabe_FPCWA

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!

Hayden Welch

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.

abaldenweck@blm.gov_BLM_EGIS

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)

CodyPatterson

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

nafizpervez

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!

Participe da Conversa

Membros conectados podem postar, seguir atualizações e mais. Novo aqui? Registre uma conta gratuita.

Links Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.