mafengwo

Posted by 王进 on 11-21,2019
# 马蜂窝总数据:225570
SELECT count(0) FROM fix_mafengwo_poi;


# 其中没有经纬度的数据:33554
select count(0) from fix_mafengwo_poi where lat = "" or lng = "" or lat is null or lng is null;


# 马蜂窝数据的城市 不在我们mt数据库的有: 1989
select * from (
select id,country,city from fix_mafengwo_poi 
GROUP BY city
) a
where a.city  not in (
select district.name from district  where type = 'C' GROUP BY district.name 
)



# 初始化数据
update fix_mafengwo_poi set mt_city_id = null;



# 筛选城市
update fix_mafengwo_poi a INNER JOIN (
select id,district.name from district  where type = 'C' GROUP BY district.name 
) b on (a.city != b.`name`) 
set city_flag = 1;




# 对马蜂窝数据进行绑定城市id
update fix_mafengwo_poi a INNER JOIN (
select id,district.name from district  where type = 'C' GROUP BY district.name 
) b on (a.city = b.`name`) 
set mt_city_id = b.id;


目前能对应上的数据最终为:108069

ALTER TABLE td.district ADD COLUMN mafengwo_flag tinyint(255) NULL COMMENT '1马蜂窝插入的数据' AFTER lvl;