PostgreSQL+GeoHash地圖點位聚合實現(xiàn)代碼

更新時間：2023年07月28日 14:43:10 作者：CharltonW

這篇文章主要介紹了PostgreSQL+GeoHash地圖點位聚合,本文通過實例代碼給大家介紹的非常詳細，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下

PG數(shù)據(jù)庫安裝擴展

需要用到pg數(shù)據(jù)庫的空間擴展postgis，在進行操作之前需要在數(shù)據(jù)庫中安裝擴展。

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION postgis_geohash;

GeoHash

GeoHash是一種地址編碼方法。他能夠把二維的空間經(jīng)緯度數(shù)據(jù)編碼成一個字符串。具體原理這里不再詳細說明，GeoHash算法大體上分為三步：

將經(jīng)緯度變成二進制
將經(jīng)緯度的二進制合并
通過Base32對合并后的二進制進行編碼

Geohash比直接用經(jīng)緯度的高效很多，而且使用者可以發(fā)布地址編碼，既能表明自己位于北海公園附近，又不至于暴露自己的精確坐標(biāo)，有助于隱私保護。

GeoHash用一個字符串表示經(jīng)度和緯度兩個坐標(biāo)。在數(shù)據(jù)庫中可以實現(xiàn)在一列上應(yīng)用索引（某些情況下無法在兩列上同時應(yīng)用索引）
GeoHash表示的并不是一個點，而是一個矩形區(qū)域
GeoHash編碼的前綴可以表示更大的區(qū)域。例如wx4g0ec1，它的前綴wx4g0e表示包含編碼wx4g0ec1在內(nèi)的更大范圍。這個特性可以用于附近地點搜索
編碼越長，表示的范圍越小，位置也越精確。因此我們就可以通過比較GeoHash匹配的位數(shù)來判斷兩個點之間的大概距離

建表

在創(chuàng)建數(shù)據(jù)庫表時，表中除了經(jīng)緯度字段以外，再創(chuàng)建兩個字段：

① 經(jīng)緯度對應(yīng)的Geometry字段（類型：geometry）

② 經(jīng)緯度對應(yīng)的geoHash值字段（類型：varchar）

如：alter table 表名 add 字段名 geometry(point, 4326); // 創(chuàng)建geometry字段
alter table 表名 add 字段名 varchar; // 創(chuàng)建geoHash字段

JPA中定義

@Type(type="jts_geometry")
@Column(name="geometry",columnDefinition = "geometry(Point,4326)")
@JsonIgnore
private Geometry geometry; // 實體類的Geometry字段

根據(jù)經(jīng)緯度計算 geometry 和 geoHash

Java生成geometry和geoHash

geometry字段和 geoHash字段均可以在java代碼中根據(jù)經(jīng)緯度生成。

根據(jù)經(jīng)緯度生成geometry

使用org.locationtech.jts.io包下的WKTReader類，可以根據(jù)經(jīng)緯度生成Geometry對象。

String wkt = "POINT("+longitude+" "+latitude+")"; // longitude 經(jīng)度，latitude緯度
WKTReader wktReader = new WKTReader();
Geometry geometry = wktReader.read(wkt); // Geometry對象
if(geometry!=null) {
    geometry.setSRID(4326);
}

