PostgreSQL+GeoHash map point aggregation implementation code

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

@Type(type="jts_geometry")
@Column(name="geometry",columnDefinition = "geometry(Point,4326)")
@JsonIgnore
private Geometry geometry; // Entity classGeometryFields

String wkt = "POINT("+longitude+" "+latitude+")"; // longitude longitude, latitudeWKTReader wktReader = new WKTReader();
Geometry geometry = (wkt); // Geometry objectif(geometry!=null) {
    (4326);
}

import .;
import ;
import ;
import ;
@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"
    };
    /**
      * Obtain the latitude and longitude of the center point according to the geoHash string
      * @param geoHashCode
      * @return lng->x lat->y
      */
    public double[] getSpaceCoordinate(String geoHashCode) {
        if((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, ());
        double x = revert(Min_Lng, Max_Lng, ());
        return new double[]{x, y};
    }
    /**
      * Get GeoHash string according to accuracy
      * @param lng longitude x
      * @param lat y
      * @param precision
      * @return
      */
    public String getGeoHash( double lng, double lat, int precise) {
        // Latitude binary string length        int latLength;
        // Longitude binary string length        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);
    }
    /**
      *Large and longitude binary string merge: even-digit longitude, odd-digit latitude, and two strings encoded and combined to generate a new string
      *
      */
    public String encode(double lat, double lng, int latLength, int lngLength) {
        if (latLength < 1 || lngLength < 1) {
            return ;
        }
        List<Character> latList = new ArrayList<>(latLength);
        List<Character> lngList = new ArrayList<>(lngLength);
        // Get the binary string of dimensions        convert(Min_Lat, Max_Lat, lat, latLength, latList);
        // Get the longitude binary string        convert(Min_Lng, Max_Lng, lng, lngLength, lngList);
        StringBuilder sb = new StringBuilder();
        for (int index = 0; index < (); index++) {
            ((index)).append((index));
        }
// If the lengths of the two are different, it means that the required accuracy is odd, and the longitude length is 1 larger than the latitude length        if (lngLength != latLength) {
            ((() - 1));
        }
        return base32Encode(());
    }
    /**
      * Convert the merged binary string to a base32 string
      *
      * @param str merged binary string
      * @return base32 string
      */
    private String base32Encode(final String str) {
        String unit = "";
        StringBuilder sb = new StringBuilder();
        for (int start = 0; start < (); start = start + 5) {
            unit = (start, start + 5);
            (base32Lookup[convertToIndex(unit)]);
        }
        return ();
    }
    /**
      * Convert binary to decimal in every five groups
      *
      * @param str Five for one unit
      * @return Decimal number
      */
    private int convertToIndex(String str) {
        int length = ();
        int result = 0;
        for (int index = 0; index < length; index++) {
            result += (index) == '0' ? 0 : 1 << (length - 1 - index);
        }
        return result;
    }
    private void convert(double min, double max, double value, int count, List<Character> list) {
        if (() > (count - 1)) {
            return;
        }
        double mid = (max + min) / 2;
        if (value < mid) {
            ('0');
            convert(min, mid, value, count, list);
        } else {
            ('1');
            convert(mid, max, value, count, list);
        }
    }
    /**
      * Convert binary string to latitude and longitude values
      *
      * @param min interval minimum
      * @param max interval maximum
      * @param list binary string list
      */
    private double revert(double min, double max, List<String> list) {
        double value = 0;
        double mid;
        if (() <= 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 (("%.6f", value));
    }
    /**
      * Separate longitude and latitude string
      *
      * @param latAndLngStr latitude and longitude binary string
      */
    private GeoHashPoint splitLatAndLng(String latAndLngStr) {
        GeoHashPoint geoHashPoint = new GeoHashPoint();
        // Latitude binary string        List<String> latList = new ArrayList<>();
       // Longitude binary string        List<String> lngList = new ArrayList<>();
        for (int i = 0; i < (); i++) {
// Odd number, latitude            if (i % 2 == 1) {
                (((i)));
            } else {
// Even number, longitude                (((i)));
            }
        }
        (latList);
        (lngList);
        return geoHashPoint;
    }
    /**
      * Convert decimal numbers to five binary numbers
      *
      * @param nums decimal number
      * @return Five binary numbers
      */
    private String convertToIndex(List<Integer> nums) {
        StringBuilder str = new StringBuilder();
        for (Integer num : nums) {
            StringBuilder sb = new StringBuilder((num));
            int length = ();
            if (length < 5) {
                for (int i = 0; i < 5 - length; i++) {
                    (0, "0");
                }
            }
            (sb);
        }
        return ();
    }
    /**
      * Convert base32 string into a merged binary string
      *
      * @param str base32 string
      * @return Merged binary string
      */
    private List<Integer> base32Decode(String str) {
        List<Integer> list = new ArrayList<>();
        for (int i = 0; i < (); i++) {
            String ch = ((i));
            for (int j = 0; j < ; j++) {
                if (base32Lookup[j].equals(ch)) {
                    (j);
                }
            }
        }
        return list;
    }
    public static class GeoHashPoint{
        /**
          * Latitude binary string
          */
        private List<String> latList;
        /**
          * Longitude binary string
          */
        private  List<String> lngList;
        public List<String> getLatList() {
            return latList;
        }
        public void setLatList(List<String> latList) {
             = latList;
        }
        public List<String> getLngList() {
            return lngList;
        }
        public void setLngList(List<String> lngList) {
             = lngList;
        }
    }
    public static void main(String[] args) {
        GeoHashUtil geoHashUtil = new GeoHashUtil();
        // Get GeoHash string according to accuracy        String geoHash = ( 120.234133,30.402616, 12);
        (geoHash);
        // Obtain the latitude and longitude of the center point according to the geoHash string        double[] spaceCoordinate = (geoHash);
        (spaceCoordinate[0]+","+spaceCoordinate[1]);
    }
}

