Spatial query optimization method based on Salted Key

Abstract

The invention discloses a space query optimization method based on a Salted Key. The method comprises the following steps: step 1, data cleaning and format conversion; step 2, converting geographic coordinates of the converted data obtained in the step 1 into Geohash codes; 3, completing data storage by adopting multiple dimensions of time and space; and 4, querying the data stored in the distributed database HBase, and obtaining a query result through two query modes. The invention belongs to the technical field of geographic information systems and data storage, solves the problems of low geographic data storage efficiency and poor query performance of large-scale geographic information in the prior art, is particularly suitable for large-scale geographic data storage and real-time query, can process a large amount of data without sacrificing performance, and is high in practicability. And application requirements of traffic flow, weather forecast and the like which need to consider space and time factors at the same time can be met.

Description

Technical Field

[0001] The present invention belongs to the technical fields of geographic information systems and data storage, and relates to a spatial query optimization method based on Salted Key. Background Art

[0002] In modern information technology, as a technology for processing and analyzing geospatial data, the Geographic Information System (GIS) is widely used in fields such as urban planning, traffic management, environmental monitoring, and public safety. With the rapid development of smart city and big data technologies, the collection, storage, and analysis of geographic information have become one of the key tasks. Currently, many geographic information data storage solutions still rely on traditional relational databases or NoSQL databases. These databases usually store geographic information (such as longitude and latitude) by converting it into text strings or other simple formats. The storage and query of spatial data usually involve complex spatial indexes.

[0003] The existing technologies have the following problems when dealing with spatial queries: 1) Query performance bottleneck: Traditional database systems, such as MySQL or MongoDB, cannot provide efficient query performance when dealing with data based on spatial location. Especially when performing large-scale range queries, the query latency and throughput often fail to meet real-time requirements.

[0004] 2) Low storage efficiency: Traditional spatial data storage methods usually adopt simple text storage formats (such as storing longitude and latitude as ordinary strings), which will lead to waste of storage space. Especially for large-scale data sets, the storage and retrieval efficiency is low.

[0005] 3) Lack of spatial index structure suitable for big data scenarios: Although current databases have introduced some spatial index technologies, such as R-tree, Quad-tree, and BSP-tree, etc., when these index methods are applied to large-scale data sets, problems such as decreased query performance and high index construction costs will still be encountered. In a distributed system environment, the construction and maintenance of these indexes also become more complex.

[0006] 4) Limitations of geocoding methods: In order to improve the efficiency of spatial data queries, many solutions adopt geocoding technologies, such as Geohash, S2, etc. Geohash converts geographical coordinates (longitude and latitude) into a string, making adjacent geographical locations also have similarity in coding, which is convenient for querying by spatial location. However, the existing Geohash coding methods have certain limitations. Especially in the storage and query of high-precision geographic data, it is difficult to balance precision and storage efficiency, and the support for large-scale geographic data sets is still insufficient.

[0007] 5) Fusion problem of geographical data and time data: In addition to spatial location, many geographical information also needs to be analyzed in combination with time data (such as year, month, moment, etc.). For example, in applications such as traffic flow monitoring and weather forecasting, the combination of geographical data and time data is crucial. Existing methods often lack effective means to process data in both spatial and temporal dimensions simultaneously, making the storage and query of geographical data more complex.

[0008] Therefore, although existing technologies have made a lot of efforts in the storage and query of geographical information, there are still significant performance and efficiency problems when facing the storage and query requirements of large-scale, high-precision, and real-time geographical data. With the continuous development of the Internet of Things (IoT) and big data technologies, how to store, index, and query large-scale geographical information more efficiently has become an urgent technical problem to be solved. Summary of the Invention

[0009] The object of the present invention is to provide a spatial query optimization method based on Salted Key, which solves the problems of low storage efficiency of geographical data and poor query performance in the prior art for large-scale geographical information.

[0010] The technical solution adopted by the present invention is that the spatial query optimization method based on Salted Key is implemented according to the following steps: Step 1, data cleaning and format conversion; Step 2, convert the geographical coordinates of the data obtained after conversion in Step 1 into Geohash codes; Step 3, complete data storage by adopting multiple dimensions of time and space; Step 4, query the data stored in the distributed database HBase, and obtain the query results through two query methods.

[0011] The beneficial effects of the present invention are as follows: By encoding geographical information (such as longitude and latitude) into Geohash values and combining time information and administrative division codes, the query mechanism is optimized, which is efficient and has a high spatial density, enabling fast response even under large-scale data sets. Specifically, it includes the following aspects: (1) Efficient data storage and query: By using Geohash encoding as the row key, the present invention can effectively utilize the characteristics of spatial adjacency, reduce the waste of storage space, and improve query performance.

[0012] (2) Support for multi-dimensional query: By combining time and space dimensions, the present invention supports efficient multi-dimensional data query, and can meet the application requirements that need to consider both spatial and time factors, such as traffic flow and weather forecasting.

[0013] (3) Good scalability and fault tolerance: The present invention is based on the distributed storage of HBase, has good scalability, can handle massive geospatial data, and has high fault tolerance.

[0014] (4) Fast geographical range query: The present invention adopts Geohash encoding, enabling geographically adjacent locations to be stored centrally, and realizing fast geographical range query through prefix matching and range filtering. Administrative division encoding can further narrow the query range and improve query efficiency; timestamps can filter out data outside this time range or time point, thus reducing the query burden. Combining the three can further narrow the query range and improve query efficiency.

[0015] (5) Adapt to big data scenarios: The present invention optimizes data storage and query methods, is applicable to large-scale geodata storage and real-time query, and can process a large amount of data without sacrificing performance. BRIEF DESCRIPTION OF THE DRAWINGS

