Spatial dynamic grid-based spatial tabulation method

Abstract

The application discloses a space sub-table method based on a space dynamic grid, which comprises the following steps: S1, creating a storage structure of a sub-table result; S2, determining an initial level and an initial grid set according to a data space range and a sub-table record number upper limit; and S3, performing a data transfer / storage / continuing sub-table operation in the sub-table space range. The application adopts a dynamic grid based on a map pyramid model and the record number contained in the grid to comprehensively determine the sub-table range, and stores the records across multiple sub-tables uniformly through a sub-table total table, so as to balance the sub-table capacity and guarantee the geometric integrity of the vector data.

Description

TECHNICAL FIELD

[0001] The application belongs to the technical field of geographic information systems, and particularly relates to a spatial subtable method based on a spatial dynamic grid. BACKGROUND

[0002] With the continuous progress of data acquisition technology, new generation surveying and mapping and design data and spatial big data are increasingly expanding, and geographic information system (GIS) software faces great challenges in storage, processing and analysis. The current vector data in geographic information data mainly uses a relational database for storage, and the database is expanded to give the database spatial data storage capacity and spatial analysis capacity.

[0003] The relational database itself is relatively easy to become a bottleneck for geographic information system data storage and access, and the single machine storage capacity, connection number and processing capacity are limited. When the data volume of a single table reaches 1000 million or 100 GB, the performance is still severely reduced even if a slave database is added, indexes are optimized and many operations are performed. For spatial data, the spatial search efficiency is much lower than the attribute search efficiency, and the demand for performance optimization is more urgent. At this time, it is necessary to consider splitting it, and the purpose of splitting is to reduce the burden of the database and shorten the query time.

[0004] Data splitting can be divided into two ways according to its splitting type: vertical (longitudinal) splitting and horizontal (transverse) splitting.

[0005] Data splitting includes two kinds of database splitting and table splitting. The content of the application is mainly for optimizing the single table data volume, and only involves table splitting but not database splitting.

[0006] Vertical table splitting is based on the "column" in the database, and a new extended table can be created for a table with many fields, and the fields that are not frequently used or have a large length can be split out to the extended table. In the case of many fields (for example, a large table has more than 100 fields), the "large table is split into small tables", which is more convenient for development and maintenance. The database loads data into the memory in units of rows, so that the table has a short field length and a high access frequency, the memory can load more data, the hit rate is higher, the disk IO is reduced, and the database performance is improved. As shown in Figure 1 .

[0007] When an application is difficult to be vertically split in a fine granularity, or the data volume after splitting is huge, there is a single database read-write and storage performance bottleneck, at this time, horizontal splitting is needed.

[0008] Horizontal sub-table is to distribute the same table into multiple databases or multiple tables according to the logical relationship of the data in the table, and each table contains only a part of the data, so that the data volume of a single table is reduced to achieve the effect of distribution. As shown in Figure 2 .

[0009] The current commonly used spatial sub-table method mainly draws on the idea of spatial index, mainly including two methods: fixed grid range for spatial sub-table according to the unit grid index strategy, and R-tree index spatial division strategy according to R-tree range for spatial sub-table.

[0010] The principle of spatial sub-table according to fixed grid range is relatively simple, which sets the overall spatial range of grid division and the size of the grid, constructs grid records with unique numbers, and the geometric figure of the grid is a rectangle, the edges of the rectangle are parallel to the coordinate axis, and the data intersecting with the grid rectangle range is saved to a sub-table, and the association between the sub-table and the grid record is stored.

[0011] The advantages of using fixed grid for spatial sub-table are: 1) the grid division rule is simple and easy to construct; 2) the size of the sub-table space range is uniform, and the covered sub-table can be quickly calculated through the query range, and the sub-tables that need to be queried can be quickly located.

[0012] Problems: 1) due to uneven distribution of data, the number of data in the sub-table will be uneven, if you want to keep the data volume in the sub-table small, you need to set a small grid range, which will generate a large number of sub-tables; if the grid range is set to be relatively large, it will cause a lot of data in some sub-tables, affecting the query efficiency; 2) for records whose geometric figures (lines or surfaces) cross multiple grids, if they are cut by grid range, the geometric figures of the same record need to be spliced during query; if they are not cut, the query results need to be de-duplicated.