-- 1. function：Update each datageometryandgeoHashThe value of the field
create or replace function func_update_geodata() returns text
as $$
declare
    rec record;
begin
    -- Iterate through all data that are not empty in latitude and longitude
    for rec in select * from Table name where Latitude and longitude is not null and Latitude and longitude != ''
    LOOP
        update Table name set pgis_geometry = st_geomfromtext('POINT('|| longitude ||' '|| latitude ||')', 4326),
                             pgis_geohash = st_geohash(st_geomfromtext('POINT('|| longitude ||' '|| latitude ||')', 4326))
        where id = ;
    END LOOP;
    return 'success';
end;
$$ language plpgsql;
-- 2. Call
select func_update_geodata();

-- 1. Create a trigger function
create or replace function func_generate_geodata_to_mytab() returns trigger as $body$
    begin
        update Table name set pgis_geometry = st_geomfromtext('POINT('|| longitude ||' '|| latitude ||')', 4326),
                             pgis_geohash = st_geohash(st_geomfromtext('POINT('|| longitude ||' '|| latitude ||')', 4326))                  
        where id = ;
        RETURN NEW;
    end;
$body$ language plpgsql;
-- 2. Create a trigger
create trigger trigger_generate_geodata_to_mytab
after insert or update of Latitude and longitude on Table name
    for each row execute procedure func_generate_geodata_to_mytab();

-- Query aggregated data
select                             as geohash,
       st_x(st_pointfromgeohash()) as longitude,
       st_y(st_pointfromgeohash()) as latitude,
                                     as aggregationCount
from (
         select left(pgis_geohash, ?2) as geohash, count(*) as count
         from Table name
         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】Wkt data transmitted from the page
 2. [?2] It is to intercept the first few geohash from the left
 3. st_x(st_pointfromgeohash('geoHash')) , st_y(st_pointfromgeohash('geoHash')) Obtain the aggregated center point coordinates according to the value of geoHash
 */

-- Query aggregation details
select *
from Table name
where pgis_geohash is not null and pgis_geohash != ''
  and left(pgis_geohash, ?2) in (?1);
/*
 1. 【?1】A collection of geohash values
 2. [?2] It is to intercept the first few geohash from the left
 */

/**
  * @param list results of aggregate query
  * @return Optimized aggregation results
  */
public List optimizationAggregation(List list){
	// Number of all aggregation points    long sum = ().mapToLong(T::getCount).sum();
    // Get the average    long average = sum / ();
    // Big aggregation	List bigList = new ArrayList<>();
    List smallList = new ArrayList<>();
    for (T item : list) {
        if (() < average) {
            (item);
        } else {
            (item);
        }
    }
    Map<T, List<T>> map = new HashMap<>();
    for(T item : bigList){
        (item, new ArrayList<>());
    }
    for(T smallItem : smallList){
        PGpoint smallPoint = ();
        int index = -1;
        // Find the closest point to the current aggregation point in bigList        double minDistance = Double.MAX_VALUE;
        for(int i = 0; i < (); i++){
            T bigItem = (i);
            PGpoint bigPoint = ();
            double distance = (, , , );
            if(distance >= minDistance){
                continue;
            }
            minDistance = distance;
            index = i;
        }
        T bigItem = (index);
        List<T> childList = (bigItem);
        if(null == childList){
            childList = new ArrayList<>();
        }
        (smallItem);
        (bigItem, childList);
    }
    // result    List<T> result = new ArrayList<>();
    ((key, value)->{
        PGpoint parentPoint = ();
        value = ().sorted((T::getCount, ())).collect(());
        for(T childItem : value){
            PGpoint childPoint = ();
            double difX = ;
            double difY = ;
            double x =  - (new BigDecimal(difX * ()).divide(new BigDecimal(()), 15, RoundingMode.HALF_DOWN).doubleValue());
            double y =  - (new BigDecimal(difY * ()).divide(new BigDecimal(()), 15, RoundingMode.HALF_DOWN).doubleValue());
            PGpoint pGpoint = new PGpoint(x, y);
            (pGpoint);
            ((x));
            ((y));
            (() + ());
            if(null == ()){
                (new HashSet<>());
            }
            ().add(());
        }
        (key);
    });
    return result;
}