[0016] Figure 1 It is a framework diagram of geospatial data storage based on Geohash encoding for the method of the present invention; Figure 2 It is a schematic diagram of Geohash encoding in Embodiment 1 of the method of the present invention; Figure 3 It is a block diagram of a distributed data storage structure based on HBase in the method of the present invention; Figure 4 It is a query flow chart in the method of the present invention; Figure 5 It is a block diagram of setting RowKey based on Salted Key in the method of the present invention. DETAILED DESCRIPTION OF THE INVENTION

[0017] The present invention will be described in detail below in conjunction with the drawings and specific embodiments.

[0018] The method for optimizing spatial query based on Salted Key of the present invention is implemented according to the following steps: Step 1, data cleaning and format conversion, the specific process is as follows: First, fill in invalid values (such as -9999) and remove noise points from the original DEM data to ensure the continuity of elevation data; Then, use tools such as GDAL (Geospatial Data Abstraction Library) for projection transformation to ensure that the output coordinates are in decimal longitude and latitude format.

[0019] Step 2: For the converted data obtained in Step 1, convert the geographical coordinates (longitude and latitude) into Geohash codes (Step 1 completes the projection transformation, and Step 2 then converts the result of Step 1). The specific process is as follows: 2.1) Use a range division method similar to the binary search method for the longitude and latitude coordinates of each pixel point in the DEM model (Digital Elevation Model) to obtain the binary code corresponding to the longitude and the binary code corresponding to the latitude; 2.2) Integrate the binary code corresponding to the longitude and the binary code corresponding to the latitude obtained in Step 2.1) in the way that the odd bits store the latitude and the even bits store the longitude; 2.3) Group the integrated longitude and latitude binary numbers into groups of 5 numbers in the order from left to right, and convert each group into a decimal number. If the total number of digits is not a multiple of 5, pad 0 at the end until the grouping is complete; 2.4) Convert the result obtained in Step 2.3) using the Base32 corresponding encoding to obtain the Geohash code corresponding to the longitude and latitude information in the DEM data.

[0020] Step 3: Complete data storage using multiple dimensions of time and space. During the storage process, combine the Geohash code obtained in Step 2 with the time dimension (such as year, month) and the administrative division code of the region as the Rowkey of the HBase database; moreover, add the longitude and latitude information and elevation information in the DEM data to the columns latitude, longitude, and elevation under the column family info in HBase.

[0021] As Figure 2 shown, it is the Rowkey design and storage process. Input the longitude and latitude, obtain the Geohash code, and combine the Geohash code with the timestamp and the administrative division code of the region as the Rowkey and add it to HBase.

[0022] As Figure 5 shown, use the designed GTC (Geohash combined with the timestamp and the administrative division code) as the Rowkey, use the longitude and latitude and elevation and other information as the data domain, and store the DEM data in the distributed database HBase. Among them, Salt is the salt value, the Rowkey is the unique identifier of each row of data in the table in the HBase database, Data is the data domain part in the table in the HBase database, and mainly contains three columns of content, that is, Longitude stores the longitude information, Latitude stores the latitude information, and Elevation stores the elevation value.

[0023] AsFigure 3 As shown, the stored procedure is as follows: First, obtain the data source, clean the data in the data source, convert the cleaned data, and convert the longitude and latitude into Geohash codes; after converting the longitude and latitude into Geohash codes, use the timestamp and the administrative division code of the region as salt values, combine them with the Geohash code to generate the RowKey, and finally store it in the HBase database.

[0024] The specific stored procedure is as follows: First, the client obtains the Region location of the HBase:meta table through ZooKeeper, determines the RegionServer where the target Region is located according to the RowKey, establishes a connection and sends a write request; the data is sequentially appended to the WAL (Write-Ahead Log), ensuring that even if the in-memory data is lost, it can be restored through the log. The WAL is stored in the form of HDFS files, supporting fault recovery and master-slave cluster data synchronization; the data is written into the MemStore (in-memory buffer) of the corresponding Region, sorted by RowKey and column family to form an ordered key-value pair; (the design of the MemStore avoids the overhead of directly writing to the disk and supports high-concurrency writing.) When the MemStore reaches the threshold (default 128MB) or a periodic flush is triggered, the data is asynchronously flushed to HDFS to generate an HFile; the HFile is stored by column family, and internally uses a block index and a time range index to achieve optimization and improve subsequent query efficiency. Figure 3 In this, ZK is ZooKeeper (a distributed coordination service framework). HMaster and HRegionserver are the two core components of HBase. HMaster is mainly responsible for source data management and cluster coordination, and HRegionserver is the core of data storage and processing, which can respond to the read and write requests of the client.

[0025] Step 4, query the data stored in the distributed database HBase, and obtain the query results through two methods. As Figure 4 shown, the query methods include two (used simultaneously), which are described as follows: Query method 1, spatial query of a rectangular area: According to the upper right vertex (lat1, lon1), lower left vertex (lat2, lon2) of the query area, the query time range t0~t1, and the administrative division code M of the region, obtain the result set A that meets the query conditions; The detailed implementation process is as follows: Convert the latitudes and longitudes of two vertices into Geohash codes G1 and G2; Combine G1, G2 with the query times t0, t1 and the administrative division code M of this area to obtain the start and end row keys r1, r2 of the query, scan the data in the distributed database HBase whose Rowkey is between r1 and r2 to obtain the result set B, filter the result set B through the HBase filter to obtain the result set A, and the result set A obtained by the query is the final result set.