[0013] R-tree is a balanced tree structure, which was first proposed by A. Guttman in 1984, and then there are many variants, which constitute the R series tree spatial index composed of R-tree, R+ tree, Hibert R-tree, SR-tree, etc.

[0014] R-tree index introduces the concept of minimum bounding rectangle, which can construct the minimum bounding rectangle of the geometric figures (points, lines, surfaces) on the map, so that the point, line and surface data are all converted into the same figure, which is a rectangle. R-tree mainly deals with three problems: first, constructing the minimum bounding rectangle according to the point, line and surface; second, how to retrieve the leaf node according to the bounding rectangle; third, how to rebuild the R-tree to maintain the balance of the R-tree when adding or deleting leaf nodes.

[0015] The construction process of the R-tree is as follows: the first step is to construct the minimum bounding rectangles of the geographic information elements; the second step is to construct the bounding rectangles on the minimum bounding rectangles until the root node of the R-tree is constructed.

[0016] Each node of the R-tree does not store the value of the spatial element. The leaf node stores the bounding rectangle of the spatial element corresponding to the node and the identification of the spatial element. The bounding rectangle is a general concept, which is a rectangle in two dimensions, a cuboid in three dimensions, and so on in high-dimensional space. The non-leaf node (the father and ancestor node of the leaf node) stores the overall bounding rectangle of the child node set and the pointer to the child node. The construction process and result of the R-tree are described in Figure 3 .

[0017] The advantages of the R-tree model for spatial partitioning are as follows: 1) by controlling the aggregation number of the root node (i.e. the number of records contained in the node reaches a given number of records, and the recursive aggregation is not performed upward), the number of data in the partition table can be well controlled, and the number of records in the partition table is relatively uniform; 2) the searching process can quickly lock the range to be searched by tracing upward along the tree structure, and the searching efficiency is relatively high.

[0018] The existing problems are as follows: 1) the R-tree construction process is a recursive process, and the logic is relatively complex; 2) the spatial range of the R-tree partition table is not fixed in size, and there may be overlaps, so the spatial coverage range of the partition table cannot be intuitively understood; 3) the records with large geometric figures will set the range of the spatial partition table to be large, and the records in the coverage range may exceed the maximum record capacity of the single table. SUMMARY

[0019] The purpose of the present application is to overcome the deficiencies of the prior art, and to provide a spatial partitioning method based on a dynamic grid based on a map pyramid model and the number of records contained in the grid to comprehensively determine the range of the partition table, and to store the records across multiple partition tables in a partition table master table, so as to balance the partition table capacity and ensure the geometric integrity of the vector data.

[0020] The purpose of the present application is achieved by the following technical scheme: a spatial partitioning method based on a spatial dynamic grid, comprising the following steps:

[0021] S1, creating a storage structure of the partition table result;

[0022] S2, determining the initial level and the initial grid set according to the data space range and the upper limit of the number of records;

[0023] S3, transferring the data in the partition table space range and continuing the partitioning operation.

[0024] In step S1, a sub-table index table is created for storing sub-table metadata for quick query, and the structure of the table includes Id, original table name ori_table, sub-table table name sub_table, spatial grid level z, column number x of the spatial grid, row number y of the spatial grid, is_overlap attribute, and extent of the spatial grid;

[0025] The table is distinguished by the is_overlap attribute, and when the value is true, it is a total table, and when the value is false, it is a sub-table;

[0026] The naming rule of the sub-table table name is: {sub_table}_{z}_{x}_{y}, and the naming rule of the total table table name is:

[0027] {ori_table}_overlap;

[0028] The table structures of the sub-table, the total table and the original table are consistent, and the identities of the records are also consistent; the sub-table is used to store records whose geometric figures are completely contained in the spatial range of the sub-table; and the total table is used to store records whose geometric figures cross the range of the sub-table.

[0029] The step S2 includes the following sub-steps:

[0030] S21, setting the upper limit maxCnt of the number of records of the sub-table;

[0031] S22, reading the data space range dataExtent and the number of records dataCnt, wherein the dataExtent is a rectangle represented by the coordinates [(minX, minY), (minX, maxY), (maxX, maxY), (maxX, minY)] of four corners of the rectangle;

