← back to Ventura Claw Leads
Generalize corridor seed to all 8 verticals (1,938 directory shells)
6572248463a41a179906e735f5ad771a9a99d42b · 2026-05-07 09:16:22 -0700 · Steve Abrams
scripts/seed-corridor-all.js extends the food-vertical seed with
per-vertical OSM-category + BTRC-NAICS-label maps:
beauty 669 (hairdresser/barber/nail/cosmetics)
retail 302 (clothing/jewelry/furniture/optical/florist/books)
creative 309 (photo/design/florist/promoter/sound/performing arts)
food 512 (already shipped)
cleaning 54 (janitorial/drycleaning/buildings)
fitness 52 (fitness centres + name-filtered amusement+rec)
pet_services 33 (pet care excl. veterinary)
auto_detail 7 (Car Washes label + name-filtered car_repair)
Hand-curated mapping deliberately excludes the green-list landmines
(regulated medical/legal/financial/real-estate/contracting, mechanical
auto, veterinary). Idempotent ON CONFLICT (slug) DO NOTHING.
Files touched
A scripts/seed-corridor-all.js
Diff
commit 6572248463a41a179906e735f5ad771a9a99d42b
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu May 7 09:16:22 2026 -0700
Generalize corridor seed to all 8 verticals (1,938 directory shells)
scripts/seed-corridor-all.js extends the food-vertical seed with
per-vertical OSM-category + BTRC-NAICS-label maps:
beauty 669 (hairdresser/barber/nail/cosmetics)
retail 302 (clothing/jewelry/furniture/optical/florist/books)
creative 309 (photo/design/florist/promoter/sound/performing arts)
food 512 (already shipped)
cleaning 54 (janitorial/drycleaning/buildings)
fitness 52 (fitness centres + name-filtered amusement+rec)
pet_services 33 (pet care excl. veterinary)
auto_detail 7 (Car Washes label + name-filtered car_repair)
Hand-curated mapping deliberately excludes the green-list landmines
(regulated medical/legal/financial/real-estate/contracting, mechanical
auto, veterinary). Idempotent ON CONFLICT (slug) DO NOTHING.
---
scripts/seed-corridor-all.js | 383 +++++++++++++++++++++++++++++++++++++++++++
1 file changed, 383 insertions(+)
diff --git a/scripts/seed-corridor-all.js b/scripts/seed-corridor-all.js
new file mode 100644
index 0000000..ce586e4
--- /dev/null
+++ b/scripts/seed-corridor-all.js
@@ -0,0 +1,383 @@
+#!/usr/bin/env node
+/**
+ * seed-corridor-all.js
+ *
+ * Seed all 8 verticals on /find from public Ventura corridor data
+ * (ventura_corridor PG: OSM POIs + LA City Business Tax Registration Certs).
+ *
+ * One row per (vertical, source) tier. Vertical mapping is hand-curated to
+ * avoid the green-list landmines:
+ * - NO regulated trades (medical/legal/financial/real-estate/contracting)
+ * - NO pharmacies/grocery in retail (overlap w/ regulated + green-list food)
+ * - NO veterinary in pet_services (regulated)
+ * - NO mechanical auto in auto_detail (green-list says "non-mechanical")
+ *
+ * Idempotent: ON CONFLICT (slug) DO NOTHING. Re-running adds new rows
+ * without touching existing ones.
+ *
+ * Usage:
+ * node scripts/seed-corridor-all.js # all 8 verticals
+ * node scripts/seed-corridor-all.js beauty retail # specific verticals
+ */
+
+require('dotenv').config({ path: require('path').resolve(__dirname, '..', '.env') });
+const slugify = require('slugify');
+const { Pool } = require('pg');
+
+const corridor = new Pool({
+ host: process.env.PGHOST || 'localhost',
+ port: parseInt(process.env.PGPORT || '5432', 10),
+ database: 'ventura_corridor',
+ user: process.env.PGUSER || process.env.USER,
+ max: 4
+});
+const target = require('../lib/db');
+
+// ---------------------------------------------------------------------------
+// Vertical → (OSM categories) + (BTRC NAICS labels) + name-keep/drop filters
+// ---------------------------------------------------------------------------
+const VERTICAL_MAP = {
+ food: {
+ osm_categories: ['amenity: restaurant','amenity: cafe','amenity: bar','amenity: fast food'],
+ btrc_labels: [
+ 'Full-service restaurants','Full-Service Restaurants','Limited-Service Restaurants',
+ 'Limited-service eating places',
+ 'Special food services (including food service contractors & caterers)',
+ 'Snack and Nonalcoholic Beverage Bars','Drinking places (alcoholic beverages)',
+ 'Coffee and Tea Manufacturing','Caterers','Food Service Contractors','Mobile Food Services'
+ ]
+ },
+ beauty: {
+ osm_categories: ['shop: hairdresser','shop: beauty','shop: cosmetics'],
+ btrc_labels: [
+ 'Beauty salons','Barber shops','Nail salons',
+ 'Cosmetics, beauty supplies, & perfume stores',
+ 'Other Personal Care Services',
+ 'Other personal care services (including diet & weight reducing centers)'
+ ]
+ },
+ retail: {
+ osm_categories: ['shop: clothes','shop: furniture','shop: department store'],
+ btrc_labels: [
+ 'Apparel, piece goods, & notions','Home furnishings stores',
+ 'Gift, novelty, & souvenir stores','Jewelry stores',
+ 'Other Clothing Stores','Clothing accessories stores',
+ "Women's clothing stores",'Family clothing stores','Furniture stores',
+ 'Books, periodicals, & newspapers','Musical instrument & supplies stores',
+ 'Jewelry, watch, precious stone, & precious metals',
+ 'Optical goods stores','Florists' /* florist sells goods, dual-fits creative */,
+ 'Other home furnishings stores'
+ ],
+ // Drop the "all other" bucket — too noisy (tobacco, candle, vape mixed in).
+ drop_btrc: ['All other miscellaneous store retailers (including tobacco, candle, & trophy shops)']
+ },
+ fitness: {
+ osm_categories: ['leisure: fitness_centre','leisure: fitness centre'],
+ btrc_labels: ['Fitness and Recreational Sports Centers'],
+ // BTRC "Other amusement & recreation services" includes fitness centers but
+ // also golf courses, marinas, etc. Name-filter to fitness terms only.
+ btrc_loose_labels_with_name_filter: [{
+ label: 'Other amusement & recreation services (including golf courses, skiing facilities, marinas, fitness centers, bowling centers, skating rinks, miniature golf courses)',
+ name_must_match: /yoga|pilates|fitness|gym|crossfit|barre|spin|cycle|martial arts|kickbox|jiu.jitsu|karate|dance|ballet|stretch|rowing|boxing/i
+ }]
+ },
+ pet_services: {
+ osm_categories: ['shop: pet'],
+ btrc_labels: ['Pet care (except veterinary) services']
+ // Veterinary explicitly excluded — regulated.
+ },
+ auto_detail: {
+ // OSM doesn't tag detail/tint specifically; corridor `shop: car_repair` is
+ // mechanical (green-list says non-mechanical only). Pull from the
+ // "Car Washes" label outright and from the broader auto-repair label
+ // filtered by name (keep detail/wash/tint, drop mechanics). Also do a
+ // name-only sweep for businesses misfiled into other labels (some car
+ // washes land under "All other personal services" or no category at all).
+ osm_categories: [],
+ btrc_labels: ['Car Washes'],
+ btrc_loose_labels_with_name_filter: [{
+ label: 'Other automotive repair & maintenance (including oil change & lubrication shops & car washes)',
+ name_must_match: /detail|wash|tint|polish|wax|ceramic|paint correction/i
+ }, {
+ label: 'Automotive mechanical & electrical repair & maintenance',
+ name_must_match: /detail|hand[- ]?wash|car wash|tint|polish|wax|ceramic|paint correction/i
+ }, {
+ label: 'Automotive parts, accessories, & tire stores',
+ name_must_match: /detail|tint|wax|ceramic/i
+ }],
+ name_sweep: /\b(detail|car wash|hand wash|window tint|auto detail|auto detailing)\b/i
+ },
+ cleaning: {
+ osm_categories: ['shop: laundry','shop: dry cleaning'],
+ btrc_labels: [
+ 'Janitorial services',
+ 'Other services to buildings & dwellings',
+ 'Drycleaning & laundry services (except coin-operated) (including laundry & drycleaning drop-off & pickup sites)'
+ ]
+ },
+ creative: {
+ osm_categories: [],
+ btrc_labels: [
+ 'Photographic services',
+ 'Florists',
+ 'Specialized design services (including interior, industrial, graphic, & fashion design)',
+ 'Promoters of performing arts, sports, & similar events',
+ 'Performing arts companies',
+ 'Sound recording industries'
+ ]
+ // Deliberately NOT including "Independent artists, writers, & performers"
+ // (1,245 rows, mostly people-as-businesses, owner-name junk).
+ }
+};
+
+const CITY_MAP = {
+ 'sherman oaks':'Sherman Oaks','studio city':'Studio City','encino':'Encino',
+ 'tarzana':'Tarzana','woodland hills':'Woodland Hills','woodland hls':'Woodland Hills'
+};
+const ZIP_TO_CITY = {
+ '91423':'Sherman Oaks','91403':'Sherman Oaks','91604':'Studio City',
+ '91436':'Encino','91316':'Encino','91356':'Tarzana','91364':'Woodland Hills'
+};
+const TARGET_CITIES = new Set(Object.values(CITY_MAP));
+
+function normalizeCity(raw, zip) {
+ if (raw) {
+ const k = String(raw).toLowerCase().split(';')[0].trim();
+ if (CITY_MAP[k]) return CITY_MAP[k];
+ }
+ if (zip) {
+ const z = String(zip).slice(0,5);
+ if (ZIP_TO_CITY[z]) return ZIP_TO_CITY[z];
+ }
+ return null;
+}
+function titleCase(s) {
+ if (!s) return s;
+ if (s !== s.toUpperCase()) return s;
+ return s.toLowerCase().replace(/\b([a-z])/g, c => c.toUpperCase());
+}
+function isPersonNameOrJunk(name) {
+ if (!name || name.length < 3) return true;
+ const looksLikePerson = /^[A-Z][A-Z]+ [A-Z][A-Z]+(?:[ ,]+[A-Z]+)?$/.test(name) &&
+ !/(LLC|INC|CORP|CO|RESTAURANT|CAFE|GRILL|KITCHEN|BAR|FOOD|BAKERY|PIZZA|BURGER|TACO|TAQUERIA|BISTRO|BBQ|JUICE|SUSHI|RAMEN|DELI|CATERING|COFFEE|TEA|PANINI|SANDWICH|SALON|BARBER|BEAUTY|NAIL|SPA|HAIR|BROW|LASH|BOUTIQUE|JEWELRY|FURNITURE|YOGA|PILATES|FITNESS|GYM|DANCE|MARTIAL|PET|GROOMING|DETAIL|WASH|TINT|CLEANING|JANITORIAL|FLORIST|PHOTO|DESIGN|STUDIO|GALLERY)/i.test(name);
+ if (looksLikePerson) return true;
+ if (/^(SUITE|UNIT|TBD|N\/A|UNKNOWN|TEST)/i.test(name)) return true;
+ return false;
+}
+
+function buildHeadline(vertical, row) {
+ const tags = (row.raw && row.raw.tags) || {};
+ const cat = row.category || '';
+ const cr = row.category_raw || '';
+ const cuisine = tags.cuisine ? tags.cuisine.replace(/[_;]/g,' ').replace(/\b\w/g, c => c.toUpperCase()) : null;
+
+ if (vertical === 'food') {
+ if (cuisine && cat === 'amenity: restaurant') return `${cuisine} restaurant on Ventura Blvd.`;
+ if (cuisine && cat === 'amenity: fast food') return `${cuisine} — quick-serve on the Boulevard.`;
+ if (cat === 'amenity: cafe') return 'Coffee + light fare on Ventura Blvd.';
+ if (cat === 'amenity: bar') return 'Neighborhood bar on the Boulevard.';
+ if (cat === 'amenity: restaurant') return 'Sit-down restaurant on Ventura Blvd.';
+ if (cat === 'amenity: fast food') return 'Quick-serve on the Boulevard.';
+ if (/Coffee|Tea/i.test(cr)) return 'Coffee + tea on Ventura Blvd.';
+ if (/Caterer/i.test(cr)) return 'Caterer + food service on Ventura Blvd.';
+ if (/Drinking places/i.test(cr)) return 'Bar on Ventura Blvd.';
+ return 'Local food + drink on Ventura Blvd.';
+ }
+ if (vertical === 'beauty') {
+ if (cat === 'shop: hairdresser') return 'Hair salon on Ventura Blvd.';
+ if (cat === 'shop: beauty') return 'Beauty studio on Ventura Blvd.';
+ if (cat === 'shop: cosmetics') return 'Cosmetics + beauty supply on Ventura Blvd.';
+ if (/Barber/i.test(cr)) return 'Barber shop on Ventura Blvd.';
+ if (/Nail/i.test(cr)) return 'Nail salon on Ventura Blvd.';
+ if (/Cosmetics/i.test(cr)) return 'Cosmetics + beauty supply on Ventura Blvd.';
+ if (/Beauty salons/i.test(cr)) return 'Hair + beauty salon on Ventura Blvd.';
+ return 'Beauty service on Ventura Blvd.';
+ }
+ if (vertical === 'retail') {
+ if (cat === 'shop: clothes') return 'Clothing boutique on Ventura Blvd.';
+ if (cat === 'shop: furniture') return 'Furniture store on Ventura Blvd.';
+ if (cat === 'shop: department store') return 'Department store on Ventura Blvd.';
+ if (/Jewelry/i.test(cr)) return 'Jewelry store on Ventura Blvd.';
+ if (/Furniture|Home furnishings/i.test(cr)) return 'Home + furniture store on Ventura Blvd.';
+ if (/Florists/i.test(cr)) return 'Florist on Ventura Blvd.';
+ if (/clothing|Apparel/i.test(cr)) return 'Clothing + apparel on Ventura Blvd.';
+ if (/Books/i.test(cr)) return 'Bookstore on Ventura Blvd.';
+ if (/Optical/i.test(cr)) return 'Optical + eyewear on Ventura Blvd.';
+ if (/Musical instrument/i.test(cr)) return 'Music + instrument shop on Ventura Blvd.';
+ if (/Gift/i.test(cr)) return 'Gift + novelty shop on Ventura Blvd.';
+ return 'Local retail on Ventura Blvd.';
+ }
+ if (vertical === 'fitness') {
+ if (cat.startsWith('leisure: fitness')) return 'Fitness studio on Ventura Blvd.';
+ return 'Fitness studio on Ventura Blvd.';
+ }
+ if (vertical === 'pet_services') {
+ if (cat === 'shop: pet') return 'Pet supply + service on Ventura Blvd.';
+ return 'Pet care service on Ventura Blvd.';
+ }
+ if (vertical === 'auto_detail') {
+ return 'Auto detail on Ventura Blvd.';
+ }
+ if (vertical === 'cleaning') {
+ if (/Janitorial/i.test(cr)) return 'Janitorial + commercial cleaning on Ventura Blvd.';
+ if (/Drycleaning/i.test(cr)) return 'Dry cleaning + laundry on Ventura Blvd.';
+ if (cat.startsWith('shop: laundry') || cat.startsWith('shop: dry'))
+ return 'Dry cleaning + laundry on Ventura Blvd.';
+ return 'Cleaning service on Ventura Blvd.';
+ }
+ if (vertical === 'creative') {
+ if (/Photographic/i.test(cr)) return 'Photographer on Ventura Blvd.';
+ if (/Florists/i.test(cr)) return 'Florist on Ventura Blvd.';
+ if (/Specialized design/i.test(cr)) return 'Design studio on Ventura Blvd.';
+ if (/Promoters/i.test(cr)) return 'Event + promotion company on Ventura Blvd.';
+ if (/Performing arts/i.test(cr)) return 'Performing arts on Ventura Blvd.';
+ if (/Sound recording/i.test(cr)) return 'Recording studio on Ventura Blvd.';
+ return 'Creative studio on Ventura Blvd.';
+ }
+ return 'Local business on Ventura Blvd.';
+}
+
+async function fetchRowsForVertical(vertical) {
+ const cfg = VERTICAL_MAP[vertical];
+ if (!cfg) throw new Error(`unknown vertical: ${vertical}`);
+ const out = [];
+
+ if (cfg.osm_categories?.length) {
+ const q = `
+ SELECT name, category, category_raw, address, street_number, street_name, city, zip,
+ lat, lng, phone, website, raw
+ FROM businesses
+ WHERE on_corridor = true AND source = 'osm'
+ AND category = ANY($1::text[])
+ `;
+ const r = await corridor.query(q, [cfg.osm_categories]);
+ out.push(...r.rows);
+ }
+ if (cfg.btrc_labels?.length) {
+ const q = `
+ SELECT name, category, category_raw, address, street_number, street_name, city, zip,
+ lat, lng, phone, website, raw
+ FROM businesses
+ WHERE on_corridor = true AND source = 'la_btrc'
+ AND category_raw = ANY($1::text[])
+ `;
+ const r = await corridor.query(q, [cfg.btrc_labels]);
+ out.push(...r.rows);
+ }
+ if (cfg.btrc_loose_labels_with_name_filter?.length) {
+ for (const flt of cfg.btrc_loose_labels_with_name_filter) {
+ const q = `
+ SELECT name, category, category_raw, address, street_number, street_name, city, zip,
+ lat, lng, phone, website, raw
+ FROM businesses
+ WHERE on_corridor = true AND source = 'la_btrc'
+ AND category_raw = $1
+ `;
+ const r = await corridor.query(q, [flt.label]);
+ out.push(...r.rows.filter(row => flt.name_must_match.test(row.name || '')));
+ }
+ }
+ // Final pass: name-pattern sweep across all corridor rows for verticals
+ // whose target businesses are scattered across mis-categorized labels
+ // (auto detail in particular — car washes get filed everywhere).
+ if (cfg.name_sweep) {
+ const q = `
+ SELECT name, category, category_raw, address, street_number, street_name, city, zip,
+ lat, lng, phone, website, raw
+ FROM businesses
+ WHERE on_corridor = true
+ AND source IN ('osm','la_btrc')
+ AND name ~* $1
+ `;
+ const r = await corridor.query(q, [cfg.name_sweep.source]);
+ out.push(...r.rows);
+ }
+ return out;
+}
+
+async function seedVertical(vertical) {
+ const rows = await fetchRowsForVertical(vertical);
+ console.log(`[${vertical}] pulled ${rows.length} candidate rows from corridor`);
+
+ const seenSlug = new Set();
+ const seenKey = new Set();
+ let inserted = 0, dups_in_batch = 0, conflicted = 0, dropped_junk = 0, dropped_city = 0;
+
+ for (const r of rows) {
+ if (isPersonNameOrJunk(r.name)) { dropped_junk++; continue; }
+ const cleanName = titleCase(r.name);
+ const city = normalizeCity(r.city, r.zip);
+ if (!city || !TARGET_CITIES.has(city)) { dropped_city++; continue; }
+
+ let street = titleCase(r.address || [r.street_number, r.street_name].filter(Boolean).join(' '));
+ if (!street) { dropped_junk++; continue; }
+ street = street.replace(new RegExp(`\\s+${city}\\s+\\d{5}(-\\d{4})?$`, 'i'), '');
+ street = street.replace(/\s+\d{5}(-\d{4})?$/, '');
+
+ const dedupeKey = `${cleanName.toLowerCase()}|${street.toLowerCase()}|${(r.zip||'').slice(0,5)}`;
+ if (seenKey.has(dedupeKey)) { dups_in_batch++; continue; }
+ seenKey.add(dedupeKey);
+
+ let slug = slugify(`${cleanName} ${city}`, { lower: true, strict: true });
+ if (seenSlug.has(slug)) {
+ const sn = (r.street_number || '').replace(/\D/g,'').slice(0,4) || '0';
+ slug = `${slug}-${sn}`;
+ if (seenSlug.has(slug)) { dups_in_batch++; continue; }
+ }
+ seenSlug.add(slug);
+
+ const headline = buildHeadline(vertical, r);
+ const description = `${cleanName} is a ${vertical.replace('_',' ')} business on the Ventura Blvd corridor in ${city}. ${headline}`;
+ const phone = r.phone ? r.phone.replace(/^\+1[-\s]?/,'').trim() : null;
+ const website = r.website || null;
+ const zip = (r.zip || '').slice(0, 10) || null;
+
+ try {
+ const ins = await target.query(
+ `INSERT INTO businesses
+ (slug, business_name, vertical, headline, description, street, city, state, zip, neighborhood,
+ latitude, longitude, phone, website, tier, claim_status, verified, status, source)
+ VALUES ($1,$2,$3,$4,$5,$6,$7,'CA',$8,$9,$10,$11,$12,$13,
+ 'free','unclaimed',false,'active','public_records')
+ ON CONFLICT (slug) DO NOTHING
+ RETURNING id`,
+ [slug, cleanName, vertical, headline, description, street, city, zip, city,
+ r.lat, r.lng, phone, website]
+ );
+ if (ins.rows.length) inserted++; else conflicted++;
+ } catch (err) {
+ console.error(`[${vertical}] insert "${cleanName}" failed: ${err.message}`);
+ dropped_junk++;
+ }
+ }
+
+ console.log(`[${vertical}] inserted=${inserted} batch_dups=${dups_in_batch} slug_collisions=${conflicted} junk=${dropped_junk} off_corridor=${dropped_city}`);
+ return { vertical, inserted, conflicted, dropped_junk, dropped_city };
+}
+
+(async () => {
+ const requested = process.argv.slice(2);
+ const verticals = requested.length ? requested : Object.keys(VERTICAL_MAP);
+ console.log(`[seed-corridor-all] verticals: ${verticals.join(', ')}`);
+
+ const results = [];
+ for (const v of verticals) {
+ if (!VERTICAL_MAP[v]) {
+ console.error(`[seed-corridor-all] skipping unknown vertical: ${v}`);
+ continue;
+ }
+ results.push(await seedVertical(v));
+ }
+
+ console.log('\n[seed-corridor-all] summary:');
+ for (const r of results) {
+ console.log(` ${r.vertical.padEnd(13)} +${r.inserted} inserted (${r.conflicted} pre-existing, ${r.dropped_junk} dropped junk)`);
+ }
+
+ await corridor.end();
+ await target.pool.end();
+ process.exit(0);
+})().catch(err => {
+ console.error('[seed-corridor-all] fatal', err);
+ process.exit(1);
+});
← 50d32a1 Seed food vertical from ventura_corridor public data (OSM +
·
back to Ventura Claw Leads
·
Drop misleading 'verified listings' meta-description copy 8485ef7 →