根據(jù)經(jīng)緯度生成geoHash

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;
import java.util.ArrayList;
import java.util.List;
@Component
public class GeoHashUtil {
    public final double Max_Lat = 90;
    public final double Min_Lat = -90;
    public final double Max_Lng = 180;
    public final double Min_Lng = -180;
    private final String[] base32Lookup = {
            "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "b", "c", "d", "e", "f", "g", "h", "j", "k",
            "m", "n", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z"
    };
    /**
     * 根據(jù)geoHash串獲取中心點經(jīng)緯度
     * @param geoHashCode
     * @return  lng->x  lat->y
     */
    public double[] getSpaceCoordinate(String geoHashCode) {
        if(StringUtils.isBlank(geoHashCode)){
            return new double[2];
        }
        List<Integer> list = base32Decode(geoHashCode);
        String str = convertToIndex(list);
        GeoHashPoint geoHashPoint = splitLatAndLng(str);
        double y = revert(Min_Lat, Max_Lat, geoHashPoint.getLatList());
        double x = revert(Min_Lng, Max_Lng, geoHashPoint.getLngList());
        return new double[]{x, y};
    }
    /**
     * 根據(jù)精度獲取GeoHash串
     * @param lng 經(jīng)度 x
     * @param lat 緯度 y
     * @param precise 精度
     * @return
     */
    public String getGeoHash( double lng, double lat, int precise) {
        // 緯度二值串長度
        int latLength;
        //  經(jīng)度二值串長度
        int lngLength;
        if (precise < 1 || precise > 12) {
            precise = 12;
        }
         latLength = (precise * 5) / 2;
        if (precise % 2 == 0) {
            lngLength = latLength;
        } else {
            lngLength = latLength + 1;
        }
        return encode(lat, lng, latLength, lngLength);
    }
    /**
     * 經(jīng)緯度二值串合并：偶數(shù)位放經(jīng)度，奇數(shù)位放緯度，把2串編碼組合生成新串
     *
     */
    public String encode(double lat, double lng, int latLength, int lngLength) {
        if (latLength < 1 || lngLength < 1) {
            return StringUtils.EMPTY;
        }
        List<Character> latList = new ArrayList<>(latLength);
        List<Character> lngList = new ArrayList<>(lngLength);
        // 獲取維度二值串
        convert(Min_Lat, Max_Lat, lat, latLength, latList);
        // 獲取經(jīng)度二值串
        convert(Min_Lng, Max_Lng, lng, lngLength, lngList);
        StringBuilder sb = new StringBuilder();
        for (int index = 0; index < latList.size(); index++) {
            sb.append(lngList.get(index)).append(latList.get(index));
        }
//        如果二者長度不一樣，說明要求的精度為奇數(shù)，經(jīng)度長度比緯度長度大1
        if (lngLength != latLength) {
            sb.append(lngList.get(lngList.size() - 1));
        }
        return base32Encode(sb.toString());
    }
    /**
     * 將合并的二值串轉(zhuǎn)為base32串
     *
     * @param str 合并的二值串
     * @return base32串
     */
    private String base32Encode(final String str) {
        String unit = "";
        StringBuilder sb = new StringBuilder();
        for (int start = 0; start < str.length(); start = start + 5) {
            unit = str.substring(start, start + 5);
            sb.append(base32Lookup[convertToIndex(unit)]);
        }
        return sb.toString();
    }
    /**
     * 每五個一組將二進制轉(zhuǎn)為十進制
     *
     * @param str 五個為一個unit
     * @return 十進制數(shù)
     */
    private int convertToIndex(String str) {
        int length = str.length();
        int result = 0;
        for (int index = 0; index < length; index++) {
            result += str.charAt(index) == '0' ? 0 : 1 << (length - 1 - index);
        }
        return result;
    }
    private void convert(double min, double max, double value, int count, List<Character> list) {
        if (list.size() > (count - 1)) {
            return;
        }
        double mid = (max + min) / 2;
        if (value < mid) {
            list.add('0');
            convert(min, mid, value, count, list);
        } else {
            list.add('1');
            convert(mid, max, value, count, list);
        }
    }
    /**
     * 將二值串轉(zhuǎn)換為經(jīng)緯度值
     *
     * @param min  區(qū)間最小值
     * @param max  區(qū)間最大值
     * @param list 二值串列表
     */
    private double revert(double min, double max, List<String> list) {
        double value = 0;
        double mid;
        if (list.size() <= 0) {
            return (max + min) / 2.0;
        }
        for (String flag : list) {
            mid = (max + min) / 2;
            if ("0".equals(flag)) {
                max = mid;
            }
            if ("1".equals(flag)) {
                min = mid;
            }
            value = (max + min) / 2;
        }
        return Double.parseDouble(String.format("%.6f", value));
    }
    /**
     * 分離經(jīng)度與緯度串
     *
     * @param latAndLngStr 經(jīng)緯度二值串
     */
    private GeoHashPoint splitLatAndLng(String latAndLngStr) {
        GeoHashPoint geoHashPoint = new GeoHashPoint();
        // 緯度二值串
        List<String> latList = new ArrayList<>();
       // 經(jīng)度二值串
        List<String> lngList = new ArrayList<>();
        for (int i = 0; i < latAndLngStr.length(); i++) {
//            奇數(shù)位，緯度
            if (i % 2 == 1) {
                latList.add(String.valueOf(latAndLngStr.charAt(i)));
            } else {
//                偶數(shù)位，經(jīng)度
                lngList.add(String.valueOf(latAndLngStr.charAt(i)));
            }
        }
        geoHashPoint.setLatList(latList);
        geoHashPoint.setLngList(lngList);
        return geoHashPoint;
    }
    /**
     * 將十進制數(shù)轉(zhuǎn)為五個二進制數(shù)
     *
     * @param nums 十進制數(shù)
     * @return 五個二進制數(shù)
     */
    private String convertToIndex(List<Integer> nums) {
        StringBuilder str = new StringBuilder();
        for (Integer num : nums) {
            StringBuilder sb = new StringBuilder(Integer.toBinaryString(num));
            int length = sb.length();
            if (length < 5) {
                for (int i = 0; i < 5 - length; i++) {
                    sb.insert(0, "0");
                }
            }
            str.append(sb);
        }
        return str.toString();
    }
    /**
     * 將base32串轉(zhuǎn)為合并的二值串
     *
     * @param str base32串
     * @return 合并的二值串
     */
    private List<Integer> base32Decode(String str) {
        List<Integer> list = new ArrayList<>();
        for (int i = 0; i < str.length(); i++) {
            String ch = String.valueOf(str.charAt(i));
            for (int j = 0; j < base32Lookup.length; j++) {
                if (base32Lookup[j].equals(ch)) {
                    list.add(j);
                }
            }
        }
        return list;
    }
    public static class GeoHashPoint{
        /**
         * 緯度二值串
         */
        private List<String> latList;
        /**
         * 經(jīng)度二值串
         */
        private  List<String> lngList;
        public List<String> getLatList() {
            return latList;
        }
        public void setLatList(List<String> latList) {
            this.latList = latList;
        }
        public List<String> getLngList() {
            return lngList;
        }
        public void setLngList(List<String> lngList) {
            this.lngList = lngList;
        }
    }
    public static void main(String[] args) {
        GeoHashUtil geoHashUtil = new GeoHashUtil();
        // 根據(jù)精度獲取GeoHash串
        String geoHash = geoHashUtil.getGeoHash( 120.234133,30.402616, 12);
        System.out.println(geoHash);
        // 根據(jù)geoHash串獲取中心點經(jīng)緯度
        double[] spaceCoordinate = geoHashUtil.getSpaceCoordinate(geoHash);
        System.out.println(spaceCoordinate[0]+","+spaceCoordinate[1]);
    }
}

