FELN: Find Existing Location with N Layers

In the layers-json post, I argued that the model needs to know what our data means before it can write a useful query. A field called STATUS with a value of 3 is not self-explanatory, and the aliases, domains, and subtypes already sitting in an ArcGIS Pro project are the cheapest explanation we will ever get. That post ended with a catalog, a Layers.json file, and a promise to do something with it.

This is the something. FELN stands for Find Existing Location with N layers, and it is a small Python library and CLI that turns a plain-language request into a structured spatial query, compiles that query to DuckDB SQL, and, the part I actually care about, measures how close one query is to another.

The shape of a question

Most of the spatial questions people ask me over the years have the same skeleton. Find the things in one layer, optionally filtered, that stand in some spatial relationship to the things in another layer, also optionally filtered. Wells near pipelines. Parcels inside a flood zone. Counties that contain at least one suspended well. The layers change, the predicates change, the relationship changes, but the skeleton does not.

So a FELN is exactly that skeleton, written down as three parallel lists:

“Find suspended wells within 5 kilometers of oil pipelines.”

{
  "layers":    ["Wells", "Pipelines"],
  "where":     ["STATUS = 2", "MEDIUM = 'OIL'"],
  "relations": ["withinDistance 5 kilometers"]
}

layers[0] is what comes back. where[i] filters layers[i], and an empty string means no filter. relations[i] relates the primary layer to layers[i+1]. One layer, no relations. Three layers, two relations, both anchored on the primary. The pipelines constrain the answer but are not returned, and the 2 in STATUS = 2 comes straight from the catalog's coded-value domain, which is the whole reason the catalog exists.

One thing to say early, because it shapes everything that follows: the WHERE clauses are simple on purpose. STATUS = 2, DEPTH > 1000, NAME LIKE 'Alpha%', a BETWEEN, a few of those joined with AND. That is the vocabulary, and it was never meant to be exhaustive. This is a starting point for the find-existing-location process, not a SQL grammar. The generator emits simple predicates so a small model has something tractable to learn, and the comparison scores them per predicate for the same reason. Nothing stops you from elaborating on it. The where entries are strings, the compiler passes them through a guard rather than a parser of its own, and a richer generator or a hand-written query can put more in them. But start simple. The interesting question is whether the loop closes at all, and simple predicates are enough to find out.

There is nothing clever in that JSON, and that is deliberate. I wanted something a model can emit, a person can read, and a program can compare. The cleverness, such as it is, lives on either side of it.

Why structure instead of SQL

My first instinct, back when I started this thread, was to have the model write the SQL directly. It works often enough to be seductive and fails in ways that are very hard to score. Two SQL strings that select the same rows can look nothing alike. Two that look almost identical can differ by one < versus <= and return different wells. If I cannot measure the output, I cannot improve the thing producing it.

The FELN structure makes the answer measurable in more than one way, and the library keeps those ways separate on purpose:

• Exact match. FELN.same() normalizes the WHERE clauses with sqlglot in DuckDB dialect, so cast(2 as SMALLINT) equals 2, identifiers are case-insensitive, and conjuncts are sorted before comparison. Secondary layers match by name, not position.
• Graded similarity. FELNCompare.partial gives credit per predicate. A dropped conjunct is worth about 0.67, an OR written as AND about 0.75, a swapped primary layer keeps half credit. Distances are compared in meters, so 5 miles and 8.05 kilometers can agree.
• Execution. When you have result sets, FELNCompare.cost blends a 70% Jaccard on the returned OBJECTIDs with 30% of the partial score, and precision and recall fall out of the ID sets in the usual way.

That last one is where the structure pays for itself twice. The same FELN can be compiled for DuckDB, or ArcPy, or Spark, each preserving its own units, coordinate systems, and boundary semantics. The generation side improves against a measurable target; the execution side is chosen for the data. Today only the DuckDB compiler exists. ArcPy and Spark are possible backends, not integrations I have written, and I would rather say that plainly than let a README imply otherwise.

What the compiler emits

FELNToDuckDB builds a chain of CTEs: one filtered L0..Ln per layer, one join CTE per spatial relation, and a final select that keeps a primary row only if it appears in every join. withinDistance becomes ST_DWithin, which includes the boundary. notWithinDistance becomes NOT EXISTS, because “more than 500 meters from every pipeline” checks the absence of any nearby pipeline, and finding one distant pipeline is not sufficient. I got that wrong once with a plain join and a >, and the result looked perfectly reasonable until I counted the rows.

The text side has the same boundary discipline. “No more than” and “at most” generate <=. “Under” and “less than” generate strict <. Distance wording only uses “within” and “no more than”, because the relation behind it is inclusive. An earlier version mapped “no more than” to <; it is fixed, the fix does not rewrite old datasets, and seeded output changed. If you generated examples before, regenerate them.

Generating the training pairs

The part that consumes the catalog is the generator. Point it at a Layers.json and it samples layers, fields, coded values, subtypes, and spatial relations into synthetic text/FELN pairs, one JSON object per line:

uv run feln generate "$HOME/Documents/ArcGIS/Projects/NorthSea/Layers.json" \
  -n 20 --seed 0 --normalize --sql --alias-suffix -o northsea-examples.jsonl

When a layer carries subtype labels, they supply the noun in the text, so a subtype Oil wells yields “Show oil wells” with kind = 1 in the WHERE, while meta.layers keeps the catalog layer name. --alias-suffix appends the layer alias to disambiguate labels shared across layers. --layer-only 0.25 phrases a quarter of the subtyped layers by alias alone, so the subtype column has to compete as an ordinary condition. These are synthetic pairs from a template, not a natural-language parser, and I want to be clear about that: the generator produces what a model should learn to emit, it does not read what a person typed.

On the NorthSea project, five catalog layers including two tables, I ran 1,000 samples in each of five generation modes, then ran them again to check reproducibility. Every record validated against the model, referenced only layers and columns in the catalog, parsed as a single DuckDB statement, and compared to its own normalized form with a structural score of 1 and a cost of 0. Profiling put nearly all of the generation time in SQL normalization, so a bounded lru_cache from the standard library took a cold run from 0.84 seconds to 0.29 for a thousand records. No new dependency.

What it is not

FELN generates SQL; it does not execute it and it is not a sandbox. The WHERE guard rejects some unsafe syntax, but it does not stop subqueries or database functions that read files. Catalog metadata, WHERE expressions, geometry and CRS options are trusted inputs. If you are going to run model-generated queries, enforce the allowed tables and columns in your application and lock the database process down; DuckDB has good guidance on that. Structural similarity and a successful parse are not security checks.

And no accuracy claim yet. The smoke tests cover generation, compilation, and comparison. DuckDB was not installed in the environment I tested from, so result IDs, CRS correctness, and runtime types were not validated against real features. Precision and recall need reference queries and execution results, and that benchmark is the next thing on the list, not something this post can report.

What interests me here is the loop. Catalog explains the data. Generator produces measurable pairs from that explanation. Compiler runs them. Compare scores the result. Close the loop with a small model and the numbers tell you whether supplying the meaning of a field actually helps, rather than whether it feels like it should. That is the experiment I set out to make possible, and it is now possible.

The source, the CLI reference, the worked examples, and the dated review notes are in the feln repository. It depends on layers-json for the catalog model, pydantic, sqlglot, and numpy, and it does not need GDAL. Try it on a project whose data you know, read the generated pairs, and tell me where the text and the SQL disagree. Those are the bugs I want.

More to come :-)