[0032] S23, calculating the initial number of sub-tables:

[0033]

[0034] Wherein, tbCnt is the number of initial sub-tables; the Ceiling method is to find the minimum integer value greater than or equal to the specified decimal number;

[0035] S24, calculating the initial level grid of the sub-table, using the GoogleMap global pyramid model as the rule of grid division, and using the ZXY rule for grid numbering, wherein Z represents the level number, the value range is [0, n]; X represents the column number, the value range is [0, 2 n -1]; Y represents the row number, the value range is [0, 2 n -1];

[0036] The initial hierarchical grid of the calculation sub-table specifically comprises the following steps:

[0037] S241, setting the initial value of the hierarchical number n as 1, and the expected sub-table number tbCnt;

[0038] S242, calculating the ratio r of the data range area and the hierarchical grid area, and the calculation formula is:

[0039]

[0040] Wherein, n is the hierarchical number; r is the ratio of the data range area and the grid area of the hierarchical level n; R is the radius of the earth, taking the value of 6378137 meters; xMin and xMax are the minimum and maximum values of the X direction of the data range; yMin and yMax are the minimum and maximum values of the Y direction of the data range;

[0041] S243, if the ratio r is greater than or equal to 2 2n , the hierarchical number n is increased by 1, and the step S242 is jumped to; when the ratio r is less than 2 2n , the step S244 is executed;

[0042] S244, calculating the number tileCnt of the grids in the hierarchical level n covered by the data range;

[0043] S245, if tileCnt is less than tbCnt, the hierarchical number n is increased by 1, and the step S244 is jumped to; if tileCnt is greater than or equal to tbCnt, the hierarchical level n is the initial hierarchical level, and the step S246 is executed;

[0044] S246, calculating the grids in the hierarchical level n overlapped by the data range, which is the initial sub-table grid range set.

[0045] The step S3 comprises the following sub-steps:

[0046] S31, extracting the first grid grid from the initial grid set of the sub-table;

[0047] S32, calculating the spatial range gridExtent of the grid grid: determining the spherical spatial range through the spherical longitude and latitude of the grid; the calculation formula of the longitude and latitude is:

[0048] lngMin=(2 (1-z) ×x-1)×180

[0049] lngMax=[2 (1-z) ×(x+1)-1]×180

[0050]

[0051]

[0052] wherein z is the level number of the grid, x is the column number of the grid, y is the row number of the grid; lngMin, lngMax, latMin, latMax are the minimum longitude value, the maximum longitude value, the minimum latitude value, and the maximum latitude value of the spatial grid, respectively;

[0053] The spherical coordinates in the spherical space range are converted into the plane coordinates to obtain the plane space range gridExtent of the grid; the conversion formula is:

[0054]

[0055]

[0056] wherein x and y are the plane coordinates; lng and lat are the longitude and latitude;

[0057] S33, the number cnt of records contained in the gridExtent space range is calculated, and the containing relationship is that the geometric figure of the record is completely within the range of gridExtent;

[0058] S34, when cnt=0, jump to step S37, otherwise execute S35;

[0059] S35, it is judged whether cnt exceeds the upper limit of the sub-table record, if yes, the next grid corresponding to the grid space range of grid is added to the sub-table grid set as the grid of the sub-table, the next sub-table grid is extracted, and jump to step S32; otherwise, execute S36;

[0060] S36, when cnt does not exceed the upper limit of the sub-table record, firstly, the sub-table corresponding to the grid is created, the records contained in the grid space range are transferred to the sub-table, then the records intersecting the grid boundary are transferred to the total table, and finally, the corresponding records of the sub-table are added to the sub-table index table;

[0061] When the original table records are transferred to the total table, the records need to be checked for duplication, if the record already exists, the record is not transferred;

[0062] S37, the next sub-table grid is extracted, and steps S32-S36 are performed until all the sub-table grids are traversed.