數(shù)據(jù)庫生成geometry和geoHash

當(dāng)應(yīng)用中對數(shù)據(jù)進行新增修改操作時，可以在代碼中生成對應(yīng)的geometry和geoHash字段的值。但有時候數(shù)據(jù)不在應(yīng)用中錄入，直接由數(shù)據(jù)工程師寫入的話，就會出現(xiàn)：
① 經(jīng)緯度新增了但是geometry和geoHash字段的值為空

② 經(jīng)緯度更新了但是沒有更新geometry和geoHash字段的值

解決：

① 讓數(shù)據(jù)工程師在寫入經(jīng)緯度的同時幫你存入或更新geometry和geoHash字段的值

② 自己手動執(zhí)行sql語句，重新生成geometry和geoHash字段的值

③ 基于第2步，為表創(chuàng)建觸發(fā)器，當(dāng)對表進行insert或update（update更新經(jīng)緯度字段）操作時，會自動存入或更新geometry和geoHash字段的值

兩個相關(guān)函數(shù)

① ST_GeomFromText 函數(shù)

示例：ST_GeomFromText('POINT(120.1307732446746 30.2678227400894)', 4326)

說明：該函數(shù)返回經(jīng)緯度對應(yīng)的Geometry對象

② st_geohash 函數(shù)

