poI地址纠偏查询存储过程理解

1、POI存储过程查询

服务器类型：PostgreSQL

function name 函数名字 address_parse_no_save(lon float8, lat float8)

DECLARE
    
  METER_PER_DEGRESS float8;
  PROJ4326 int;
  user_point RECORD;--存储用户输入坐标转换为wkb格式的点记录
  addr_table_num float8;
  county RECORD;--存储区县地址的记录
  road_sql_str varchar;
  road_dist float8;
  road_name varchar;
  poi_sql_str varchar;
  poi_dist float8;
  poi_name varchar;
  poi_angle varchar :='';
  poi_x float8;
  poi_y float8;
  x float8;
  y float8;
  angle float8;

BEGIN
    METER_PER_DEGRESS := 111194.872221777;
    PROJ4326 := 4326;
    --地点
    SELECT INTO user_point geomfromtext('POINT(' || lon ||' ' || lat ||')',PROJ4326) AS point,substr(st_geohash(geomfromtext('POINT(' || lon ||' ' || lat ||')',PROJ4326),24),0,7)||'%' AS geohash7,substr(st_geohash(geomfromtext('POINT(' || lon ||' ' || lat ||')',PROJ4326),24),0,9)||'%' AS geohash9,st_geohash(geomfromtext('POINT(' || lon ||' ' || lat ||')',PROJ4326),24) geohash24 FROM (SELECT 1) m1;
    --省 区县
    SELECT INTO county *,substr(split_part(m1.addr, ' ', 1),0,3) province FROM addr_county m1 WHERE st_contains(m1.the_geom, user_point.point) LIMIT 1;

    --地址解析
    IF county.addr IS NULL THEN
    RETURN '';-- 没有找到区县返回空字符串
    ELSE 
        BEGIN
            
                --查找已解析的结果集，存在则返回结果。
                IF county.province='浙江' THEN
                    BEGIN
                        addr_table_num:=1;
                    END;
                ELSEIF county.province='云南' THEN
                    BEGIN
                        addr_table_num:=2;
                    END;
                ELSEIF county.province='新疆' THEN
                    BEGIN
                        addr_table_num:=3;
                    END;
                ELSEIF county.province='香港' THEN
                    BEGIN
                        addr_table_num:=4;
                    END;
                ELSEIF county.province='西藏' THEN
                    BEGIN
                        addr_table_num:=5;
                    END;
                ELSEIF county.province='台湾' THEN
                    BEGIN
                        addr_table_num:=6;
                    END;
                ELSEIF county.province='四川' THEN
                    BEGIN
                        addr_table_num:=7;
                    END;
                ELSEIF county.province='陕西' THEN
                    BEGIN
                        addr_table_num:=8;
                    END;
                ELSEIF county.province='山西' THEN
                    BEGIN
                        addr_table_num:=9;
                    END;
                ELSEIF county.province='山东' THEN
                    BEGIN
                        addr_table_num:=10;
                    END;
                ELSEIF county.province='青海' THEN
                    BEGIN
                        addr_table_num:=11;
                    END;
                ELSEIF county.province='宁夏' THEN
                    BEGIN
                        addr_table_num:=12;
                    END;
                ELSEIF county.province='内蒙' THEN
                    BEGIN
                        addr_table_num:=13;
                    END;
                ELSEIF county.province='辽宁' THEN
                    BEGIN
                        addr_table_num:=14;
                    END;
                ELSEIF county.province='江西' THEN
                    BEGIN
                        addr_table_num:=15;
                    END;
                ELSEIF county.province='吉林' THEN
                    BEGIN
                        addr_table_num:=16;
                    END;
                ELSEIF county.province='湖南' THEN
                    BEGIN
                        addr_table_num:=17;
                    END;
                ELSEIF county.province='湖北' THEN
                    BEGIN
                        addr_table_num:=18;
                    END;
                ELSEIF county.province='黑龙' THEN
                    BEGIN
                        addr_table_num:=19;
                    END;
                ELSEIF county.province='河南' THEN
                    BEGIN
                        addr_table_num:=20;
                    END;
                ELSEIF county.province='北京' THEN
                    BEGIN
                        addr_table_num:=21;
                    END;
                ELSEIF county.province='天津' THEN
                    BEGIN
                        addr_table_num:=22;
                    END;
                ELSEIF county.province='河北' THEN
                    BEGIN
                        addr_table_num:=23;
                    END;
                ELSEIF county.province='海南' THEN
                    BEGIN
                        addr_table_num:=24;
                    END;
                ELSEIF county.province='贵州' THEN
                    BEGIN
                        addr_table_num:=25;
                    END;
                ELSEIF county.province='广西' THEN
                    BEGIN
                        addr_table_num:=26;
                    END;
                ELSEIF county.province='甘肃' THEN
                    BEGIN
                        addr_table_num:=27;
                    END;
                ELSEIF county.province='福建' THEN
                    BEGIN
                        addr_table_num:=28;
                    END;
                ELSEIF county.province='澳门' THEN
                    BEGIN
                        addr_table_num:=29;
                    END;
                ELSEIF county.province='安徽' THEN
                    BEGIN
                        addr_table_num:=30;
                    END;
                ELSEIF county.province='上海' THEN
                    BEGIN
                        addr_table_num:=31;
                    END;
                ELSEIF county.province='重庆' THEN
                    BEGIN
                        addr_table_num:=32;
                    END;
                ELSEIF county.province='江苏' THEN
                    BEGIN
                        addr_table_num:=33;
                    END;
                ELSEIF county.province='广东' THEN
                    BEGIN
                        addr_table_num:=34;
                    END;

                END IF;
            

            
            road_sql_str := 'SELECT st_distance(geomfromtext(''POINT('|| lon ||' ' || lat ||')'','|| PROJ4326 ||'), m1.the_geom)*'||METER_PER_DEGRESS||' dist,m1."name" FROM addr_road_'||addr_table_num||' m1 WHERE m1.geohash like '''||user_point.geohash7||''' AND st_distance(geomfromtext(''POINT('|| lon ||' ' || lat ||')'','|| PROJ4326 ||'), m1.the_geom)*'||METER_PER_DEGRESS||'<=2000 ORDER BY st_distance(geomfromtext(''POINT('|| lon ||' ' || lat ||')'','|| PROJ4326 ||'), m1.the_geom)*'||METER_PER_DEGRESS||'  LIMIT 1;';
            EXECUTE  road_sql_str INTO road_dist,road_name ;

            IF road_name IS NULL THEN
                BEGIN
                    RETURN county.addr;
                END;
            ELSE 
                BEGIN
                    poi_sql_str := 'SELECT st_distance(geomfromtext(''POINT('|| lon ||' ' || lat ||')'','|| PROJ4326 ||'), m1.the_geom)*'||METER_PER_DEGRESS||' dist,m1."name",st_x( m1.the_geom) poi_x,st_y( m1.the_geom) poi_y FROM addr_poi_'||addr_table_num||' m1 WHERE m1.geohash like '''||user_point.geohash7||''' AND st_distance(geomfromtext(''POINT('|| lon ||' ' || lat ||')'','|| PROJ4326 ||'), m1.the_geom)*'||METER_PER_DEGRESS||'<=2000 ORDER BY st_distance(geomfromtext(''POINT('|| lon ||' ' || lat ||')'','|| PROJ4326 ||'), m1.the_geom)*'||METER_PER_DEGRESS||'  LIMIT 1;';
                    EXECUTE  poi_sql_str INTO poi_dist,poi_name,poi_x,poi_y ;

                    IF poi_name IS NULL THEN
                        BEGIN
                            RETURN county.addr ||' '||road_name ||'(附近' ||  "floor"(road_dist) || '米)';
                        END;
                    ELSE 
                        BEGIN
                            
                            x:=poi_x-lon;
                            y:=poi_y-lat;            
                            IF x=0 THEN
                                x:=0.000001;
                            END IF;
                            angle:="floor"(atan((y)/(x))*180/pi());
                            
                            IF x>0 AND y>0 THEN 
                                BEGIN
                                    IF angle>=0 AND angle<=23 THEN
                                        poi_angle:='西';
                                    ELSEIF angle>=24 AND angle<=68 THEN
                                        poi_angle:='西南';
                                    ELSEIF angle>=69 AND angle<=90 THEN
                                        poi_angle:='南';
                                    END IF;
                                END;
                            ELSEIF x<0 AND y>0 THEN 
                                BEGIN
                                    IF angle<=0 AND angle>=-23 THEN
                                        poi_angle:='东';
                                    ELSEIF angle<=-24 AND angle>=-68 THEN
                                        poi_angle:='东南';
                                    ELSEIF angle<=-69 AND angle>=-90 THEN
                                        poi_angle:='南';
                                    END IF;
                                END;
                            ELSEIF x<0 AND y<0 THEN 
                                BEGIN
                                    IF angle>=0 AND angle<=23 THEN
                                        poi_angle:='东';
                                    ELSEIF angle>=24 AND angle<=68 THEN
                                        poi_angle:='东北';
                                    ELSEIF angle>=69 AND angle<=90 THEN
                                        poi_angle:='北';
                                    END IF;
                                END;
                            ELSEIF x>0 AND y<0 THEN 
                                BEGIN
                                    IF angle<=0 AND angle>=-23 THEN
                                        poi_angle:='西';
                                    ELSEIF angle<=-24 AND angle>=-68 THEN
                                        poi_angle:='西北';
                                    ELSEIF angle<=-69 AND angle>=-90 THEN
                                        poi_angle:='北';
                                    END IF;
                                END;
                            END IF;
                            RETURN county.addr ||' '|| road_name || '(附近' ||  "floor"(road_dist) || '米)' ||' '|| poi_name || '('||poi_angle||'方' ||  "floor"(poi_dist) || '米)';
                        END;
                    END IF;
                END;
            END IF;

        END;
  END IF;
    -- 没有找到任何数据，返回空字符串
  RETURN '';
END;

View Code

2、POI存储过程理解

-- 调用function:-----------------

-- query-- SELECT kwt_address_parse_no_save('103.783432', '30.77454534');
-- reslt-- 四川省成都市生态大道(附近278米) 五福桥[村庄](西南方399米)

------------------------------------------------实现方案-------------------------------------------------
1、实际需求：根据一个经纬度解析出一个与物理世界对应的一个字符串地址描述。
2、实施方案：对经纬度禁行范围检索，逐级缩小搜索范围，从而更加精确地锁定目标地址。

实现步骤：
(1)对地址的详细程度做出分层级的定义，即依次定义:省，市，县，辖区，街道，路，门牌号，兴趣点，兴趣点距离，方向等
(2)确定传入参数的个数2，即：经度(longitude)，纬度(latitude)
(3)首先获取一个经纬度在数据库中粗粒度的地址信息
(4)然后在对应的地址中去找省区县，如果没找到返回" "地址，否则继续下一步
(5)验证已解析的结果集，存在则返回结果{
1)、逐级比较：省，市，县，辖区，街道，路，门牌号，兴趣点
2)、检索兴趣点的时候解析经纬度与最近的道路和兴趣点进行匹配，通过经纬度算出方向和距离
3)、拼接查询出来的层级定义的变量，并返回字符串组合
}。
3、涉及算法：
(1)、经纬度距离计算：

public static class Distance{

private static String unit; //距离单位

private static double distance;//距离大小
/**
*@param utype:距离单位："m"/"km"
*@param latlng1 经纬度
*@param latlng2 经纬度
*/
public double getDistance(String utype,String latlng1,String latlng2) {
String []latlng_1=latlng1.split(",");
String []latlng_2=latlng2.split(",");
double lata=Double.parseDouble(latlng_1[0]);
double lnga=Double.parseDouble(latlng_1[1]);
double latb=Double.parseDouble(latlng_2[0]);
double lngb=Double.parseDouble(latlng_2[1]);
double distance= 111119*Math.sqrt(Math.pow(lnga - lngb, 2) +Math.pow(lata - latb, 2));
String num=distance+"";
distance =Double.parseDouble(num);
if("km".equals(utype)){
distance=Math.round(distance*100)/100000;
}else{
distance=Math.round(distance*100)/100;
}
unit = utype;
}

}

View Code

(2)、经纬度角度计算：

atan=(p1.y-p2.y)/(p2.x-p1.x))*180/Math.PI

posted on 2013-11-07 16:16 boonya 阅读(706) 评论(0) 编辑收藏举报