"The palest ink is better than the best memory"——好记性不如烂笔头。2013~2015补记
题记:
2014.7月业务数据增长,附近人脉功能模块导致Mysql数据库成为服务器瓶颈
关键词:
附近的人、LBS、Geo、geoNear、maxDistance、distanceMultiplier、2dsphere
方案:
1.基于Mysql数据库,ABS函数(当前出现问题)
2.基于Mysql采用GeoHash索引(生产环境5.1版本不支持,全库升级比较麻烦)
3.基于Solr的geodist和geohash(上家公司做附近商铺实现过,目前还没引入Solr)
4.基于Mongo的geo(当前正在使用Mongo,值得尝试一下,和Solr比较比较)
MongoDB原生支持地理位置索引,可以直接用于位置距离计算和查询。查询结果默认将会由近到远排序,而且查询结果也包含目标点对象、距离目标点的距离等信息
代码:
1.mongo库中数据格式:
{
"_id": ObjectId("582c13293e5467eefbd94ad9"),
"uid": NumberInt(2448286),
"gps": {
"lon": 121.470238,
"lat": 31.272408
},
"flag": NumberInt(1),
"createTime": NumberLong(1479440591566)
}
2.需要添加geo索引(https://docs.mongodb.com/manual/core/geospatial-indexes/):
db.mydb.createIndex( { "gps": "2d" } )
3.核心java实现代码(建议先熟悉官方文档,写好shell):
/**
* 保存或更新GPS
* @param uid
* @param longitude
* @param latitude
* @param flag
* @param createTime
* @return
* @throws Exception
*/
@Override
public int saveOrUpdateGPS(int uid, double longitude, double latitude,
int flag,long createTime) throws Exception {
if(longitude < -180 || longitude > 180 || latitude < -90 || latitude > 90){
return -1;
}
DBObject where = new BasicDBObject("uid", uid);
DBCollection dbc = this.getUserGPSCollection();
DBObject gps = new BasicDBObject();
gps.put("lon", longitude);
gps.put("lat", latitude);
DBObject doc = new BasicDBObject();
doc.put("uid", uid);
doc.put("gps", gps);
doc.put("flag", flag);
doc.put("createTime", createTime);
DBObject newDoc = new BasicDBObject();
newDoc.put("$set", doc);
return dbc.update(where, newDoc, true, true).getN();
}
/**
* 获取附近用户
* <p>
*
* <Strong>MongoDB实现参考官方资料:</Strong>
* <br>
* 1.<link>http://docs.mongodb.org/manual/core/aggregation-introduction/</link>
* 2.<link>http://docs.mongodb.org/manual/reference/operator/aggregation/geoNear/</link>
*
* <p>
* @param uid
* @param startTime
* @param longitude
* @param latitude
* @param maxDistance(单位KM)
* @param maxNum
* @param page
* @param pageSize
* @return
* @throws Exception
*/
@Override
public List<JSONObject> getNearUIDS(int uid,long startTime, double longitude,
double latitude, double maxDistance,int maxNum ,int page,int pageSize) throws Exception {
DBObject near = new BasicDBObject("lon", longitude).append("lat", latitude);
DBObject where = new BasicDBObject();
where.put("flag", 1);
where.put("uid", new BasicDBObject(QueryOperators.NE,uid));
where.put("createTime", new BasicDBObject(QueryOperators.GTE, startTime));
DBObject geoFields = new BasicDBObject();
geoFields.put("geoNear", "user_gps");
geoFields.put("near", near);
geoFields.put("spherical", "true");
geoFields.put("maxDistance", maxDistance/6371);
geoFields.put("query", where);
geoFields.put("distanceMultiplier", 6371);//注意
geoFields.put("distanceField", "dis");
geoFields.put("num", maxNum);
DBObject geo = new BasicDBObject("$geoNear",geoFields);
DBObject projectFields = new BasicDBObject();
projectFields.put("_id", 0);
projectFields.put("uid", 1);
projectFields.put("dis", 1);
projectFields.put("createTime", 1);
DBObject project = new BasicDBObject("$project", projectFields );
// DBObject orderBy = new BasicDBObject("dis",1);
// orderBy.put("createTime", -1);
// DBObject sort = new BasicDBObject("$sort",orderBy);//整体sort可以去掉
DBObject skip = new BasicDBObject("$skip",(page - 1) * pageSize);
DBObject limit = new BasicDBObject("$limit",pageSize);
DBCollection dbc = this.getUserGPSCollection();
// AggregationOutput out = dbc.aggregate(geo,project,sort,skip,limit);
AggregationOutput out = dbc.aggregate(geo,project,skip,limit);
Iterable<DBObject> res = out.results();
Iterator<DBObject> iterator = res.iterator();
List<JSONObject> resList = new LinkedList<JSONObject>();
while(iterator.hasNext()){
DBObject one = iterator.next();
JSONObject json = JSONObject.fromObject(one.toString());
resList.add(json);
}
return resList;
}
实际产品:
(不显示实际具体距离,是有意而为之)
参考:
1.http://docs.mongodb.org/manual/core/aggregation-introduction/
2.http://docs.mongodb.org/manual/reference/operator/aggregation/geoNear/
3.http://www.infoq.com/cn/articles/depth-study-of-Symfony2
4.https://wiki.apache.org/solr/SpatialSearch/