A query optimization strategy for SHP data coordinate transformation based on Control Point Library in Webgis

Article copyright by the author Xiaohui Li is and the blog park, if reproduced please clearly indicate the source: http://www.cnblogs.com/naaoveGIS/

1. Preface

At present, the project is based on the control Point Library of SHP data coordinate transformation, the process is roughly: Traverse layer features, get each feature's coordinate string, query control point Library, coordinate transformation, build new features, and finally build a new layer. The efficiency of this method is as follows:

A. The control point Library has 100W control point pairs.

B. The SHP data to be converted has 5K polygon features.

It takes about 120 minutes to complete the conversion.

This efficiency is relatively low and time-consuming increases exponentially if you want to batch-convert more layer group data with a large number of features. Here, we have done some column optimization explorations.

2. Analysis of Ideas

The above method, the most time-consuming is the large table query, that is, in the control Point library to query the corresponding reference coordinates.

There are many kinds of optimization strategies for large table queries, such as changing large tables to multiple small tables, establishing related indexes based on business, and so on. Here we explore another method, the flowchart is as follows:

The core of this idea is: after traversing the acquisition of a single feature, a view of the local information is established based on the extent of the feature, reducing unnecessary full-table queries.

The final result test is:

A. The control point Library has 100W control point pairs.

B. The SHP data to be converted has 5K polygon features.

It takes about 5 minutes to complete the conversion.

3. Further optimization

The above approach is to use the View for query optimization, but you also need to control code destruction after each view is established. This process can affect efficiency.

3.1 Transaction Temp Table

Temporary tables are divided for session staging tables and transactional temporary tables.

A session temp table is a statement that the temporary table is definitely relevant. When a session does not exit, the data in the temporary tables exists and the session exits, and the data in the temporary table disappears. Also, in the case of multi-user operation, one session never blocks another session using a temporary table. Even if you lock a temporary table, a session does not block other sessions from using temporary tables.

A Transaction Temp table is a transaction-related, data in a temporary table that is truncated by itself when a transaction is committed or a transaction is rolled back.

Depending on the business requirements, the transaction temp table is selected here, mainly using the function that the data will be truncate automatically after the transaction ends.

The construction rules for the transaction temp table are as follows:

Create Global temporary table table_name
(col1 type1, col2 type2 ...)
on commit delete rows;

3.2 Test Results

After using the transaction staging table for retrofitting, the test results are as follows:

A. The control point Library has 100W control point pairs.

B. The SHP data to be converted has 5K polygon features.

It takes about 2.5 minutes to complete the conversion.

4. Summary

The core idea here is to make large table changes, but the small table is designed as a temporary table, this method is very obvious when filtering query data.

-----Welcome reprint, but retain the copyright, please indicate the source in obvious place: http://www.cnblogs.com/naaoveGIS/

A query optimization strategy for SHP data coordinate transformation based on Control Point Library in Webgis