[0026] Query method 2, spatial query of a circular area: According to the upper right vertex (lat1, lon1), lower left vertex (lat2, lon2) of the query area, the query time range t0~t1 and the administrative division code M of this area, obtain the result set A that meets the query conditions; The detailed implementation process is as follows: Convert the latitudes and longitudes of two vertices into Geohash codes G1 and G2, combine G1, G2 with the query times t0, t1 and the administrative division code M of this area to obtain the start and end row keys r1, r2 of the query, scan the data in the distributed database HBase whose Rowkey is between r1 and r2 to obtain the result set B, then filter the result set B through the HBase filter to obtain the result set A1, filter the result set A1 through the query radius d to obtain the result set A, and the result set A obtained by the query is the final result set.

[0027] Embodiment 1 Step 1, data cleaning and format conversion. Select the 30-meter DEM data (Digital Elevation Model) of Beijing, Anhui, and Fujian as the data source, fill in the invalid values of the original DEM data (preferably with a value of -9999), remove the noise points, and remove the data with elevation values greater than 1000m and less than -50m to ensure the continuity of the elevation data. Convert the original geographic coordinates into the latitude and longitude format suitable for Geohash coding, and use the GDAL (Geospatial Data Abstraction Library) tool for projection transformation.

[0028] Step 2, set the number of digits of the Geohash code to 12, and convert the result obtained in Step 1 into a Geohash code by converting the geographic coordinates (latitude and longitude).

[0029] Such as Figure 1As shown in the figure, the specific implementation process is as follows: The longitude and latitude coordinates of each pixel point in the DEM data are divided by a method similar to the binary search method to obtain the binary code corresponding to the longitude and the binary code corresponding to the latitude. Then, the above codes are integrated in the way that the odd bits store the latitude and the even bits store the longitude. Next, the longitude and latitude binary numbers obtained by the integration are grouped into groups of five numbers each, and each group is converted into a decimal number. Finally, the result obtained in the previous step is converted using the Base32 corresponding code, and finally the Geohash code corresponding to the longitude and latitude information in the DEM data is obtained.

[0030] Step 3: Adopt multi-dimensional data storage of time and space, and store the DEM data in the distributed database HBase. The storage structure of HBase is as Figure 2 shown. During the storage process, the Geohash code obtained in Step 2 is combined with the minute-level time dimension (format: YYYYMMDDHHmm) and the administrative division code of this area as the Rowkey of the HBase database. The column family is designed as info:latitude (WGS84), longitude (WGS84), elevation (meter).

[0031] Step 4: Query the data stored in the distributed database HBase in Step 3. The specific query methods include two types, which are described as follows: Query Method 1: Spatial query of a rectangular area: According to the upper right vertex (lat1, lon1), lower left vertex (lat2, lon2) of the query area, the query time range t0~t1, and the administrative division code M of this area, the result set A that meets the query conditions is obtained. The detailed implementation process is to convert the longitude and latitude of the two vertices into Geohash codes G1 and G2. Combine G1, G2 with the query time t0, t1, and the administrative division code M of this area to obtain the start and end row keys r1, r2 of the query. Scan the data in HBase whose Rowkey is between r1 and r2 to obtain the result set B, and filter B through the HBase filter to obtain the result set A, and return the result set A.

[0032] Query Method 2, Spatial Query of Circular Area: According to the upper right vertex (lat1, lon1), lower left vertex (lat2, lon2) of the query area, the time range t0~t1 for query, and the administrative division code M of this area, the result set A that meets the query conditions is obtained. The detailed implementation process is as follows: Convert the longitude and latitude of the two vertices into Geohash codes G1 and G2, combine G1, G2 with the query time t0, t1 and the administrative division code M of this area to obtain the start and end row keys r1 and r2 for the query, scan the data in HBase whose Rowkey is between r1 and r2 to obtain the result set B, filter B through the HBase filter to obtain the result set A1, filter the result set A1 through the query radius d to obtain the result set A, and return the result set A.

[0033] The test data parameters are as follows: The area range is selected as Dongcheng District, Beijing, the geographical coordinate range is (longitude 116.397°E~116.417°E, latitude 39.91°N~39.93°N), the time range is selected as 07:00~09:00 on March 14, 2025 simulated, and the time format is YYYYMMDDHHmm. The test radius: a 1-kilometer circular area, the center point coordinates are (116.407°E, 39.92°N), and the Geohash precision is 12 bits. The method of using "Geohash + time + administrative division code" as the Rowkey is Scheme A (i.e., the method of the present invention), the method of using "Geohash + time" as the Rowkey is Scheme B, the method of using "Geohash + longitude + latitude" as the Rowkey is Scheme C, and the method of using "longitude + latitude" is Scheme D. The differences in the storage and query performance of the above several schemes are shown in Table 1 below.

[0034] During the implementation of this Embodiment 1, some optimizations were made to all queries and storage, including modifying the internal configuration file of Hadoop to improve the performance of data storage and query. For Solution D in this Embodiment 1, if no optimization is done, a full-table scan is required, and the query time will be much longer than it is now. In this Embodiment 1, the logical code for query and storage of Solution D was optimized. Double filtering of RowFilter (prefix matching) + SingleColumnValueFilter (latitude and longitude range filtering) was adopted to reduce the data transfer volume. Two SingleColumnValueFilters were created to perform double-boundary filtering (lower limit and upper limit) on longitude and latitude respectively, and the FilterList combination logic was used. By setting scan.setCaching(1000) and scan.setBatch(500), the batch reading efficiency was improved. When the data volume is extremely large, in this Embodiment 1, the number of nodes was expanded, and the original data was cropped through the Warp method of GDAL (Geospatial Data Abstraction Library, an open-source geospatial data processing library) to reduce the data volume. When creating an HBase table, Snappy compression was enabled through the column family attribute COMPRESSION =>'SNAPPY', and physical compression was triggered through "major_compact", which can greatly reduce the physical space occupied by the HBase table.

[0035] Table 1. Comparative differences in the performance of the four solutions

[0036] Embodiment 2 Step 1, data cleaning and format conversion. Select the annual 30m cultivated land dataset in Guangyuan City, Sichuan Province as the original DEM data, fill in invalid values (such as -9999) and remove noise points from the original DEM data to ensure the continuity of elevation data. Convert the original geographic coordinates into the latitude and longitude format suitable for Geohash encoding, and use the GDAL tool for projection transformation.

[0037] Step 2: Set the Geohash encoding digits to 8, and convert the result obtained in Step 1 by converting the geographical coordinates (latitude and longitude) into Geohash encoding. The specific implementation process is to use a binary search-like range division for the latitude and longitude coordinates of each pixel point in the DEM to obtain the binary encoding corresponding to the longitude and the binary encoding corresponding to the latitude. Then, integrate the above-generated encodings by placing the latitude in the odd positions and the longitude in the even positions. Next, group the integrated latitude and longitude binary numbers into groups of five numbers each, and convert each group into a decimal number. Finally, convert the result obtained in the previous step using the Base32 corresponding encoding to finally obtain the Geohash encoding corresponding to the latitude and longitude information in the DEM data.

[0038] Step 3: Adopt multi-dimensional data storage of time and space. During the storage process, combine the Geohash encoding obtained in Step 2 with the time dimension at the annual level (format: YYYY) and the administrative division code 510800 of this area as the Rowkey of the HBase database. The column family is designed as info: latitude (WGS84), longitude (WGS84), elevation (meter); soil: moisture (humidity), ph (acidity and alkalinity).

[0039] Step 4: Query the data stored in the distributed database HBase in Step 3. There are two specific query methods, which are described as follows: Query Method 1: Spatial query of a rectangular area: Obtain the result set A that meets the query conditions according to the upper right vertex (lat1, lon1), lower left vertex (lat2, lon2) of the query area, the query time range t0~t1, and the administrative division code M of this area. The detailed implementation process is to convert the latitude and longitude of the two vertices into Geohash encodings G1 and G2. Combine G1, G2 with the query time t0, t1, and the administrative division code M of this area to obtain the start and end row keys r, scan the data in HBase whose Rowkey is between r1 and r2 to obtain the result set B, and filter B through the HBase filter to obtain the result set A, and return the result set A.

[0040] Query Method 2, Spatial Query of Circular Area: Obtain the result set A that meets the query conditions based on the upper-right vertex (lat1, lon1), lower-left vertex (lat2, lon2) of the query area, the time range t0~t1 for query, and the administrative division code M of this area. The detailed implementation process is as follows: Convert the latitudes and longitudes of the two vertices into Geohash codes G1 and G2, combine G1, G2 with the query times t0, t1 and the administrative division code M of this area to obtain the start and end row keys r1, r2 for the query, scan the data in HBase with the Rowkey between r1 and r2 to get the result set B, filter B through the HBase filter to get the result set A1, filter the result set A1 through the query radius d to get the result set A, and return the result set A.

[0041] The real data parameters are as follows: The area range is selected as Guangyuan City, Sichuan Province (administrative division code 510800). Geographic coordinate range: Northeast vertex: 32.65°N latitude (lat1 = 32.65), 105.93°E longitude (lon1 = 105.93); Southwest vertex: 32.45°N latitude (lat2 = 32.45), 105.50°E longitude (lon2 = 105.50). Time range: 2010~2020 (annual data). Query radius: 1 km (center point of the circular area: 32.55°N latitude, 105.70°E longitude). Geohash precision: 8 digits (actual precision is about 19 meters). For Scheme A (i.e., the method of the present invention), Geohash + year + administrative division is adopted. The latitude and longitude of the query point are 32.55°N, 105.70°E, and the Geohash code is wtsj1e5j. Therefore, the code for Scheme A is wtsj1e5j_2010_51080; the code for Scheme B is 32.550000_105.700000_2010_510800. The query time consumption between Scheme A and Scheme B is shown in Table 2 below.

[0042] Table 2. Comparison of the differences in query performance between the two schemes

[0043] Example 3 Step 1, Data cleaning and format conversion. Select the geological disaster meteorological warning data set of Yunnan Province as the original DEM data, fill in invalid values (such as -9999) for the original DEM data, remove noise points, and remove the data with elevation values greater than 1000m and less than -50m to ensure the continuity of elevation data. Convert the original geographic coordinates into the latitude and longitude format suitable for Geohash coding, and perform projection transformation using the GDAL tool.

[0044] Step 2: Set the number of digits of the Geohash code to 10, and convert the result obtained in Step 1 by converting the geographical coordinates (longitude and latitude) into Geohash codes. The specific implementation process is to divide the range of each pixel point's longitude and latitude in the DEM using a binary search-like method to obtain the binary code corresponding to the longitude and the binary code corresponding to the latitude. Then, integrate the generated codes by placing the latitude code in odd positions and the longitude code in even positions. Next, group the integrated longitude and latitude binary numbers into groups of five numbers each, and convert each group into a decimal number. Finally, convert the result obtained in the previous step using the Base32 corresponding code to finally obtain the Geohash code corresponding to the longitude and latitude information in the DEM data.

[0045] Step 3: Adopt multi-dimensional data storage of time and space. During the storage process, combine the Geohash code obtained in Step 2 with the hour-level time dimension (format: YYYYMMDDHH) and the administrative division code 530000 of this region as the Rowkey of the HBase database. The column family is designed as info: latitude (WGS84), longitude (WGS84), elevation (meter); deform: rate (monthly deformation rate cm / month), risk_level (risk level).

