3826

Committed 14 Apr 2026 05:20PM UTC coverage: 70.922% (-0.4%) from 71.314%

Build # 3826

Build Type

push

circleci

Committed by

invalid-email-address

Commit Message

fix: sync all locations in polygon scope before postcode remap

When a location is created/updated, the remap task only synced the single
location_id to PostgreSQL. But that ID is the location being edited, not
necessarily the one whose polygon changed. Newly created locations (like
Millhouses) weren't in PostGIS yet, so the KNN query used stale data and
mapped postcodes to the wrong area.

Now syncs all locations intersecting the polygon scope before running KNN,
matching V1's behavior where setGeometry() synced inline before remapping.

Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

Coverage Stats

13100 of 20081 branches covered (65.24%)

Branch coverage included in aggregate %.

16 of 18 new or added lines in 1 file covered. (88.89%)

12 existing lines in 4 files now uncovered.

92502 of 128817 relevant lines covered (71.81%)

17.29 hits per line

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

61.61

/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();
        }

        $geomFilter = '';
        $params = [];

        if ($polygon) {
            $geomFilter = "ST_Contains(ST_GeomFromText(?, {$this->srid}), locations_spatial.geometry) AND";
            $params[] = $polygon;
        }

        // Fetch all full postcodes (contain a space) within the scope.
        $postcodes = DB::select("
            SELECT DISTINCT locations_spatial.locationid, locations.name,
                   locations.lat, locations.lng, locations.areaid
            FROM locations_spatial
            INNER JOIN locations ON locations_spatial.locationid = locations.id
            WHERE {$geomFilter} locations.type = 'Postcode'
            AND LOCATE(' ', locations.name) > 0
        ", $params);

        $count = 0;
        $updated = 0;

        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}/" . count($postcodes) . ", updated {$updated}");
            }
        }

        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,
                name,
                ST_Area(location) AS area,
                dist,
                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,
                       name,
                       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
            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
    {
        $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');
        $uniq = '_' . uniqid();

        $pgsql->statement("DROP TABLE IF EXISTS locations_tmp{$uniq}");
        $pgsql->statement("CREATE TABLE locations_tmp{$uniq} (
            id serial PRIMARY KEY,
            locationid bigint UNIQUE NOT NULL,
            name text,
            type location_type,
            area numeric,
            location geometry
        )");
        $pgsql->statement("ALTER TABLE locations_tmp{$uniq} SET UNLOGGED");

        // Fetch non-excluded polygon locations from MySQL.
        $locations = DB::select("
            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 le.locationid IS NULL
            AND ST_Dimension(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) = 2
            AND type != 'Postcode'
        ");

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

            $pgsql->insert(
                "INSERT INTO locations_tmp{$uniq} (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]
            );
        }

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

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

        Log::info('PostcodeRemapService: synced ' . count($locations) . ' 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()	5✔
26	{
27	$this->srid = (int) config('freegle.srid', 3857);	5✔
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	3✔
41	{
42	if (! $this->postgresAvailable()) {	3✔
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();	2✔
50
51	if ($polygon) {	2✔
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);	2✔
NEW 59	} elseif ($locationId) {	×
60	$this->syncSingleLocation($locationId);	×
61	} else {
62	$this->syncAllLocations();	×
63	}
64
65	$geomFilter = '';	2✔
66	$params = [];	2✔
67
68	if ($polygon) {	2✔
69	$geomFilter = "ST_Contains(ST_GeomFromText(?, {$this->srid}), locations_spatial.geometry) AND";	2✔
70	$params[] = $polygon;	2✔
71	}
72
73	// Fetch all full postcodes (contain a space) within the scope.
74	$postcodes = DB::select("	2✔
75	SELECT DISTINCT locations_spatial.locationid, locations.name,
76	locations.lat, locations.lng, locations.areaid
77	FROM locations_spatial
78	INNER JOIN locations ON locations_spatial.locationid = locations.id
79	WHERE {$geomFilter} locations.type = 'Postcode'	2✔
80	AND LOCATE(' ', locations.name) > 0
81	", $params);	2✔
82
83	$count = 0;	2✔
84	$updated = 0;	2✔
85
86	foreach ($postcodes as $pc) {	2✔
87	$newAreaId = $this->findNearestArea($pc->lng, $pc->lat);	2✔
88
89	if ($newAreaId && $newAreaId != $pc->areaid) {	2✔
90	DB::update('UPDATE locations SET areaid = ? WHERE id = ?', [	2✔
91	$newAreaId,	2✔
92	$pc->locationid,	2✔
93	]);	2✔
94	$updated++;	2✔
95	}
96
97	$count++;	2✔
98
99	if ($count % 1000 === 0) {	2✔
100	Log::info("PostcodeRemapService: processed {$count}/" . count($postcodes) . ", updated {$updated}");	×
101	}
102	}
103
104	Log::info("PostcodeRemapService: remapped {$updated}/{$count} postcodes");	2✔
105
106	return $updated;	2✔
107	}
108
109	/**
110	* Find the nearest area location for a given point using PostGIS KNN.
111	*
112	* Uses expanding buffer intersection levels (matching V1 algorithm) to find
113	* the smallest area that contains or is very close to the point.
114	*
115	* @param float $lng Longitude
116	* @param float $lat Latitude
117	* @return int\|null Location ID of the best matching area, or null.
118	*/
119	public function findNearestArea(float $lng, float $lat): ?int	3✔
120	{
121	$result = DB::connection('pgsql')->select("	3✔
122	WITH ourpoint AS (
123	SELECT ST_MakePoint(?, ?) AS p
124	)
125	SELECT
126	locationid,
127	name,
128	ST_Area(location) AS area,
129	dist,
130	CASE
131	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.00015625), ?)) THEN 1
132	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.0003125), ?)) THEN 2
133	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.000625), ?)) THEN 3
134	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.00125), ?)) THEN 4
135	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.0025), ?)) THEN 5
136	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.005), ?)) THEN 6
137	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.01), ?)) THEN 7
138	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.02), ?)) THEN 8
139	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.04), ?)) THEN 9
140	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.08), ?)) THEN 10
141	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.16), ?)) THEN 11
142	WHEN ST_Intersects(location, ST_SetSRID(ST_Buffer((SELECT p FROM ourpoint), 0.32), ?)) THEN 12
143	END AS intersects
144	FROM (
145	SELECT locationid,
146	name,
147	location,
148	location <-> ST_SetSRID((SELECT p FROM ourpoint), ?) AS dist
149	FROM locations
150	WHERE ST_Area(location) BETWEEN 0.00001 AND 0.15
151	ORDER BY location <-> ST_SetSRID((SELECT p FROM ourpoint), ?)
152	LIMIT 10
153	) q
154	ORDER BY intersects ASC, area ASC
155	LIMIT 1
156	", [	3✔
157	$lng, $lat,	3✔
158	// 12 SRID params for the buffer intersections
159	$this->srid, $this->srid, $this->srid, $this->srid,	3✔
160	$this->srid, $this->srid, $this->srid, $this->srid,	3✔
161	$this->srid, $this->srid, $this->srid, $this->srid,	3✔
162	// 2 SRID params for the KNN subquery
163	$this->srid, $this->srid,	3✔
164	]);	3✔
165
166	if (count($result) > 0) {	3✔
167	return (int) $result[0]->locationid;	3✔
168	}
169
170	return NULL;	×
171	}
172
173	/**
174	* Ensure the PostgreSQL locations table and indexes exist.
175	*/
176	public function ensurePostgresSchema(): void	2✔
177	{
178	$pgsql = DB::connection('pgsql');	2✔
179
180	$pgsql->statement('CREATE EXTENSION IF NOT EXISTS postgis');	2✔
181	$pgsql->statement('CREATE EXTENSION IF NOT EXISTS btree_gist');	2✔
182
183	// Create the location_type enum if it doesn't exist.
184	$typeExists = $pgsql->selectOne("SELECT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'location_type')");	2✔
185	if (! $typeExists->exists) {	2✔
186	$pgsql->statement("CREATE TYPE location_type AS ENUM('Road','Polygon','Line','Point','Postcode')");	×
187	}
188
189	// Create the locations table if it doesn't exist.
190	$tableExists = $pgsql->selectOne("SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'locations')");	2✔
191	if (! $tableExists->exists) {	2✔
192	$pgsql->statement('CREATE TABLE locations (	×
193	id serial PRIMARY KEY,
194	locationid bigint UNIQUE NOT NULL,
195	name text,
196	type location_type,
197	area numeric,
198	location geometry
199	)');	×
200	$pgsql->statement('CREATE INDEX idx_locations_location ON locations USING gist (location)');	×
201	}
202	}
203
204	/**
205	* Sync a single location from MySQL to PostgreSQL (upsert).
206	*
207	* Matches V1's per-location sync in setGeometry() — fast for single updates.
208	*/
209	private function syncSingleLocation(int $locationId): void	×
210	{
211	$loc = DB::selectOne("	×
212	SELECT locations.id, name, type,
213	ST_AsText(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) AS geom
214	FROM locations
215	LEFT JOIN locations_excluded le ON locations.id = le.locationid
216	WHERE locations.id = ?
217	AND le.locationid IS NULL
218	AND ST_Dimension(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) = 2
219	AND type != 'Postcode'
220	", [$locationId]);	×
221
222	if (! $loc \|\| ! $loc->geom) {	×
223	return;	×
224	}
225
226	DB::connection('pgsql')->statement(	×
227	"INSERT INTO locations (locationid, name, type, area, location)	×
228	VALUES (?, ?, ?, ST_Area(ST_GeomFromText(?, ?)), ST_GeomFromText(?, ?))
229	ON CONFLICT (locationid) DO UPDATE SET
230	name = EXCLUDED.name,
231	type = EXCLUDED.type,
232	area = EXCLUDED.area,
233	location = EXCLUDED.location",	×
234	[$loc->id, $loc->name, $loc->type, $loc->geom, $this->srid, $loc->geom, $this->srid]	×
235	);	×
236	}
237
238	/**
239	* Sync all non-postcode polygon locations within a WKT polygon to PostgreSQL.
240	*
241	* When a location is created or its geometry is changed, the remap task carries
242	* the polygon scope. We need to sync all locations intersecting that scope —
243	* not just the edited location — because newly created or recently modified
244	* locations in the area may not yet be in PostgreSQL.
245	*/
246	private function syncLocationsInPolygon(string $polygon): void	2✔
247	{
248	$locations = DB::select("	2✔
249	SELECT locations.id, locations.name, locations.type,
250	ST_AsText(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE ls.geometry END) AS geom
251	FROM locations_spatial ls
252	INNER JOIN locations ON ls.locationid = locations.id
253	LEFT JOIN locations_excluded le ON locations.id = le.locationid
254	WHERE le.locationid IS NULL
255	AND ST_Intersects(ls.geometry, ST_GeomFromText(?, {$this->srid}))	2✔
256	AND ST_Dimension(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE ls.geometry END) = 2
257	AND locations.type != 'Postcode'
258	", [$polygon]);	2✔
259
260	$synced = 0;	2✔
261
262	foreach ($locations as $loc) {	2✔
263	if (! $loc->geom) {	2✔
NEW 264	continue;	×
265	}
266
267	DB::connection('pgsql')->statement(	2✔
268	"INSERT INTO locations (locationid, name, type, area, location)	2✔
269	VALUES (?, ?, ?, ST_Area(ST_GeomFromText(?, ?)), ST_GeomFromText(?, ?))
270	ON CONFLICT (locationid) DO UPDATE SET
271	name = EXCLUDED.name,
272	type = EXCLUDED.type,
273	area = EXCLUDED.area,
274	location = EXCLUDED.location",	2✔
275	[$loc->id, $loc->name, $loc->type, $loc->geom, $this->srid, $loc->geom, $this->srid]	2✔
276	);	2✔
277
278	$synced++;	2✔
279	}
280
281	Log::info("PostcodeRemapService: synced {$synced} locations in polygon scope to PostgreSQL");	2✔
282	}
283
284	/**
285	* Full sync of all non-postcode polygon locations from MySQL to PostgreSQL.
286	*
287	* Uses a temp table + atomic swap matching V1's copyLocationsToPostgresql().
288	* Used when no specific location ID is provided (full remap).
289	*/
290	private function syncAllLocations(): void	×
291	{
292	$pgsql = DB::connection('pgsql');	×
293	$uniq = '_' . uniqid();	×
294
295	$pgsql->statement("DROP TABLE IF EXISTS locations_tmp{$uniq}");	×
296	$pgsql->statement("CREATE TABLE locations_tmp{$uniq} (	×
297	id serial PRIMARY KEY,
298	locationid bigint UNIQUE NOT NULL,
299	name text,
300	type location_type,
301	area numeric,
302	location geometry
303	)");	×
304	$pgsql->statement("ALTER TABLE locations_tmp{$uniq} SET UNLOGGED");	×
305
306	// Fetch non-excluded polygon locations from MySQL.
307	$locations = DB::select("	×
308	SELECT locations.id, name, type,
309	ST_AsText(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) AS geom
310	FROM locations
311	LEFT JOIN locations_excluded le ON locations.id = le.locationid
312	WHERE le.locationid IS NULL
313	AND ST_Dimension(CASE WHEN ourgeometry IS NOT NULL THEN ourgeometry ELSE geometry END) = 2
314	AND type != 'Postcode'
315	");	×
316
317	foreach ($locations as $loc) {	×
318	if (! $loc->geom) {	×
319	continue;	×
320	}
321
322	$pgsql->insert(	×
323	"INSERT INTO locations_tmp{$uniq} (locationid, name, type, area, location)	×
324	VALUES (?, ?, ?, ST_Area(ST_GeomFromText(?, ?)), ST_GeomFromText(?, ?))",	×
325	[$loc->id, $loc->name, $loc->type, $loc->geom, $this->srid, $loc->geom, $this->srid]	×
326	);	×
327	}
328
329	// Build index on temp table before swap.
330	$pgsql->statement("CREATE INDEX idx_loc_tmp{$uniq} ON locations_tmp{$uniq} USING gist (location)");	×
331
332	// Atomic swap: rename current to old, temp to current, drop old.
333	$pgsql->statement("ALTER TABLE IF EXISTS locations RENAME TO locations_old{$uniq}");	×
334	$pgsql->statement("ALTER TABLE locations_tmp{$uniq} RENAME TO locations");	×
335	$pgsql->statement("ALTER INDEX idx_loc_tmp{$uniq} RENAME TO idx_locations_location");	×
336	$pgsql->statement("DROP TABLE IF EXISTS locations_old{$uniq}");	×
337
338	Log::info('PostcodeRemapService: synced ' . count($locations) . ' locations to PostgreSQL');	×
339	}
340
341	/**
342	* Check if the PostgreSQL connection is available.
343	*/
344	public function postgresAvailable(): bool	3✔
345	{
346	try {
347	DB::connection('pgsql')->getPdo();	3✔
348
349	return TRUE;	2✔
350	} catch (\Throwable $e) {	1✔
351	return FALSE;	1✔
352	}
353	}
354	}

Freegle / Iznik / 3826

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