28731

Committed 27 Jul 2026 05:40PM UTC coverage: 72.808% (-0.2%) from 73.051%

Build # 28731

Build Type

push

circleci

Committed by

web-flow

Commit Message

Merge pull request #1184 from Freegle/fix/new-area-remap-seeds-local-spatial

fix(digest): seed the batch-local spatial index before remapping a new area

Coverage Stats

13464 of 17673 branches covered (76.18%)

Branch coverage included in aggregate %.

25 of 30 new or added lines in 2 files covered. (83.33%)

179 existing lines in 7 files now uncovered.

141124 of 194650 relevant lines covered (72.5%)

38.12 hits per line

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

83.78

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

<?php

namespace App\Services;

use Illuminate\Http\Client\ConnectionException;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Http;
use Illuminate\Support\Facades\Log;

/**
 * Remaps postcodes to their nearest enclosing area using the spatial server.
 *
 * 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.
 *
 * Queries the iznik-spatial-go service (HTTP) instead of PostgreSQL/PostGIS.
 * The spatial server maintains its own SQLite R-tree index built from MySQL
 * and uses the same 12-level progressive buffer expansion algorithm as V1.
 */
class PostcodeRemapService
{
    private string $spatialServerUrl;

    public function __construct(private SpatialAdminService $spatialAdmin)
    {
        $this->spatialServerUrl = config('freegle.spatial_server_url', 'http://localhost:8194');
    }

    /**
     * Remap postcodes within a given WKT polygon to their nearest area.
     *
     * @param int|null $locationId The area whose geometry changed. When given, the
     *   area is seeded into THIS process's spatial index before the KNN lookups
     *   below, so a brand-new area is found immediately (see seedArea()).
     * @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
    {
        // In production each container runs its own spatial-knn instance with an
        // independent in-memory index (one per DB server, plus one on the batch
        // container). The write-time upsert in the Go API seeds the API's instance,
        // NOT the one this remap queries, so without seeding here findNearestArea()
        // cannot see a just-drawn area until the 15-minute delta. The area then gets
        // no postcodes and looks unsaved until the nightly full remap (Discourse #9950).
        if ($locationId) {
            $this->seedArea($locationId);
        }

        $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) {
            $srid = (int) config('freegle.srid', 3857);
            if ($locationId) {
                $pcQuery->where(function ($q) use ($polygon, $locationId, $srid) {
                    $q->whereRaw(
                        "ST_Contains(ST_GeomFromText(?, {$srid}), locations_spatial.geometry)",
                        [$polygon],
                    )->orWhere('locations.areaid', $locationId);
                });
            } else {
                $pcQuery->whereRaw(
                    "ST_Contains(ST_GeomFromText(?, {$srid}), locations_spatial.geometry)",
                    [$polygon],
                );
            }
        }

        $count   = 0;
        $updated = 0;

        $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;
    }

    /**
     * Seed one area's current geometry into the spatial index this process
     * queries, so findNearestArea() can return it straight away instead of
     * waiting for the next delta sync. Non-fatal: a failure just falls back to
     * the delta and the nightly full remap.
     */
    private function seedArea(int $locationId): void
    {
        $row = DB::table('locations')
            ->where('id', $locationId)
            ->selectRaw('name, `type`, ST_AsText(COALESCE(ourgeometry, geometry)) AS wkt')
            ->first();

        if (!$row || empty($row->wkt)) {
            return;
        }

        $this->spatialAdmin->upsertItems('locations', [[
            'id'    => $locationId,
            'wkt'   => $row->wkt,
            'extra' => ['name' => $row->name, 'type' => $row->type],
        ]]);
    }

