百度地图查询视野范围内的建筑物

缺乏、安全感 2022-01-17 00:27 478阅读 0赞

根据用户移动地图的位置，显示在视野范围内的建筑物，简单的思路是，添加地图监听，当地图移动结束之后，计算出当前屏幕四个角的GeoPoint，根据这4个点，通过mysql的spatial中的函数，完成在此范围内的建筑物的查询。

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][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][http_dev.mysql.com_doc_refman_5.0_en_spatial-extensions.html]

l  [ 关于hibernate支持mysql-spatial][_hibernate_mysql-spatial]：

[http://blog.csdn.net/b10090120/article/details/8698787][_hibernate_mysql-spatial]

[http://www.hibernatespatial.org/tutorial.html][http_www.hibernatespatial.org_tutorial.html]

拓展mysqlspatial函数：

1)  空间中的点是否相等

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)  查找区域的中心点

USE smartu;
     
    DELIMITER $$
     
    CREATE DEFINER = 'smart'@'localhost'
    FUNCTION GetCenterPoint(g GEOMETRY)
    RETURNS POINT
    DETERMINISTIC
    NO SQL
    BEGIN
      DECLARE envelope  POLYGON;
      DECLARE sw, ne    POINT; #South-West and North-East points
      DECLARE lat, lng  DOUBLE;
     
      SET envelope =exteriorring(Envelope(g));
      SET sw =pointn(envelope, 1);
      SET ne =pointn(envelope, 3);
      SET lat = x(sw) + (x(ne)- x(sw)) / 2;
      SET lng = y(sw) + (y(ne)- y(sw)) / 2;
      RETURN POINT(lat, lng);
    END
    $$
     
    DELIMITER ;

3)  LineN

USE smartu;
     
    DELIMITER $$
     
    CREATE DEFINER = 'smart'@'localhost'
    FUNCTION LineN(ls LINESTRING,
                   n  INT
                   )
    RETURNS LINESTRING
    DETERMINISTIC
    NO SQL
    BEGIN
      IF n >= numpoints(ls)THEN
        RETURN NULL;
      END IF;
      RETURNLineString(pointn(ls, n), pointn(ls, n + 1));
    END
    $$
     
    DELIMITER ;

4)  计算两点间的空间距离

USE smartu;
     
    DELIMITER $$
     
    CREATE DEFINER = 'smart'@'localhost'
    FUNCTION DISTANCE(lat1 DOUBLE,
                      lon1DOUBLE,
                      lat2DOUBLE,
                      lon2DOUBLE
                      )
    RETURNS DOUBLE
    DETERMINISTIC
    NO SQL
    COMMENT 'counts distance (km) between 2 points on Earth surface'
    BEGIN
      DECLARE dtor DOUBLEDEFAULT 57.295800;
     
      RETURN (6371 *acos(sin(lat1 / dtor) * sin(lat2 / dtor) +
      cos(lat1 / dtor) *cos(lat2 / dtor) *
      cos(lon2 / dtor - lon1 /dtor)));
    END
    $$
     
    DELIMITER ;

5)  是否为0

USE smartu;
     
    DELIMITER $$
     
    CREATE DEFINER = 'smart'@'localhost'
    FUNCTION IsZero(n DOUBLE)
    RETURNS TINYINT(1)
    DETERMINISTIC
    NO SQL
    BEGIN
      DECLARE epsilon DOUBLEDEFAULT 0.00000000001;
     
      RETURN (abs(n) <=epsilon);
    END
    $$
     
    DELIMITER ;

2.  项目前台、后台的实现

1)  项目后台实现

l  数据库设计

--创建表
    createtable smart_u_convenience_item_spatial(
        item_spatial_id varchar(36) not null,
        location point not null,
        latitude varchar(20),
        longitude varchar(20),
        convenience_item_code varchar(500),
        convenience_item_name varchar(500),
        primary key (`item_spatial_id`),
        spatial key `sp_index`(location)
    )ENGINE=MyISAM;
    --往表中插入数据
    INSERTINTO smart_u_convenience_item_spatial
    SELECTt.convenience_item_id
         , PointFromText(concat('POINT(',t.item_latitude, ' ', t.item_longitude, ')'))
         , t.item_latitude
         , t.item_longitude
         , t.convenience_item_code
         , t.convenience_item_name
       from smart_u_convenience_item t;

l  后台代码使用到的查询点的sql

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,
                       geomfromtext(concat('POLYGON((', 3.9921123E7, ' ', 1.16365462E8,
                                                    ',', 3.9921123E7, ' ', 1.16441881E8,
                                                   ',', 3.9879484E7, ' ', 1.16441881E8,
                                                   ',', 3.9879484E7, ' ', 1.16365462E8,
                                                     ',', 3.9921123E7, ' ', 1.16365462E8,'))'))))) t
    ORDER BY
     item_longitude ASC

2)  前台设计

