PostgreSQL+GeoHash地圖點位聚合實現代碼
更新時間：2023年07月28日 14:43:10 作者：CharltonW
這篇文章主要介紹了PostgreSQL+GeoHash地圖點位聚合,本文通過實例代碼給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下
PG數據庫安裝擴展
需要用到pg數據庫的空間擴展postgis，在進行操作之前需要在數據庫中安裝擴展。
CREATE EXTENSION postgis; CREATE EXTENSION postgis_topology; CREATE EXTENSION postgis_geohash;
GeoHash
GeoHash是一種地址編碼方法。他能夠把二維的空間經緯度數據編碼成一個字符串。具體原理這里不再詳細說明，GeoHash算法大體上分為三步：
- 將經緯度變成二進制
- 將經緯度的二進制合并
- 通過Base32對合并后的二進制進行編碼
Geohash比直接用經緯度的高效很多，而且使用者可以發(fā)布地址編碼，既能表明自己位于北海公園附近，又不至于暴露自己的精確坐標，有助于隱私保護。
- GeoHash用一個字符串表示經度和緯度兩個坐標。在數據庫中可以實現在一列上應用索引（某些情況下無法在兩列上同時應用索引）
- GeoHash表示的并不是一個點，而是一個矩形區(qū)域
- GeoHash編碼的前綴可以表示更大的區(qū)域。例如wx4g0ec1，它的前綴wx4g0e表示包含編碼wx4g0ec1在內的更大范圍。 這個特性可以用于附近地點搜索
- 編碼越長，表示的范圍越小，位置也越精確。因此我們就可以通過比較GeoHash匹配的位數來判斷兩個點之間的大概距離
建表
在創(chuàng)建數據庫表時，表中除了經緯度字段以外，再創(chuàng)建兩個字段：
① 經緯度對應的Geometry字段（類型：geometry）
② 經緯度對應的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字段
根據經緯度計算 geometry 和 geoHash
Java生成geometry和geoHash
geometry字段 和 geoHash字段均可以在java代碼中根據經緯度生成。
根據經緯度生成geometry
使用org.locationtech.jts.io包下的WKTReader類，可以根據經緯度生成Geometry對象。
String wkt = "POINT("+longitude+" "+latitude+")"; // longitude 經度，latitude緯度
WKTReader wktReader = new WKTReader();
Geometry geometry = wktReader.read(wkt); // Geometry對象
if(geometry!=null) {
geometry.setSRID(4326);
}根據經緯度生成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"
};
/**
* 根據geoHash串獲取中心點經緯度
* @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};
}
/**
* 根據精度獲取GeoHash串
* @param lng 經度 x
* @param lat 緯度 y
* @param precise 精度
* @return
*/
public String getGeoHash( double lng, double lat, int precise) {
// 緯度二值串長度
int latLength;
// 經度二值串長度
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);
}
/**
* 經緯度二值串合并：偶數位放經度，奇數位放緯度，把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);
// 獲取經度二值串
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));
}
// 如果二者長度不一樣，說明要求的精度為奇數，經度長度比緯度長度大1
if (lngLength != latLength) {
sb.append(lngList.get(lngList.size() - 1));
}
return base32Encode(sb.toString());
}
/**
* 將合并的二值串轉為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();
}
/**
* 每五個一組將二進制轉為十進制
*
* @param str 五個為一個unit
* @return 十進制數
*/
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);
}
}
/**
* 將二值串轉換為經緯度值
*
* @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));
}
/**
* 分離經度與緯度串
*
* @param latAndLngStr 經緯度二值串
*/
private GeoHashPoint splitLatAndLng(String latAndLngStr) {
GeoHashPoint geoHashPoint = new GeoHashPoint();
// 緯度二值串
List<String> latList = new ArrayList<>();
// 經度二值串
List<String> lngList = new ArrayList<>();
for (int i = 0; i < latAndLngStr.length(); i++) {
// 奇數位，緯度
if (i % 2 == 1) {
latList.add(String.valueOf(latAndLngStr.charAt(i)));
} else {
// 偶數位，經度
lngList.add(String.valueOf(latAndLngStr.charAt(i)));
}
}
geoHashPoint.setLatList(latList);
geoHashPoint.setLngList(lngList);
return geoHashPoint;
}
/**
* 將十進制數轉為五個二進制數
*
* @param nums 十進制數
* @return 五個二進制數
*/
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串轉為合并的二值串
*
* @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;
/**
* 經度二值串
*/
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();
// 根據精度獲取GeoHash串
String geoHash = geoHashUtil.getGeoHash( 120.234133,30.402616, 12);
System.out.println(geoHash);
// 根據geoHash串獲取中心點經緯度
double[] spaceCoordinate = geoHashUtil.getSpaceCoordinate(geoHash);
System.out.println(spaceCoordinate[0]+","+spaceCoordinate[1]);
}
}數據庫生成geometry和geoHash
當應用中對數據進行新增修改操作時，可以在代碼中生成對應的geometry和geoHash字段的值。但有時候數據不在應用中錄入，直接由數據工程師寫入的話，就會出現：
① 經緯度新增了但是geometry和geoHash字段的值為空② 經緯度更新了但是沒有更新geometry和geoHash字段的值
解決：
① 讓數據工程師在寫入經緯度的同時幫你存入或更新geometry和geoHash字段的值
② 自己手動執(zhí)行sql語句，重新生成geometry和geoHash字段的值
③ 基于第2步，為表創(chuàng)建觸發(fā)器，當對表進行insert或update（update更新經緯度字段）操作時，會自動存入或更新geometry和geoHash字段的值
兩個相關函數
① ST_GeomFromText 函數
示例：ST_GeomFromText('POINT(120.1307732446746 30.2678227400894)', 4326)
說明：該函數返回經緯度對應的Geometry對象
② st_geohash 函數
示例：st_geohash(ST_GeomFromText('POINT(120.1307732446746 30.2678227400894)', 4326))
說明： 該函數返回經緯度對應的geoHash值
手動執(zhí)行sql
手動執(zhí)行sql, 查詢所有經緯度不為空的數據，然后更新每條數據的geometry和geoHash字段的值
-- 1. 函數：更新每條數據的geometry和geoHash字段的值
create or replace function func_update_geodata() returns text
as $$
declare
rec record;
begin
-- 遍歷所有經緯度不為空的數據
for rec in select * from 表名 where 經緯度 is not null and 經緯度 != ''
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. 調用
select func_update_geodata();觸發(fā)器生成geometry和geoHash
-- 1. 創(chuàng)建觸發(fā)器函數
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 經緯度 on 表名
for each row execute procedure func_generate_geodata_to_mytab();聚合查詢
使用JPA的原生sql查詢，@Query(nativeQuery = true, value="sql語句")
查詢聚合數據
-- 查詢聚合數據
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數據
2. 【?2】為從左邊截取geohash的前幾位
3. st_x(st_pointfromgeohash('geoHash的值')) 、st_y(st_pointfromgeohash('geoHash的值')) 根據geoHash的值獲取聚合后的中心點坐標
*/查詢聚合詳情
-- 查詢聚合詳情 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ā)現在地圖上展示效果不好，聚合點在地圖上橫豎規(guī)律排布，因此聚合后我們可以在java代碼中進行融合優(yōu)化處理。
思路：
將聚合后的每組聚合點里的點相加，然后除以聚合點的數量得出一個平均值（可以根據情況在這個平均數上乘以一個比例）
遍歷聚合的list，將大于等于平均值的聚合點和小于平均值的聚合點拆開放在兩個集合里（分別為A和B）
遍歷小于平均值的聚合點集合(A)，找到與當前點距離最近的高于平均數的一個聚合點b，把a融合至B
遍歷B，重新計算并設置融合后的經緯度
/**
* @param list 聚合查詢的結果
* @return 優(yōu)化后的聚合結果
*/
public List optimizationAggregation(List list){
// 所有聚合點數量
long sum = list.stream().mapToLong(T::getCount).sum();
// 獲取平均數
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找出距離當前聚合點最近的點
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);
}
// 結果
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;
}到此這篇關于PostgreSQL+GeoHash地圖點位聚合的文章就介紹到這了,更多相關PostgreSQL地圖點位聚合內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
使用python-slim鏡像遇到無法使用PostgreSQL的問題及解決方法
這篇文章主要介紹了使用python-slim鏡像遇到無法使用PostgreSQL的問題及解決方法,本文給大家介紹的非常詳細,感興趣的朋友跟隨小編一起看看吧2024-08-08
postgresql實現對已有數據表分區(qū)處理的操作詳解
這篇文章主要為大家詳細介紹了postgresql實現對已有數據表分區(qū)處理的操作的相關知識,文中的示例代碼講解詳細,感興趣的小伙伴可以跟隨小編一起學習一下2023-12-12
這篇文章主要介紹了PostgreSQL 如何修改文本類型字段的存儲方式，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
這篇文章主要介紹了PostgreSQL 刪除check約束的實現，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-02-02
這篇文章主要介紹了PostgreSQL 查找當前數據庫的所有表操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
postgresql數據庫配置文件postgresql.conf，pg_hba.conf,pg_ident.conf
這篇文章主要為大家介紹了postgresql數據庫中三個重要的配置文件postgresql.conf，pg_hba.conf,pg_ident.conf使用示例詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2023-02-02
這篇文章主要介紹了postgresql數據庫使用說明_實現時間范圍查詢，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
Windows?系統(tǒng)?PostgreSQL?手工安裝配置方法
這篇文章主要介紹了Windows?系統(tǒng)?PostgreSQL?手工安裝配置方法,本文主要說一下在?Windows?系統(tǒng)中安裝?PostgreSQL?的方法，我這里沒有采用?exe?安裝包的形式去安裝，EDB?發(fā)布的那個?exe?安裝包形式的對于中文環(huán)境數據庫的排序規(guī)則設定有問題，需要的朋友可以參考下2022-09-09
這篇文章主要介紹了PostgreSQL 恢復誤刪數據的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
在使用PostgreSQL數據庫時,忘記數據庫密碼可能會影響到正常的開發(fā)和維護工作,這篇文章主要介紹了Postgresql數據庫密碼忘記的詳細解決方法,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2025-06-06