示例：st_geohash(ST_GeomFromText('POINT(120.1307732446746 30.2678227400894)', 4326))

說明： 該函數(shù)返回經(jīng)緯度對應(yīng)的geoHash值

手動執(zhí)行sql

手動執(zhí)行sql, 查詢所有經(jīng)緯度不為空的數(shù)據(jù)，然后更新每條數(shù)據(jù)的geometry和geoHash字段的值

-- 1. 函數(shù)：更新每條數(shù)據(jù)的geometry和geoHash字段的值
create or replace function func_update_geodata() returns text
as $$
declare
    rec record;
begin
    -- 遍歷所有經(jīng)緯度不為空的數(shù)據(jù)
    for rec in select * from 表名 where 經(jīng)緯度 is not null and 經(jīng)緯度 != ''
    LOOP
        update 表名 set pgis_geometry = st_geomfromtext('POINT('|| longitude ||' '|| latitude ||')', 4326),
                             pgis_geohash = st_geohash(st_geomfromtext('POINT('|| longitude ||' '|| latitude ||')', 4326))
        where id = rec.id;
    END LOOP;
    return 'success';
end;
$$ language plpgsql;
-- 2. 調(diào)用
select func_update_geodata();

觸發(fā)器生成geometry和geoHash

-- 1. 創(chuàng)建觸發(fā)器函數(shù)
create or replace function func_generate_geodata_to_mytab() returns trigger as $body$
    begin
        update 表名 set pgis_geometry = st_geomfromtext('POINT('|| longitude ||' '|| latitude ||')', 4326),
                             pgis_geohash = st_geohash(st_geomfromtext('POINT('|| longitude ||' '|| latitude ||')', 4326))                  
        where id = NEW.id;
        RETURN NEW;
    end;
$body$ language plpgsql;
-- 2. 創(chuàng)建觸發(fā)器
create trigger trigger_generate_geodata_to_mytab
after insert or update of 經(jīng)緯度 on 表名
    for each row execute procedure func_generate_geodata_to_mytab();

聚合查詢

使用JPA的原生sql查詢，@Query(nativeQuery = true, value="sql語句")

查詢聚合數(shù)據(jù)

-- 查詢聚合數(shù)據(jù)
select t.geohash                            as geohash,
       st_x(st_pointfromgeohash(t.geohash)) as longitude,
       st_y(st_pointfromgeohash(t.geohash)) as latitude,
       t.count                              as aggregationCount
from (
         select left(pgis_geohash, ?2) as geohash, count(*) as count
         from 表名
         where pgis_geohash is not null
           and pgis_geohash != ''
           and case when ?1 != '' then st_contains(st_geometryfromtext(?1, 4326), pgis_geometry) else 1 = 1 end
         group by geohash) t;
/*
1. 【?1】為頁面?zhèn)鱽淼腤kt數(shù)據(jù)
2. 【?2】為從左邊截取geohash的前幾位
3. st_x(st_pointfromgeohash('geoHash的值')) 、st_y(st_pointfromgeohash('geoHash的值')) 根據(jù)geoHash的值獲取聚合后的中心點坐標(biāo)
*/

查詢聚合詳情

-- 查詢聚合詳情
select *
from 表名
where pgis_geohash is not null and pgis_geohash != ''
  and left(pgis_geohash, ?2) in (?1);