[0063] The beneficial effects of the present application are: the present application adopts dynamic grid based on map pyramid model and the record quantity contained in the grid to comprehensively determine the range of the sub-table, and stores the records across multiple sub-tables through a sub-table total table to achieve the purpose of balancing the sub-table capacity and ensuring the geometric integrity of the vector data. The sub-table created by this method has clear spatial range and does not overlap with each other, and the division of the sub-table is automatically controlled by the table capacity (if the capacity is exceeded, the range is further subdivided according to the next level). The query rule is simple and efficient, and only 2 queries are needed to get the result: (1) quickly locate the sub-table where the data is located from the index table through the spatial range; (2) query the records meeting the conditions from the sub-table, query the records meeting the conditions from the total table, and return the query results. BRIEF DESCRIPTION OF DRAWINGS

[0064] Figure 1 For vertical sub-table intention;

[0065] Figure 2 For horizontal sub-table intention;

[0066] Figure 3 For R-tree spatial sub-table area division schematic diagram;

[0067] Figure 4 For flowchart of the present application;

[0068] Figure 5 For sub-table index table structure schematic diagram;

[0069] Figure 6 For sub-table result storage structure schematic diagram;

[0070] Figure 7 For initial level grid range flowchart schematic diagram;

[0071] Figure 8 For grid range data sub-table flowchart schematic diagram. DETAILED DESCRIPTION

[0072] The technical solutions of the present application will be further described below in combination with the drawings.

[0073] As Figure 4 shown, a spatial sub-table method based on spatial dynamic grid of the present application comprises the following steps:

[0074] S1, create a storage structure of sub-table result; create a sub-table index table for storing sub-table metadata (the data records are stored in the sub-table, and the information (metadata) of the sub-table is stored in the sub-table index table) to facilitate fast query, and the structure is as shown in Figure 5As shown, including Id (table unique identifier), original table name ori_table, sub-table table name sub_table, spatial grid level z, column number x of the spatial grid, row number y of the spatial grid, is_overlap attribute and extent of the spatial grid;

[0075] The is_overlap attribute is used to distinguish whether the table is a total table or a sub-table, and when the value is true, it is a total table, and when the value is false, it is a sub-table;

[0076] The naming rule of the sub-table table name is: {sub_table}_{z}_{x}_{y}, and the grid where it is located can be intuitively known through the table name, so that quick positioning can be performed; the naming rule of the total table table name is: {ori_table}_overlap;

[0077] The sub-table, total table and original table structures are consistent, and the identifiers of the records are also consistent; the sub-table is used to store records whose geometric figures are completely contained in the spatial range of the sub-table; the total table is used to store records whose geometric figures cross the range of the sub-table (the geometric figure of the record intersects with the boundary of the sub-table). The storage structure of the sub-table result is as shown in Figure 6 .

[0078] S2, determining an initial level and an initial grid set according to a data space range and a record number upper limit of the sub-table; including the following sub-steps:

[0079] S21, setting a record number upper limit maxCnt of the sub-table; it is tested that for a single machine database space table, the record number upper limit: points are 5 million, and lines and surfaces are 400,000, so that a very high access efficiency can be achieved, and the upper limit can be adjusted according to the specific server performance.

[0080] S22, reading a data space range dataExtent and a record number dataCnt, wherein the dataExtent is a rectangle represented by rectangular four corner coordinates [(minX, minY), (minX, maxY), (maxX, maxY), (maxX, minY)];

[0081] S23, calculating an initial sub-table number:

[0082]

[0083] Ceiling method is a method for obtaining the minimum integer value greater than or equal to a specified decimal number, such as Ceiling(2.3) = 3;

[0084] S24, calculate the initial hierarchical grid of the sub-table, adopt the GoogleMap global pyramid model widely used in the industry as the rule of grid division. The grid number adopts ZXY rule, wherein Z represents hierarchical number, the value range is [0, n]; X represents column number, the value range is [0, 2 n -1]; Y represents row number, the value range is [0, 2 n -1].

[0085] As shown in Figure 7 , the calculation of the initial hierarchical grid of the sub-table specifically includes the following steps:

[0086] S241, set the initial value of hierarchical number n as 1, and the expected number of sub-tables tbCnt;

[0087] S242, calculate the ratio r of the data range area and the hierarchical grid area, the calculation formula is:

[0088]

[0089] Wherein, n is the hierarchical number; r is the ratio of the data range area and the grid area of the hierarchical level n; R is the earth radius, the value is 6378137 meters; xMin and xMax are the minimum and maximum values of the X direction of the data range; yMin and yMax are the minimum and maximum values of the Y direction of the data range;

