20959

Committed 13 Jun 2026 02:43PM UTC coverage: 71.021% (+1.5%) from 69.555%

Build # 20959

Build Type

push

circleci

Committed by

edwh

Commit Message

feat(web): redirect /councils to /partnerships

The councils content now lives at /partnerships; add a route rule so the old
/councils URL (which 404'd) redirects there.

Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>

Coverage Stats

10705 of 14260 branches covered (75.07%)

Branch coverage included in aggregate %.

116833 of 165317 relevant lines covered (70.67%)

35.88 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

83.33

/iznik-batch/app/Services/PostcodeRemapService.php

<?php

namespace App\Services;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

/**
 * Remaps postcodes to their nearest enclosing area using PostGIS KNN queries.
 *
 * When a location area's geometry is created or modified, postcodes within
 * that geometry need to be reassigned to the correct (smallest, nearest) area.
 * This mirrors the V1 PHP Location::remapPostcodes() logic.
 *
 * Uses PostgreSQL/PostGIS for efficient K-nearest-neighbor spatial queries,
 * and MySQL for reading/writing the canonical locations data.
 */
class PostcodeRemapService
{
    /**
     * SRID used for spatial operations (Web Mercator).
     */
    private int $srid;

    public function __construct()
    {
        $this->srid = (int) config('freegle.srid', 3857);
    }

    /**
     * Remap postcodes within a given WKT polygon to their nearest area.
     *
     * Syncs the affected location to PostgreSQL first, then queries PostGIS
     * KNN to find the best area for each postcode.
     *
     * @param int|null $locationId The location that was modified (for incremental sync).
     * @param string|null $polygon WKT polygon to scope the remap. NULL = remap all.
     * @return int Number of postcodes remapped.
     */
    public function remapPostcodes(?int $locationId = NULL, ?string $polygon = NULL): int
    {
        if (! $this->postgresAvailable()) {
            Log::warning('PostcodeRemapService: PostgreSQL connection not available, skipping remap');

            return 0;
        }

        // Ensure PostgreSQL schema exists and sync location data.
        $this->ensurePostgresSchema();

        if ($polygon) {
            // Sync all locations within the polygon scope to PostgreSQL.
            // This is critical: the location_id in the task is the location that was edited,
            // but other locations within the polygon (e.g. newly created areas) may also need
            // syncing. V1 synced the edited location inline in setGeometry() before calling
            // remapPostcodes(), and relied on a nightly cron for everything else. We do better
            // by syncing all locations in the affected area.
            $this->syncLocationsInPolygon($polygon);
        } elseif ($locationId) {
            $this->syncSingleLocation($locationId);
        } else {
            $this->syncAllLocations();
        }

        // Build the postcode selector as a Builder and stream it in id-keyed
        // chunks via chunkById(). MySQL's default PDO buffer mode loads the
        // entire result set into client memory at execute() time, so a plain
        // cursor() on this query still tips past 1.6 GB — chunkById() runs
        // many small LIMIT queries instead, keeping peak memory at one batch.
        // Per-postcode-row 1:1 with locations_spatial in practice, so the
        // DISTINCT on the upstream query is dropped here without changing
        // the iterated set.
        $pcQuery = DB::table('locations_spatial')
            ->join('locations', 'locations_spatial.locationid', '=', 'locations.id')
            ->where('locations.type', 'Postcode')
            ->whereRaw("LOCATE(' ', locations.name) > 0")
            ->select(
                'locations.id as locations_id',
                'locations_spatial.locationid',
                'locations.name',
                'locations.lat',
                'locations.lng',
                'locations.areaid',
            );

        if ($polygon) {
            // Include postcodes that either fall within the polygon OR currently point at
            // the affected location via areaid. The latter catches postcodes whose areaid
            // was assigned by KNN (buffered intersection / nearest-neighbour) rather than
            // strict containment — those sit outside the polygon but still need remapping
            // when the location they reference is excluded or its geometry changes.
            if ($locationId) {
                $pcQuery->where(function ($q) use ($polygon, $locationId) {
                    $q->whereRaw(
                        "ST_Contains(ST_GeomFromText(?, {$this->srid}), locations_spatial.geometry)",
                        [$polygon],
                    )->orWhere('locations.areaid', $locationId);
                });
            } else {
                $pcQuery->whereRaw(
                    "ST_Contains(ST_GeomFromText(?, {$this->srid}), locations_spatial.geometry)",
                    [$polygon],
                );
            }
        }

        $count   = 0;
        $updated = 0;

        // chunkById issues `WHERE locations.id > $last LIMIT N` queries until
        // exhausted. 1000 rows / batch keeps each query's result buffer well
        // under 1 MB while still amortising round-trip cost.
        $pcQuery->orderBy('locations.id')->chunkById(1000, function ($postcodes) use (&$count, &$updated) {
            foreach ($postcodes as $pc) {
                $newAreaId = $this->findNearestArea($pc->lng, $pc->lat);

                if ($newAreaId && $newAreaId != $pc->areaid) {
                    DB::update('UPDATE locations SET areaid = ? WHERE id = ?', [
                        $newAreaId,
                        $pc->locationid,
                    ]);
                    $updated++;
                }

                $count++;

                if ($count % 1000 === 0) {
                    Log::info("PostcodeRemapService: processed {$count}, updated {$updated}");
                }
            }
        }, 'locations.id', 'locations_id');

        Log::info("PostcodeRemapService: remapped {$updated}/{$count} postcodes");

        return $updated;
    }

    /**
     * Find the nearest area location for a given point using PostGIS KNN.
     *
     * Uses expanding buffer intersection levels (matching V1 algorithm) to find
     * the smallest area that contains or is very close to the point.
     *
     * @param float $lng Longitude
     * @param float $lat Latitude
     * @return int|null Location ID of the best matching area, or null.
     */
    public function findNearestArea(float $lng, float $lat): ?int
    {
        $result = DB::connection('pgsql')->select("
            WITH ourpoint AS (
                SELECT ST_MakePoint(?, ?) AS p
            )
            SELECT locationid FROM (
                SELECT
                    locationid,
                    ST_Area(location) AS area,
                    CASE
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.00015625), ?)) THEN 1
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.0003125), ?)) THEN 2
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.000625), ?)) THEN 3
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.00125), ?)) THEN 4
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.0025), ?)) THEN 5
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.005), ?)) THEN 6
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.01), ?)) THEN 7
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.02), ?)) THEN 8
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.04), ?)) THEN 9
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.08), ?)) THEN 10
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.16), ?)) THEN 11
                        WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.32), ?)) THEN 12
                    END AS intersects
                FROM (
                    SELECT locationid,
                           location,
                           location <-> ST_SetSRID((SELECT p FROM ourpoint), ?) AS dist
                    FROM locations
                    WHERE ST_Area(location) BETWEEN 0.00001 AND 0.15
                    ORDER BY location <-> ST_SetSRID((SELECT p FROM ourpoint), ?)
                    LIMIT 10
                ) q
            ) candidates
            WHERE intersects IS NOT NULL
            ORDER BY intersects ASC, area ASC
            LIMIT 1
        ", [
            $lng, $lat,
            // 12 SRID params for the buffer intersections
            $this->srid, $this->srid, $this->srid, $this->srid,
            $this->srid, $this->srid, $this->srid, $this->srid,
            $this->srid, $this->srid, $this->srid, $this->srid,
            // 2 SRID params for the KNN subquery
            $this->srid, $this->srid,
        ]);

        if (count($result) > 0) {
            return (int) $result[0]->locationid;
        }

        return NULL;
    }

    /**
     * Ensure the PostgreSQL locations table and indexes exist.
     */
    public function ensurePostgresSchema(): void
    {
        $pgsql = DB::connection('pgsql');

        $pgsql->statement('CREATE EXTENSION IF NOT EXISTS postgis');
        $pgsql->statement('CREATE EXTENSION IF NOT EXISTS btree_gist');

        // Create the location_type enum if it doesn't exist.
        $typeExists = $pgsql->selectOne("SELECT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'location_type')");
        if (! $typeExists->exists) {
            $pgsql->statement("CREATE TYPE location_type AS ENUM('Road','Polygon','Line','Point','Postcode')");
        }

        // Create the locations table if it doesn't exist.
        $tableExists = $pgsql->selectOne("SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'locations')");
        if (! $tableExists->exists) {
            $pgsql->statement('CREATE TABLE locations (
                id serial PRIMARY KEY,
                locationid bigint UNIQUE NOT NULL,
                name text,
                type location_type,
                area numeric,
                location geometry
            )');
            $pgsql->statement('CREATE INDEX idx_locations_location ON locations USING gist (location)');
        }
    }

    /**
     * Sync a single location from MySQL to PostgreSQL (upsert).
     *
     * Matches V1's per-location sync in setGeometry() — fast for single updates.
     */
    private function syncSingleLocation(int $locationId): void
    {
        $loc = DB::selectOne("
            SELECT locations.id, name, type,
                   ST_AsText(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) AS geom
            FROM locations
            LEFT JOIN locations_excluded le ON locations.id = le.locationid
            WHERE locations.id = ?
            AND le.locationid IS NULL
            AND ST_Dimension(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) = 2
            AND type != 'Postcode'
        ", [$locationId]);

        if (! $loc || ! $loc->geom) {
            return;
        }

        DB::connection('pgsql')->statement(
            "INSERT INTO locations (locationid, name, type, area, location)
             VALUES (?, ?, ?, ST_Area(ST_GeomFromText(?, ?)), ST_GeomFromText(?, ?))
             ON CONFLICT (locationid) DO UPDATE SET
                 name = EXCLUDED.name,
                 type = EXCLUDED.type,
                 area = EXCLUDED.area,
                 location = EXCLUDED.location",
            [$loc->id, $loc->name, $loc->type, $loc->geom, $this->srid, $loc->geom, $this->srid]
        );
    }

    /**
     * Sync all non-postcode polygon locations within a WKT polygon to PostgreSQL.
     *
     * When a location is created or its geometry is changed, the remap task carries
     * the polygon scope. We need to sync all locations intersecting that scope —
     * not just the edited location — because newly created or recently modified
     * locations in the area may not yet be in PostgreSQL.
     */
    private function syncLocationsInPolygon(string $polygon): void
    {
        // First remove any now-excluded locations within the polygon scope from PostgreSQL.
        // Without this, a location excluded after being synced would remain in PG's KNN
        // index until the nightly syncAllLocations table-swap rebuilds from scratch,
        // meaning exclusions wouldn't take effect for postcode remapping until then.
        $excludedInScope = DB::select("
            SELECT DISTINCT le.locationid
            FROM locations_excluded le
            INNER JOIN locations_spatial ls ON ls.locationid = le.locationid
            WHERE ST_Intersects(ls.geometry, ST_GeomFromText(?, {$this->srid}))
        ", [$polygon]);

        if (!empty($excludedInScope)) {
            $ids = array_map(fn ($r) => (int) $r->locationid, $excludedInScope);
            DB::connection('pgsql')->delete(
                'DELETE FROM locations WHERE locationid IN (' . implode(',', $ids) . ')'
            );
            Log::info('PostcodeRemapService: removed ' . count($ids) . ' excluded locations from PostgreSQL within polygon scope');
        }

        $locations = DB::select("
            SELECT locations.id, locations.name, locations.type,
                   ST_AsText(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE ls.geometry END) AS geom
            FROM locations_spatial ls
            INNER JOIN locations ON ls.locationid = locations.id
            LEFT JOIN locations_excluded le ON locations.id = le.locationid
            WHERE le.locationid IS NULL
            AND ST_Intersects(ls.geometry, ST_GeomFromText(?, {$this->srid}))
            AND ST_Dimension(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE ls.geometry END) = 2
            AND locations.type != 'Postcode'
        ", [$polygon]);

        $synced = 0;

        foreach ($locations as $loc) {
            if (! $loc->geom) {
                continue;
            }

            DB::connection('pgsql')->statement(
                "INSERT INTO locations (locationid, name, type, area, location)
                 VALUES (?, ?, ?, ST_Area(ST_GeomFromText(?, ?)), ST_GeomFromText(?, ?))
                 ON CONFLICT (locationid) DO UPDATE SET
                     name = EXCLUDED.name,
                     type = EXCLUDED.type,
                     area = EXCLUDED.area,
                     location = EXCLUDED.location",
                [$loc->id, $loc->name, $loc->type, $loc->geom, $this->srid, $loc->geom, $this->srid]
            );

            $synced++;
        }

        Log::info("PostcodeRemapService: synced {$synced} locations in polygon scope to PostgreSQL");
    }

    /**
     * Full sync of all non-postcode polygon locations from MySQL to PostgreSQL.
     *
     * Uses a temp table + atomic swap matching V1's copyLocationsToPostgresql().
     * Used when no specific location ID is provided (full remap).
     */
    private function syncAllLocations(): void
    {
        $pgsql = DB::connection('pgsql');
        // uniqid(more_entropy=true) -> 23 hex chars; pure uniqid() can repeat
        // within the same microsecond, and we want the suffix to be unique
        // across any concurrent / racing invocation.
        $uniq = '_' . uniqid('', true);
        // PG identifiers can't contain '.', which more_entropy adds.
        $uniq = str_replace('.', '_', $uniq);
        $tableName = "locations_tmp{$uniq}";
        $indexName = "idx_loc_tmp{$uniq}";

        // Defensive cleanup of orphan tmp tables left behind by crashed
        // previous runs (e.g. OOM during the streaming insert). Each run uses
        // a uniq() suffix so any locations_tmp_* sitting in pg_tables is not
        // ours and is safe to drop. Skip our own table for paranoia — even
        // though uniqid() collisions are astronomically unlikely, the
        // 2026-05-15 01:06 failure ("relation does not exist" on first chunk
        // INSERT) had no other plausible explanation we could nail down.
        foreach ($pgsql->select(
            "SELECT table_name FROM information_schema.tables
             WHERE table_schema = 'public' AND table_name LIKE 'locations_tmp_%'
               AND table_name <> ?",
            [$tableName],
        ) as $t) {
            $pgsql->statement("DROP TABLE IF EXISTS \"{$t->table_name}\"");
        }

        // Single CREATE UNLOGGED TABLE — the previous CREATE-then-ALTER-SET-UNLOGGED
        // sequence rewrites the table on disk and is the most plausible trigger
        // of the catalog-visibility error seen on 2026-05-15 01:06. UNLOGGED is
        // safe for this temp staging table because we DROP/rename it as part of
        // the swap before the run ends, so crash-recovery truncation never
        // matters.
        $pgsql->statement("DROP TABLE IF EXISTS \"{$tableName}\"");
        $pgsql->statement("CREATE UNLOGGED TABLE \"{$tableName}\" (
            id serial PRIMARY KEY,
            locationid bigint UNIQUE NOT NULL,
            name text,
            type location_type,
            area numeric,
            location geometry
        )");

        // Fetch non-excluded polygon locations from MySQL in id-keyed chunks.
        // ST_AsText output for complex polygons runs to many KB each, and the
        // full set is ~50k rows — a single buffered SELECT tips past 512M.
        // chunkById() paginates via LIMIT so peak memory stays at one batch.
        $locQuery = DB::table('locations')
            ->leftJoin('locations_excluded as le', 'locations.id', '=', 'le.locationid')
            ->whereNull('le.locationid')
            ->whereRaw("ST_Dimension(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) = 2")
            ->where('locations.type', '!=', 'Postcode')
            ->select(
                'locations.id',
                'locations.name',
                'locations.type',
                DB::raw('ST_AsText(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) AS geom'),
            );

        $syncedCount = 0;
        $locQuery->orderBy('locations.id')->chunkById(500, function ($locations) use (&$syncedCount, $pgsql, $tableName) {
            foreach ($locations as $loc) {
                if (! $loc->geom) {
                    continue;
                }

                $pgsql->insert(
                    "INSERT INTO \"{$tableName}\" (locationid, name, type, area, location)
                     VALUES (?, ?, ?, ST_Area(ST_GeomFromText(?, ?)), ST_GeomFromText(?, ?))",
                    [$loc->id, $loc->name, $loc->type, $loc->geom, $this->srid, $loc->geom, $this->srid],
                );
                $syncedCount++;
            }
        }, 'locations.id', 'id');

        // Build index on temp table before swap.
        $pgsql->statement("CREATE INDEX \"{$indexName}\" ON \"{$tableName}\" USING gist (location)");

        // Atomic swap: rename current to old, temp to current, drop old.
        $oldTable = "locations_old{$uniq}";
        $pgsql->statement("ALTER TABLE IF EXISTS locations RENAME TO \"{$oldTable}\"");
        $pgsql->statement("ALTER TABLE \"{$tableName}\" RENAME TO locations");
        $pgsql->statement("DROP INDEX IF EXISTS idx_locations_location");
        $pgsql->statement("ALTER INDEX \"{$indexName}\" RENAME TO idx_locations_location");
        $pgsql->statement("DROP TABLE IF EXISTS \"{$oldTable}\"");

        Log::info("PostcodeRemapService: synced {$syncedCount} locations to PostgreSQL");
    }

    /**
     * Check if the PostgreSQL connection is available.
     */
    public function postgresAvailable(): bool
    {
        try {
            DB::connection('pgsql')->getPdo();

            return TRUE;
        } catch (\Throwable $e) {
            return FALSE;
        }
    }
}

1	<?php
2
3	namespace App\Services;
4
5	use Illuminate\Support\Facades\DB;
6	use Illuminate\Support\Facades\Log;
7
8	/**
9	* Remaps postcodes to their nearest enclosing area using PostGIS KNN queries.
10	*
11	* When a location area's geometry is created or modified, postcodes within
12	* that geometry need to be reassigned to the correct (smallest, nearest) area.
13	* This mirrors the V1 PHP Location::remapPostcodes() logic.
14	*
15	* Uses PostgreSQL/PostGIS for efficient K-nearest-neighbor spatial queries,
16	* and MySQL for reading/writing the canonical locations data.
17	*/
18	class PostcodeRemapService
19	{
20	/**
21	* SRID used for spatial operations (Web Mercator).
22	*/
23	private int $srid;
24
25	public function __construct()	9✔
26	{
27	$this->srid = (int) config('freegle.srid', 3857);	9✔
28	}
29
30	/**
31	* Remap postcodes within a given WKT polygon to their nearest area.
32	*
33	* Syncs the affected location to PostgreSQL first, then queries PostGIS
34	* KNN to find the best area for each postcode.
35	*
36	* @param int\|null $locationId The location that was modified (for incremental sync).
37	* @param string\|null $polygon WKT polygon to scope the remap. NULL = remap all.
38	* @return int Number of postcodes remapped.
39	*/
40	public function remapPostcodes(?int $locationId = NULL, ?string $polygon = NULL): int	7✔
41	{
42	if (! $this->postgresAvailable()) {	7✔
43	Log::warning('PostcodeRemapService: PostgreSQL connection not available, skipping remap');	1✔
44
45	return 0;	1✔
46	}
47
48	// Ensure PostgreSQL schema exists and sync location data.
49	$this->ensurePostgresSchema();	6✔
50
51	if ($polygon) {	6✔
52	// Sync all locations within the polygon scope to PostgreSQL.
53	// This is critical: the location_id in the task is the location that was edited,
54	// but other locations within the polygon (e.g. newly created areas) may also need
55	// syncing. V1 synced the edited location inline in setGeometry() before calling
56	// remapPostcodes(), and relied on a nightly cron for everything else. We do better
57	// by syncing all locations in the affected area.
58	$this->syncLocationsInPolygon($polygon);	4✔
59	} elseif ($locationId) {	2✔
60	$this->syncSingleLocation($locationId);	×
61	} else {
62	$this->syncAllLocations();	2✔
63	}
64
65	// Build the postcode selector as a Builder and stream it in id-keyed
66	// chunks via chunkById(). MySQL's default PDO buffer mode loads the
67	// entire result set into client memory at execute() time, so a plain
68	// cursor() on this query still tips past 1.6 GB — chunkById() runs
69	// many small LIMIT queries instead, keeping peak memory at one batch.
70	// Per-postcode-row 1:1 with locations_spatial in practice, so the
71	// DISTINCT on the upstream query is dropped here without changing
72	// the iterated set.
73	$pcQuery = DB::table('locations_spatial')	6✔
74	->join('locations', 'locations_spatial.locationid', '=', 'locations.id')	6✔
75	->where('locations.type', 'Postcode')	6✔
76	->whereRaw("LOCATE(' ', locations.name) > 0")	6✔
77	->select(	6✔
78	'locations.id as locations_id',	6✔
79	'locations_spatial.locationid',	6✔
80	'locations.name',	6✔
81	'locations.lat',	6✔
82	'locations.lng',	6✔
83	'locations.areaid',	6✔
84	);	6✔
85
86	if ($polygon) {	6✔
87	// Include postcodes that either fall within the polygon OR currently point at
88	// the affected location via areaid. The latter catches postcodes whose areaid
89	// was assigned by KNN (buffered intersection / nearest-neighbour) rather than
90	// strict containment — those sit outside the polygon but still need remapping
91	// when the location they reference is excluded or its geometry changes.
92	if ($locationId) {	4✔
93	$pcQuery->where(function ($q) use ($polygon, $locationId) {	4✔
94	$q->whereRaw(	4✔
95	"ST_Contains(ST_GeomFromText(?, {$this->srid}), locations_spatial.geometry)",	4✔
96	[$polygon],	4✔
97	)->orWhere('locations.areaid', $locationId);	4✔
98	});	4✔
99	} else {
100	$pcQuery->whereRaw(	×
101	"ST_Contains(ST_GeomFromText(?, {$this->srid}), locations_spatial.geometry)",	×
102	[$polygon],	×
103	);	×
104	}
105	}
106
107	$count = 0;	6✔
108	$updated = 0;	6✔
109
110	// chunkById issues `WHERE locations.id > $last LIMIT N` queries until
111	// exhausted. 1000 rows / batch keeps each query's result buffer well
112	// under 1 MB while still amortising round-trip cost.
113	$pcQuery->orderBy('locations.id')->chunkById(1000, function ($postcodes) use (&$count, &$updated) {	6✔
114	foreach ($postcodes as $pc) {	4✔
115	$newAreaId = $this->findNearestArea($pc->lng, $pc->lat);	4✔
116
117	if ($newAreaId && $newAreaId != $pc->areaid) {	4✔
118	DB::update('UPDATE locations SET areaid = ? WHERE id = ?', [	4✔
119	$newAreaId,	4✔
120	$pc->locationid,	4✔
121	]);	4✔
122	$updated++;	4✔
123	}
124
125	$count++;	4✔
126
127	if ($count % 1000 === 0) {	4✔
128	Log::info("PostcodeRemapService: processed {$count}, updated {$updated}");	×
129	}
130	}
131	}, 'locations.id', 'locations_id');	6✔
132
133	Log::info("PostcodeRemapService: remapped {$updated}/{$count} postcodes");	6✔
134
135	return $updated;	6✔
136	}
137
138	/**
139	* Find the nearest area location for a given point using PostGIS KNN.
140	*
141	* Uses expanding buffer intersection levels (matching V1 algorithm) to find
142	* the smallest area that contains or is very close to the point.
143	*
144	* @param float $lng Longitude
145	* @param float $lat Latitude
146	* @return int\|null Location ID of the best matching area, or null.
147	*/
148	public function findNearestArea(float $lng, float $lat): ?int	5✔
149	{
150	$result = DB::connection('pgsql')->select("	5✔
151	WITH ourpoint AS (
152	SELECT ST_MakePoint(?, ?) AS p
153	)
154	SELECT locationid FROM (
155	SELECT
156	locationid,
157	ST_Area(location) AS area,
158	CASE
159	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.00015625), ?)) THEN 1
160	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.0003125), ?)) THEN 2
161	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.000625), ?)) THEN 3
162	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.00125), ?)) THEN 4
163	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.0025), ?)) THEN 5
164	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.005), ?)) THEN 6
165	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.01), ?)) THEN 7
166	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.02), ?)) THEN 8
167	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.04), ?)) THEN 9
168	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.08), ?)) THEN 10
169	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.16), ?)) THEN 11
170	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.32), ?)) THEN 12
171	END AS intersects
172	FROM (
173	SELECT locationid,
174	location,
175	location <-> ST_SetSRID((SELECT p FROM ourpoint), ?) AS dist
176	FROM locations
177	WHERE ST_Area(location) BETWEEN 0.00001 AND 0.15
178	ORDER BY location <-> ST_SetSRID((SELECT p FROM ourpoint), ?)
179	LIMIT 10
180	) q
181	) candidates
182	WHERE intersects IS NOT NULL
183	ORDER BY intersects ASC, area ASC
184	LIMIT 1
185	", [	5✔
186	$lng, $lat,	5✔
187	// 12 SRID params for the buffer intersections
188	$this->srid, $this->srid, $this->srid, $this->srid,	5✔
189	$this->srid, $this->srid, $this->srid, $this->srid,	5✔
190	$this->srid, $this->srid, $this->srid, $this->srid,	5✔
191	// 2 SRID params for the KNN subquery
192	$this->srid, $this->srid,	5✔
193	]);	5✔
194
195	if (count($result) > 0) {	5✔
196	return (int) $result[0]->locationid;	5✔
197	}
198
199	return NULL;	1✔
200	}
201
202	/**
203	* Ensure the PostgreSQL locations table and indexes exist.
204	*/
205	public function ensurePostgresSchema(): void	6✔
206	{
207	$pgsql = DB::connection('pgsql');	6✔
208
209	$pgsql->statement('CREATE EXTENSION IF NOT EXISTS postgis');	6✔
210	$pgsql->statement('CREATE EXTENSION IF NOT EXISTS btree_gist');	6✔
211
212	// Create the location_type enum if it doesn't exist.
213	$typeExists = $pgsql->selectOne("SELECT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'location_type')");	6✔
214	if (! $typeExists->exists) {	6✔
215	$pgsql->statement("CREATE TYPE location_type AS ENUM('Road','Polygon','Line','Point','Postcode')");	×
216	}
217
218	// Create the locations table if it doesn't exist.
219	$tableExists = $pgsql->selectOne("SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'locations')");	6✔
220	if (! $tableExists->exists) {	6✔
221	$pgsql->statement('CREATE TABLE locations (	1✔
222	id serial PRIMARY KEY,
223	locationid bigint UNIQUE NOT NULL,
224	name text,
225	type location_type,
226	area numeric,
227	location geometry
228	)');	1✔
229	$pgsql->statement('CREATE INDEX idx_locations_location ON locations USING gist (location)');	1✔
230	}
231	}
232
233	/**
234	* Sync a single location from MySQL to PostgreSQL (upsert).
235	*
236	* Matches V1's per-location sync in setGeometry() — fast for single updates.
237	*/
238	private function syncSingleLocation(int $locationId): void	×
239	{
240	$loc = DB::selectOne("	×
241	SELECT locations.id, name, type,
242	ST_AsText(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) AS geom
243	FROM locations
244	LEFT JOIN locations_excluded le ON locations.id = le.locationid
245	WHERE locations.id = ?
246	AND le.locationid IS NULL
247	AND ST_Dimension(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) = 2
248	AND type != 'Postcode'
249	", [$locationId]);	×
250
251	if (! $loc \|\| ! $loc->geom) {	×
252	return;	×
253	}
254
255	DB::connection('pgsql')->statement(	×
256	"INSERT INTO locations (locationid, name, type, area, location)	×
257	VALUES (?, ?, ?, ST_Area(ST_GeomFromText(?, ?)), ST_GeomFromText(?, ?))
258	ON CONFLICT (locationid) DO UPDATE SET
259	name = EXCLUDED.name,
260	type = EXCLUDED.type,
261	area = EXCLUDED.area,
262	location = EXCLUDED.location",	×
263	[$loc->id, $loc->name, $loc->type, $loc->geom, $this->srid, $loc->geom, $this->srid]	×
264	);	×
265	}
266
267	/**
268	* Sync all non-postcode polygon locations within a WKT polygon to PostgreSQL.
269	*
270	* When a location is created or its geometry is changed, the remap task carries
271	* the polygon scope. We need to sync all locations intersecting that scope —
272	* not just the edited location — because newly created or recently modified
273	* locations in the area may not yet be in PostgreSQL.
274	*/
275	private function syncLocationsInPolygon(string $polygon): void	4✔
276	{
277	// First remove any now-excluded locations within the polygon scope from PostgreSQL.
278	// Without this, a location excluded after being synced would remain in PG's KNN
279	// index until the nightly syncAllLocations table-swap rebuilds from scratch,
280	// meaning exclusions wouldn't take effect for postcode remapping until then.
281	$excludedInScope = DB::select("	4✔
282	SELECT DISTINCT le.locationid
283	FROM locations_excluded le
284	INNER JOIN locations_spatial ls ON ls.locationid = le.locationid
285	WHERE ST_Intersects(ls.geometry, ST_GeomFromText(?, {$this->srid}))	4✔
286	", [$polygon]);	4✔
287
288	if (!empty($excludedInScope)) {	4✔
289	$ids = array_map(fn ($r) => (int) $r->locationid, $excludedInScope);	2✔
290	DB::connection('pgsql')->delete(	2✔
291	'DELETE FROM locations WHERE locationid IN (' . implode(',', $ids) . ')'	2✔
292	);	2✔
293	Log::info('PostcodeRemapService: removed ' . count($ids) . ' excluded locations from PostgreSQL within polygon scope');	2✔
294	}
295
296	$locations = DB::select("	4✔
297	SELECT locations.id, locations.name, locations.type,
298	ST_AsText(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE ls.geometry END) AS geom
299	FROM locations_spatial ls
300	INNER JOIN locations ON ls.locationid = locations.id
301	LEFT JOIN locations_excluded le ON locations.id = le.locationid
302	WHERE le.locationid IS NULL
303	AND ST_Intersects(ls.geometry, ST_GeomFromText(?, {$this->srid}))	4✔
304	AND ST_Dimension(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE ls.geometry END) = 2
305	AND locations.type != 'Postcode'
306	", [$polygon]);	4✔
307
308	$synced = 0;	4✔
309
310	foreach ($locations as $loc) {	4✔
311	if (! $loc->geom) {	3✔
312	continue;	×
313	}
314
315	DB::connection('pgsql')->statement(	3✔
316	"INSERT INTO locations (locationid, name, type, area, location)	3✔
317	VALUES (?, ?, ?, ST_Area(ST_GeomFromText(?, ?)), ST_GeomFromText(?, ?))
318	ON CONFLICT (locationid) DO UPDATE SET
319	name = EXCLUDED.name,
320	type = EXCLUDED.type,
321	area = EXCLUDED.area,
322	location = EXCLUDED.location",	3✔
323	[$loc->id, $loc->name, $loc->type, $loc->geom, $this->srid, $loc->geom, $this->srid]	3✔
324	);	3✔
325
326	$synced++;	3✔
327	}
328
329	Log::info("PostcodeRemapService: synced {$synced} locations in polygon scope to PostgreSQL");	4✔
330	}
331
332	/**
333	* Full sync of all non-postcode polygon locations from MySQL to PostgreSQL.
334	*
335	* Uses a temp table + atomic swap matching V1's copyLocationsToPostgresql().
336	* Used when no specific location ID is provided (full remap).
337	*/
338	private function syncAllLocations(): void	2✔
339	{
340	$pgsql = DB::connection('pgsql');	2✔
341	// uniqid(more_entropy=true) -> 23 hex chars; pure uniqid() can repeat
342	// within the same microsecond, and we want the suffix to be unique
343	// across any concurrent / racing invocation.
344	$uniq = '_' . uniqid('', true);	2✔
345	// PG identifiers can't contain '.', which more_entropy adds.
346	$uniq = str_replace('.', '_', $uniq);	2✔
347	$tableName = "locations_tmp{$uniq}";	2✔
348	$indexName = "idx_loc_tmp{$uniq}";	2✔
349
350	// Defensive cleanup of orphan tmp tables left behind by crashed
351	// previous runs (e.g. OOM during the streaming insert). Each run uses
352	// a uniq() suffix so any locations_tmp_* sitting in pg_tables is not
353	// ours and is safe to drop. Skip our own table for paranoia — even
354	// though uniqid() collisions are astronomically unlikely, the
355	// 2026-05-15 01:06 failure ("relation does not exist" on first chunk
356	// INSERT) had no other plausible explanation we could nail down.
357	foreach ($pgsql->select(	2✔
358	"SELECT table_name FROM information_schema.tables	2✔
359	WHERE table_schema = 'public' AND table_name LIKE 'locations_tmp_%'
360	AND table_name <> ?",	2✔
361	[$tableName],	2✔
362	) as $t) {	2✔
363	$pgsql->statement("DROP TABLE IF EXISTS \"{$t->table_name}\"");	1✔
364	}
365
366	// Single CREATE UNLOGGED TABLE — the previous CREATE-then-ALTER-SET-UNLOGGED
367	// sequence rewrites the table on disk and is the most plausible trigger
368	// of the catalog-visibility error seen on 2026-05-15 01:06. UNLOGGED is
369	// safe for this temp staging table because we DROP/rename it as part of
370	// the swap before the run ends, so crash-recovery truncation never
371	// matters.
372	$pgsql->statement("DROP TABLE IF EXISTS \"{$tableName}\"");	2✔
373	$pgsql->statement("CREATE UNLOGGED TABLE \"{$tableName}\" (	2✔
374	id serial PRIMARY KEY,
375	locationid bigint UNIQUE NOT NULL,
376	name text,
377	type location_type,
378	area numeric,
379	location geometry
380	)");	2✔
381
382	// Fetch non-excluded polygon locations from MySQL in id-keyed chunks.
383	// ST_AsText output for complex polygons runs to many KB each, and the
384	// full set is ~50k rows — a single buffered SELECT tips past 512M.
385	// chunkById() paginates via LIMIT so peak memory stays at one batch.
386	$locQuery = DB::table('locations')	2✔
387	->leftJoin('locations_excluded as le', 'locations.id', '=', 'le.locationid')	2✔
388	->whereNull('le.locationid')	2✔
389	->whereRaw("ST_Dimension(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) = 2")	2✔
390	->where('locations.type', '!=', 'Postcode')	2✔
391	->select(	2✔
392	'locations.id',	2✔
393	'locations.name',	2✔
394	'locations.type',	2✔
395	DB::raw('ST_AsText(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) AS geom'),	2✔
396	);	2✔
397
398	$syncedCount = 0;	2✔
399	$locQuery->orderBy('locations.id')->chunkById(500, function ($locations) use (&$syncedCount, $pgsql, $tableName) {	2✔
400	foreach ($locations as $loc) {	×
401	if (! $loc->geom) {	×
402	continue;	×
403	}
404
405	$pgsql->insert(	×
406	"INSERT INTO \"{$tableName}\" (locationid, name, type, area, location)	×
407	VALUES (?, ?, ?, ST_Area(ST_GeomFromText(?, ?)), ST_GeomFromText(?, ?))",	×
408	[$loc->id, $loc->name, $loc->type, $loc->geom, $this->srid, $loc->geom, $this->srid],	×
409	);	×
410	$syncedCount++;	×
411	}
412	}, 'locations.id', 'id');	2✔
413
414	// Build index on temp table before swap.
415	$pgsql->statement("CREATE INDEX \"{$indexName}\" ON \"{$tableName}\" USING gist (location)");	2✔
416
417	// Atomic swap: rename current to old, temp to current, drop old.
418	$oldTable = "locations_old{$uniq}";	2✔
419	$pgsql->statement("ALTER TABLE IF EXISTS locations RENAME TO \"{$oldTable}\"");	2✔
420	$pgsql->statement("ALTER TABLE \"{$tableName}\" RENAME TO locations");	2✔
421	$pgsql->statement("DROP INDEX IF EXISTS idx_locations_location");	2✔
422	$pgsql->statement("ALTER INDEX \"{$indexName}\" RENAME TO idx_locations_location");	2✔
423	$pgsql->statement("DROP TABLE IF EXISTS \"{$oldTable}\"");	2✔
424
425	Log::info("PostcodeRemapService: synced {$syncedCount} locations to PostgreSQL");	2✔
426	}
427
428	/**
429	* Check if the PostgreSQL connection is available.
430	*/
431	public function postgresAvailable(): bool	7✔
432	{
433	try {
434	DB::connection('pgsql')->getPdo();	7✔
435
436	return TRUE;	6✔
437	} catch (\Throwable $e) {	1✔
438	return FALSE;	1✔
439	}
440	}
441	}

Freegle / Iznik / 20959

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous