1. mysql spatial介绍
从MySQL4.0开始加入了Spatial扩展功能,实现了OpenGIS规定的几何数据类型,在SQL中的简单空间运算。但是MySQL对空间查询的支持不够完善,要进行复杂的空间运算,建议使用postgreSQL数据库的postGIS。
下图是MySQL Spatial接口及类的结构(有背景颜色的框代表接口):
有关mysql的详细介绍,可以参考一下链接中的文章。
l 官方参考文档(中文):
http://dev.mysql.com/doc/refman/5.1/zh/spatial-extensions-in-mysql.html#mysql-spatial-datatypes
l 官方参考文档(英文):
http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html
l 关于hibernate支持
http://www.hibernatespatial.org/tutorial.html
拓展mysqlspatial函数:
1) 空间中的点是否相等
[delphi] view plain copy
- DELIMITER $$
- CREATE DEFINER = 'smart'@'localhost'
- FUNCTION ArePointsEqual(p1 POINT,
- p2POINT
- )
- RETURNS TINYINT(1)
- DETERMINISTIC
- NO SQL
- BEGIN
- RETURN IsZero(x(p1) -x(p2)) AND IsZero(y(p1) - y(p2));
- END
- $$
- DELIMITER ;
2) 查找区域的中心点
[sql] view plain copy
- USE smartu;
- DELIMITER $$
- CREATE DEFINER = 'smart'@'localhost'
- FUNCTION
- RETURNS
- DETERMINISTIC
- NO
- BEGIN
- DECLARE
- DECLARE sw, ne POINT; #South-West and
- DECLARE lat, lng DOUBLE;
- SET
- SET
- SET
- SET
- SET
- RETURN
- END
- $$
- DELIMITER ;
3) LineN
[sql] view plain copy
- USE smartu;
- DELIMITER $$
- CREATE DEFINER = 'smart'@'localhost'
- FUNCTION
- INT
- )
- RETURNS
- DETERMINISTIC
- NO
- BEGIN
- THEN
- RETURN NULL;
- END
- RETURNLineString(pointn(ls, n), pointn(ls, n + 1));
- END
- $$
- DELIMITER ;
4) 计算两点间的空间距离
[sql] view plain copy
- USE smartu;
- DELIMITER $$
- CREATE DEFINER = 'smart'@'localhost'
- FUNCTION DISTANCE(lat1 DOUBLE,
- lon1DOUBLE,
- lat2DOUBLE,
- lon2DOUBLE
- )
- RETURNS DOUBLE
- DETERMINISTIC
- NO
- COMMENT 'counts distance (km) between 2 points on Earth surface'
- BEGIN
- DECLARE
- RETURN
- cos(lat1 / dtor) *cos(lat2 / dtor) *
- cos(lon2 / dtor - lon1 /dtor)));
- END
- $$
- DELIMITER ;
5) 是否为0
[sql] view plain copy
- USE smartu;
- DELIMITER $$
- CREATE DEFINER = 'smart'@'localhost'
- FUNCTION IsZero(n DOUBLE)
- RETURNS
- DETERMINISTIC
- NO
- BEGIN
- DECLARE
- RETURN (abs(n) <=epsilon);
- END
- $$
- DELIMITER ;
2. 项目前台、后台的实现
1) 项目后台实现
l 数据库设计
[sql] view plain copy
- --创建表
- createtable smart_u_convenience_item_spatial(
- varchar(36) not null,
- not null,
- varchar(20),
- varchar(20),
- varchar(500),
- varchar(500),
- primary key
- key
- )ENGINE=MyISAM;
- --往表中插入数据
- INSERTINTO smart_u_convenience_item_spatial
- SELECTt.convenience_item_id
- 'POINT(',t.item_latitude, ' ', t.item_longitude, ')'))
- , t.item_latitude
- , t.item_longitude
- , t.convenience_item_code
- , t.convenience_item_name
- from
l 后台代码使用到的查询点的sql
[sql] view plain copy
- SELECT
- FROM
- SELECT
- FROM
- smart_u_convenience_item t
- WHERE
- t.convenience_item_idIN
- (SELECTs.item_spatial_id
- FROM
- smart_u_convenience_item_spatial s
- WHERE
- intersects(location,
- 'POLYGON((', 3.9921123E7, ' ', 1.16365462E8,
- ',', 3.9921123E7, ' ', 1.16441881E8,
- ',', 3.9879484E7, ' ', 1.16441881E8,
- ',', 3.9879484E7, ' ', 1.16365462E8,
- ',', 3.9921123E7, ' ', 1.16365462E8,'))'))))) t
- ORDER BY
- ASC
2) 前台设计
[java] view plain copy
- MKMapViewListener mapViewListener = new
- @Override
- public void
- // 此处可以实现地图移动完成事件的状态监听
- "mapMoveFinish");
- this.getBsnsDisInfo(BsnsDisAllActivity.this.getGeoPointMap());
- }
- @Override
- public void
- ","+poi.geoPt.getLongitudeE6());
- }
- @Override
- public void
- "onGetCurrentMap");
- }
- @Override
- public void
- "onMapAnimationFinish");
- this.getBsnsDisInfo(BsnsDisAllActivity.this.getGeoPointMap());
- }
- };
- mMapView.regMapViewListener(app.mBMapManager,mapViewListener);
- private
- Projectionprojection = mMapView.getProjection();
- Map<String,Double>polygon = newHashMap<String,Double>();
- 0, 0);
- "top_x",(double) top.getLatitudeE6());
- "top_y",(double)top.getLongitudeE6());
- 0);
- "right_x",(double)right.getLatitudeE6());
- "right_y",(double)right.getLongitudeE6());
- GeoPointdown = projection.fromPixels(mMapView.getWidth(), mMapView.getHeight());
- "down_x",(double)down.getLatitudeE6());
- "down_y",(double)down.getLongitudeE6());
- 0, mMapView.getHeight());
- "left_x",(double)left.getLatitudeE6());
- "left_y",(double)left.getLongitudeE6());
- return
- }
- private
- final
- new
- public void
- try{
- new
- OrderByEntityorder = newOrderByEntity();
- "item_longitude");
- "asc");
- orderByEntity.add(order);
- // OrderByEntityorder2 = new OrderByEntity();
- // order.setOrderCol("item_latitude");
- // order.setOrderType("asc");
- // orderByEntity.add(order2);
- null, null);
- Log.i(TAG,ack.getAckCode().toString());
- Log.i(TAG,ack.getAckMsg().toString());
- new
- if(ack.getAckCode().toString().indexOf("INFO") != -1){
- msg.what = RESULT_BSNS_CAT;
- else{
- msg.what = RESULT_ERROR;
- }
- msg.obj = ack;
- myHandler.sendMessage(msg);
- catch(Exception e){
- }
- };
- }.start();
- }
- new
- public void
- switch
- case
- ConvenienceItemAckEntityack = (ConvenienceItemAckEntity)msg.obj;
- pinItemMark(ack.getResults());
- break;
- default:
- break;
- }
- };
- };
- private
- convenieceItemList= newArrayList<ConvenienceItemEntity>();
- mGeoList.clear();
- latLoc.clear();
- for(inti=0;i<itemList.size();i++){
- ConvenienceItemEntityitemEntity = itemList.get(i);
- int
- int
- OverlayItemitem = newOverlayItem(newGeoPoint(lat, loc),itemEntity.getConvenienceItemName() ,itemEntity.getConvenienceItemId());
- this).inflate(
- null);// 获取要转换的View资源
- TextViewTestText = (TextView)drawableView.findViewById(R.id.map_drawable_text);
- //将每个点的Title在弹窗中以文本形式显示出来
- BitmapdrawableBitmap = convertViewToBitmap(drawableView);
- Drawabledraw = newBitmapDrawable(drawableBitmap);
- item.setMarker(draw);
- convenieceItemList.add(itemEntity);
- mGeoList.add(item);
- PointlocPoint = newPoint();
- 1;
- locPoint.y = lat;
- latLoc.add(locPoint);
- }
- addMarker(mGeoList);
- for (int i = 0; i < latLoc.size(); i++){
- for (int j = 1; j < latLoc.size() - i; j++) {
- 1));
- Point p2 = latLoc.get(j);
- if(p1.y>p2.y){
- Point temp = p1;
- 1, p2);
- latLoc.set(j, p1);
- }
- if(latLoc.get(j-1).x==0){
- 1;
- }
- }
- }
- }