From mboxrd@z Thu Jan 1 00:00:00 1970 From: Michael Tremer To: location@lists.ipfire.org Subject: [PATCH 05/10] importer: Use geofeeds for country assignment Date: Tue, 27 Sep 2022 16:48:42 +0000 Message-ID: <20220927164847.3409646-5-michael.tremer@ipfire.org> In-Reply-To: <20220927164847.3409646-1-michael.tremer@ipfire.org> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============6652812360308338021==" List-Id: --===============6652812360308338021== Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Signed-off-by: Michael Tremer --- src/scripts/location-importer.in | 25 +++++++++++++++++++++++++ 1 file changed, 25 insertions(+) diff --git a/src/scripts/location-importer.in b/src/scripts/location-importer= .in index 12035f1..f8d2dc8 100644 --- a/src/scripts/location-importer.in +++ b/src/scripts/location-importer.in @@ -291,6 +291,8 @@ class CLI(object): SELECT network FROM networks UNION SELECT network FROM network_overrides + UNION + SELECT network FROM geofeed_networks ), =20 ordered_networks AS ( @@ -333,6 +335,29 @@ class CLI(object): SELECT country FROM autnum_overrides overrides WHERE networks.autnum =3D overrides.number ), + ( + SELECT + geofeed_networks.country AS country + FROM + network_geofeeds + + -- Join the data from the geofeeds + LEFT JOIN + geofeeds ON network_geofeeds.url =3D geofeeds.url + LEFT JOIN + geofeed_networks ON geofeeds.id =3D geofeed_networks.geofeed_id + + -- Check whether we have a geofeed for this network + WHERE + networks.network <<=3D network_geofeeds.network + AND + networks.network <<=3D geofeed_networks.network + + -- Filter for the best result + ORDER BY + masklen(geofeed_networks.network) DESC + LIMIT 1 + ), networks.country ) AS country, =20 --=20 2.30.2 --===============6652812360308338021==--