[0046] Step 4: Query the data stored in the distributed database HBase in Step 3. The specific query methods include two types, which are described as follows: Query Method 1: Spatial query of a rectangular area: Obtain the result set A that meets the query conditions according to the upper right vertex (lat1, lon1), lower left vertex (lat2, lon2) of the query area, the query time range t0~t1, and the administrative division code M of this region. The detailed implementation process is to convert the longitude and latitude of the two vertices into Geohash codes G1 and G2. Combine G1, G2 with the query time t0, t1, and the administrative division code M of this region to obtain the start and end row keys r1, r2 of the query. Scan the data in HBase whose Rowkey is between r1 and r2 to obtain the result set B, and filter B through the HBase filter to obtain the result set A, and return the result set A.

[0047] Query Method 2, Spatial Query of Circular Area: Obtain the result set A that meets the query conditions based on the upper-right vertex (lat1, lon1), lower-left vertex (lat2, lon2) of the query area, the time range t0~t1 for query, and the administrative division code M of this area. The detailed implementation process is as follows: Convert the latitudes and longitudes of the two vertices into Geohash codes G1 and G2, combine G1, G2 with the query times t0, t1, and the administrative division code M of this area to obtain the start and end row keys r1 and r2 for the query. Scan the data in HBase with Rowkey between r1 and r2 to get the result set B. Filter B through the HBase filter to get the result set A1. Filter the result set A1 through the query radius d to get the result set A, and return the result set A.

[0048] For the test area, a partial area of Kunming City, Yunnan Province (administrative division code 530000) is selected as the test range. Vertices of the rectangular query area: The upper-right vertex is selected as 25.05°N (lat1 = 25.05), 103.82°E (lon1 = 103.82), and the lower-left vertex is selected as 24.80°N (lat2 = 24.80), 102.65°E (lon2 = 102.65). The center point of the circular query area is selected as 24.92°N, 103.20°E, and the radius is 500 meters. The time range is selected as 12:00 on January 1, 2023 to 18:00 on January 1, 2023 (hourly data). The 10-bit Geohash code wk4bzw5v6q (accuracy 0.6m) is obtained from the center point coordinates (24.92°N, 103.20°E). Encoding of rectangular vertices: The Geohash code wk5k3x1z2h is obtained from the upper-right vertex (25.05°N, 103.82°E), and wk0qj7s8mn is obtained from the lower-left vertex (24.80°N, 102.65°E). A comparison table of storage performance and query performance between Scheme A (using "Geohash + timestamp + administrative division code" as the Rowkey) (i.e., the method of the present invention) and Scheme B (using "latitude and longitude + timestamp + administrative division code" as the Rowkey) is shown in Tables 3 and 4.

[0049] Table 3. Comparison of Storage Performance Differences between the Two Schemes

[0050] Table 4. Comparison of Query Performance Differences between the Two Schemes

[0051] Example 4 Step 1, data cleaning and format conversion. Select the railway settlement observation data as the original DEM data, fill in the invalid values (such as -9999) in the original DEM data, remove the noise points, and remove the data with elevation values greater than 1000m and less than -50m to ensure the continuity of the elevation data. Convert the original geographic coordinates to the longitude and latitude format suitable for Geohash encoding, and use GDAL for projection transformation.

[0052] Step 2, set the Geohash encoding digits to 12, and convert the result obtained in Step 1 to Geohash encoding by converting the geographic coordinates (longitude and latitude). The specific implementation process is to divide the longitude and latitude coordinates of each pixel point in the DEM by the range division method similar to the binary search method to obtain the binary encoding corresponding to the longitude and the binary encoding corresponding to the latitude. Then, integrate the above-generated encodings by placing the latitude in the odd positions and the longitude in the even positions. Next, group the integrated longitude and latitude binary numbers into groups of every 5 numbers, and convert each group into a decimal number. Finally, convert the result obtained in the previous step using the Base32 corresponding encoding to finally obtain the Geohash encoding corresponding to the longitude and latitude information in the DEM data.

[0053] Step 3, adopt multi-dimensional data storage of time and space. During the storage process, combine the Geohash encoding obtained in Step 2 with the time dimension at the quarterly level (format: YYYYQ) and the administrative division code 510800 of this region as the Rowkey of the HBase database. The column family is designed as info:latitude(WGS84),longitude(WGS84),elevation(m); meteor:wind_speed (wind speed m / s), precipitation (rainfall mm).

[0054] Step 4, query the data stored in the distributed database HBase in Step 3. There are two specific query methods, which are described as follows: Query method 1, spatial query of a rectangular area: Obtain the result set A that meets the query conditions according to the upper right vertex (lat1, lon1), lower left vertex (lat2, lon2) of the query area, the query time range t0~t1, and the administrative division code M of this region. The detailed implementation process is to convert the longitude and latitude of the two vertices into Geohash encodings G1 and G2. Combine G1, G2 with the query time t0, t1, and the administrative division code M of this region to obtain the start and end row keys r1 and r2 of the query. Scan the data in HBase with the Rowkey between r1 and r2 to obtain the result set B, and filter B through the HBase filter to obtain the result set A, and return the result set A.

[0055] Query Method 2, Spatial Query of Circular Area: Obtain the result set A that meets the query conditions based on the upper-right vertex (lat1, lon1), lower-left vertex (lat2, lon2) of the query area, the time range t0~t1 for query, and the administrative division code M of this area. The detailed implementation process is as follows: Convert the latitudes and longitudes of the two vertices into Geohash codes G1 and G2, combine G1, G2 with the query time t0, t1 and the administrative division code M of this area to obtain the start and end row keys r1, r2 for query, scan the data in HBase with Rowkey between r1 and r2 to get the result set B, filter B through the HBase filter to get the result set A1, filter the result set A1 through the query radius d to get the result set A, and return the result set A.

