← back to Sister Parish Onboarding
tick 5: Shopify still 0 · cross-match SP × PD in Lab space (219 matches, 73 × 3) · tileability score (11 seamless / 38 near / 24 photo)
280a3a3887f81658cdb8749049d6281f3e689f10 · 2026-05-11 16:40:11 -0700 · Steve
Files touched
A scripts/cross_match_pd.pyA scripts/tileability_check.py
Diff
commit 280a3a3887f81658cdb8749049d6281f3e689f10
Author: Steve <steve@designerwallcoverings.com>
Date: Mon May 11 16:40:11 2026 -0700
tick 5: Shopify still 0 · cross-match SP × PD in Lab space (219 matches, 73 × 3) · tileability score (11 seamless / 38 near / 24 photo)
---
scripts/cross_match_pd.py | 124 +++++++++++++++++++++++++++++++++++++++++++
scripts/tileability_check.py | 97 +++++++++++++++++++++++++++++++++
2 files changed, 221 insertions(+)
diff --git a/scripts/cross_match_pd.py b/scripts/cross_match_pd.py
new file mode 100644
index 0000000..e7766d8
--- /dev/null
+++ b/scripts/cross_match_pd.py
@@ -0,0 +1,124 @@
+#!/usr/bin/env python3
+"""
+For each Sister Parish pattern, find the 3 nearest public-domain pattern
+images (from wallco.ai's pd_source_designs) by color distance in CIE Lab space.
+
+Lab distance is perceptually meaningful where raw RGB or HSL is not —
+a sage green and an olive green are close in Lab, far apart in RGB.
+
+Output: dw_unified.sp_pd_color_matches join table.
+Idempotent: TRUNCATE + re-insert each run.
+"""
+import json
+import subprocess
+from typing import List, Tuple
+
+def psql_json(sql: str):
+ out = subprocess.run(['psql', 'dw_unified', '-At', '-q', '-c', sql],
+ capture_output=True, text=True, check=True).stdout.strip()
+ return json.loads(out) if out else []
+
+def hex_to_rgb(h: str):
+ if not h or not h.startswith('#'): return None
+ h = h.lstrip('#')
+ if len(h) != 6: return None
+ return int(h[0:2], 16), int(h[2:4], 16), int(h[4:6], 16)
+
+# sRGB → linear → XYZ → Lab (D65). Standard formulas.
+def srgb_to_linear(c):
+ c /= 255.0
+ return c / 12.92 if c <= 0.04045 else ((c + 0.055) / 1.055) ** 2.4
+
+def rgb_to_lab(rgb):
+ r, g, b = (srgb_to_linear(v) for v in rgb)
+ # sRGB D65 matrix
+ x = r * 0.4124564 + g * 0.3575761 + b * 0.1804375
+ y = r * 0.2126729 + g * 0.7151522 + b * 0.0721750
+ z = r * 0.0193339 + g * 0.1191920 + b * 0.9503041
+ # D65 reference white
+ xn, yn, zn = 0.95047, 1.00000, 1.08883
+ def f(t):
+ return t ** (1/3) if t > 0.008856 else (7.787 * t + 16/116)
+ L = 116 * f(y / yn) - 16
+ a = 500 * (f(x / xn) - f(y / yn))
+ b2 = 200 * (f(y / yn) - f(z / zn))
+ return (L, a, b2)
+
+def lab_dist(a, b):
+ return ((a[0]-b[0])**2 + (a[1]-b[1])**2 + (a[2]-b[2])**2) ** 0.5
+
+# Pull data
+sp = psql_json("SELECT COALESCE(json_agg(t),'[]'::json) FROM (SELECT sp_product_id, title, dominant_hex FROM sisterparish_catalog WHERE dominant_hex IS NOT NULL ORDER BY sp_product_id) t;")
+pd = psql_json("SELECT COALESCE(json_agg(t),'[]'::json) FROM (SELECT id, source, title, dominant_hex, source_url, local_path FROM pd_source_designs WHERE dominant_hex IS NOT NULL) t;")
+
+print(f"SP patterns with hex: {len(sp)}")
+print(f"PD images with hex: {len(pd)}")
+if not sp or not pd:
+ print("Need both corpora populated; exiting")
+ raise SystemExit(0)
+
+# Pre-compute PD Lab values
+pd_lab = []
+for p in pd:
+ rgb = hex_to_rgb(p['dominant_hex'])
+ if rgb is None: continue
+ pd_lab.append((p, rgb_to_lab(rgb)))
+
+# Match each SP
+matches = []
+for s in sp:
+ rgb = hex_to_rgb(s['dominant_hex'])
+ if rgb is None: continue
+ sl = rgb_to_lab(rgb)
+ scored = [(lab_dist(sl, p_lab), p) for (p, p_lab) in pd_lab]
+ scored.sort(key=lambda x: x[0])
+ for rank, (dist, pmatch) in enumerate(scored[:3], 1):
+ matches.append((s['sp_product_id'], s['title'], s['dominant_hex'],
+ pmatch['id'], pmatch['source'], pmatch['title'],
+ pmatch['dominant_hex'], pmatch['source_url'], pmatch['local_path'],
+ rank, round(dist, 2)))
+
+# Write to PG
+ddl = """
+CREATE TABLE IF NOT EXISTS sp_pd_color_matches (
+ sp_product_id BIGINT NOT NULL,
+ sp_title TEXT,
+ sp_hex TEXT,
+ pd_id BIGINT NOT NULL,
+ pd_source TEXT,
+ pd_title TEXT,
+ pd_hex TEXT,
+ pd_source_url TEXT,
+ pd_local_path TEXT,
+ rank INTEGER,
+ lab_distance NUMERIC(8,2),
+ computed_at TIMESTAMPTZ DEFAULT NOW(),
+ PRIMARY KEY (sp_product_id, pd_id)
+);
+TRUNCATE sp_pd_color_matches;
+"""
+subprocess.run(['psql', 'dw_unified', '-q', '-c', ddl], check=True)
+
+def esc(v):
+ if v is None: return 'NULL'
+ return "'" + str(v).replace("'", "''") + "'"
+
+with open('/tmp/sp_pd_matches.sql', 'w') as f:
+ f.write("BEGIN;\n")
+ for m in matches:
+ sp_id, sp_t, sp_h, pd_id, pd_src, pd_t, pd_h, pd_u, pd_lp, rank, dist = m
+ f.write(f"INSERT INTO sp_pd_color_matches (sp_product_id,sp_title,sp_hex,pd_id,pd_source,pd_title,pd_hex,pd_source_url,pd_local_path,rank,lab_distance) "
+ f"VALUES ({sp_id},{esc(sp_t)},{esc(sp_h)},{pd_id},{esc(pd_src)},{esc(pd_t)},{esc(pd_h)},{esc(pd_u)},{esc(pd_lp)},{rank},{dist});\n")
+ f.write("COMMIT;\n")
+subprocess.run(['psql', 'dw_unified', '-q', '-f', '/tmp/sp_pd_matches.sql'], check=True)
+import os; os.unlink('/tmp/sp_pd_matches.sql')
+
+print(f"\nWrote {len(matches)} matches ({len(sp)} SP × 3 nearest PD)")
+
+# Spot-check 5
+print("\nSpot-check (5 SP patterns + nearest PD):")
+out = subprocess.run(['psql', 'dw_unified', '-c',
+ "SELECT sp_title, sp_hex, pd_title, pd_hex, pd_source, lab_distance "
+ "FROM sp_pd_color_matches WHERE rank=1 ORDER BY lab_distance LIMIT 5;"],
+ capture_output=True, text=True)
+print(out.stdout)
diff --git a/scripts/tileability_check.py b/scripts/tileability_check.py
new file mode 100644
index 0000000..27b7c98
--- /dev/null
+++ b/scripts/tileability_check.py
@@ -0,0 +1,97 @@
+#!/usr/bin/env python3
+"""
+Quick tileability score for each Sister Parish primary_image.
+
+Idea: a perfectly seamless tile has zero edge mismatch when wrapped left↔right
+and top↔bottom. We measure the per-pixel difference between opposite edges
+(20-pixel strips) and average it.
+
+Scores:
+ 0 - 10 → effectively seamless (probably a true repeat)
+ 10 - 30 → near-seamless, would need slight matchup
+ 30 - 80 → photo with cropped pattern; not seamless
+ 80+ → clearly not a repeat (e.g. styled shot, room scene)
+
+Writes to dw_unified.sisterparish_catalog.tileability JSONB.
+"""
+import json, os, subprocess
+from pathlib import Path
+from PIL import Image, ImageOps
+import numpy as np
+
+ROOT = Path.home() / 'Projects' / 'sister-parish-onboarding'
+
+def psql_json(sql):
+ out = subprocess.run(['psql','dw_unified','-At','-q','-c',sql],
+ capture_output=True, text=True, check=True).stdout.strip()
+ return json.loads(out) if out else []
+
+def edge_score(path, strip_px=20):
+ img = Image.open(path).convert('RGB')
+ img = ImageOps.exif_transpose(img)
+ # Resize to a fixed working size so different image sizes are comparable
+ img.thumbnail((800, 800))
+ a = np.asarray(img, dtype=np.float32)
+ h, w, _ = a.shape
+ s = min(strip_px, w // 8, h // 8)
+ # Horizontal seam: left strip vs right strip
+ L = a[:, :s, :]
+ R = a[:, -s:, :]
+ horiz = float(np.mean(np.abs(L - R)))
+ # Vertical seam: top vs bottom
+ T = a[:s, :, :]
+ B = a[-s:, :, :]
+ vert = float(np.mean(np.abs(T - B)))
+ return round((horiz + vert) / 2, 2), round(horiz, 2), round(vert, 2)
+
+# Ensure column exists
+subprocess.run(['psql','dw_unified','-q','-c',
+ 'ALTER TABLE sisterparish_catalog ADD COLUMN IF NOT EXISTS tileability JSONB;'], check=True)
+
+# Pull each SP record's primary_image; we don't have local images for SP — only URLs.
+# Use /tmp/sp_imgs/ which was populated by extract_dominant_hex.py
+sp = psql_json("SELECT COALESCE(json_agg(t),'[]'::json) FROM (SELECT sp_product_id, title, primary_image FROM sisterparish_catalog WHERE primary_image IS NOT NULL) t;")
+
+img_dir = Path('/tmp/sp_imgs')
+scored = []
+for s in sp:
+ pid = s['sp_product_id']
+ # Find image file (could be .jpg / .jpeg / .png)
+ candidates = list(img_dir.glob(f'sp_{pid}.*'))
+ if not candidates:
+ continue
+ img_path = candidates[0]
+ try:
+ score, h, v = edge_score(img_path)
+ verdict = ('seamless' if score < 10
+ else 'near' if score < 30
+ else 'photo' if score < 80
+ else 'scene')
+ scored.append((pid, score, h, v, verdict, s['title']))
+ except Exception as e:
+ print(f" err {pid}: {e}")
+
+# Write back
+with open('/tmp/sp_tileability.sql','w') as f:
+ f.write("BEGIN;\n")
+ for pid, score, h, v, verdict, title in scored:
+ obj = {"score": score, "edge_horiz": h, "edge_vert": v, "verdict": verdict}
+ obj_str = json.dumps(obj).replace("'", "''")
+ f.write(f"UPDATE sisterparish_catalog SET tileability='{obj_str}'::jsonb WHERE sp_product_id={pid};\n")
+ f.write("COMMIT;\n")
+subprocess.run(['psql','dw_unified','-q','-f','/tmp/sp_tileability.sql'], check=True)
+os.unlink('/tmp/sp_tileability.sql')
+
+print(f"\nScored {len(scored)} SP images")
+# Verdict distribution
+from collections import Counter
+v_count = Counter(s[4] for s in scored)
+print("Verdict distribution:", dict(v_count))
+
+print("\nMost-tileable (top 8):")
+for s in sorted(scored, key=lambda x: x[1])[:8]:
+ print(f" score={s[1]:>5.1f} {s[4]:>9} {s[5]}")
+
+print("\nLeast-tileable (top 5):")
+for s in sorted(scored, key=lambda x: x[1], reverse=True)[:5]:
+ print(f" score={s[1]:>5.1f} {s[4]:>9} {s[5]}")
← 1c08599 tick 4: Shopify still 0 SP products · recorded 10 Backend-C
·
back to Sister Parish Onboarding
·
SP color v2: center-crop 80% before palette extraction — nea 134a484 →