From patchwork Tue Sep 27 16:48:42 2022 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Michael Tremer X-Patchwork-Id: 6019 Return-Path: Received: from mail01.ipfire.org (mail01.haj.ipfire.org [172.28.1.202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) client-signature ECDSA (P-384)) (Client CN "mail01.haj.ipfire.org", Issuer "R3" (verified OK)) by web04.haj.ipfire.org (Postfix) with ESMTPS id 4McQZ33sB5z3wdD for ; Tue, 27 Sep 2022 16:48:59 +0000 (UTC) Received: from mail02.haj.ipfire.org (mail02.haj.ipfire.org [172.28.1.201]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) client-signature ECDSA (P-384)) (Client CN "mail02.haj.ipfire.org", Issuer "R3" (verified OK)) by mail01.ipfire.org (Postfix) with ESMTPS id 4McQZ11rQqz2Rc; Tue, 27 Sep 2022 16:48:57 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4McQZ049T2z3032; Tue, 27 Sep 2022 16:48:56 +0000 (UTC) Received: from mail01.ipfire.org (mail01.haj.ipfire.org [172.28.1.202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) client-signature ECDSA (P-384)) (Client CN "mail01.haj.ipfire.org", Issuer "R3" (verified OK)) by mail02.haj.ipfire.org (Postfix) with ESMTPS id 4McQYz1FWgz2xNS for ; Tue, 27 Sep 2022 16:48:55 +0000 (UTC) Received: from [127.0.0.1] (localhost [127.0.0.1]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) server-digest SHA384) (No client certificate requested) by mail01.ipfire.org (Postfix) with ESMTPSA id 4McQYy5MnJz2Qn; Tue, 27 Sep 2022 16:48:54 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1664297334; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=VZVsXz43rmil1d6Ug+KNWKEyJEsZ7L2C/3I3OJ2C1zA=; b=2cjDXbcPj86Zwa0/QfAYyveD4lUw2Fh3txsevVzicrC0RXbSwfW4sjoWLq9Ejt59DtT0eY 7dHDArP6q8SRoADQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1664297334; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=VZVsXz43rmil1d6Ug+KNWKEyJEsZ7L2C/3I3OJ2C1zA=; b=B7k7TEp2Cn6/j998S+Rp1aPAKbYk5jHCzT45Qp6duehAomfIk40i8nCuJqgDSdGEzIqOZM 4XQkfgjUhvlIXHq2neyDHkGrEY13l5nfGDyF0V4AwypCgwEuvnkHAmX5qJ0wHdPU85awHb uKh6uo6kdHiOS0tRqNtyYf4r/gvQNg2Fph9vho1EWnYRWt+TSBRKrSKsQkiWraB96SW+w6 /vYWVmraN9oeDL8DlRMiYbZyo5DV15S9IS6L0NqygAYBVwSpF6deCi7lgWckasJ7LBc5jN JYmQTej4G/nvC6b5QBrybONcdhy8mDWsddXKUsDhOTWzqYAiEMS7LsPmhgcE4w== 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> References: <20220927164847.3409646-1-michael.tremer@ipfire.org> MIME-Version: 1.0 X-BeenThere: location@lists.ipfire.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Cc: Michael Tremer Errors-To: location-bounces@lists.ipfire.org Sender: "Location" 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 ), ordered_networks AS ( @@ -333,6 +335,29 @@ class CLI(object): SELECT country FROM autnum_overrides overrides WHERE networks.autnum = overrides.number ), + ( + SELECT + geofeed_networks.country AS country + FROM + network_geofeeds + + -- Join the data from the geofeeds + LEFT JOIN + geofeeds ON network_geofeeds.url = geofeeds.url + LEFT JOIN + geofeed_networks ON geofeeds.id = geofeed_networks.geofeed_id + + -- Check whether we have a geofeed for this network + WHERE + networks.network <<= network_geofeeds.network + AND + networks.network <<= geofeed_networks.network + + -- Filter for the best result + ORDER BY + masklen(geofeed_networks.network) DESC + LIMIT 1 + ), networks.country ) AS country,