[0056] The test area selects a certain railway section in Sichuan Province (administrative division code 510800). For the rectangular area query, the upper-right vertex of the query vertex is selected as: 30.658°N (lat1 = 30.658), 104.072°E (lon1 = 104.072), and the lower-left vertex is: 30.642°N (lat2 = 30.642), 104.055°E (lon2 = 104.055). The center point of the circular query area is (30.650°N, 104.063°E), the radius is 200 meters, and the time range selects quarterly data from 2022Q1 to 2023Q4. Convert the center point coordinates (30.650°N, 104.063°E) into a 12-bit Geohash code: wm3vzq1z5x6p (accuracy 0.3m) The Geohash code of the upper-right vertex G2 (30.658°N, 104.072°E) of the rectangular vertex code is converted to: wm3vzq3b1z2h, and the Geohash code of the lower-left vertex G1 (30.642°N, 104.055°E) is converted to wm3vzq0y9x8m. Adopt the method of using "Geohash + quarterly timestamp + administrative division code" as the Rowkey as Scheme A (i.e., the method of the present invention), and adopt the method of using "latitude and longitude + quarterly timestamp + administrative division code" as the Rowkey as Scheme B. The comparison of storage performance and query performance between the two is shown in Tables 5 and 6.

[0057] Table 5. Comparison of Storage Performance Differences between the Two Schemes

[0058] Table 6. Comparison of Query Performance Differences between the Two Schemes

[0059] Example 5 Step 1, data cleaning and format conversion. Select the CIM (City Information Modeling) dataset of Nanjing Normal University as the original DEM data, fill in invalid values (such as -9999) for the original DEM data, remove noise points, and remove data with elevation values greater than 1000m and less than -50m to ensure the continuity of elevation data. Convert the original geographic coordinates to the latitude and longitude format suitable for Geohash encoding, and use the GDAL tool for projection transformation.

[0060] Step 2, set the Geohash encoding number of digits to 8, and convert the result obtained in Step 1 into Geohash encoding by converting the geographic coordinates (latitude and longitude). The specific process is to divide the range of each pixel point's latitude and longitude coordinates in the DEM using a binary search-like method to obtain the binary encoding corresponding to longitude and the binary encoding corresponding to latitude. Then, integrate the above-generated encodings by placing the latitude encoding in odd positions and the longitude encoding in even positions. Next, group the integrated latitude and longitude binary numbers into groups of 5 numbers each, and convert each group into a decimal number. Finally, convert the result obtained in the previous step using the Base32 corresponding encoding to finally obtain the Geohash encoding corresponding to the latitude and longitude information in the DEM data.

[0061] Step 3, adopt multi-dimensional data storage of time and space. During the storage process, combine the Geohash encoding obtained in Step 2 with the time dimension at the quarterly level (format: YYYYQ) and the administrative division code 510800 of this region as the Rowkey of the HBase database. The column family is designed as info: latitude (WGS84), longitude (WGS84), elevation (meters); 3d_model: texture_hash (texture hash), point_cloud (point cloud density).

[0062] Step 4, query the data stored in the distributed database HBase in Step 3. There are two specific query methods, which are described as follows: Query Method 1, Spatial Query of Rectangular Area: Obtain the result set A that meets the query conditions based on the upper right vertex (lat1, lon1), lower left vertex (lat2, lon2) of the query area, the query time range t0~t1, and the administrative division code M of this area. The detailed implementation process is to convert the latitudes and longitudes of the two vertices into Geohash codes G1 and G2. Combine G1, G2 with the query time t0, t1, and the administrative division code M of this area to obtain the start and end row keys r1, r2 of the query. Scan the data in HBase with the Rowkey between r1 and r2 to get the result set B, filter B through the HBase filter to get the result set A, and return the result set A.

[0063] Query Method 2, Spatial Query of Circular Area: Obtain the result set A that meets the query conditions based on the upper right vertex (lat1, lon1), lower left vertex (lat2, lon2) of the query area, the query time range t0~t1, and the administrative division code M of this area. The detailed implementation process is as follows: Convert the latitudes and longitudes of the two vertices into Geohash codes G1 and G2. Combine G1, G2 with the query time t0, t1, and the administrative division code M of this area to obtain the start and end row keys r1, r2 of the query. Scan the data in HBase with the Rowkey between r1 and r2 to get the result set B, filter B through the HBase filter to get the result set A1, filter the result set A1 through the query radius d to get the result set A, and return the result set A.

[0064] The test data set selects the CIM data set of Nanjing Normal University (the administrative division code of Nanjing is 320110), and the parameter settings are as follows: Vertices of the rectangular query area: Upper right vertex: 32.07°N (lat1 = 32.07), 118.82°E (lon1 = 118.82); Lower left vertex: 31.95°N (lat2 = 31.95), 118.62°E (lon2 = 118.62). Circular query area: Center point (32.01°N, 118.72°E), radius 500 meters. Time range: 2021Q1~2023Q4 (quarterly data). Geohash precision: 8 bits (precision 38 meters). The Geohash code converted from the center point coordinates is wtv8gj3z, the Geohash code converted from the upper right vertex is wtv9mh1q, and the Geohash code converted from the lower left vertex is wtv6sx8n. The Geohash scheme is used as Scheme A (i.e., the method of the present invention), and the latitude and longitude scheme is used as Scheme B. The comparison of storage performance and query performance between Scheme A and Scheme B is shown in Tables 7 and 8.

[0065] Table 7. Comparison of Storage Performance Differences between the Two Schemes

[0066] Table 8. Comparison of the query performance differences between the two schemes

