CN111046244A - PostGIS-based power grid resource graph superposition analysis method and device - Google Patents
PostGIS-based power grid resource graph superposition analysis method and device Download PDFInfo
- Publication number
- CN111046244A CN111046244A CN201911244651.4A CN201911244651A CN111046244A CN 111046244 A CN111046244 A CN 111046244A CN 201911244651 A CN201911244651 A CN 201911244651A CN 111046244 A CN111046244 A CN 111046244A
- Authority
- CN
- China
- Prior art keywords
- power grid
- grid resource
- univocal
- graphic data
- postgis
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000004458 analytical method Methods 0.000 title claims abstract description 65
- 238000004220 aggregation Methods 0.000 claims abstract description 25
- 230000002776 aggregation Effects 0.000 claims abstract description 25
- 238000000034 method Methods 0.000 claims abstract description 20
- 238000003860 storage Methods 0.000 claims description 35
- 238000004590 computer program Methods 0.000 claims description 13
- 230000004048 modification Effects 0.000 claims description 5
- 238000012986 modification Methods 0.000 claims description 5
- 238000004364 calculation method Methods 0.000 claims description 3
- 210000002729 polyribosome Anatomy 0.000 claims 1
- 238000010586 diagram Methods 0.000 description 19
- 230000006870 function Effects 0.000 description 6
- 238000012545 processing Methods 0.000 description 6
- 238000009826 distribution Methods 0.000 description 5
- 239000000872 buffer Substances 0.000 description 4
- 238000011056 performance test Methods 0.000 description 4
- 230000008569 process Effects 0.000 description 4
- 238000012732 spatial analysis Methods 0.000 description 4
- 238000005520 cutting process Methods 0.000 description 3
- 230000004044 response Effects 0.000 description 3
- 230000003321 amplification Effects 0.000 description 2
- 230000006837 decompression Effects 0.000 description 2
- 238000003199 nucleic acid amplification method Methods 0.000 description 2
- 238000005457 optimization Methods 0.000 description 2
- 230000002035 prolonged effect Effects 0.000 description 2
- 230000009286 beneficial effect Effects 0.000 description 1
- 230000006835 compression Effects 0.000 description 1
- 238000007906 compression Methods 0.000 description 1
- 238000010276 construction Methods 0.000 description 1
- 238000013500 data storage Methods 0.000 description 1
- 230000003203 everyday effect Effects 0.000 description 1
- 238000007689 inspection Methods 0.000 description 1
- 230000001788 irregular Effects 0.000 description 1
- 238000007726 management method Methods 0.000 description 1
- 238000004519 manufacturing process Methods 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
- 239000002699 waste material Substances 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/90—Details of database functions independent of the retrieved data types
- G06F16/901—Indexing; Data structures therefor; Storage structures
- G06F16/9024—Graphs; Linked lists
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/90—Details of database functions independent of the retrieved data types
- G06F16/903—Querying
- G06F16/9032—Query formulation
- G06F16/90324—Query formulation using system suggestions
- G06F16/90328—Query formulation using system suggestions using search space presentation or visualization, e.g. category or range presentation and selection
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06Q—INFORMATION AND COMMUNICATION TECHNOLOGY [ICT] SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES; SYSTEMS OR METHODS SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES, NOT OTHERWISE PROVIDED FOR
- G06Q50/00—Information and communication technology [ICT] specially adapted for implementation of business processes of specific business sectors, e.g. utilities or tourism
- G06Q50/06—Energy or water supply
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- General Physics & Mathematics (AREA)
- Business, Economics & Management (AREA)
- Data Mining & Analysis (AREA)
- Health & Medical Sciences (AREA)
- General Engineering & Computer Science (AREA)
- Economics (AREA)
- General Health & Medical Sciences (AREA)
- Mathematical Physics (AREA)
- Software Systems (AREA)
- Public Health (AREA)
- Water Supply & Treatment (AREA)
- Computational Linguistics (AREA)
- Human Resources & Organizations (AREA)
- Marketing (AREA)
- Primary Health Care (AREA)
- Strategic Management (AREA)
- Tourism & Hospitality (AREA)
- General Business, Economics & Management (AREA)
- Management, Administration, Business Operations System, And Electronic Commerce (AREA)
Abstract
The invention provides a power grid resource graph superposition analysis method and a device based on a PostGIS, wherein the method comprises the following steps: establishing gist indexes for a power grid resource table in a PostGIS database, wherein the power grid resource table comprises power grid resource graphic data, and the power grid resource graphic data comprises planar graphic data of a plurality of layers; carrying out index aggregation on gist indexes in the power grid resource table to obtain aggregated power grid resource graphic data; splitting each polysemous surface in the planar graphic data of each layer into a plurality of univocal surfaces to obtain the univocal surface data of each layer; and performing superposition analysis on the aggregated power grid resource graphic data based on the univocal surface data of each layer. The invention can perform superposition analysis on the power grid resource graphic data and save server resources.
Description
Technical Field
The invention relates to the field of electric power GIS space analysis, in particular to a power grid resource graph superposition analysis method and device based on a PostGIS.
Background
In the construction of a national grid information system, a Geographic Information System (GIS) becomes an indispensable component, and the display, alarm positioning, spatial analysis and the like of grid resource graphs based on the GIS become the most common functions of the grid information system, for example, a large number of surface graphs such as five-area graphs and weather early warning are accessed in a running inspection management and control system, and the grid resource graphs composed of the surface graphs need to be superposed and analyzed. Such an overlay analysis task generally has the following features: firstly, the planar graph features are complex and extremely irregular, such as nationwide distribution characteristics of a rainstorm isosurface; secondly, the analysis task is heavy, such as the analysis task of hundreds of batches of weather early warning every day; thirdly, the analysis timeliness requirement is high, if the weather early warning data is pushed once every 6 hours, if the analysis task is completed in more than 6 hours, the analysis result has no guiding significance; fourthly, the types of the power grid resources affected by analysis are multiple, and the quantity of single power grid resources is also multiple. In the prior art, when superposition analysis is carried out, the problem of serious resource waste of the server exists, so that the performance of the server is very low.
Disclosure of Invention
The embodiment of the invention provides a power grid resource graph superposition analysis method based on a PostGIS (geographic information System), which is used for carrying out superposition analysis on power grid resource graph data and saving server resources, and comprises the following steps:
establishing gist indexes for a power grid resource table in a PostGIS database, wherein the power grid resource table comprises power grid resource graphic data, and the power grid resource graphic data comprises planar graphic data of a plurality of layers;
carrying out index aggregation on gist indexes in the power grid resource table to obtain aggregated power grid resource graphic data;
splitting each polysemous surface in the planar graphic data of each layer into a plurality of univocal surfaces to obtain the univocal surface data of each layer;
and performing superposition analysis on the aggregated power grid resource graphic data based on the univocal surface data of each layer.
The embodiment of the invention provides a power grid resource graph superposition analysis device based on a PostGIS (geographic information System), which is used for carrying out superposition analysis on power grid resource graph data and saving server resources, and comprises the following components:
the gist index establishing module is used for establishing gist indexes for a power grid resource table in a PostGIS database, wherein the power grid resource table comprises power grid resource graphic data, and the power grid resource graphic data comprises planar graphic data of a plurality of layers;
the index aggregation module is used for carrying out index aggregation on gist indexes in the power grid resource table to obtain aggregated power grid resource graphic data;
the splitting module is used for splitting each polysemous surface in the planar graphic data of each layer into a plurality of univocal surfaces to obtain the univocal surface data of each layer;
and the analysis module is used for performing superposition analysis on the aggregated power grid resource graphic data based on the univocal surface data of each layer.
The embodiment of the invention also provides computer equipment which comprises a memory, a processor and a computer program which is stored on the memory and can run on the processor, wherein the processor realizes the power grid resource graph superposition analysis method based on the PostGIS when executing the computer program.
The embodiment of the invention also provides a computer readable storage medium, which stores a computer program for executing the power grid resource graph overlay analysis method based on the PostGIS.
In the embodiment of the invention, a gist index is established for a power grid resource table in a PostGIS database, wherein the power grid resource table comprises power grid resource graphic data, and the power grid resource graphic data comprises planar graphic data of a plurality of layers; carrying out index aggregation on gist indexes in the power grid resource table to obtain aggregated power grid resource graphic data; splitting each polysemous surface in the planar graphic data of each layer into a plurality of univocal surfaces to obtain the univocal surface data of each layer; and performing superposition analysis on the aggregated power grid resource graphic data based on the univocal surface data of each layer. In the process, index aggregation is carried out on the gist index in the power grid resource table, so that the power grid resource graphic data are stored more intensively, the data searching times are greatly reduced during the gist index range scanning, and the data cache is hit more easily, so that the server resource is saved; each polysemous surface in the planar graphic data of each layer is divided into a plurality of monosemous surfaces, so that the range associated by the index of the whole query is greatly reduced, namely, the invalid query space is greatly compressed, and the server resources are further saved.
Drawings
In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings used in the description of the embodiments or the prior art will be briefly described below, it is obvious that the drawings in the following description are only some embodiments of the present invention, and for those skilled in the art, other drawings can be obtained according to the drawings without creative efforts. In the drawings:
FIG. 1 is a flowchart of a PostGIS-based power grid resource graph overlay analysis method in an embodiment of the present invention;
FIG. 2 is a diagram illustrating an index scan range before splitting the polysemous surface according to an embodiment of the present invention;
FIG. 3 is a diagram illustrating an index scan range after polysemous splitting according to an embodiment of the present invention;
FIG. 4 is a schematic diagram of a semantic surface satisfying a predetermined complexity requirement obtained in an embodiment of the present invention;
fig. 5 is a detailed flowchart of a power grid resource graph overlay analysis method based on the PostGIS according to an embodiment of the present invention;
fig. 6 is a schematic diagram of a power grid resource graph overlay analysis device based on the PostGIS in the embodiment of the present invention.
Detailed Description
In order to make the objects, technical solutions and advantages of the embodiments of the present invention more apparent, the embodiments of the present invention are further described in detail below with reference to the accompanying drawings. The exemplary embodiments and descriptions of the present invention are provided to explain the present invention, but not to limit the present invention.
In the description of the present specification, the terms "comprising," "including," "having," "containing," and the like are used in an open-ended fashion, i.e., to mean including, but not limited to. Reference to the description of the terms "one embodiment," "a particular embodiment," "some embodiments," "for example," etc., means that a particular feature, structure, or characteristic described in connection with the embodiment or example is included in at least one embodiment or example of the application. In this specification, the schematic representations of the terms used above do not necessarily refer to the same embodiment or example. Furthermore, the particular features, structures, or characteristics described may be combined in any suitable manner in any one or more embodiments or examples. The sequence of steps involved in the embodiments is for illustrative purposes to illustrate the implementation of the present application, and the sequence of steps is not limited and can be adjusted as needed.
Fig. 1 is a flowchart of a power grid resource graph overlay analysis method based on a PostGIS in the embodiment of the present invention, and as shown in fig. 1, the method includes:
102, carrying out index aggregation on a gist index in a power grid resource table to obtain aggregated power grid resource graphic data;
103, splitting each ambiguous surface in the planar graphic data of each layer into a plurality of ambiguous surfaces to obtain the ambiguous surface data of each layer;
and 104, performing superposition analysis on the aggregated power grid resource graphic data based on the univocal surface data of each layer.
In the embodiment of the invention, index aggregation is carried out on the gist index in the power grid resource table, so that the power grid resource graphic data are stored more intensively, the data searching times are greatly reduced during the gist index range scanning, and the data cache is hit more easily, thereby saving the server resource; each polysemous surface in the planar graphic data of each layer is divided into a plurality of monosemous surfaces, so that the range associated by the index of the whole query is greatly reduced, namely, the invalid query space is greatly compressed, and the server resources are further saved.
In step 101, a PostGIS is a spatial database storing a grid resource table, where the grid resource table may be a table of a power station, a tower, a line, etc., and a gist index is a general index interface, which is called a generalized search Tree, and is not only suitable for the search of spatial data types, but also suitable for other data types. The method comprises the following steps of establishing a gist index for a power grid resource table in a post GIS database, namely establishing the gist index in advance for a get column of a power station, a tower, a line and the like, wherein query performance is improved mainly based on the gist index, and the instruction for establishing the gist index is as follows:
Create index tablename_geom_idx on tablename using gist(geom)；
in step 102, index aggregation is performed on the gist index in the power grid resource table, and the purpose of index aggregation is to physically reorder all power grid resource graphic data rows in the power grid resource table corresponding to the gist index according to the sequence of the gist index condition, so that power grid resource graphic data storage is more centralized by reordering, the number of data search times is greatly reduced during gist index range scanning, and data cache is more easily hit. The commands for index aggregation are as follows:
cluster tablename_geom_idx on tablename；
in step 103, the planar graphic data of each layer includes multiple ambiguous surfaces (planar graphics of MultiPolygon type) and multiple unambiguous surfaces (planar graphics of Polygon type), where a ambiguous surface is a set composed of multiple ambiguous surfaces, and there is usually a large discontinuous area between a ambiguous surface and a ambiguous surface, resulting in a particularly large circumscribed rectangle of the whole meaning. If the circumscribed rectangle of the planar graph is larger, the records of the planar graph scanned by the gist index are more than the records to be inquired actually, the calculation times when the scanned planar graph and the planar graph inquired actually are overlapped are increased, the CPU (Central processing Unit) of the database is amplified, and the performance of the server is reduced. Therefore, the single polysemous surface is divided into the plurality of monosemous surfaces, and each monosemous surface uses the own circumscribed rectangle as the index condition, so that discontinuous areas between the monosemous surfaces can be dissolved, and the range associated by the index of the whole query is greatly reduced. Fig. 2 is a schematic diagram of an index scanning range before splitting of an ambiguous surface in the embodiment of the present invention, specifically, a distribution diagram of a rainstorm isosurface and 12 ten thousand towers at a certain time, fig. 3 is a schematic diagram of an index scanning range after splitting of an ambiguous surface in the embodiment of the present invention, and fig. 3 is a distribution diagram of a rainstorm isosurface and 12 ten thousand towers at a certain time corresponding to fig. 2, and it is seen that an invalid query space is greatly compressed.
In step 104, since the univocal surface data of the multiple layers is obtained previously, the aggregated power grid resource graph data can be subjected to superposition analysis.
In an embodiment, the method further comprises:
if the storage mode of the planar graphic data of the plurality of layers of the power grid resource graphic data in the PostGIS database is a main mode, the storage mode of the planar graphic data is changed from the main mode to an external mode.
In the above embodiment, because the planar graphic data is very complex, such as a five-region graph, a weather warning isosurface graph, and planar graphics are all large objects, the PostGIS database defaults to adopting a main compression storage mode, when in actual use, decompression is performed first and then the application is performed, which is time-consuming, but the external mode is adopted to store the column objects of the planar graphics, which is uncompressed storage, which reduces decompression time consumption, and when in use, the large objects are not required to be decompressed reversely, so that the large objects are directly extracted and used, i.e., the storage space is sacrificed, the query response time is improved, the query result can be returned quickly, and the modification command for changing the storage mode of the planar graphic data from the main mode to the external mode is as follows:
alter table tablename alter column geom set storage external；
in an embodiment, after splitting each ambiguous surface in the planar graphics data of each layer into a plurality of ambiguous surfaces, the method further includes:
for each univocal surface, when the complexity of the univocal surface does not meet the preset complexity requirement, splitting each univocal surface into a plurality of univocal surfaces meeting the preset complexity requirement.
In an embodiment, the method further comprises:
and calculating the complexity of the univocal plane according to the circumscribed rectangular area and the actual area of the univocal plane.
In the above two embodiments, as can be seen from fig. 3, in some service scenarios, even the split univocal plane is still too complex, i.e. the circumscribed rectangle is larger than the actual graph. The method includes the steps that a ST _ Subdivide method provided by a PostGIS database is adopted, Polygon is cut into smaller and simpler univocal surfaces, each univocal surface is simple enough, a self external rectangle is used as an index association condition for each univocal surface, an original large number of invalid query ranges are compressed, and each univocal surface can be split into a plurality of univocal surfaces meeting preset complexity requirements when the complexity of the univocal surface does not meet the preset complexity requirements. Fig. 4 is a schematic diagram of a univocal surface meeting a preset complexity requirement obtained in the embodiment of the present invention, and fig. 4 is a distribution diagram of a rainstorm isosurface and 12 ten thousand towers corresponding to fig. 3 at a certain moment.
Based on the foregoing embodiment, the present invention provides the following embodiment to explain a detailed flow of a power grid resource graph overlay analysis method based on a PostGIS, and fig. 5 is a detailed flow chart of the power grid resource graph overlay analysis method based on the PostGIS according to the embodiment of the present invention, as shown in fig. 5, in an embodiment, the detailed flow of the power grid resource graph overlay analysis method based on the PostGIS includes:
and step 506, performing superposition analysis on the aggregated power grid resource graphic data based on the univocal surface data of each layer.
Of course, it can be understood that the detailed flow of the power grid resource graph overlay analysis method based on the PostGIS may have other variations, and the related variations all fall within the scope of the present invention.
A specific embodiment is given below to illustrate a specific application of the power grid resource graph overlay analysis method based on the PostGIS provided by the embodiment of the present invention.
First, taking an rainstorm isosurface and 12-ten-thousand-tower distribution as an example at a certain time, as shown in fig. 2, a gist index is established for a power grid resource table in a PostGIS database, and a command for establishing the gist index is as follows:
create index wnzy_gt_geom_idx on wnzy_gt using gist(geom)；
after the gist index is established, the performance test statements for the server running the method of the present invention are as follows:
explain(analyse,verbose,timing,costs,buffers)Select count(a.*)fromwnzy_gt a,
rain_multi b where ST_Intersects(a.geom,b.geom)；
table 1 sets up a look-up performance comparison table for the gist index.
TABLE 1 list of query performance comparison tables before and after gist index establishment
| gist index | Query mode | Scanning recording | Filter records | Return record | Time consuming to query |
| Is not established | Sequential scanning | 128254 | 112867 | 15387 | 92414.269ms |
| Has already been established | Index scanning | 128211 | 112824 | 15387 | 46971.095ms |
As can be seen from Table 1, after the gist index is established, the query time is shortened by half. The number of filter records is very large compared to the number of return records, and it can also be seen from fig. 2 that the range of index scans is very large covering almost all the tower data. It also means that the existing GIS space analysis solution used conventionally has a serious "CPU magnification problem" when processing the overlay analysis of complex planar graphics.
And carrying out index aggregation on the gist index in the power grid resource table, wherein the index aggregation command is as follows:
cluster wnzy_gt_geom_idx on wnzy_gt；
table 2 is a comparison table of spatial analysis performance before and after index aggregation.
TABLE 2 index comparison table of spatial analysis performance before and after clustering
| Analysis method | Scanning cache pages | Return record | Time consuming to query |
| Before cluster | 12793 | 15387 | 54.245ms |
| After gathering | 3333 | 15387 | 50.178ms |
As can be seen from the table 2, before the indexes are gathered, the original power grid resource graphic data are dispersedly stored in different cache pages, and after the indexes are gathered, the power grid resource graphic data are stored more intensively, so that the scanning cache pages are fewer, IO (input/output) expenses are needed when the database engine scans the cache pages, the number of the IO scanning cache pages is reduced through the index gathering, and the query speed is increased.
If the storage mode of the planar graphic data of the plurality of layers of the power grid resource graphic data in the PostGIS database is a main mode, the storage mode of the planar graphic data is changed from the main mode to an external mode, and the modification command is as follows:
alter table rain_multi alter column geom set storage external；
update rain_multi set geom＝st_setsrid(geom,4326)；
the following performance test commands were used:
explain(analyse,verbose,timing,costs,buffers)Select count(a.*)fromwnzy_gt a,
rain_multi b where ST_Intersects(a.geom,b.geom)；
table 3 is a comparison table of query performance before and after modifying the storage manner of the planar graphic data.
TABLE 3 Inquiry performance comparison table before and after modifying storage mode of planar graph data
| Geom storage strategy | Query mode | Scanning recording | Filter records | Return record | Time consuming to query |
| Main strategy | Index scanning | 128211 | 112824 | 15387 | 46971.095ms |
| External strategy | Index scanning | 128211 | 112824 | 15387 | 7166.465ms |
As can be seen from table 3, the query performance can be greatly improved by only changing the storage policy of the complex planar pattern array.
Splitting each polysemous surface in the planar graphic data of each layer into a plurality of monosemous surfaces, wherein the splitting command is as follows, an ST _ NumGeometries function returns the number of the polysemons forming the polysemon object of the polysemons, ST _ geometriN is used for extracting a certain polysemon object from the polysemons, and the extracted result is the splitting result of the polysemons:
create table rain_polygon(id serial,geom geometry(Polygon,4326))；
alter table rain_polygon alter column geom set storage external；
insert into rain_polygon(geom)select ST_GeometryN(a.geom,n)As geomfrom rain_multi a
cross join generate_series(1,ST_NumGeometries(a.geom))n；
the performance test command is as follows:
explain(analyse,verbose,timing,costs,buffers)Select count(a.*)fromwnzy_gt a,
rain_polygon b where ST_Intersects(a.geom,b.geom)；
table 4 is a comparison table of spatial performance before and after polysemous surface splitting.
TABLE 4 comparison table of spatial properties before and after splitting of polysemous surface
| Analysis method | Query mode | Scanning recording | Filter records | Return record | Time consuming to query |
| Before splitting | Index scanning | 128211 | 112824 | 15387 | 7166.465ms |
| After splitting | Index scanning | 15643 | 256 | 15387 | 1174.476ms |
As can be seen from table 4, the scan record after splitting is about 1/8 before splitting, the number of filter records is small, the index hit rate after splitting is very accurate, and the query time after splitting is also about 1/6 before splitting. Referring to fig. 3, it can be seen that the range of the split circumscribed rectangle is greatly reduced, and a large number of discontinuous regions between the univocal planes Polygon are not in the query range.
And then, for each single meaning surface, when the complexity of the single meaning surface does not meet the preset complexity requirement, splitting each single meaning surface into a plurality of single meaning surfaces meeting the preset complexity requirement to form a smaller single meaning surface Polygon graph set.
Referring to fig. 3, even though the split univocal plane Polygon has some situations of complex graphs, small actual graph area and large external rectangle, the graph can be split into more atomic univocal plane Polygon to continue to compress the invalid query space.
The split command is as follows:
create table rain_subdivide(id serial,geom geometry(Polygon,4326))；
alter table rain_subdivide alter geom set storage external；
insert into rain_Subdivide(geom)select ST_SubDivide(geom,40)geom fromrain_polygon；
the performance test statement is as follows:
explain(analyse,verbose,timing,costs,buffers)Select count(a.*)fromwnzy_gt a,
rain_Subdivide b where ST_Intersects(a.geom,b.geom)；
table 5 is a comparison table of spatial properties before and after the splitting of the unambiguous plane.
TABLE 5 comparison table of the spatial analysis performance before and after complex Polygon cutting
| Analysis method | Query mode | Scanning recording | Filter records | Return record | Time consuming to query |
| Before cutting | Index scanning | 15643 | 256 | 15387 | 1174.476ms |
| After cutting | Index scanning | 15403 | 16 | 15387 | 54.245ms |
As can be seen from table 5, the number of the scan records after splitting is further reduced compared with that before splitting, the number of the filter records is less, the hit rate of the index after splitting is more concentrated on accuracy, and the query time after splitting is about 1/21 before splitting. Referring to fig. 4, it can be seen that each circumscribed rectangle of the decomposed atomized single-sense surfaces Polygon is small, and the discontinuous regions between the single-sense surfaces Polygon are completely disappeared.
Table 6 summarizes the query time consumption of each step, and it can be seen that the total query time consumption is not much, and the overall efficiency is high.
TABLE 6 query time for each step
The embodiment of the invention has the beneficial effects that:
(1) by changing the storage mode of the facet graphic data from a main mode to an external mode, a gist index is established for a power grid resource table in a PostGIS database and index aggregation is carried out, so that the IO amplification problem is effectively avoided, and the performance of a server is improved;
(2) by splitting each polysemous surface into a plurality of monosemous surfaces and splitting each polysemous surface into a plurality of monosemous surfaces meeting the preset complexity requirement, the effective query range of the spatial index is greatly optimized, the problem of CPU amplification is effectively avoided, and the performance of the server is improved;
(3) after the double optimization, the query return time provided by the embodiment of the invention is shortened by 500-fold compared with the query return time without the optimization, and the superposition analysis performance is greatly improved, so that a larger amount of analysis tasks can be completed within the specified time.
In summary, in the method provided in the embodiment of the present invention, a gist index is established for a power grid resource table in a PostGIS database, where the power grid resource table includes power grid resource graph data, and the power grid resource graph data includes planar graph data of multiple layers; carrying out index aggregation on gist indexes in the power grid resource table to obtain aggregated power grid resource graphic data; splitting each polysemous surface in the planar graphic data of each layer into a plurality of univocal surfaces to obtain the univocal surface data of each layer; and performing superposition analysis on the aggregated power grid resource graphic data based on the univocal surface data of each layer. In the process, index aggregation is carried out on the gist index in the power grid resource table, so that the power grid resource graphic data are stored more intensively, the data searching times are greatly reduced during the gist index range scanning, and the data cache is hit more easily, so that the server resource is saved; each polysemous surface in the planar graphic data of each layer is divided into a plurality of monosemous surfaces, so that the range associated by the index of the whole query is greatly reduced, namely, the invalid query space is greatly compressed, and the server resources are further saved. In addition, the storage mode of the planar graphic data is changed from a main mode to an external mode, so that the query response time can be prolonged, and the query result can be returned quickly. Each univocal surface is divided into a plurality of univocal surfaces meeting the preset complexity requirement, so that the coverage range of the external rectangle can be compressed, the index scanning range is reduced, and the query performance is integrally improved.
Based on the same inventive concept, the embodiment of the present invention further provides a power grid resource graph overlay analysis device based on the PostGIS, as described in the following embodiments. Because the principles for solving the problems are similar to the power grid resource graph overlay analysis method based on the PostGIS, the implementation of the device can refer to the implementation of the method, and repeated parts are not repeated.
Fig. 6 is a schematic diagram of a power grid resource graph overlay analysis device based on a PostGIS in an embodiment of the present invention, and as shown in fig. 6, the device includes:
a gist index establishing module 601, configured to establish a gist index for a power grid resource table in a PostGIS database, where the power grid resource table includes power grid resource graph data, and the power grid resource graph data includes multiple layers of planar graph data;
the index aggregation module 602 is configured to perform index aggregation on a gist index in the power grid resource table to obtain aggregated power grid resource graph data;
a splitting module 603, configured to split each ambiguous surface in the planar graphics data of each layer into multiple ambiguous surfaces, and obtain ambiguous surface data of each layer;
and the analysis module 604 is configured to perform superposition analysis on the aggregated power grid resource graph data based on the univocal surface data of each layer.
In one embodiment, the apparatus further comprises a storage modification module 605 configured to:
if the storage mode of the planar graphic data of the plurality of layers of the power grid resource graphic data in the PostGIS database is a main mode, the storage mode of the planar graphic data is changed from the main mode to an external mode.
In an embodiment, the splitting module 603 is further configured to:
for each univocal surface, when the complexity of the univocal surface does not meet the preset complexity requirement, splitting each univocal surface into a plurality of univocal surfaces meeting the preset complexity requirement.
In an embodiment, the apparatus further comprises a complexity calculation module 606 configured to:
and calculating the complexity of the univocal plane according to the circumscribed rectangular area and the actual area of the univocal plane.
In summary, in the apparatus provided in the embodiment of the present invention, a gist index is established for a power grid resource table in a PostGIS database, where the power grid resource table includes power grid resource graph data, and the power grid resource graph data includes planar graph data of multiple layers; carrying out index aggregation on gist indexes in the power grid resource table to obtain aggregated power grid resource graphic data; splitting each polysemous surface in the planar graphic data of each layer into a plurality of univocal surfaces to obtain the univocal surface data of each layer; and performing superposition analysis on the aggregated power grid resource graphic data based on the univocal surface data of each layer. In the process, index aggregation is carried out on the gist index in the power grid resource table, so that the power grid resource graphic data are stored more intensively, the data searching times are greatly reduced during the gist index range scanning, and the data cache is hit more easily, so that the server resource is saved; each polysemous surface in the planar graphic data of each layer is divided into a plurality of monosemous surfaces, so that the range associated by the index of the whole query is greatly reduced, namely, the invalid query space is greatly compressed, and the server resources are further saved. In addition, the storage mode of the planar graphic data is changed from a main mode to an external mode, so that the query response time can be prolonged, and the query result can be returned quickly. Each univocal surface is divided into a plurality of univocal surfaces meeting the preset complexity requirement, so that the coverage range of the external rectangle can be compressed, the index scanning range is reduced, and the query performance is integrally improved.
As will be appreciated by one skilled in the art, embodiments of the present invention may be provided as a method, system, or computer program product. Accordingly, the present invention may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the present invention may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.
The present invention is described with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each flow and/or block of the flow diagrams and/or block diagrams, and combinations of flows and/or blocks in the flow diagrams and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instruction means which implement the function specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
The above-mentioned embodiments are intended to illustrate the objects, technical solutions and advantages of the present invention in further detail, and it should be understood that the above-mentioned embodiments are only exemplary embodiments of the present invention, and are not intended to limit the scope of the present invention, and any modifications, equivalent substitutions, improvements and the like made within the spirit and principle of the present invention should be included in the scope of the present invention.
Claims (10)
1. A power grid resource graph superposition analysis method based on a PostGIS is characterized by comprising the following steps:
establishing gist indexes for a power grid resource table in a PostGIS database, wherein the power grid resource table comprises power grid resource graphic data, and the power grid resource graphic data comprises planar graphic data of a plurality of layers;
carrying out index aggregation on gist indexes in the power grid resource table to obtain aggregated power grid resource graphic data;
splitting each polysemous surface in the planar graphic data of each layer into a plurality of univocal surfaces to obtain the univocal surface data of each layer;
and performing superposition analysis on the aggregated power grid resource graphic data based on the univocal surface data of each layer.
2. The PostGIS-based grid resource graph overlay analysis method of claim 1, further comprising:
if the storage mode of the planar graphic data of the plurality of layers of the power grid resource graphic data in the PostGIS database is a main mode, the storage mode of the planar graphic data is changed from the main mode to an external mode.
3. The method for superimposed analysis of power grid resource graphics based on PostGIS as claimed in claim 1, wherein after splitting each polysome in the planar graphics data of each layer into a plurality of monosome, further comprising:
for each univocal surface, when the complexity of the univocal surface does not meet the preset complexity requirement, splitting each univocal surface into a plurality of univocal surfaces meeting the preset complexity requirement.
4. The PostGIS-based power grid resource graph overlay analysis method according to claim 3, further comprising:
and calculating the complexity of the univocal plane according to the circumscribed rectangular area and the actual area of the univocal plane.
5. The utility model provides a power grid resource figure stack analytical equipment based on postGIS which characterized in that includes:
the gist index establishing module is used for establishing gist indexes for a power grid resource table in a PostGIS database, wherein the power grid resource table comprises power grid resource graphic data, and the power grid resource graphic data comprises planar graphic data of a plurality of layers;
the index aggregation module is used for carrying out index aggregation on gist indexes in the power grid resource table to obtain aggregated power grid resource graphic data;
the splitting module is used for splitting each polysemous surface in the planar graphic data of each layer into a plurality of univocal surfaces to obtain the univocal surface data of each layer;
and the analysis module is used for performing superposition analysis on the aggregated power grid resource graphic data based on the univocal surface data of each layer.
6. The PostGIS-based grid resource pattern overlay analysis apparatus of claim 5, further comprising a storage modification module configured to:
if the storage mode of the planar graphic data of the plurality of layers of the power grid resource graphic data in the PostGIS database is a main mode, the storage mode of the planar graphic data is changed from the main mode to an external mode.
7. The PostGIS-based grid resource graph overlay analysis apparatus of claim 5, wherein the splitting module is further configured to:
for each univocal surface, when the complexity of the univocal surface does not meet the preset complexity requirement, splitting each univocal surface into a plurality of univocal surfaces meeting the preset complexity requirement.
8. The PostGIS-based grid resource graph overlay analysis apparatus of claim 7, further comprising a complexity calculation module for:
and calculating the complexity of the univocal plane according to the circumscribed rectangular area and the actual area of the univocal plane.
9. A computer device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, characterized in that the processor implements the method of any of claims 1 to 4 when executing the computer program.
10. A computer-readable storage medium, characterized in that the computer-readable storage medium stores a computer program for executing the method of any one of claims 1 to 4.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201911244651.4A CN111046244A (en) | 2019-12-06 | 2019-12-06 | PostGIS-based power grid resource graph superposition analysis method and device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201911244651.4A CN111046244A (en) | 2019-12-06 | 2019-12-06 | PostGIS-based power grid resource graph superposition analysis method and device |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN111046244A true CN111046244A (en) | 2020-04-21 |
Family
ID=70233688
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201911244651.4A Pending CN111046244A (en) | 2019-12-06 | 2019-12-06 | PostGIS-based power grid resource graph superposition analysis method and device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111046244A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN114490899A (en) * | 2021-12-27 | 2022-05-13 | 北京吉威数源信息技术有限公司 | Multi-layer space big data superposition and combination method, device, equipment and storage medium |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20130211797A1 (en) * | 2012-02-13 | 2013-08-15 | TaKaDu Ltd. | System and method for analyzing gis data to improve operation and monitoring of water distribution networks |
| CN104950187A (en) * | 2014-11-27 | 2015-09-30 | 国网山东省电力公司应急管理中心 | Power-grid-GIS-based lightning analysis and early warning method and system thereof |
| JP2016071833A (en) * | 2014-09-24 | 2016-05-09 | コリア インスティチュート オブ ジオサイエンス アンド ミネラル リソースズ | Method of analyzing two-dimensional geothermal resource data using web-based three-dimensional sectional view |
| CN105975619A (en) * | 2016-05-23 | 2016-09-28 | 国网山东省电力公司临沂供电公司 | Power grid graph drawing method based on mobile GIS platform |
| CN107066542A (en) * | 2017-03-14 | 2017-08-18 | 中国科学院计算技术研究所 | Vector space overlay analysis parallel method and system in GIS-Geographic Information System |
-
2019
- 2019-12-06 CN CN201911244651.4A patent/CN111046244A/en active Pending
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20130211797A1 (en) * | 2012-02-13 | 2013-08-15 | TaKaDu Ltd. | System and method for analyzing gis data to improve operation and monitoring of water distribution networks |
| JP2016071833A (en) * | 2014-09-24 | 2016-05-09 | コリア インスティチュート オブ ジオサイエンス アンド ミネラル リソースズ | Method of analyzing two-dimensional geothermal resource data using web-based three-dimensional sectional view |
| CN104950187A (en) * | 2014-11-27 | 2015-09-30 | 国网山东省电力公司应急管理中心 | Power-grid-GIS-based lightning analysis and early warning method and system thereof |
| CN105975619A (en) * | 2016-05-23 | 2016-09-28 | 国网山东省电力公司临沂供电公司 | Power grid graph drawing method based on mobile GIS platform |
| CN107066542A (en) * | 2017-03-14 | 2017-08-18 | 中国科学院计算技术研究所 | Vector space overlay analysis parallel method and system in GIS-Geographic Information System |
Non-Patent Citations (7)
| Title |
|---|
| DIGOAL: "PostgreSQL空间st_contains ，st_within空间包含搜索优化-降 IO 和降CPU(bound box) (多边形 GiST优化)", pages 1 - 20 * |
| JASIM,M ETC.: "New graph mining algorithm for vector GIS systems", pages 335 - 338 * |
| MB5FF40AFD04638: "postgresql-数据库物理存储", pages 1 - 7, Retrieved from the Internet <URL:https://blog.51cto.com/u_15076218/4601723> * |
| 大肚熊: "postgresql TOAST存储方式", pages 1 - 2, Retrieved from the Internet <URL:https://www.cnblogs.com/daduxiong/archive/2010/08/23/1806706.html> * |
| 谢靖 等: "GIS数据支持的面状要素的地图自动综合", pages 181 - 185 * |
| 遥想公瑾当年: "基于PostGIS叠加分析优化——气象预警分析案例实践", pages 1 - 11 * |
| 郝宇星: "基于地图服务的配电网设备信息检索系统的研究", pages 042 - 1230 * |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN114490899A (en) * | 2021-12-27 | 2022-05-13 | 北京吉威数源信息技术有限公司 | Multi-layer space big data superposition and combination method, device, equipment and storage medium |
| CN114490899B (en) * | 2021-12-27 | 2022-09-23 | 北京吉威数源信息技术有限公司 | Multi-layer space big data superposition and combination method, device, equipment and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| EP2743845A1 (en) | Graph traversal operator inside a column store | |
| CN103646079A (en) | Distributed index for graph database searching and parallel generation method of distributed index | |
| CN103309958A (en) | OLAP star connection query optimizing method under CPU and GPU mixing framework | |
| Al Naami et al. | GISQF: An efficient spatial query processing system | |
| CN111881326A (en) | Graph data storage method, device and equipment and readable storage medium | |
| CN111563081A (en) | Vector element parallel computing method and device, storage medium and terminal | |
| CN105447172A (en) | Data processing method and system under Hadoop platform | |
| CN116126901A (en) | Data processing method, device, electronic equipment and computer readable storage medium | |
| CN115905630A (en) | Graph database query method, device, equipment and storage medium | |
| CN104239470A (en) | Distributed environment-oriented space data compound processing system and method | |
| CN111046244A (en) | PostGIS-based power grid resource graph superposition analysis method and device | |
| CN113127741B (en) | Cache method for reading and writing data of mass users and posts in part-time post recommendation system | |
| CN108319604B (en) | Optimization method for association of large and small tables in hive | |
| CN108334532B (en) | Spark-based Eclat parallelization method, system and device | |
| CN112699134A (en) | Distributed graph database storage and query method based on graph subdivision | |
| CN117370337A (en) | Partition creation method, partition creation device, computer equipment and storage medium | |
| CN113449052A (en) | Method for establishing spatial index, method and device for querying spatial region | |
| CN111046054A (en) | Method and system for analyzing power marketing business data | |
| CN112269797B (en) | Multidimensional query method of satellite remote sensing data on heterogeneous computing platform | |
| Giannousis et al. | Distributed execution of spatial SQL queries | |
| CN110083598B (en) | Spark-oriented remote sensing data indexing method, system and electronic equipment | |
| CN113704296A (en) | Spark SQL-based computational push-down query optimization method | |
| Li et al. | SP-phoenix: a massive spatial point data management system based on phoenix | |
| KR20160047239A (en) | The column group selection method for storing datea efficiently in the mixed olap/oltp workload environment | |
| CN116450872B (en) | Spark distributed vector grid turning method, system and equipment |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |