#!/usr/bin/env python3 """ count_charleston.py — Sterling Digital Partners field study No. 05 Charleston Tri-County, South Carolina: the point-of-sale reset. Reproduces EVERY figure published at https://glvtl.com/field-study/charleston-commercial-record/ Standard library only. No API key, no login, no fee. python3 count_charleston.py # count and print python3 count_charleston.py --csv out.csv # also write the counts CSV python3 count_charleston.py --cache DIR # cache raw pulls in DIR Sources, all public ArcGIS REST services, read 20 September 2026: Charleston County gisccapps.charlestoncounty.org/arcgis/rest/services/ ProVal/ParcelMap/MapServer/0 Berkeley County gis.berkeleycountysc.gov/arcgis/rest/services/ API/CAD_API/MapServer/0 Dorchester County services.arcgis.com/ehmkfEMN55hUsomR/ArcGIS/rest/ services/Parcels/FeatureServer/0 TWO TRAPS THIS SCRIPT EXISTS TO HANDLE — both cost us hours: 1. Charleston's service returns MORE rows than it advertises. Paging it with resultOffset returns 197,677 rows against an advertised count of 197,620. Setting orderByFields does NOT fix this — we tried, and still got 57 duplicate OBJECTIDs. The only reliable fix is to de-duplicate on the OBJECTID and reconcile the result against returnCountOnly. Berkeley and Dorchester page clean, so this is a property of that one service, not of ArcGIS. Every pull below is reconciled regardless. 2. Dorchester's Legal_ResideNOcYES field holds 'YES'/'NO', not 'Y'/'N'. A where-clause of Legal_ResideNOcYES='N' returns zero rows and no error. Licence: CC BY 4.0. Attribute to Sterling Digital Partners with a link to the study above. """ import argparse, csv, datetime, json, os, statistics, sys, time import urllib.parse, urllib.request UTC = datetime.timezone.utc CHARLESTON = ("https://gisccapps.charlestoncounty.org/arcgis/rest/services" "/ProVal/ParcelMap/MapServer/0") BERKELEY = ("https://gis.berkeleycountysc.gov/arcgis/rest/services" "/API/CAD_API/MapServer/0") DORCHESTER = ("https://services.arcgis.com/ehmkfEMN55hUsomR/ArcGIS/rest" "/services/Parcels/FeatureServer/0") # Charleston class codes we count as commercial. Published so the # classification is auditable and arguable; it is ours, not the county's. CHS_COMMERCIAL = { "167", # CONDO COMMON COMM "195", # COMM-APP-RES "200", # SPCLTY-APT "250", # SPCLTY-COMMCONDO "300", # BUILDNG-ONLY "304", # MFG/INDUST "460", # AUTO-PARKING "500", # General Commercial "530", # SPCLTY-RTL "580", # SPCLTY-RST "630", # SPCLTY-WHS "650", # SPCLTY-OFC "700", # SPCLTY-HTL "730", # GOLF COURSE "910", # COM-DEV-ACRS "952", # VAC-COMM-LOT } # South Carolina statutory assessment ratios, S.C. Code Ann. 12-43-220. RATIO_LEGAL_RESIDENCE = 0.04 # 12-43-220(c), owner-occupied RATIO_OTHER = 0.06 # 12-43-220(e), "all other real property" # NB 12-43-220(f) is PERSONAL property at 10.5% and is not the commercial # ratio. 12-37-3135(B)(1) cross-references (e) alone. ATI_EXEMPTION = 0.25 # 12-37-3135(B)(2)(a), opt-in, 6% property only BUCKETS = [("Sold before 2012", 0, 2011), ("Sold 2012-2014", 2012, 2014), ("Sold 2015-2017", 2015, 2017), ("Sold 2018-2021", 2018, 2021), ("Sold 2022-2025", 2022, 2026)] FIGURES = [] # (metric, value, share, unit, finding, note) def fig(finding, metric, value, unit="", note="", share=None): sh = "" if share is None else f"{share:.1f}%" FIGURES.append((metric, value, sh, unit, finding, note)) val = f"{value:,}" if isinstance(value, int) else ( f"{value:,.1f}" if isinstance(value, float) else str(value)) print(f" {metric:<62} {val:>14} {unit} {sh}") return value # -------------------------------------------------------------------------- # ArcGIS plumbing # -------------------------------------------------------------------------- def _get(url, params, tries=4): q = urllib.parse.urlencode(params) last = None for i in range(tries): try: with urllib.request.urlopen(url.rstrip("/") + "/query?" + q, timeout=180) as r: d = json.load(r) if "error" in d: raise RuntimeError(d["error"]) return d except Exception as e: # noqa: BLE001 last = e if i < tries - 1: time.sleep(2 * (i + 1)) raise RuntimeError(f"{url}: {last}") def advertised(url, where="1=1"): return _get(url, {"where": where, "returnCountOnly": "true", "f": "json"})["count"] PAGED_RAW = {} def pull(url, fields, oid_field, page=1000, cache=None, label=""): """Page a layer, de-duplicate on OBJECTID, reconcile against the count.""" if cache: path = os.path.join(cache, label + ".json") if os.path.exists(path): raw_rows = json.load(open(path)) # The cache holds the rows AS PAGED, before de-duplication, so a # cached run reports the same paged count as a cold one. Getting # this wrong once published a CSV whose paged-row figure silently # equalled the de-duplicated figure. PAGED_RAW[label] = len(raw_rows) rows = list({r[oid_field]: r for r in raw_rows}.values()) print(f" {label}: {len(raw_rows):,} rows from cache, " f"{len(rows):,} distinct") return rows want, rows, off = advertised(url), [], 0 while True: d = _get(url, {"where": "1=1", "outFields": ",".join(fields), "returnGeometry": "false", "orderByFields": oid_field + " ASC", "resultOffset": off, "resultRecordCount": page, "f": "json"}) feats = d.get("features", []) if not feats: break rows += [f["attributes"] for f in feats] off += len(feats) if len(feats) < page: break raw_rows = rows raw = len(rows) PAGED_RAW[label] = raw rows = list({r[oid_field]: r for r in rows}.values()) if raw != len(rows): print(f" {label}: paged {raw:,} rows, {raw - len(rows):,} of them " f"duplicate OBJECTIDs -> {len(rows):,} distinct") if len(rows) != want: raise SystemExit(f"{label}: reconciliation FAILED, {len(rows):,} " f"distinct rows against {want:,} advertised") print(f" {label}: {len(rows):,} rows, reconciled against " f"returnCountOnly ({want:,}) OK") if cache: os.makedirs(cache, exist_ok=True) json.dump(raw_rows, open(os.path.join(cache, label + ".json"), "w")) return rows def epoch_year(ms): if not ms: return None return datetime.datetime.fromtimestamp(ms / 1000, UTC).year def slash_year(s): """Dorchester stores SALE_DATE as an 'M/D/YYYY' string.""" if not s: return None parts = str(s).strip().split("/") if len(parts) != 3: return None try: y = int(parts[2]) except ValueError: return None return y if 1700 < y < 2100 else None def pct(a, b): return 100.0 * a / b if b else 0.0 def gradient(label, rows, year_of, gap_of, section): """Median taxable-value gap by the year the property last changed hands.""" print(f"\n {label}: median shortfall below the county's own market value") out = [] for name, lo, hi in BUCKETS: vals = sorted(gap_of(r) for r in rows if (y := year_of(r)) is not None and lo <= y <= hi) if not vals: continue med = 100 * statistics.median(vals) share = pct(sum(1 for v in vals if v > 0.01), len(vals)) out.append((name, len(vals), med, share)) print(f" {name:<18} n={len(vals):>6,} median {med:>5.1f}% " f"below market {share:>5.1f}% show any gap") FIGURES.append((f"{label} / {name} / parcels", len(vals), "", "parcels", section, "")) FIGURES.append((f"{label} / {name} / median gap below market", round(med, 1), f"{med:.1f}%", "%", section, "")) FIGURES.append((f"{label} / {name} / share showing any gap", round(share, 1), f"{share:.1f}%", "%", section, "gap greater than 1%")) return out # -------------------------------------------------------------------------- def main(): ap = argparse.ArgumentParser() ap.add_argument("--csv") ap.add_argument("--cache") a = ap.parse_args() print(f"\ncount_charleston.py — run {datetime.datetime.now(UTC):%Y-%m-%d %H:%M} UTC") # ---------------------------------------------------------------- pulls print("\nPULLING. Three counties, three CAMA vendors, one metro.") chs = pull(CHARLESTON, [ "SDE.P_POLY_PARCEL.OBJECTID", "SDE.CAMA.PARCEL_ID", "SDE.CAMA.TAX_DISTRICT", "SDE.CAMA.CLASS_CODE", "SDE.CAMA.PROP_CITY", "SDE.CAMA.SALE_PRICE", "SDE.CAMA.LAND_APPR", "SDE.CAMA.IMP_APPR", "SDE.CAMA.APPRAISAL", "SDE.CAMA.RECORDED_DATE", "SDE.CAMA.PROP_TYPE"], "SDE.P_POLY_PARCEL.OBJECTID", 1000, a.cache, "charleston") bkl = pull(BERKELEY, [ "OBJECTID_1", "O_TMS", "CommBuildingCount", "TotalTaxValue", "BuildingMarket", "LandMarket", "SaleDate", "SalePrice", "Validity", "TaxDistrict", "TMACode", "DateGenerated", "City"], "OBJECTID_1", 1000, a.cache, "berkeley") dor = pull(DORCHESTER, [ "OBJECTID", "TMS", "ActVal_Mkt", "AssdVal", "SALE_DATE", "SALE_PRICE", "DEFAULTTAXDISTRICT", "Legal_ResideNOcYES", "Agricultural_Use"], "OBJECTID", 2000, a.cache, "dorchester") P = "SDE.CAMA." chs_code = lambda r: (str(r[P + "CLASS_CODE"] or "")).strip()[:3] # ------------------------------------------------- 01 the reset, Berkeley print("\nFINDING 01 — the point-of-sale reset") bkl_comm = [r for r in bkl if (r["CommBuildingCount"] or 0) > 0] fig("01", "Berkeley parcels with a commercial building", len(bkl_comm), "parcels") bkl_ok = [r for r in bkl_comm if (r["BuildingMarket"] or 0) + (r["LandMarket"] or 0) > 0 and (r["TotalTaxValue"] or 0) > 0] fig("01", "Berkeley commercial with both a taxable and a market value", len(bkl_ok), "parcels") def bkl_gap(r): mkt = (r["BuildingMarket"] or 0) + (r["LandMarket"] or 0) return 1 - (r["TotalTaxValue"] / mkt) gradient("Berkeley commercial", bkl_ok, lambda r: epoch_year(r["SaleDate"]), bkl_gap, "01") never = [bkl_gap(r) for r in bkl_ok if not r["SaleDate"]] if never: fig("01", "Berkeley commercial with no recorded sale date", len(never), "parcels") fig("01", "Berkeley commercial, no sale date, median gap", round(100 * statistics.median(never), 1), "%") # ----------------------------------------------- 01 the reset, Dorchester dor_six = [r for r in dor if r["Legal_ResideNOcYES"] == "NO" and r["Agricultural_Use"] == "NO" and (r["ActVal_Mkt"] or 0) > 0 and (r["AssdVal"] or 0) > 0] fig("01", "Dorchester 6% class with both an assessed and a market value", len(dor_six), "parcels") def dor_gap(r): return 1 - (r["AssdVal"] / RATIO_OTHER) / r["ActVal_Mkt"] gradient("Dorchester 6% class", dor_six, lambda r: slash_year(r["SALE_DATE"]), dor_gap, "01") over = sum(1 for r in dor_six if r["AssdVal"] / r["ActVal_Mkt"] > RATIO_OTHER + 0.0005) fig("01", "Dorchester 6% class whose implied ratio exceeds 6%", over, "parcels", "class assigned from the legal-residence flag; " "these are where that assignment or the market value is wrong", share=pct(over, len(dor_six))) # --------------------------------------------- 02 the exemption nobody takes print("\nFINDING 02 — the 25% exemption almost nobody appears to hold") # S.C. Code Ann. 12-37-3135 allows 6%-class property undergoing an # assessable transfer of interest after 2010 an exemption of 25% of the # ATI fair market value. It is OPT-IN: 12-37-3135(C) requires the owner to # notify the assessor before 31 January of the first year claimed. If a # buyer holds it, the taxable value should sit about a quarter below # market. We count how many recently-sold commercial parcels do. bkl_fresh = [r for r in bkl_ok if (y := epoch_year(r["SaleDate"])) and 2022 <= y <= 2026] fig("02", "Berkeley commercial parcels last sold 2022-2025", len(bkl_fresh), "parcels") band = sum(1 for r in bkl_fresh if 0.245 <= bkl_gap(r) <= 0.255) none_ = sum(1 for r in bkl_fresh if bkl_gap(r) < 0.005) fig("02", "Berkeley 2022-2025 commercial sales in the 25% ATI-exemption band", band, "parcels", "gap between 24.5% and 25.5% below market", share=pct(band, len(bkl_fresh))) fig("02", "Berkeley 2022-2025 commercial sales taxed on the full market value", none_, "parcels", "gap under 0.5%", share=pct(none_, len(bkl_fresh))) # ------------------------------------------ 02b the cause is not recorded tma = sum(1 for r in bkl if (r["TMACode"] or "").strip()) fig("02", "Berkeley rows carrying a TMACode, the nearest field to an ATI", tma, "rows", "of %d rows on the roll" % len(bkl), share=pct(tma, len(bkl))) # ----------------------------------------------- 03 one metro, three records print("\nFINDING 03 — one metro, three incompatible records") fig("03", "Charleston County parcels", len(chs), "parcels") fig("03", "Berkeley County parcels", len(bkl), "parcels") fig("03", "Dorchester County parcels", len(dor), "parcels") fig("03", "Tri-county parcels, all three layers", len(chs) + len(bkl) + len(dor), "parcels") chs_val = sum(1 for r in chs if (r[P + "APPRAISAL"] or 0) > 0) fig("03", "Charleston parcels carrying an appraised (market) value", chs_val, "parcels") fig("03", "Charleston parcels carrying a taxable or assessed value", 0, "parcels", "no such field exists in the layer's 47 columns") fig("03", "Charleston distinct tax districts", len({(r[P + "TAX_DISTRICT"] or "").strip() for r in chs}), "districts") fig("03", "Berkeley distinct tax districts", len({(r["TaxDistrict"] or "").strip() for r in bkl}), "districts") fig("03", "Dorchester distinct default tax districts", len({r["DEFAULTTAXDISTRICT"] for r in dor}), "districts") # ---------------------------------------------------- 04 Berkeley Validity print("\nFINDING 04 — the only qualification code in the metro, undocumented") from collections import Counter vc = Counter((r["Validity"] or "").strip() or "(blank)" for r in bkl) fig("04", "Distinct Validity codes on the Berkeley roll", len([k for k in vc if k != "(blank)"]), "codes") fig("04", "Berkeley rows with no Validity code at all", vc["(blank)"], "rows") bkl_recent = [r for r in bkl_comm if (y := epoch_year(r["SaleDate"])) and 2024 <= y <= 2025] fig("04", "Berkeley commercial parcels last sold in 2024 or 2025", len(bkl_recent), "parcels") nonclean = sum(1 for r in bkl_recent if (r["Validity"] or "").strip() not in ("0", "0A", "")) fig("04", "Berkeley 2024-25 commercial sales coded other than 0 or 0A", nonclean, "parcels", "", share=pct(nonclean, len(bkl_recent))) # The same count on the window the statistics hub uses for every market, # so Berkeley is comparable to Sarasota, Manatee, Miami-Dade and # Mecklenburg. NB these codes are undocumented: this is a count of a code # other than the two that dominate ordinary sales, NOT a count of sales # the county has called non-arm's-length. Berkeley says no such thing. bkl_2025 = [r for r in bkl_comm if (y := epoch_year(r["SaleDate"])) and y >= 2025] fig("04", "Berkeley commercial parcels last sold from 2025 onward", len(bkl_2025), "parcels") nc25 = sum(1 for r in bkl_2025 if (r["Validity"] or "").strip() not in ("0", "0A", "")) fig("04", "Berkeley 2025-onward commercial sales coded other than 0 or 0A", nc25, "parcels", "meaning of the codes not published by the county", share=pct(nc25, len(bkl_2025))) for code, n in sorted(vc.items(), key=lambda kv: -kv[1]): FIGURES.append((f"Berkeley Validity code {code} / rows", n, f"{pct(n, len(bkl)):.1f}%", "rows", "04", "meaning not published by the county")) # ------------------------------------------------- 05 Charleston transfers print("\nFINDING 05 — what a Charleston transfer records") chs_comm = [r for r in chs if chs_code(r) in CHS_COMMERCIAL] fig("05", "Charleston commercial parcels, our published code list", len(chs_comm), "parcels") chs_rec = [r for r in chs_comm if (y := epoch_year(r[P + "RECORDED_DATE"])) and 2024 <= y <= 2025] fig("05", "Charleston commercial transfers recorded in 2024 or 2025", len(chs_rec), "transfers") cheap = sum(1 for r in chs_rec if (r[P + "SALE_PRICE"] or 0) <= 100) zero = sum(1 for r in chs_rec if (r[P + "SALE_PRICE"] or 0) == 0) fig("05", "Charleston 2024-25 commercial transfers recorded at $100 or less", cheap, "transfers", "", share=pct(cheap, len(chs_rec))) fig("05", "Charleston 2024-25 commercial transfers recorded at exactly $0", zero, "transfers", "", share=pct(zero, len(chs_rec))) # -------------------------------------------------- 06 the columns lie print("\nFINDING 06 — three fields that mislead on their names alone") raw_classes = {c for c in (str(r[P + "CLASS_CODE"] or "").strip() for r in chs) if c} num_classes = {c[:3] for c in raw_classes if c} fig("06", "Charleston distinct CLASS_CODE strings", len(raw_classes), "") fig("06", "Charleston distinct numeric class codes", len(num_classes), "", "grouping on the description splits code 730 into two") golf = Counter(str(r[P + "CLASS_CODE"] or "").strip() for r in chs if chs_code(r) == "730") for k, n in golf.most_common(): fig("06", f"Charleston class code 730 spelled '{k}'", n, "parcels") mp = Counter((r[P + "PROP_CITY"] or "").strip() for r in chs) fig("06", "Charleston parcels with situs city MOUNT PLEASANT", mp.get("MOUNT PLEASANT", 0), "parcels") fig("06", "Charleston parcels with situs city MT PLEASANT", mp.get("MT PLEASANT", 0), "parcels", "the same town, spelled two ways in one column") fig("06", "Charleston distinct situs city values", len(mp), "values") chs_city = [r for r in chs if (r[P + "PROP_CITY"] or "").strip().upper() == "CHARLESTON"] fig("06", "Charleston parcels addressed CHARLESTON", len(chs_city), "parcels") fig("06", "Tax districts holding a parcel addressed CHARLESTON", len({(r[P + "TAX_DISTRICT"] or "").strip() for r in chs_city}), "districts") fig("06", "Charleston rows returned by paging the service", PAGED_RAW.get("charleston", len(chs)), "rows", "against an advertised count of %d, with a stable sort order set" % len(chs)) fig("06", "Charleston duplicate rows among those paged", PAGED_RAW.get("charleston", len(chs)) - len(chs), "rows") fig("06", "Berkeley rows carrying any Validity code at all", len(bkl) - vc["(blank)"], "rows") fig("06", "Berkeley distinct City values, the owner's mailing city", len({(r["City"] or "").strip() for r in bkl}), "values", "not a situs city") st = Counter((r[P + "PROP_TYPE"] or "").strip() for r in chs) top_st, top_n = st.most_common(1)[0] fig("06", f"Charleston parcels whose PROP_TYPE is '{top_st}'", top_n, "parcels", "PROP_TYPE holds the street suffix, not a " "property type") bad = sum(1 for r in dor if r["SALE_PRICE"] and not str(r["SALE_PRICE"]).strip().isdigit()) fig("06", "Dorchester SALE_PRICE values that are not a number", bad, "rows", "the column is typed as a string") # ------------------------------------------------------------------ out print(f"\n{len(FIGURES)} figures produced.") if a.csv: with open(a.csv, "w", newline="") as fh: w = csv.writer(fh) w.writerow(["metric", "value", "share", "unit", "finding", "note"]) for row in FIGURES: w.writerow(row) print(f"Wrote {a.csv}") if __name__ == "__main__": main()