spatial-joins-and-keys
Skill2 starsChanged 5 days agoApache-2.0View on GitHub
Getting attribute joins and spatial joins right, covering key types, leading-zero padding (CSJ, county FIPS, ZIP), case and whitespace, null keys, one-to-many row blowups, intersects vs within vs contains, boundary effects and reporting the match rate honestly. Use it when asked to "join a table to a layer", "spatial join points to polygons", "match records by ID", "why didn't my join match", "low match rate", or when a join returns too many rows.
In this repository15 other files it gives its agents.
- CLAUDE.mdCLAUDE.md
- arcpy-scriptingSkill
- crs-and-datumsSkill
- enterprise-geodatabasesSkill
- imagery-and-stacSkill
- lidar-with-pdalSkill
- linear-referencingSkill
- maps-and-deliverablesSkill
- open-data-sourcingSkill
- raster-and-terrainSkill
- sop-conversionSkill
- spatial-data-qaSkill
- spatial-sqlSkill
- working-with-columbusSkill
- writing-clearly-and-conciselySkill
The file
We split this skill into the rules it gives an agent
11 rules. About 0 are common, 7 are distinctive, and 4 are very specific to this project.
≈0
Common · estimate
Many files like this have a rule of this kind.
≈7
Distinctive · estimate
This author's own choices. Worth a look.
4
Very specific
Tied to their project's names, files or tools. Probably skip.
- Common
- Distinctive
- Very specific
- Distinctive: line 20
Use it when there is no shared key, or to check that the shared key agrees with location.
- Distinctive: line 29
Look at ten real values from each side next to each other.
- Distinctive: line 48
Keep the original field and add the normalized key as a new field.
- Distinctive: line 69
If a key repeats on the right side, every left row with that key is copied once per repeat.
- Distinctive: line 76
Never "fix" a blowup by dropping duplicates afterwards without knowing which row was right.
- Distinctive: line 100
Always give, for the left side:
- Distinctive: line 108
Use a left join to see what did not match.
- Very specific: line 162
When unmatched keys match once leading zeros are ignored, the fix names the call: "K of them match once leading zeros are ignored: join …
- Very specific: line 165
When they match once spaces and letter case are ignored, the fix says to join again with "trim": true, "ignore_case": true.
- Very specific: line 199
If the right input is in another CRS, it is reprojected to the left's, with a crs.agreement info check.
- Very specific: line 208
Before building a join, spatial join or intersect, Columbus counts the rows it would produce.
≈ “Common” is an estimate: at least 1 in 5 of the 364,980 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.
Files
Spatial joins and keys
A join that runs is not a join that worked. The usual failures are quiet: keys that lost their zeros, numbers joined to text, nulls that never match, a right side that repeats keys and multiplies rows, and points on a boundary that match two polygons. This skill is about catching those before reporting a result.
General practice (with or without Columbus)
1. Decide what kind of join you need
- Attribute join: rows match when a key field is equal on both sides (route id, CSJ, FIPS code, parcel id). Fast and exact, if the keys are clean.
- Spatial join: rows match when geometries meet a predicate (a point within a county). Use it when there is no shared key, or to check that the shared key agrees with location.
- Decide the expected cardinality before you run it: one-to-one, many-to-one (many crashes to one county), or one-to-many (one route to many segments). Write it down; it is how you will know the result is wrong.
2. Profile the keys on both sides first
For each key: its type (text or number), its length range, how many distinct values, how many nulls and empty strings, and whether it repeats on the side that should be unique. Look at ten real values from each side next to each other.
3. Text vs number, and leading zeros
Codes are text, even when they are all digits. Storing them as numbers drops leading zeros, and spreadsheets do it silently on open.
| Key | Correct form | Typical damage |
| TxDOT CSJ (control-section-job) | 9 characters: 4 control + 2 section + 3 job, e.g. 001513077 (shown as 0015-13-077) | 1513077 after Excel; dashes on one side only |
| County FIPS | 3 characters, e.g. Bexar 029, Travis 453, Harris 201 | 29 |
| State + county FIPS (GEOID) | 5 characters, e.g. 48029, 48453 | 48029 survives, but 06037 (Los Angeles) becomes 6037 |
| State FIPS | 2 characters: Texas 48, California 06 | 6 |
| Census tract GEOID | 11 characters | Scientific notation in spreadsheets |
| ZIP code | 5 characters | Texas ZIPs start with 7 and survive; New England 02134 becomes 2134 |
| Route ids | As the LRS defines them, e.g. IH0035, SH0071 | IH35, IH 35, ih0035 |
Fix: convert both sides to text, then left-pad with zeros to the documented width. Pad to the width the data dictionary gives, not to "whatever the longest value is". Keep the original field and add the normalized key as a new field.
ZIP codes are not areas. Census ZCTAs approximate them; a ZIP-to-ZCTA join is many-to-one and does not cover PO-box-only ZIPs.
4. Case, whitespace and formatting
'IH0035','ih0035'and'IH0035 'are three different keys. Fixed-widthCHAR(n)columns in Oracle and SQL Server often carry trailing spaces.- Normalize the same way on both sides: trim, upper-case, remove separators (
-, spaces) only when the dictionary says they are not significant. - Watch for look-alike characters in hand-typed keys:
Ovs0,lvs1, non-breaking spaces.
5. Nulls never match
A null or empty key cannot match anything, and must not be allowed to match another null. Count them separately from "has a key, no partner". Report them as their own number.
6. One-to-many blowups
If a key repeats on the right side, every left row with that key is copied once per repeat. Sums and counts afterwards are inflated.
- Count rows before and after. More rows out than left rows in means the right side repeats keys.
- Decide: is one-to-many intended (keep it, say so), or should the right side be deduplicated (pick the current record) or aggregated (sum, count, max) first?
- Never "fix" a blowup by dropping duplicates afterwards without knowing which row was right.
7. Spatial predicates and boundaries
| Predicate | True when | Watch out |
intersects | The geometries share any point, including boundaries | A point on a shared county line matches both counties; a road crossing three districts matches all three |
within | Left is inside right, and not only on its boundary | A point exactly on a boundary matches nothing |
contains | Right is inside left (the reverse of within) | Same boundary rule, from the other side |
touches | Boundaries meet but interiors do not | Rarely what an assignment needs |
crosses, overlaps | Lines crossing; same-dimension partial overlap | For network or overlay questions |
- For "which polygon is this point in", use
within, and look at points that matched none (on a boundary, outside the area, or a CRS mismatch). - For "which polygon does this line or polygon belong to", a plain predicate matches several. Assign by largest overlap, by a representative point inside the feature, or by the start point, and say which rule you used.
- Both inputs must be in the same CRS. Check it before you trust a zero-match result.
- Data digitized at different scales do not line up exactly. A point 2 m outside a parcel may still belong to it; a small buffer in a projected CRS, or snapping, can help, but say that you used a tolerance and what it was.
8. Report the match rate honestly
Always give, for the left side:
- total rows, matched, unmatched, and the percentage unmatched;
- how many of the unmatched had a null or empty key;
- rows out vs rows in (one-to-many);
- a few examples of unmatched keys, and the likely reason (padding, case, a code not in the lookup, out of area).
Use a left join to see what did not match. An inner join hides unmatched rows. Never drop unmatched rows without saying how many and why. "Joined 4,812 of 5,000 crashes to segments (96.2%); 188 had no match, 41 of them with an empty CSJ" is a good report. "Join complete" is not.
If the Columbus tools are available
Look at the keys first
describe_dataset {"connection": "roads-prod", "dataset": "ROADS.ROADWAY_INVENTORY"}
sample_rows {"connection": "roads-prod", "dataset": "ROADS.ROADWAY_INVENTORY", "n": 20}
context_search {"query": "CSJ", "kind": "dataset"}describe_dataset gives each field's type. sample_rows shows real values: padded or not,
case, nulls. The context pack often says the rule outright, for example "CSJ values from
spreadsheets often lose their leading zeros; pad to 9 before joining".
Attribute join
vector_op {
"op": "join",
"inputs": {"left": "austin:roads",
"right": {"connection": "austin-ref", "dataset": "surfaces"}},
"params": {"left_key": "route_id", "right_key": "route_id", "how": "left"}
}right_keydefaults toleft_key.howisleft(default) orinner.- Rows with a null or empty key are never matched, and on a left join they are kept.
- The output keeps the left geometry; the right side's geometry is dropped. Right fields
whose names clash get a
_rightsuffix. - CSV inputs are read as text, so
"0042"stays"0042". Key options, applied the same way to both sides before matching:
key_as_text: true: compare as text (whole numbers lose any trailing.0).pad: N: zero-fill all-digit keys to N characters ("42"becomes"000000042"with 9).trim: true: remove leading and trailing spaces.ignore_case: true: compare without letter case.
The keys are compared on normalized copies: the output keeps each side's original values. A
join.keys_normalizedinfo check says what was done. The options must be realtrueorfalse;"false"as text is refused.
Checks:
join.key_types(refused): one key is a number (or true/false) and the other is text, so nothing would match. Join again with"key_as_text": true, and add"pad": Nif the codes are ones that lost their leading zeros.join.unmatched: message "N of M rows (P%) had no match on<key>", plus "; K of those have an empty<key>, which never matches" when there are empty keys.detailis{left, matched, empty_keys}. Severity isinfoat 0 unmatched,warnbelow 40%,failat 40% or more.- When unmatched keys match once leading zeros are ignored, the
fixnames the call: "K of them match once leading zeros are ignored: join again with "key_as_text": true, "pad": W". - When they match once spaces and letter case are ignored, the fix says to join again with
"trim": true, "ignore_case": true. - Other causes (separators such as
-, a code that is not in the other table) are not detected. Comparesample_rowsfrom both sides yourself.
- When unmatched keys match once leading zeros are ignored, the
join.many_to_many(warn): "The join produced X rows from N: D key values repeat on the right side",detail{left, out}. Deduplicate or aggregate the right side, or confirm that one-to-many is intended.
A padded CSJ join, after the first attempt reported lost zeros:
vector_op {
"op": "join",
"inputs": {"left": "roads-prod:ROADS.ROADWAY_INVENTORY", "right": "ws://<session>/results/surfaces-<id>.csv"},
"params": {"left_key": "csj", "key_as_text": true, "pad": 9}
}For other normalization (removing separators, mapping old codes to new), build a cleaned
table with sql_select and join its handle; the result is a CSV, so keys stay text. For
example REPLACE(csj, '-', ''). Say in the report what you changed.
Spatial join
vector_op {
"op": "spatial_join",
"inputs": {"left": "ws://<session>/results/crashes-<id>.gpkg", "right": "austin-ref:county"},
"params": {"predicate": "within", "how": "left"}
}predicate:intersects(default),within,contains,touches,crosses,overlaps.how:left(default) orinner.- If the right input is in another CRS, it is reprojected to the left's, with a
crs.agreementinfo check. join.unmatchedhere isinfoorwarnonly, neverfail: "N of M features (P%) had no match (within)". Judge a high rate yourself.join.many_to_many(warn): "some features match more than one". The fix suggests aggregating the right side or usingwithin.
Output size
Before building a join, spatial join or intersect, Columbus counts the rows it would
produce. Past the organization's read limit (default 200,000) it refuses:
Refused (output.too_large): join would produce N rows, more than the read limit of L.
Deduplicate or aggregate the right side, or narrow the inputs with where. Do not split the
job into pieces to get under the limit without telling the person.
A dataset input bigger than the read limit is refused before any join runs. A handle that
was cut off when it was made carries input.truncated into the join result: the match rate
then describes only part of the data.
Report
Quote join.unmatched and join.many_to_many messages as they are, add the empty-key
count, the fix you applied (if any), and the provenance id of the join.
Does it work?
Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.
No reviews yet
People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.
- It worked
- Partly
- It didn't
Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.
Your trail
Files you open will collect here, so you can find them again.
Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.
Now do this for your own.
Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.