[0090] S243, if the ratio r is greater than or equal to 2 2n , the hierarchical number n is increased by 1, and jump to S242; when the ratio r is less than 2 2n , execute S244;

[0091] S244, calculate the number tileCnt of the grid in the hierarchical level n covered by the data range;

[0092] S245, if tileCnt is less than tbCnt, the hierarchical number n is increased by 1, and jump to S244; if tileCn is greater than or equal to tbCnt, the hierarchical level n is the initial hierarchical level, and execute S246;

[0093] S246, calculate the grid superimposed in the hierarchical level n of the data range, which is the initial sub-table grid range set.

[0094] S3, data in the sub-table space range is migrated / continued sub-table operation, according to the data amount in the space grid range where the sub-table is located, it is judged whether to continue sub-table or to migrate the data in the sub-table range, the initial sub-table grid is traversed, the data record number cnt contained in the grid space range (the geometric figure of the record is completely in the grid space range) is queried. If cnt exceeds the upper limit of sub-table record, the grid is further divided, and the grid set corresponding to the grid position in the next level is used as the sub-table grid to sub-table; if cnt does not exceed the upper limit of sub-table record, the sub-table of the grid is created, the data contained in the grid space range is migrated, the record intersecting with the grid boundary is migrated to the total table, and the sub-table index information is recorded. As shown in FIG. Figure 8 , specifically comprising the following sub-steps:

[0095] S31, the first grid grid is extracted from the initial sub-table grid set;

[0096] S32, the space range gridExtent of the grid grid is calculated: the spherical space range is determined by the spherical longitude and latitude of the grid; the longitude and latitude calculation formula is:

[0097] lngMin = (2 (1-z) ×x-1)×180

[0098] lngMax = [2 (1-z) ×(x+1)-1]×180

[0099]

[0100]

[0101] Wherein, z is the level number where the grid is located, x is the column number where the grid is located, y is the row number where the grid is located; lngMin, lngMax, latMin, latMax are the minimum longitude value, the maximum longitude value, the minimum latitude value and the maximum latitude value of the space grid respectively;

[0102] The spherical coordinates in the spherical space range are converted into plane coordinates to obtain the plane space range gridExtent of the grid grid; the conversion formula is:

[0103]

[0104]

[0105] Wherein, x, y are plane coordinates; lng, lat are longitude and latitude;

[0106] S33, calculate the number of records cnt contained in the gridExtent space range, the containing relationship is that the geometry of the record is completely within the range of the gridExtent;

[0107] S34, when cnt=0, jump to step S37, otherwise execute S35;

[0108] S35, judge whether the cnt exceeds the upper limit of the sub-table record, if yes, add the next grid corresponding to the grid space range of the grid to the sub-table grid set as the grid of the sub-table, extract the next sub-table grid, and jump to step S32; otherwise, execute S36;

[0109] S36, when the cnt does not exceed the upper limit of the sub-table record, firstly create the sub-table corresponding to the grid, and transfer the records contained in the grid space range to the sub-table; then transfer the records intersecting with the grid boundary to the total table; finally, add the corresponding record of the sub-table to the sub-table index table;

[0110] When the original table record is transferred to the total table, the record needs to be checked for duplication, if the record already exists (the record identifier already exists), the record will not be transferred;

[0111] S37, extract the next sub-table grid, and perform steps S32-S36 until all sub-table grids are traversed.

[0112] Those skilled in the art will appreciate that the embodiments described herein are presented for the purpose of helping the reader understand the principles of the present application, and should be understood as not limiting the scope of protection of the present application to such specific statements and embodiments. Those skilled in the art can make various other specific modifications and combinations according to the technical inspiration disclosed in the present application without departing from the essence of the present application, and these modifications and combinations are still within the scope of protection of the present application.

Claims