[0067] Example 6 Step 1, data cleaning and format conversion. Select the global typhoon track dataset IBTrACS (International Best Track Archive for Climate Stewardship) as the original DEM data, perform invalid value processing on the original DEM data (fill the missing values of fields such as wind speed and air pressure with -9999, delete duplicate records), eliminate abnormal elevation value data (>1000m or < -50m), and retain the key points of the typhoon track (central pressure, wind speed, longitude and latitude). For the typhoon track with interrupted time series, linearly interpolate to complete the position data of the missing moments. Use GDAL to convert the original geographic coordinates into a Geohash-compatible longitude and latitude format to ensure projection consistency.

[0068] Step 2, set the Geohash encoding digit to 8, and convert the result obtained in Step 1 into a Geohash encoding by converting the geographic coordinates (longitude and latitude). The specific implementation process is to divide the longitude and latitude coordinates of each pixel point in the DEM using a binary search-like range division method to obtain the binary encoding corresponding to the longitude and the binary encoding corresponding to the latitude. Then, integrate the above encodings by placing the latitude in the odd positions and the longitude in the even positions. Next, group the integrated longitude and latitude binary numbers into groups of 5 numbers each, and convert each group into a decimal number. Finally, convert the result obtained in the previous step using the Base32 corresponding encoding to obtain the Geohash encoding corresponding to the longitude and latitude information in the DEM data.

[0069] Step 3, adopt multi-dimensional data storage of time and space. During the storage process, combine the Geohash encoding obtained in Step 2 with the annual-level time dimension (format: YYYY) and the administrative division code 510800 of this region as the Rowkey of the HBase database. The column family is designed as info:latitude(WGS84),longitude(WGS84),elevation (meter); engineer:settlement (settlement amount mm), construction_date (construction date).

[0070] Step 4, query the data stored in the distributed database HBase in Step 3. The specific query methods include two types, which are described as follows: Query Method 1, Spatial Query in a Rectangular Area: Obtain the result set A that meets the query conditions based on the upper-right vertex (lat1, lon1), lower-left vertex (lat2, lon2) of the query area, the time range t0~t1 for the query, and the administrative division code M of the region. The detailed implementation process is to convert the latitudes and longitudes of the two vertices into Geohash codes G1 and G2. Combine G1, G2 with the query times t0, t1, and the administrative division code M of the region to obtain the start and end row keys r1 and r2 for the query. Scan the data in HBase with Rowkeys between r1 and r2 to obtain the result set B, and filter B through the HBase filter to obtain the result set A, and return the result set A.

[0071] Query Method 2, Spatial Query in a Circular Area: Obtain the result set A that meets the query conditions based on the upper-right vertex (lat1, lon1), lower-left vertex (lat2, lon2) of the query area, the time range t0~t1 for the query, and the administrative division code M of the region. The detailed implementation process is as follows: Convert the latitudes and longitudes of the two vertices into Geohash codes G1 and G2, combine G1, G2 with the query times t0, t1, and the administrative division code M of the region to obtain the start and end row keys r1 and r2 for the query. Scan the data in HBase with Rowkeys between r1 and r2 to obtain the result set B, filter B through the HBase filter to obtain the result set A1, and filter the result set A1 through the query radius d to obtain the result set A, and return the result set A.

[0072] The experimental parameters are as follows: Select 10 million typhoon track data (from 2010 to 2023) for the data volume. For the rectangular query area, select the range from 20°N to 25°N and 110°E to 120°E. For the circular area, select the center point at 22.3°N, 113.5°E and a radius of 100 kilometers. Scheme A uses "Geohash (8 digits) + year + administrative division code" (i.e., the method of the present invention); Scheme B uses "latitude_longitude (6 decimal places) + year + administrative division code"; Scheme C uses "year + administrative division code + Geohash". The column family design is shown in Table 9 below, the storage efficiency is shown in Table 10, and the query performance is shown in Table 11.

[0073] Table 9, Column Family Design Table

[0074] Table 10, Comparison of the Storage Performance Differences between the Two Schemes

[0075] Table 11, Comparison of the Query Performance Differences between the Two Schemes

[0076] The above experimental examples show that the Rowkey design method involved in the present invention can effectively improve storage efficiency and query performance. The reasons are as follows: 1) Geohash encoding maps adjacent geographical locations to similar prefix strings, and HBase stores data in an ordered manner according to the Rowkey. Therefore, during query, the target area can be quickly located through prefix range scanning, avoiding full-table scanning. Among 10 million typhoon path data, the rectangular query of the Geohash scheme takes 350 ms, while the longitude and latitude scheme takes 1300 ms (the gap is about 3.7 times). 2) Using the timestamp (such as YYYYQ) as the middle field of the Rowkey (Geohash_time_administrative division code) ensures that data in the same time period is continuously stored, avoiding cross-Region queries. 3) The length of Geohash is fixed, which is much shorter than the floating-point numbers of longitude and latitude, saving 30% - 40% of the storage space. Among 100 million data, the Geohash scheme occupies 1.8 TB, and the longitude and latitude scheme occupies 3.2 TB. The writing time of the Geohash scheme is 85 minutes, and the longitude and latitude scheme takes 140 minutes (reduced by about 40%). 4) The administrative division code is used as the suffix of the Rowkey, directly limiting the query area and avoiding cross-region data scanning.

[0077] As can be seen from the above embodiments, the method of the present invention adopts the Rowkey design of Geohash + timestamp + administrative division code. Through spatial locality, time-ordered storage, and precise partitioning, the query performance is improved: the spatial query speed is increased by 3 - 4 times, and the time query efficiency is optimized by 20%. The storage cost is reduced: 30% - 40% of the storage space is saved, and the I / O pressure is reduced. Complex scenario support: It naturally supports spatio-temporal joint queries and adapts to high-precision requirements such as typhoon monitoring and urban planning. The experimental results reveal the advantages of the row key design based on Geohash encoding, the storage mechanism of HBase, and the optimized query strategy when dealing with geographical information datasets.