MKMapViewListener mapViewListener = new MKMapViewListener() {
     
             @Override
             public void onMapMoveFinish() {
                // 此处可以实现地图移动完成事件的状态监听
                Log.e(TAG,"mapMoveFinish");
               BsnsDisAllActivity.this.getBsnsDisInfo(BsnsDisAllActivity.this.getGeoPointMap());           
             }
     
             @Override
             public void onClickMapPoi(MapPoipoi) {
                Log.e(TAG,poi.geoPt.getLatitudeE6()+","+poi.geoPt.getLongitudeE6());         
             }
     
             @Override
             public void onGetCurrentMap(Bitmaparg0) {
                Log.e(TAG,"onGetCurrentMap");
               
             }
     
             @Override
             public void onMapAnimationFinish(){
                Log.e(TAG,"onMapAnimationFinish");
               BsnsDisAllActivity.this.getBsnsDisInfo(BsnsDisAllActivity.this.getGeoPointMap());
             }
          };
          mMapView.regMapViewListener(app.mBMapManager,mapViewListener);
     
    private Map<String,Double> getGeoPointMap(){
     
             Projectionprojection = mMapView.getProjection();
     
             Map<String,Double>polygon = newHashMap<String,Double>();
             GeoPointtop = projection.fromPixels(0, 0);
             polygon.put("top_x",(double) top.getLatitudeE6());
             polygon.put("top_y",(double)top.getLongitudeE6());
             GeoPointright = projection.fromPixels(mMapView.getWidth(), 0);
             polygon.put("right_x",(double)right.getLatitudeE6());
             polygon.put("right_y",(double)right.getLongitudeE6());
             GeoPointdown = projection.fromPixels(mMapView.getWidth(), mMapView.getHeight());
             polygon.put("down_x",(double)down.getLatitudeE6());
             polygon.put("down_y",(double)down.getLongitudeE6());
             GeoPointleft = projection.fromPixels(0, mMapView.getHeight());
             polygon.put("left_x",(double)left.getLatitudeE6());
             polygon.put("left_y",(double)left.getLongitudeE6());
            
             return polygon;
          }
     
     
    private voidgetBsnsDisInfo(Map<String,Double>polygon){
              final Map<String,Double> tPolygon =polygon;
             new Thread(){
                public void run() {
                    try{
                       List<OrderByEntity>orderByEntity = new ArrayList();
                       OrderByEntityorder = newOrderByEntity();
                       order.setOrderCol("item_longitude");
                       order.setOrderType("asc");
                       orderByEntity.add(order);
       //              OrderByEntityorder2 = new OrderByEntity();
       //              order.setOrderCol("item_latitude");
       //              order.setOrderType("asc");
       //              orderByEntity.add(order2);
      
                       ConvenienceItemAckEntityack = SUService.getInstance().getMapBsns(app.nowUser, tPolygon, orderByEntity, null, null);
                       Log.i(TAG,ack.getAckCode().toString());
                       Log.i(TAG,ack.getAckMsg().toString());
                      Message msg = new Message();
                       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();
          }
          
           Handler myHandler = new Handler(){
             public void handleMessage(Messagemsg) {
                switch (msg.what) {
                case RESULT_BSNS_CAT:
                    ConvenienceItemAckEntityack = (ConvenienceItemAckEntity)msg.obj;
                    pinItemMark(ack.getResults());
                    break;
     
                default:
                    break;
                }
             };
          };
         
          private voidpinItemMark(List<ConvenienceItemEntity> itemList){
             convenieceItemList= newArrayList<ConvenienceItemEntity>();
             mGeoList.clear();
             latLoc.clear();
             for(inti=0;i<itemList.size();i++){
                ConvenienceItemEntityitemEntity = itemList.get(i);
                int lat = Integer.parseInt(itemEntity.getItemLatitude());
                int loc = Integer.parseInt(itemEntity.getItemLongtude());
                OverlayItemitem = newOverlayItem(newGeoPoint(lat, loc),itemEntity.getConvenienceItemName() ,itemEntity.getConvenienceItemId());
               
                ViewdrawableView = LayoutInflater.from(BsnsDisAllActivity.this).inflate(
                       R.layout.map_drawable_pin, null);// 获取要转换的View资源
                TextViewTestText = (TextView)drawableView.findViewById(R.id.map_drawable_text);
                TestText.setText(item.getTitle());//将每个点的Title在弹窗中以文本形式显示出来     
               
                BitmapdrawableBitmap = convertViewToBitmap(drawableView);
               
                Drawabledraw = newBitmapDrawable(drawableBitmap);
         
                item.setMarker(draw);
               
                convenieceItemList.add(itemEntity);
                mGeoList.add(item);
                PointlocPoint = newPoint();
                locPoint.x = i+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++) {
                       Point p1 = latLoc.get((j-1));
                       Point p2 = latLoc.get(j);
                       
                       if(p1.y>p2.y){
                          Point temp = p1;
                          latLoc.set(j-1, p2);
                          latLoc.set(j, p1);
                       }
                       if(latLoc.get(j-1).x==0){
                          selectedItemLat = j-1;
                       }
                     }
             }
          }

[http_dev.mysql.com_doc_refman_5.1_zh_spatial-extensions-in-mysql.html_mysql-spatial-datatypes]: http://dev.mysql.com/doc/refman/5.1/zh/spatial-extensions-in-mysql.html#mysql-spatial-datatypes
[http_dev.mysql.com_doc_refman_5.0_en_spatial-extensions.html]: http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html
[_hibernate_mysql-spatial]: http://blog.csdn.net/b10090120/article/details/8698787
[http_www.hibernatespatial.org_tutorial.html]: http://www.hibernatespatial.org/tutorial.html