`migrations/005_pound_kennel_categories.sql`

58 lines
-- Add 'pound' (government animal control) and 'kennel' (private boarding/breeding
-- kennel that's distinct from "boarding"). OSM doesn't tag these separately, so
-- we use detection heuristics on name patterns when ingesting.
--
-- Existing rows that are pounds-disguised-as-shelters get reclassified by a
-- name-match UPDATE at the bottom of this migration (idempotent on re-run).

BEGIN;

-- Postgres CHECK constraints are immutable; drop+recreate to extend.
ALTER TABLE businesses DROP CONSTRAINT IF EXISTS businesses_category_check;
ALTER TABLE businesses
  ADD CONSTRAINT businesses_category_check
  CHECK (category IN (
    'vet_clinic','emergency_vet','specialty_hospital',
    'groomer','pet_store','shelter','breeder',
    'trainer','boarding','daycare','dog_park',
    'pound','kennel','rescue',
    'other'
  ));

-- Reclassify obvious "animal control" facilities currently filed as 'shelter'.
-- These are typically government-run impoundment + adoption combined.
UPDATE businesses
   SET category = 'pound'
 WHERE category = 'shelter'
   AND (
     name ILIKE '%animal control%'
     OR name ILIKE '%animal services%'
     OR name ILIKE '%animal care and control%'
     OR name ILIKE '% pound%'
     OR name ILIKE '%dept of animal%'
     OR name ILIKE '%department of animal%'
     OR name ILIKE '% acc' OR name ILIKE '% ac'      -- e.g., "Sacramento ACC"
   );

-- Reclassify obvious "rescue" orgs (private 501(c)(3)) into a distinct bucket
-- so the public can tell them apart from county pounds.
UPDATE businesses
   SET category = 'rescue'
 WHERE category = 'shelter'
   AND (
     name ILIKE '%rescue%'
     OR name ILIKE '%humane society%'
     OR name ILIKE '%spca%'
     OR name ILIKE '%aspca%'
   );

-- Reclassify "kennels" (commercial breeding/training/boarding kennels with
-- 'kennel' in the name) out of generic 'boarding'. Keeps the boarding bucket
-- focused on day-boarding facilities like Wagville / Camp Bow Wow types.
UPDATE businesses
   SET category = 'kennel'
 WHERE category = 'boarding'
   AND name ILIKE '%kennel%';

COMMIT;