27245

Committed 15 Jul 2026 12:43PM UTC coverage: 72.324% (+0.002%) from 72.322%

Build # 27245

Build Type

push

circleci

Committed by

edwh

Commit Message

fix(ripple): name the "quicker to get to" note's points with in-group postcodes

The reach moderator note ("This post is quicker to get to for Freeglers in P
than P is to Q") named its P/Q points with Location::describeNearest, which
returns the globally-nearest postcode with no group constraint. For a point near
the group's edge that landed on a NEIGHBOURING group's postcode - a Hackney mod
saw Islington/Newham postcodes "justifying" the ripple (Discourse 9808/583).

Add Location::describeNearestInGroup(lat, lng, groupid): take the nearest few
postcodes and keep the nearest one that is inside the group's polyindex polygon
(matching groupsNear's containment test), falling back to the unconstrained
nearest if none are in-group or the group has no usable polygon. Use it for the
P/Q points in ProximityNotesCommand.

Coverage Stats

13055 of 17207 branches covered (75.87%)

Branch coverage included in aggregate %.

23 of 25 new or added lines in 2 files covered. (92.0%)

2 existing lines in 2 files now uncovered.

136535 of 189627 relevant lines covered (72.0%)

37.84 hits per line

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

80.95

/iznik-batch/app/Models/Location.php

<?php

namespace App\Models;

use App\Services\SpatialQueryService;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use OwenIt\Auditing\Contracts\Auditable;

class Location extends Model implements Auditable
{
    use \OwenIt\Auditing\Auditable;

    // Fields exposed by getPublic() - mirrors the legacy V1 PHP Location::$publicatts.
    private const PUBLIC_ATTS = ['id', 'osm_id', 'name', 'type', 'popularity', 'postcodeid', 'areaid', 'lat', 'lng', 'maxdimension'];

    protected $table = 'locations';
    protected $guarded = ['id'];
    public $timestamps = false;

    protected $casts = [
        'lat' => 'decimal:6',
        'lng' => 'decimal:6',
        'osm_place' => 'boolean',
        'osm_amenity' => 'boolean',
        'osm_shop' => 'boolean',
    ];

    /**
     * Nearest full postcode to a point, via the spatial server's KNN index
     * (the "postcodes" point dataset). Returns null if the spatial server has
     * nothing nearby or is unreachable.
     */
    public static function closestPostcode(float $lat, float $lng): ?object
    {
        $ids = (new SpatialQueryService())->nearestIds('postcodes', $lat, $lng, 1);
        if (empty($ids)) {
            return null;
        }

        return DB::table('locations')->where('id', $ids[0])
            ->select('id', 'name', 'lat', 'lng')
            ->first();
    }

    /**
     * "AB10 1XG (Gilcomston)" or just "AB10 1XG" if no area — used for the ripple
     * proximity ("quicker to get to") moderator note. Does not change
     * closestPostcode()'s existing return shape; this is a separate helper with its
     * own (postcode, area) join.
     */
    public static function describeNearest(float $lat, float $lng): ?string
    {
        $ids = (new SpatialQueryService())->nearestIds('postcodes', $lat, $lng, 1);
        if (empty($ids)) {
            return null;
        }

        $loc = DB::table('locations as p')
            ->leftJoin('locations as a', 'a.id', '=', 'p.areaid')
            ->where('p.id', $ids[0])
            ->select('p.name as postcode', 'a.name as area')
            ->first();
        if (!$loc) {
            return null;
        }

        return $loc->area ? "{$loc->postcode} ({$loc->area})" : $loc->postcode;
    }

    /**
     * Like describeNearest(), but names the point with a postcode that actually
     * lies inside the given group's area polygon (groups.polyindex).
     *
     * The ripple "quicker to get to" note picks in-group points near the group's
     * edge; naming them with the single globally-nearest postcode landed on a
     * NEIGHBOURING group's postcode, so a Hackney mod saw Islington/Newham
     * postcodes "justifying" the ripple (Discourse 9808/583). Take the nearest few
     * postcodes and keep the nearest one that is inside the group; if none are (or
     * the group has no usable polygon) fall back to the unconstrained nearest so we
     * still produce a note.
     */
    public static function describeNearestInGroup(float $lat, float $lng, int $groupid): ?string
    {
        $ids = (new SpatialQueryService())->nearestIds('postcodes', $lat, $lng, 10);
        if (empty($ids)) {
            return null;
        }

        $srid  = config('freegle.srid', 3857);
        $order = implode(',', array_map('intval', $ids));

        // Point built from the postcode's own lat/lng as POINT(lng lat) SRID 3857,
        // matching groupsNear()'s containment test (so no dependence on the SRID
        // stored on locations.geometry). FIELD() preserves the KNN nearest-first order.
        $loc = DB::selectOne(
            "SELECT p.name AS postcode, a.name AS area
             FROM locations p
             LEFT JOIN locations a ON a.id = p.areaid
             WHERE p.id IN ($order)
               AND p.lat IS NOT NULL AND p.lng IS NOT NULL
               AND ST_Contains(
                     (SELECT polyindex FROM `groups` WHERE id = ? AND polyindex IS NOT NULL),
                     ST_GeomFromText(CONCAT('POINT(', p.lng, ' ', p.lat, ')'), ?)
                   )
             ORDER BY FIELD(p.id, $order)
             LIMIT 1",
            [$groupid, $srid]
        );

        if ($loc) {
            return $loc->area ? "{$loc->postcode} ({$loc->area})" : $loc->postcode;
        }

        // No candidate inside the group (or no usable polygon) — name the nearest
        // overall so we still produce a note rather than none.
        $fallback = DB::table('locations as p')
            ->leftJoin('locations as a', 'a.id', '=', 'p.areaid')
            ->where('p.id', $ids[0])
            ->select('p.name as postcode', 'a.name as area')
            ->first();
        if (!$fallback) {
            return null;
        }

        return $fallback->area ? "{$fallback->postcode} ({$fallback->area})" : $fallback->postcode;
    }

    public static function findByName(string $name): ?int
    {
        return static::getByName($name)?->id;
    }

    public static function getByName(string $name): ?object
    {
        $canon = strtolower(preg_replace("/[^A-Za-z0-9]/", '', $name));
        return DB::table('locations')->where('canon', 'LIKE', $canon)->first();
    }

    public static function groupsNear(float $lat, float $lng, int $radiusMiles = 50, int $limit = 10): array
    {
        $srid     = config('freegle.srid', 3857);
        $pointWkt = "POINT($lng $lat)";

        // Polygon-containment check: if the point lies inside one or more group
        // polyindex polygons, those groups are authoritative and beat the centroid-
        // distance heuristic (V1 parity; fixes Discourse #9763 where a group with
        // a close centroid but non-containing polyindex shadowed the correct group).
        $containing = DB::select(
            "SELECT id
             FROM `groups`
             WHERE publish = 1 AND listable = 1
               AND ST_Contains(polyindex, ST_GeomFromText(?, ?))
             ORDER BY haversine(lat, lng, ?, ?) ASC
             LIMIT ?",
            [$pointWkt, $srid, $lat, $lng, $limit]
        );

        if (!empty($containing)) {
            return array_column($containing, 'id');
        }

        // No group polygon contains the point; fall back to centroid distance.
        $rows = DB::select(
            "SELECT id
             FROM `groups`
             WHERE publish = 1 AND listable = 1
               AND haversine(lat, lng, ?, ?) < ?
             ORDER BY haversine(lat, lng, ?, ?) ASC
             LIMIT ?",
            [$lat, $lng, $radiusMiles, $lat, $lng, $limit]
        );

        return array_column($rows, 'id');
    }
}

1	<?php
2
3	namespace App\Models;
4
5	use App\Services\SpatialQueryService;
6	use Illuminate\Database\Eloquent\Model;
7	use Illuminate\Support\Facades\DB;
8	use OwenIt\Auditing\Contracts\Auditable;
9
10	class Location extends Model implements Auditable
11	{
12	use \OwenIt\Auditing\Auditable;
13
14	// Fields exposed by getPublic() - mirrors the legacy V1 PHP Location::$publicatts.
15	private const PUBLIC_ATTS = ['id', 'osm_id', 'name', 'type', 'popularity', 'postcodeid', 'areaid', 'lat', 'lng', 'maxdimension'];
16
17	protected $table = 'locations';
18	protected $guarded = ['id'];
19	public $timestamps = false;
20
21	protected $casts = [
22	'lat' => 'decimal:6',
23	'lng' => 'decimal:6',
24	'osm_place' => 'boolean',
25	'osm_amenity' => 'boolean',
26	'osm_shop' => 'boolean',
27	];
28
29	/**
30	* Nearest full postcode to a point, via the spatial server's KNN index
31	* (the "postcodes" point dataset). Returns null if the spatial server has
32	* nothing nearby or is unreachable.
33	*/
34	public static function closestPostcode(float $lat, float $lng): ?object	6✔
35	{
36	$ids = (new SpatialQueryService())->nearestIds('postcodes', $lat, $lng, 1);	6✔
37	if (empty($ids)) {	6✔
38	return null;	2✔
39	}
40
41	return DB::table('locations')->where('id', $ids[0])	4✔
42	->select('id', 'name', 'lat', 'lng')	4✔
43	->first();	4✔
44	}
45
46	/**
47	* "AB10 1XG (Gilcomston)" or just "AB10 1XG" if no area — used for the ripple
48	* proximity ("quicker to get to") moderator note. Does not change
49	* closestPostcode()'s existing return shape; this is a separate helper with its
50	* own (postcode, area) join.
51	*/
52	public static function describeNearest(float $lat, float $lng): ?string	1✔
53	{
54	$ids = (new SpatialQueryService())->nearestIds('postcodes', $lat, $lng, 1);	1✔
55	if (empty($ids)) {	1✔
56	return null;	×
57	}
58
59	$loc = DB::table('locations as p')	1✔
60	->leftJoin('locations as a', 'a.id', '=', 'p.areaid')	1✔
61	->where('p.id', $ids[0])	1✔
62	->select('p.name as postcode', 'a.name as area')	1✔
63	->first();	1✔
64	if (!$loc) {	1✔
65	return null;	×
66	}
67
68	return $loc->area ? "{$loc->postcode} ({$loc->area})" : $loc->postcode;	1✔
69	}
70
71	/**
72	* Like describeNearest(), but names the point with a postcode that actually
73	* lies inside the given group's area polygon (groups.polyindex).
74	*
75	* The ripple "quicker to get to" note picks in-group points near the group's
76	* edge; naming them with the single globally-nearest postcode landed on a
77	* NEIGHBOURING group's postcode, so a Hackney mod saw Islington/Newham
78	* postcodes "justifying" the ripple (Discourse 9808/583). Take the nearest few
79	* postcodes and keep the nearest one that is inside the group; if none are (or
80	* the group has no usable polygon) fall back to the unconstrained nearest so we
81	* still produce a note.
82	*/
83	public static function describeNearestInGroup(float $lat, float $lng, int $groupid): ?string	2✔
84	{
85	$ids = (new SpatialQueryService())->nearestIds('postcodes', $lat, $lng, 10);	2✔
86	if (empty($ids)) {	2✔
NEW 87	return null;	×
88	}
89
90	$srid = config('freegle.srid', 3857);	2✔
91	$order = implode(',', array_map('intval', $ids));	2✔
92
93	// Point built from the postcode's own lat/lng as POINT(lng lat) SRID 3857,
94	// matching groupsNear()'s containment test (so no dependence on the SRID
95	// stored on locations.geometry). FIELD() preserves the KNN nearest-first order.
96	$loc = DB::selectOne(	2✔
97	"SELECT p.name AS postcode, a.name AS area	2✔
98	FROM locations p
99	LEFT JOIN locations a ON a.id = p.areaid
100	WHERE p.id IN ($order)	2✔
101	AND p.lat IS NOT NULL AND p.lng IS NOT NULL
102	AND ST_Contains(
103	(SELECT polyindex FROM `groups` WHERE id = ? AND polyindex IS NOT NULL),
104	ST_GeomFromText(CONCAT('POINT(', p.lng, ' ', p.lat, ')'), ?)
105	)
106	ORDER BY FIELD(p.id, $order)	2✔
107	LIMIT 1",	2✔
108	[$groupid, $srid]	2✔
109	);	2✔
110
111	if ($loc) {	2✔
112	return $loc->area ? "{$loc->postcode} ({$loc->area})" : $loc->postcode;	1✔
113	}
114
115	// No candidate inside the group (or no usable polygon) — name the nearest
116	// overall so we still produce a note rather than none.
117	$fallback = DB::table('locations as p')	1✔
118	->leftJoin('locations as a', 'a.id', '=', 'p.areaid')	1✔
119	->where('p.id', $ids[0])	1✔
120	->select('p.name as postcode', 'a.name as area')	1✔
121	->first();	1✔
122	if (!$fallback) {	1✔
NEW 123	return null;	×
124	}
125
126	return $fallback->area ? "{$fallback->postcode} ({$fallback->area})" : $fallback->postcode;	1✔
127	}
128
UNCOV 129	public static function findByName(string $name): ?int	×
130	{
131	return static::getByName($name)?->id;	×
132	}
133
134	public static function getByName(string $name): ?object	14✔
135	{
136	$canon = strtolower(preg_replace("/[^A-Za-z0-9]/", '', $name));	14✔
137	return DB::table('locations')->where('canon', 'LIKE', $canon)->first();	14✔
138	}
139
140	public static function groupsNear(float $lat, float $lng, int $radiusMiles = 50, int $limit = 10): array	21✔
141	{
142	$srid = config('freegle.srid', 3857);	21✔
143	$pointWkt = "POINT($lng $lat)";	21✔
144
145	// Polygon-containment check: if the point lies inside one or more group
146	// polyindex polygons, those groups are authoritative and beat the centroid-
147	// distance heuristic (V1 parity; fixes Discourse #9763 where a group with
148	// a close centroid but non-containing polyindex shadowed the correct group).
149	$containing = DB::select(	21✔
150	"SELECT id	21✔
151	FROM `groups`
152	WHERE publish = 1 AND listable = 1
153	AND ST_Contains(polyindex, ST_GeomFromText(?, ?))
154	ORDER BY haversine(lat, lng, ?, ?) ASC
155	LIMIT ?",	21✔
156	[$pointWkt, $srid, $lat, $lng, $limit]	21✔
157	);	21✔
158
159	if (!empty($containing)) {	21✔
160	return array_column($containing, 'id');	21✔
161	}
162
163	// No group polygon contains the point; fall back to centroid distance.
164	$rows = DB::select(	×
165	"SELECT id	×
166	FROM `groups`
167	WHERE publish = 1 AND listable = 1
168	AND haversine(lat, lng, ?, ?) < ?
169	ORDER BY haversine(lat, lng, ?, ?) ASC
170	LIMIT ?",	×
171	[$lat, $lng, $radiusMiles, $lat, $lng, $limit]	×
172	);	×
173
174	return array_column($rows, 'id');	×
175	}
176	}

Freegle / Iznik / 27245

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