Выбор из Maxmind дБ

Question

У меня есть база данных maxmind с 2 таблицами, geoIP и geoLOC

тогда у меня есть список из примерно 10000 городов / населенных пунктов, и я хочу, чтобы лат и lng каждого города отображались на карте

Я бы не использовал 10000 петель, чтобы получить детали: я думал, что лучше использовать IN утверждение T-SQL и поставить все названия городов, но проблема в том, что почти во всех крупных городах есть более 1 ссылки, и поэтому я получаю некоторые дублированные результаты

Я пытался использовать разные:

SELECT l.*
FROM geoloc l 
JOIN geoip i
ON l.geoname_Id = (SELECT TOP 1 i.geoname_id
                   WHERE l.locale_code='en'
                       AND country_iso='US'
                       AND l.city IN ('seattle','boston','detroit'))

но я получаю 14284 результатов вместо 3, в то время как

Какой способ достижения такого результата, и может ли это решение быть более эффективным или может быть лучше использовать для следующего цикла?

Таблицы приведенных здесь определений:

CREATE TABLE dbo.GeoLoc (
geoname_Id bigint NULL,
locale_code nvarchar(5) COLLATE Latin1_General_CI_AS NULL,
continent_code nvarchar(2) COLLATE Latin1_General_CI_AS NULL,
continent_name nvarchar(50) COLLATE Latin1_General_CI_AS NULL,
country_iso nvarchar(2) COLLATE Latin1_General_CI_AS NULL,
country_name nvarchar(100) COLLATE Latin1_General_CI_AS NULL,
sub1_iso nvarchar(100) COLLATE Latin1_General_CI_AS NULL,
sub1_name nvarchar(100) COLLATE Latin1_General_CI_AS NULL,
sub2_iso nvarchar(130) COLLATE Latin1_General_CI_AS NULL,
sub2_name nvarchar(150) COLLATE Latin1_General_CI_AS NULL,
city nvarchar(255) COLLATE Latin1_General_CI_AS NULL,
metro_code nvarchar(100) COLLATE Latin1_General_CI_AS NULL,
time_zone nvarchar(150) COLLATE Latin1_General_CI_AS NULL
)

а также

CREATE TABLE dbo.GeoIP (
sIP bigint NULL,
eIP bigint NULL,
startIp nvarchar(20) COLLATE Latin1_General_CI_AS NULL,
geoname_id bigint NULL,
rc_geonameid nvarchar(30) COLLATE Latin1_General_CI_AS NULL,
rcg nvarchar(20) COLLATE Latin1_General_CI_AS NULL,
isProxy bit NULL,
isSat bit NULL,
postalcode nvarchar(20) COLLATE Latin1_General_CI_AS NULL,
lat nvarchar(20) COLLATE Latin1_General_CI_AS NULL,
lng nvarchar(20) COLLATE Latin1_General_CI_AS NULL
)
INSERT INTO @GeoLoc(geoname_Id,locale_code,country_iso,sub1_iso,city)
VALUES 
(1,'en','US','WA','seattle'),
(2,'en','US','MA','boston'),
(3,'en','US','MI','detroit'),
(4,'en','US','VA','boston'),
(5,'en','US','TX','boston'),
(6,'en','US','WA','Z'),
(7,'en','US','NY','boston'),
(8,'en','US','GA','boston')

INSERT INTO @GeoIP(geoname_id,lat,lng)
VALUES
(1,47.6062,-122.3321),
(1,47.6062,-122.3321),
(1,47.7396,-122.3426),
(1,47.4323,-121.8034),
(1,47.6738,-122.3419),
(1,47.4323,-121.8034),
(1,47.6062,-122.3321),
(2,42.6207,-78.7213),
(2,42.6207,-78.7213),
(2,42.6207,-78.7213),
(2,42.6207,-78.7213),
(3,42.3523,-83.0271),
(3,42.3314,-83.0457),
(3,42.3539,-83.2120),
(3,42.3314,-83.0457),
(3,42.3756,-83.1085)

