• Home
  • Features
  • Pricing
  • Docs
  • Announcements
  • Sign In

Freegle / Iznik / 3826

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

push

circleci

invalid-email-address
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>

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
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
}
STATUS · Troubleshooting · Open an Issue · Sales · Support · CAREERS · ENTERPRISE · START FREE · SCHEDULE DEMO
ANNOUNCEMENTS · TWITTER · TOS & SLA · Supported CI Services · What's a CI service? · Automated Testing

© 2026 Coveralls, Inc