[0078] The method of the present invention aims to improve the efficiency and accuracy of querying large-scale spatial data. First, Geohash encoding is used to convert the spatial location (such as longitude and latitude) into a unique row key, and this row key is stored in the HBase database together with the corresponding geographical location information and additional attributes (such as timestamp, administrative division code, etc.). During the query process, a range query is performed based on the row key generated by Geohash encoding, and the target data is accurately located through filtering conditions such as administrative division code and timestamp. At the same time, the present invention adopts a caching mechanism and Bloom Filter to optimize the query performance, reduce disk I / O, and improve the response speed. It can efficiently support the storage and query of large-scale spatial data and is applicable to the spatial data management and analysis in fields such as smart cities and geographic information systems (GIS).

Claims

1. A spatial query optimization method based on Salted Key, characterized in that Implement according to the following steps: Step 1, data cleaning and format conversion; Step 2, convert the geographical coordinates of the converted data obtained in Step 1 into Geohash codes; Step 3, complete data storage using multi-dimensions of time and space; Step 4, query the data stored in the distributed database HBase and obtain the query results through two query methods.

2. The method for optimizing spatial queries based on Salted Key according to claim 1, wherein In Step 1, the specific process is: First, fill in the invalid values and remove the noise points from the original DEM data to ensure the continuity of the elevation data; Then, perform projection transformation to ensure that the output coordinates are in decimal longitude and latitude format.

3. The method for optimizing spatial queries based on Salted Key according to claim 1, wherein In Step 2, the specific process is: 2.1) Divide the longitude and latitude coordinates of each pixel point in the DEM model using a method similar to binary search range division to obtain the binary code corresponding to the longitude and the binary code corresponding to the latitude; 2.2) Integrate the binary code corresponding to the longitude and the binary code corresponding to the latitude obtained in Step 2.1) by placing the latitude in the odd positions and the longitude in the even positions; 2.3) Group the integrated longitude and latitude binary numbers into groups of 5 numbers in the order from left to right, and convert each group into a decimal number. If the total number of digits is not a multiple of 5, pad 0 at the end until the grouping is complete; 2.4) Convert the result obtained in Step 2.3) using the Base32 corresponding encoding to obtain the Geohash code corresponding to the longitude and latitude information in the DEM data.

4. The method for optimizing spatial queries based on Salted Key according to claim 1, wherein In Step 3, during the data storage process, combine the Geohash code obtained in Step 2 with the time dimension and the administrative division code of the region where it is located as the RowKey of the HBase database; and add the longitude and latitude information and elevation information in the DEM data to the columns latitude, longitude, and elevation under the column family info in HBase.

5. The method for optimizing spatial queries based on Salted Key according to claim 4, wherein The storage process is as follows: First, obtain the data source, clean the data source, convert the cleaned data, and convert the longitude and latitude into Geohash codes; After converting the longitude and latitude into Geohash codes, use the timestamp and the administrative division code of the region as salt values, combine them with the Geohash code to generate the RowKey, and finally store it in the HBase database.

6. The method for optimizing spatial queries based on Salted Key according to claim 5, wherein The specific storage process is: First, the client obtains the Region location of the HBase:meta table through ZooKeeper, determines the RegionServer where the target Region is located according to the RowKey, establishes a connection and sends a write request; The data is appended to the WAL in order to ensure that even if the in-memory data is lost, it can be restored through the log. The WAL is stored in the form of HDFS files, which supports fault recovery and master-slave cluster data synchronization; the data is written into the MemStore of the corresponding Region, sorted by RowKey and column family to form an ordered key-value pair; when the MemStore reaches the threshold or triggers regular flushing, the data is asynchronously flushed to HDFS to generate an HFile; the HFile is stored by column family and uses block indexing and time range indexing internally to achieve optimization.

7. The method for optimizing spatial queries based on Salted Key according to claim 1, wherein In step 4, the first query method is the spatial query of a rectangular area: According to the upper right vertex (lat1, lon1), lower left vertex (lat2, lon2) of the query area, the query time range t0~t1, and the administrative division code M of this area, the result set A that meets the query conditions is obtained; The detailed implementation process is as follows: Convert the longitude and latitude of the two vertices into Geohash codes G1 and G2; Combine G1, G2 with the query times t0, t1, and the administrative division code M of this area to obtain the start and end row keys r1, r2 of the query. Scan the data in the distributed database HBase whose Rowkey is between r1 and r2 to obtain the result set B, and filter the result set B through the HBase filter to obtain the result set A. The result set A obtained by the query is the final result set.

8. The method for optimizing spatial queries based on Salted Key according to claim 1, characterized in that, In step 4, the second query method is the spatial query of a circular area: According to the upper right vertex (lat1, lon1), lower left vertex (lat2, lon2) of the query area, the query time range t0~t1, and the administrative division code M of this area, the result set A that meets the query conditions is obtained; The detailed implementation process is as follows: Convert the longitude and latitude of the two vertices into Geohash codes G1 and G2, combine G1, G2 with the query times t0, t1, and the administrative division code M of this area to obtain the start and end row keys r1, r2 of the query. Scan the data in the distributed database HBase whose Rowkey is between r1 and r2 to obtain the result set B, then filter the result set B through the HBase filter to obtain the result set A1, and filter the result set A1 through the query radius d to obtain the result set A. The result set A obtained by the query is the final result set.

Images