← back to Ventura Claw Leads
scripts/seed-corridor-all.js
389 lines
#!/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, city) {
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;
// city-suffix: 'in Sherman Oaks.' replaces the bland 'on Ventura Blvd.'
// tail. Keeps headlines deterministic but generates 5x as many distinct
// phrasings (one per neighborhood) — better SEO match for 'X in <city>'
// searches and reduces visible repetition on the find grid.
const SUF = ` in ${city}.`;
if (vertical === 'food') {
if (cuisine && cat === 'amenity: restaurant') return `${cuisine} restaurant${SUF}`;
if (cuisine && cat === 'amenity: fast food') return `${cuisine} — quick-serve${SUF}`;
if (cat === 'amenity: cafe') return `Coffee + light fare${SUF}`;
if (cat === 'amenity: bar') return `Neighborhood bar${SUF}`;
if (cat === 'amenity: restaurant') return `Sit-down restaurant${SUF}`;
if (cat === 'amenity: fast food') return `Quick-serve${SUF}`;
if (/Coffee|Tea/i.test(cr)) return `Coffee + tea${SUF}`;
if (/Caterer/i.test(cr)) return `Caterer + food service${SUF}`;
if (/Drinking places/i.test(cr)) return `Bar${SUF}`;
return `Local food + drink${SUF}`;
}
if (vertical === 'beauty') {
if (cat === 'shop: hairdresser') return `Hair salon${SUF}`;
if (cat === 'shop: beauty') return `Beauty studio${SUF}`;
if (cat === 'shop: cosmetics') return `Cosmetics + beauty supply${SUF}`;
if (/Barber/i.test(cr)) return `Barber shop${SUF}`;
if (/Nail/i.test(cr)) return `Nail salon${SUF}`;
if (/Cosmetics/i.test(cr)) return `Cosmetics + beauty supply${SUF}`;
if (/Beauty salons/i.test(cr)) return `Hair + beauty salon${SUF}`;
return `Beauty service${SUF}`;
}
if (vertical === 'retail') {
if (cat === 'shop: clothes') return `Clothing boutique${SUF}`;
if (cat === 'shop: furniture') return `Furniture store${SUF}`;
if (cat === 'shop: department store') return `Department store${SUF}`;
if (/Jewelry/i.test(cr)) return `Jewelry store${SUF}`;
if (/Furniture|Home furnishings/i.test(cr)) return `Home + furniture store${SUF}`;
if (/Florists/i.test(cr)) return `Florist${SUF}`;
if (/clothing|Apparel/i.test(cr)) return `Clothing + apparel${SUF}`;
if (/Books/i.test(cr)) return `Bookstore${SUF}`;
if (/Optical/i.test(cr)) return `Optical + eyewear${SUF}`;
if (/Musical instrument/i.test(cr)) return `Music + instrument shop${SUF}`;
if (/Gift/i.test(cr)) return `Gift + novelty shop${SUF}`;
return `Local retail${SUF}`;
}
if (vertical === 'fitness') {
if (cat.startsWith('leisure: fitness')) return `Fitness studio${SUF}`;
return `Fitness studio${SUF}`;
}
if (vertical === 'pet_services') {
if (cat === 'shop: pet') return `Pet supply + service${SUF}`;
return `Pet care service${SUF}`;
}
if (vertical === 'auto_detail') {
return `Auto detail${SUF}`;
}
if (vertical === 'cleaning') {
if (/Janitorial/i.test(cr)) return `Janitorial + commercial cleaning${SUF}`;
if (/Drycleaning/i.test(cr)) return `Dry cleaning + laundry${SUF}`;
if (cat.startsWith('shop: laundry') || cat.startsWith('shop: dry'))
return `Dry cleaning + laundry${SUF}`;
return `Cleaning service${SUF}`;
}
if (vertical === 'creative') {
if (/Photographic/i.test(cr)) return `Photographer${SUF}`;
if (/Florists/i.test(cr)) return `Florist${SUF}`;
if (/Specialized design/i.test(cr)) return `Design studio${SUF}`;
if (/Promoters/i.test(cr)) return `Event + promotion company${SUF}`;
if (/Performing arts/i.test(cr)) return `Performing arts${SUF}`;
if (/Sound recording/i.test(cr)) return `Recording studio${SUF}`;
return `Creative studio${SUF}`;
}
return `Local business${SUF}`;
}
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, city);
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);
});