1. A spatial table partitioning method based on a spatial dynamic grid, characterized in that, Includes the following steps: S1. Create the storage structure for the results of the table partitioning; S2. Determine the initial level and initial grid set based on the data space range and the upper limit of the number of records in the sub-table; S3. Perform transfer / continue table partitioning operations on the data within the table partitioning space; Includes the following sub-steps: S31. Extract the first grid from the initial grid set of the table partitioning; S32. Calculate the spatial extent of the grid: gridExtent is determined by the spherical latitude and longitude of the grid; the formula for calculating latitude and longitude is: ； ； ； ； Where z is the level number of the grid, x is the column number of the grid, and y is the row number of the grid; lngMin, lngMax, latMin, and latMax are the minimum longitude, maximum longitude, minimum latitude, and maximum latitude of the spatial grid, respectively. Converting spherical coordinates within the spherical space to planar coordinates yields the planar spatial extent of the grid, gridExtent; the conversion formula is as follows: ； ； Where x and y are planar coordinates; lng and lat are longitude and latitude, respectively; S33. Calculate the number of records cnt contained within the gridExtent space, where the containment relationship is that the geometry of the records is completely within the gridExtent. S34. When cnt=0, jump to step S37; otherwise, execute S35. S35. Determine if cnt exceeds the upper limit of the table record. If so, add the next layer of grid corresponding to the grid space range to the table grid set, extract the next table grid, and jump to step S32; otherwise, execute S36. S36. When cnt does not exceed the upper limit of the table record, first create the table corresponding to the grid, and transfer the records contained in the grid space range to the table; then transfer the records where the geometry intersects with the grid boundary to the main table; finally, add the corresponding record of the table to the table index table. When transferring records from the original table to the master table, the records need to be deduplicated. If a record already exists, it will not be transferred. S37. Extract the next sub-table network and repeat steps S32-S36 until all sub-table networks have been traversed.

2. The spatial table partitioning method based on spatial dynamic grid according to claim 1, characterized in that, In step S1, a partitioned index table is created to store partitioned metadata for easy and fast querying. Its structure includes Id, original table name ori_table, partitioned table name sub_table, spatial grid level z, column number of the spatial grid x, row number of the spatial grid y, is_overlap attribute, and spatial grid extent. The `is_overlap` attribute is used to distinguish whether a table is a main table or a sub-table. A value of `true` indicates a main table, while a value of `false` indicates a sub-table. The naming convention for sub-tables is: {sub_table}_{z}_{x}_{y}, and the naming convention for the main table is: {ori_table}_overlap; The structure of the sub-table, the master table, and the original table must be consistent, and the record identifiers must also be consistent. The sub - tables are used to store records whose geometric figures are completely contained within the sub - table space range; the total table is used to store records whose geometric figures span the sub - table range.

3. The spatial table partitioning method based on spatial dynamic grid according to claim 1, characterized in that, The step S2 includes the following sub - steps: S21. Set the upper limit of the number of records in the sub - table, maxCnt; S22. Read the data space range dataExtent and the number of records dataCnt, where dataExtent is a rectangle represented by the coordinates of the four corners of the rectangle [(minX, minY), (minX, maxY), (maxX, maxY), (maxX, minY)]; S23. Calculate the initial number of sub - tables: ； where tbCnt is the number of initial sub - tables; the Ceiling method is to find the smallest integer value greater than or equal to the specified decimal number; S24. Calculate the initial hierarchical grid for the subdivided table. The Google Maps global pyramid model is used as the grid division rule. The grid numbering follows the ZXY rule, where Z represents the level number, with a value range of [0, n]; X represents the column number, with a value range of [0, n]. ]; Y represents the row number, with a value range of [0, ..., Y]. ]; Calculating the initial hierarchical grid of the sub - tables specifically includes the following steps: S241. Set the initial value of the hierarchical number n to 1 and the expected number of sub - tables tbCnt; S242. Calculate the ratio r of the data range area to the hierarchical grid area, and the calculation formula is: ； where n is the hierarchical number; r is the ratio of the data range area to the grid area at level n; R is the radius of the earth, with a value of 6378137 meters; S243, if the ratio The level number n increments by 1, then jumps to S242; when the ratio Execute S244 at that time; S244. Calculate the number of grids tileCnt covered by the data range at level n; S245. If tileCnt < tbCnt, increment the hierarchical number n by 1 and jump to S244; if tileCn ≥ tbCnt, level n is the initial level, and execute S246; S246. Calculate the grids superimposed by the data range at level n, which is the set of initial sub - table grid ranges.

Images