← back to Ventura Claw Leads
Seed food vertical from ventura_corridor public data (OSM + LA BTRC)
50d32a1e02af717407a8b105057729620aa247d3 · 2026-05-07 09:09:12 -0700 · Steve Abrams
scripts/seed-corridor-food.js pulls 1,062 corridor food rows from the
ventura_corridor PG (89 OSM POIs + 973 LA BTRC NAICS-classified rows),
filters out NAICS-mistagged rows ('Barber shops' fuzz-matched to
'amenity: bar') and obvious owner-name registrations, normalizes city
casing, and inserts as tier='free' claim_status='unclaimed'
source='public_records' shells. /find?vertical=food now renders 512
real Ventura Blvd businesses across Sherman Oaks/Studio City/Encino/
Tarzana/Woodland Hills, up from 5 demo placeholders.
Files touched
A scripts/seed-corridor-food.js
Diff
commit 50d32a1e02af717407a8b105057729620aa247d3
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu May 7 09:09:12 2026 -0700
Seed food vertical from ventura_corridor public data (OSM + LA BTRC)
scripts/seed-corridor-food.js pulls 1,062 corridor food rows from the
ventura_corridor PG (89 OSM POIs + 973 LA BTRC NAICS-classified rows),
filters out NAICS-mistagged rows ('Barber shops' fuzz-matched to
'amenity: bar') and obvious owner-name registrations, normalizes city
casing, and inserts as tier='free' claim_status='unclaimed'
source='public_records' shells. /find?vertical=food now renders 512
real Ventura Blvd businesses across Sherman Oaks/Studio City/Encino/
Tarzana/Woodland Hills, up from 5 demo placeholders.
---
scripts/seed-corridor-food.js | 253 ++++++++++++++++++++++++++++++++++++++++++
1 file changed, 253 insertions(+)
diff --git a/scripts/seed-corridor-food.js b/scripts/seed-corridor-food.js
new file mode 100644
index 0000000..aef2fa3
--- /dev/null
+++ b/scripts/seed-corridor-food.js
@@ -0,0 +1,253 @@
+#!/usr/bin/env node
+/**
+ * seed-corridor-food.js
+ *
+ * Seed the food vertical on /find?vertical=food using public Ventura corridor
+ * data (OSM POIs + LA City Business Tax Registration Certificates) already
+ * normalized into the `ventura_corridor` PG database (~/Projects/ventura).
+ *
+ * - OSM source: 89 hand-tagged restaurants/cafes/bars on the Boulevard with
+ * names, websites, phones, cuisine — clean signal.
+ * - la_btrc source: ~575 NAICS-classified food businesses with tax-cert addr.
+ * We filter on `category_raw` (NAICS label), not the noisy fuzzy-matched
+ * `category`, so "Barber shops" don't sneak in as bars.
+ *
+ * Output: rows in ventura_claw_leads.businesses with vertical='food',
+ * tier='free', claim_status='unclaimed', source='public_records'. These are
+ * directory shells — businesses claim them via the standard /claim flow.
+ */
+
+require('dotenv').config({ path: require('path').resolve(__dirname, '..', '.env') });
+const slugify = require('slugify');
+const { Pool } = require('pg');
+
+// Source DB: corridor ingest (~/Projects/ventura)
+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
+});
+
+// Target DB: ventura-claw-leads
+const target = require('../lib/db');
+
+// LA City BTRC NAICS labels we accept as "food vertical".
+// Excludes "Barber shops" (286 false positives), "Other direct selling"
+// (multi-level marketing), and "Other food mfg." (manufacturers, not retail).
+const BTRC_FOOD_LABELS = new Set([
+ 'Full-service restaurants',
+ 'Full-Service Restaurants',
+ 'Limited-Service Restaurants',
+ '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'
+]);
+
+// Map LA-area city values (corridor data is mixed-case + UPPERCASE) to the
+// canonical Title Case used by ventura-claw-leads view templates.
+const CITY_MAP = {
+ 'sherman oaks': 'Sherman Oaks',
+ 'studio city': 'Studio City',
+ 'encino': 'Encino',
+ 'tarzana': 'Tarzana',
+ 'woodland hills': 'Woodland Hills',
+ 'woodland hls': 'Woodland Hills'
+};
+
+// ZIP fallback when city is missing — Ventura Blvd ZIPs only.
+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 key = String(raw).toLowerCase().split(';')[0].trim();
+ if (CITY_MAP[key]) return CITY_MAP[key];
+ }
+ if (zip) {
+ const z = String(zip).slice(0, 5);
+ if (ZIP_TO_CITY[z]) return ZIP_TO_CITY[z];
+ }
+ return null;
+}
+
+// "JAMBA JUICE #8" -> "Jamba Juice #8". Keep numbers/punct, just titlecase
+// each whitespace-separated word. la_btrc rows arrive ALL CAPS.
+function titleCaseName(name) {
+ if (!name) return name;
+ if (name === name.toLowerCase() || name !== name.toUpperCase()) return name;
+ return name.toLowerCase().replace(/\b([a-z])/g, c => c.toUpperCase());
+}
+
+// Same idea for street addresses.
+function titleCaseStreet(s) {
+ if (!s) return s;
+ if (s !== s.toUpperCase()) return s;
+ return s.toLowerCase().replace(/\b([a-z])/g, c => c.toUpperCase());
+}
+
+// Build a per-row headline. For OSM rows we have cuisine + amenity tags; for
+// BTRC rows we have NAICS label. Either gives us a one-line description that
+// reads better than blank.
+function buildHeadline({ name, category, raw, category_raw }) {
+ const tags = (raw && raw.tags) || {};
+ const cuisine = tags.cuisine ? tags.cuisine.replace(/[_;]/g, ' ').replace(/\b\w/g, c => c.toUpperCase()) : null;
+ const isCafe = category === 'amenity: cafe';
+ const isBar = category === 'amenity: bar';
+ const isFast = category === 'amenity: fast food';
+ const isRest = category === 'amenity: restaurant';
+
+ if (cuisine && isRest) return `${cuisine} restaurant on Ventura Blvd.`;
+ if (cuisine && isFast) return `${cuisine} — quick-serve on the Boulevard.`;
+ if (isCafe) return `Coffee + light fare on Ventura Blvd.`;
+ if (isBar) return `Neighborhood bar on the Boulevard.`;
+ if (isRest) return `Sit-down restaurant on Ventura Blvd.`;
+ if (isFast) return `Quick-serve on the Boulevard.`;
+
+ if (category_raw) {
+ if (/Coffee|Tea/i.test(category_raw)) return `Coffee + tea on Ventura Blvd.`;
+ if (/Caterer|Food Service Contractor/i.test(category_raw))
+ return `Caterer + food-service on the Boulevard.`;
+ if (/Drinking places/i.test(category_raw)) return `Bar on Ventura Blvd.`;
+ if (/Limited-Service/i.test(category_raw)) return `Quick-serve on Ventura Blvd.`;
+ if (/Snack|Beverage Bars/i.test(category_raw))
+ return `Snacks + drinks on the Boulevard.`;
+ if (/Mobile Food/i.test(category_raw)) return `Mobile food vendor on Ventura Blvd.`;
+ }
+ return `Local food + drink on Ventura Blvd.`;
+}
+
+// Skip rows whose name is obviously a person (la_btrc has a chunk of
+// owner-name registrations like "DIANA ROBINSON" mis-tagged as bars). Also
+// skip pure numerical / placeholder names.
+function isPersonNameOrJunk(name) {
+ if (!name) return true;
+ if (name.length < 3) return true;
+ // "FIRSTNAME LASTNAME" all-caps with no digits and no business-y suffix
+ const looksLikePerson = /^[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)/i.test(name);
+ if (looksLikePerson) return true;
+ // Generic strip-mall placeholders sometimes appear
+ if (/^(SUITE|UNIT|TBD|N\/A|UNKNOWN)/i.test(name)) return true;
+ return false;
+}
+
+(async () => {
+ console.log('[seed-corridor-food] pulling food rows from ventura_corridor…');
+
+ // OSM-source rows (clean POIs)
+ const osmSql = `
+ SELECT name, category, category_raw, address, street_number, street_name, city, zip,
+ lat, lng, phone, website, raw
+ FROM businesses
+ WHERE on_corridor = true
+ AND merged_into IS NULL
+ AND source = 'osm'
+ AND category IN ('amenity: restaurant','amenity: cafe','amenity: bar','amenity: fast food')
+ `;
+
+ // la_btrc rows filtered on NAICS labels (avoids the "Barber shops"->bar bug)
+ const btrcSql = `
+ SELECT name, category, category_raw, address, street_number, street_name, city, zip,
+ lat, lng, phone, website, raw
+ FROM businesses
+ WHERE on_corridor = true
+ AND merged_into IS NULL
+ AND source = 'la_btrc'
+ AND category_raw = ANY($1::text[])
+ `;
+
+ const [{ rows: osmRows }, { rows: btrcRows }] = await Promise.all([
+ corridor.query(osmSql),
+ corridor.query(btrcSql, [Array.from(BTRC_FOOD_LABELS)])
+ ]);
+
+ console.log(`[seed-corridor-food] pulled ${osmRows.length} osm rows + ${btrcRows.length} btrc rows`);
+
+ // OSM is higher-trust signal — ingest first so subsequent BTRC dups are skipped.
+ const all = [...osmRows, ...btrcRows];
+ const seenSlug = new Set();
+ const seenNameAddr = new Set(); // dedupe key: lower(name)|street_number+street_name|zip
+
+ let inserted = 0, skipped = 0, conflicted = 0, dropped_junk = 0, dropped_city = 0;
+
+ for (const r of all) {
+ const cleanName = titleCaseName(r.name);
+ if (isPersonNameOrJunk(r.name)) { dropped_junk++; continue; }
+
+ const city = normalizeCity(r.city, r.zip);
+ if (!city || !TARGET_CITIES.has(city)) { dropped_city++; continue; }
+
+ const street = titleCaseStreet(r.address || [r.street_number, r.street_name].filter(Boolean).join(' '));
+ if (!street) { dropped_junk++; continue; }
+
+ // Strip city/zip suffix from address ("14704 Ventura Boulevard Sherman Oaks 91403")
+ let cleanStreet = street;
+ cleanStreet = cleanStreet.replace(new RegExp(`\\s+${city}\\s+\\d{5}(-\\d{4})?$`, 'i'), '');
+ cleanStreet = cleanStreet.replace(/\s+\d{5}(-\d{4})?$/, '');
+
+ const dedupeKey = `${cleanName.toLowerCase()}|${cleanStreet.toLowerCase()}|${(r.zip||'').slice(0,5)}`;
+ if (seenNameAddr.has(dedupeKey)) { skipped++; continue; }
+ seenNameAddr.add(dedupeKey);
+
+ // Slug must be globally unique. Append city for collision safety
+ // (Chipotle on Ventura appears in 3 cities).
+ let slug = slugify(`${cleanName} ${city}`, { lower: true, strict: true });
+ if (seenSlug.has(slug)) {
+ // Disambiguate with last 4 of zip + first 3 of street number
+ const sn = (r.street_number || '').replace(/\D/g, '').slice(0, 4) || '0';
+ slug = `${slug}-${sn}`;
+ }
+ seenSlug.add(slug);
+
+ const headline = buildHeadline(r);
+ const description = `${cleanName} is a food & drink 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,'food',$3,$4,$5,$6,'CA',$7,$8,$9,$10,$11,$12,
+ 'free','unclaimed',false,'active','public_records')
+ ON CONFLICT (slug) DO NOTHING
+ RETURNING id`,
+ [slug, cleanName, headline, description, cleanStreet, city, zip, city,
+ r.lat, r.lng, phone, website]
+ );
+ if (ins.rows.length) inserted++;
+ else conflicted++;
+ } catch (err) {
+ console.error(`[seed-corridor-food] insert failed for "${cleanName}": ${err.message}`);
+ skipped++;
+ }
+ }
+
+ console.log(`[seed-corridor-food] inserted=${inserted} skipped(dup)=${skipped} conflicted(slug-exists)=${conflicted} dropped_junk=${dropped_junk} dropped_city=${dropped_city}`);
+
+ await corridor.end();
+ await target.pool.end();
+ process.exit(0);
+})().catch(err => {
+ console.error('[seed-corridor-food] fatal', err);
+ process.exit(1);
+});
← 92a37d6 feat(seo): image sitemap support for Google Image search
·
back to Ventura Claw Leads
·
Generalize corridor seed to all 8 verticals (1,938 directory 6572248 →