diff mbox series

[05/10] importer: Use geofeeds for country assignment

Message ID 20220927164847.3409646-5-michael.tremer@ipfire.org
State Accepted
Commit e87dbd4483cf26f06e757247ccf11909acad5038
Headers
Series [01/10] importer: Store geofeed URLs from RIR data |

Commit Message

Michael Tremer Sept. 27, 2022, 4:48 p.m. UTC 
  Signed-off-by: Michael Tremer <michael.tremer@ipfire.org>
---
 src/scripts/location-importer.in | 25 +++++++++++++++++++++++++
 1 file changed, 25 insertions(+)
diff mbox series

Patch

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,