从maxmind数据库中选择

Question

我有一个maxmind数据库，有2个表，geoIP和geooc

然后我有一个大约10000个城市/城镇的列表，我想得到每个城市的晚和液化天然气显示在地图上

我会避免使用10000个循环来获取详细信息:我认为最好使用T-SQL的IN语句，并把所有的城市名称但问题是几乎所有的大城市都有不止一个参考资料，因此我得到了一些重复的结果

我尝试使用distinct:

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，而

是实现这样的结果的方法，并且，这个解决方案是否可以更好地执行或使用for next循环?

here表定义:

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)

因此geoIP中的geoname_id不是唯一的，因为有许多记录具有相同的geoname_id(既因为有许多IP块引用同一个城市，也因为，对于一些城市的分辨率是在邮政编码级别，因此也有不同的lat和lng具有相同的geoname_id(对于我们的使用，我们发现的第一个可以接受)。但是有一个进一步的问题出现了:西雅图在GeoName中只出现了1次，但底特律出现了两次，波士顿出现了4次:因为在乔治亚州，弗吉尼亚州，马萨诸塞州和纽约州都有一个名为波士顿的城市，我认为这使得查询过于复杂:-(

Answer 1

据我所知-您希望每个城市的记录具有latlong值。请记住，在MaxMind的原始数据中，每个城市的每个邮政编码都有多个条目。

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