    /**
     * Find the nearest area for a given point via the spatial server.
     *
     * @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
    {
        // type=Area restricts the expanding-buffer search to non-postcode
        // locations, matching the V1 PostGIS candidate pool (which synced
        // everything except postcodes).
        try {
            $response = Http::get("{$this->spatialServerUrl}/v1/locations/knn", [
                'lng'   => $lng,
                'lat'   => $lat,
                'limit' => 1,
                'type'  => 'Area',
            ]);
        } catch (ConnectionException $e) {
            // A transient blip on the spatial server (e.g. mid-rebuild) must not
            // abort the whole remap. Treat it like a failed lookup: skip this
            // postcode (return null => no update) so the run continues.
            Log::warning("PostcodeRemapService: spatial server unreachable for lng={$lng} lat={$lat}: {$e->getMessage()}");
            return null;
        }

        if ($response->successful()) {
            $results = $response->json('results', []);
            return !empty($results) ? (int) $results[0]['id'] : null;
        }

        Log::warning("PostcodeRemapService: spatial server returned {$response->status()} for lng={$lng} lat={$lat}");
        return null;
    }
}

1	<?php
2
3	namespace App\Services;
4
5	use Illuminate\Http\Client\ConnectionException;
6	use Illuminate\Support\Facades\DB;
7	use Illuminate\Support\Facades\Http;
8	use Illuminate\Support\Facades\Log;
9
10	/**
11	* Remaps postcodes to their nearest enclosing area using the spatial server.
12	*
13	* When a location area's geometry is created or modified, postcodes within
14	* that geometry need to be reassigned to the correct (smallest, nearest) area.
15	* This mirrors the V1 PHP Location::remapPostcodes() logic.
16	*
17	* Queries the iznik-spatial-go service (HTTP) instead of PostgreSQL/PostGIS.
18	* The spatial server maintains its own SQLite R-tree index built from MySQL
19	* and uses the same 12-level progressive buffer expansion algorithm as V1.
20	*/
21	class PostcodeRemapService
22	{
23	private string $spatialServerUrl;
24
25	public function __construct(private SpatialAdminService $spatialAdmin)	9✔
26	{
27	$this->spatialServerUrl = config('freegle.spatial_server_url', 'http://localhost:8194');	9✔
28	}
29
30	/**
31	* Remap postcodes within a given WKT polygon to their nearest area.
32	*
33	* @param int\|null $locationId The area whose geometry changed. When given, the
34	* area is seeded into THIS process's spatial index before the KNN lookups
35	* below, so a brand-new area is found immediately (see seedArea()).
36	* @param string\|null $polygon WKT polygon to scope the remap. NULL = remap all.
37	* @return int Number of postcodes remapped.
38	*/
39	public function remapPostcodes(?int $locationId = NULL, ?string $polygon = NULL): int	4✔
40	{
41	// In production each container runs its own spatial-knn instance with an
42	// independent in-memory index (one per DB server, plus one on the batch
43	// container). The write-time upsert in the Go API seeds the API's instance,
44	// NOT the one this remap queries, so without seeding here findNearestArea()
45	// cannot see a just-drawn area until the 15-minute delta. The area then gets
46	// no postcodes and looks unsaved until the nightly full remap (Discourse #9950).
47	if ($locationId) {	4✔
48	$this->seedArea($locationId);	1✔
49	}
50
51	$pcQuery = DB::table('locations_spatial')	4✔
52	->join('locations', 'locations_spatial.locationid', '=', 'locations.id')	4✔
53	->where('locations.type', 'Postcode')	4✔
54	->whereRaw("LOCATE(' ', locations.name) > 0")	4✔
55	->select(	4✔
56	'locations.id as locations_id',	4✔
57	'locations_spatial.locationid',	4✔
58	'locations.name',	4✔
59	'locations.lat',	4✔
60	'locations.lng',	4✔
61	'locations.areaid',	4✔
62	);	4✔
63
64	if ($polygon) {	4✔
65	$srid = (int) config('freegle.srid', 3857);	3✔
66	if ($locationId) {	3✔
67	$pcQuery->where(function ($q) use ($polygon, $locationId, $srid) {	1✔
68	$q->whereRaw(	1✔
69	"ST_Contains(ST_GeomFromText(?, {$srid}), locations_spatial.geometry)",	1✔
70	[$polygon],	1✔
71	)->orWhere('locations.areaid', $locationId);	1✔
72	});	1✔
73	} else {
74	$pcQuery->whereRaw(	2✔
75	"ST_Contains(ST_GeomFromText(?, {$srid}), locations_spatial.geometry)",	2✔
76	[$polygon],	2✔
77	);	2✔
78	}
79	}
80
81	$count = 0;	4✔
82	$updated = 0;	4✔
83
84	$pcQuery->orderBy('locations.id')->chunkById(1000, function ($postcodes) use (&$count, &$updated) {	4✔
85	foreach ($postcodes as $pc) {	×
86	$newAreaId = $this->findNearestArea($pc->lng, $pc->lat);	×
87
88	if ($newAreaId && $newAreaId != $pc->areaid) {	×
89	DB::update('UPDATE locations SET areaid = ? WHERE id = ?', [	×
90	$newAreaId,	×
91	$pc->locationid,	×
92	]);	×
93	$updated++;	×
94	}
95
96	$count++;	×
97
98	if ($count % 1000 === 0) {	×
99	Log::info("PostcodeRemapService: processed {$count}, updated {$updated}");	×
100	}
101	}
102	}, 'locations.id', 'locations_id');	4✔
103
104	Log::info("PostcodeRemapService: remapped {$updated}/{$count} postcodes");	4✔
105
106	return $updated;	4✔
107	}
108
109	/**
110	* Seed one area's current geometry into the spatial index this process
111	* queries, so findNearestArea() can return it straight away instead of
112	* waiting for the next delta sync. Non-fatal: a failure just falls back to
113	* the delta and the nightly full remap.
114	*/
115	private function seedArea(int $locationId): void	1✔
116	{
117	$row = DB::table('locations')	1✔
118	->where('id', $locationId)	1✔
119	->selectRaw('name, `type`, ST_AsText(COALESCE(ourgeometry, geometry)) AS wkt')	1✔
120	->first();	1✔
121
122	if (!$row \|\| empty($row->wkt)) {	1✔
NEW 123	return;	×
124	}
125
126	$this->spatialAdmin->upsertItems('locations', [[	1✔
127	'id' => $locationId,	1✔
128	'wkt' => $row->wkt,	1✔
129	'extra' => ['name' => $row->name, 'type' => $row->type],	1✔
130	]]);	1✔
131	}
132
133	/**
134	* Find the nearest area for a given point via the spatial server.
135	*
136	* @param float $lng Longitude
137	* @param float $lat Latitude
138	* @return int\|null Location ID of the best matching area, or null.
139	*/
140	public function findNearestArea(float $lng, float $lat): ?int	5✔
141	{
142	// type=Area restricts the expanding-buffer search to non-postcode
143	// locations, matching the V1 PostGIS candidate pool (which synced
144	// everything except postcodes).
145	try {
146	$response = Http::get("{$this->spatialServerUrl}/v1/locations/knn", [	5✔
147	'lng' => $lng,	5✔
148	'lat' => $lat,	5✔
149	'limit' => 1,	5✔
150	'type' => 'Area',	5✔
151	]);	5✔
152	} catch (ConnectionException $e) {	1✔
153	// A transient blip on the spatial server (e.g. mid-rebuild) must not
154	// abort the whole remap. Treat it like a failed lookup: skip this
155	// postcode (return null => no update) so the run continues.
156	Log::warning("PostcodeRemapService: spatial server unreachable for lng={$lng} lat={$lat}: {$e->getMessage()}");	1✔
157	return null;	1✔
158	}
159
160	if ($response->successful()) {	4✔
161	$results = $response->json('results', []);	3✔
162	return !empty($results) ? (int) $results[0]['id'] : null;	3✔
163	}
164
165	Log::warning("PostcodeRemapService: spatial server returned {$response->status()} for lng={$lng} lat={$lat}");	1✔
166	return null;	1✔
167	}
168	}

Freegle / Iznik / 28731

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