поэтому geoname_id в geoIP не является univoque, поскольку существует много записей с одинаковым geoname_ID (оба из-за того, что много IP-блоков ссылаются на один и тот же город, а также потому, что для некоторых городов разрешение находится на уровне почтового индекса, поэтому существуют также разные значения lat и lng с тот же geoname_ID (для нашего использования может быть приемлемым первый найденный нами). Но возникает еще одна проблема: Сиэтл в GeoName присутствует только 1 раз, но Детройт присутствует дважды, а Бостон - 4 раза: так как город под названием Бостон в Грузии, Вирджинии, Массачусетсе и Нью-Йорке, и я полагаю, что это делает запрос слишком сложным:-(

Answer 1

Из того, что я понимаю, - вы хотите получить запись по городу с лат-длинным значением. Помните, что в необработанных данных MaxMind для каждого почтового индекса имеется несколько записей на город. Каждый со своими значениями Lat и Long.

SELECT GL.geoname_Id, GL.city, LatLong.Lat, LatLong.Long
FROM GeoLoc GL with(NOLOCK)
OUTER APPLY (SELECT Top(1) Lat,Long from GeoIP GI WITH(NOLOCK) WHERE GI.geoname_id = GL.geoname_id order by GI.geoname_id) LatLong
WHERE GL.locale_code = 'en' 
  AND GL.countru_iso = 'US'
  AND GL.city IN ('seattle','boston','detroit')

Answer 2

Я не уверен на 100%, что вы после, но я надеюсь, что один из следующих будет то, что вы ищете.

Если нет, воспользуйтесь приведенными ниже примерами, чтобы лучше объяснить свою проблему. Если бы вы могли дать мне более точные данные, основанные на моих примерах, чем это очень помогло бы. Также пример с результатами, которые вы хотите, и какие результаты вы получаете.

DECLARE @GeoLoc TABLE
(
   geoname_Id bigint,   --PK
   locale_code nvarchar(5),
   continent_code nvarchar(2),
   continent_name nvarchar(50),
   country_iso nvarchar(2),
   country_name nvarchar(100),
   sub1_iso nvarchar(100),
   sub1_name nvarchar(100),
   sub2_iso nvarchar(130),
   sub2_name nvarchar(150),
   city nvarchar(255),
   metro_code nvarchar(100),
   time_zone nvarchar(150)
)

INSERT INTO @GeoLoc(geoname_Id,locale_code,country_iso,city)
VALUES 
   (1,'en','US','seattle'),
   (2,'en','US','boston'),
   (3,'en','US','detroit'),
   (4,'en','US','X'),
   (5,'en','US','Y'),
   (6,'en','US','Z') 


DECLARE @GeoIP TABLE
(
   sIP bigint,
   eIP bigint,
   startIp nvarchar(20),
   geoname_id bigint,   --FK
   rc_geonameid nvarchar(30),
   rcg nvarchar(20),
   isProxy bit,
   isSat bit,
   postalcode nvarchar(20),
   lat nvarchar(20),
   lng nvarchar(20)
)

INSERT INTO @GeoIP(geoname_id,lat,lng)
VALUES
   (1,1,2),
   (2,1,2),
   (3,5,6)  


--Each cities location
------------------------------
SELECT      GP.lat,GP.lng,GL.city
FROM            @GeoLoc AS GL
INNER JOIN  @GeoIP AS GP ON GL.geoname_Id = GP.geoname_id
--Filter here WHERE         GL.city IN ('')

--Each location only once and one city name (The last one alphabetically)
------------------------------
SELECT      GP.lat,GP.lng,MAX(GL.city)
FROM            @GeoLoc AS GL
INNER JOIN  @GeoIP AS GP ON GL.geoname_Id = GP.geoname_id
--Filter here WHERE         GL.city IN ('')
GROUP BY        GP.lat,GP.lng

--Each location only once and all city names but only one result per location
------------------------------
SELECT      GP.lat,GP.lng,(SELECT STUFF((SELECT ',' + city FROM @GeoLoc AS GL INNER JOIN @GeoIP AS GP2 ON GL.geoname_Id = GP2.geoname_id WHERE GP.lat = GP2.lat AND GP.lng = GP2.lng ORDER BY GL.city FOR XML PATH('')),1,1,''))
FROM            @GeoIP AS GP
INNER JOIN  @GeoLoc AS GL ON GP.geoname_id = GL.geoname_Id
--Filter here WHERE         GL.city IN ('')
GROUP BY        GP.lat,GP.lng