/*
1. 【?1】為geohash值的集合
2. 【?2】為從左邊截取geohash的前幾位
*/

優(yōu)化

geoHash目前聚合后發(fā)現(xiàn)在地圖上展示效果不好，聚合點在地圖上橫豎規(guī)律排布，因此聚合后我們可以在java代碼中進行融合優(yōu)化處理。

思路：

將聚合后的每組聚合點里的點相加，然后除以聚合點的數(shù)量得出一個平均值（可以根據(jù)情況在這個平均數(shù)上乘以一個比例）

遍歷聚合的list，將大于等于平均值的聚合點和小于平均值的聚合點拆開放在兩個集合里（分別為A和B）

遍歷小于平均值的聚合點集合(A)，找到與當(dāng)前點距離最近的高于平均數(shù)的一個聚合點b，把a融合至B

遍歷B，重新計算并設(shè)置融合后的經(jīng)緯度

/**
 * @param list 聚合查詢的結(jié)果
 * @return     優(yōu)化后的聚合結(jié)果
 */
public List optimizationAggregation(List list){
	// 所有聚合點數(shù)量
    long sum = list.stream().mapToLong(T::getCount).sum();
    // 獲取平均數(shù)
    long average = sum / list.size();
    // 大聚合
	List bigList = new ArrayList<>();
    List smallList = new ArrayList<>();
    for (T item : list) {
        if (item.getCount() < average) {
            smallList.add(item);
        } else {
            bigList.add(item);
        }
    }
    Map<T, List<T>> map = new HashMap<>();
    for(T item : bigList){
        map.put(item, new ArrayList<>());
    }
    for(T smallItem : smallList){
        PGpoint smallPoint = smallItem.getGeoPoint();
        int index = -1;
        // 在bigList找出距離當(dāng)前聚合點最近的點
        double minDistance = Double.MAX_VALUE;
        for(int i = 0; i < bigList.size(); i++){
            T bigItem = bigList.get(i);
            PGpoint bigPoint = bigItem.getGeoPoint();
            double distance = GeometryUtil.getDistance(smallPoint.x, smallPoint.y, bigPoint.x, bigPoint.y);
            if(distance >= minDistance){
                continue;
            }
            minDistance = distance;
            index = i;
        }
        T bigItem = bigList.get(index);
        List<T> childList = map.get(bigItem);
        if(null == childList){
            childList = new ArrayList<>();
        }
        childList.add(smallItem);
        map.put(bigItem, childList);
    }
    // 結(jié)果
    List<T> result = new ArrayList<>();
    map.forEach((key, value)->{
        PGpoint parentPoint = key.getGeoPoint();
        value = value.stream().sorted(Comparator.comparing(T::getCount, Comparator.reverseOrder())).collect(Collectors.toList());
        for(T childItem : value){
            PGpoint childPoint = childItem.getGeoPoint();
            double difX = parentPoint.x-childPoint.x;
            double difY = parentPoint.y-childPoint.y;
            double x = parentPoint.x - (new BigDecimal(difX * childItem.getCount()).divide(new BigDecimal(key.getCount()), 15, RoundingMode.HALF_DOWN).doubleValue());
            double y = parentPoint.y - (new BigDecimal(difY * childItem.getCount()).divide(new BigDecimal(key.getCount()), 15, RoundingMode.HALF_DOWN).doubleValue());
            PGpoint pGpoint = new PGpoint(x, y);
            key.setGeoPoint(pGpoint);
            key.setLongitude(String.valueOf(x));
            key.setLatitude(String.valueOf(y));
            key.setCount(key.getCount() + childItem.getCount());
            if(null == key.getGeohashSet()){
                key.setGeohashSet(new HashSet<>());
            }
            key.getGeohashSet().add(childItem.getGeohash());
        }
        result.add(key);
    });
    return result;
}

到此這篇關(guān)于PostgreSQL+GeoHash地圖點位聚合的文章就介紹到這了,更多相關(guān)PostgreSQL地圖點位聚合內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: