我很難嘗試改進兩個空間表之間的交叉點，我想收到有關表設計、查詢或 dba 配置的任何提示。

表：

teste.recorte_grade表現在有 1,655,569 行，但這是為這個 900 萬行表的測試而製作的子樣本。

CREATE TABLE teste.recorte_grade
(
 id integer NOT NULL DEFAULT nextval('teste."Recorte_grade_id_seq"'::regclass),
 id_gre character varying(21),
 indice_gre character varying(16),
 the_geom geometry(Polygon),
 CONSTRAINT "Recorte_grade_pkey" PRIMARY KEY (id)
)
WITH (
 OIDS=FALSE
);
CREATE INDEX sidx_recorte_grade_geom
 ON teste.recorte_grade
 USING gist
 (the_geom);

表teste2.uso_2012有 177,888 行，這是它將擁有的所有數據。

CREATE TABLE teste2.uso_2012
(
 id integer NOT NULL,
 gridcode smallint NOT NULL,
 geom geometry(MultiPolygon) NOT NULL,
 CONSTRAINT pk_id_uso_2012 PRIMARY KEY (id)
)
WITH (
 OIDS=FALSE
);    
CREATE INDEX idx_hash_calsse_uso_2012_teste2
 ON teste2.uso_2012
 USING hash
 (gridcode);    
CREATE INDEX sidx_uso_2012_geom_teste2
 ON teste2.uso_2012
 USING gist
 (geom);

問題：

我想要的只是gridcode兩個表之間每個交集的區域和，基本上，這個查詢的結果：

Select grade.id, uso.gridcode, , st_area(st_intersection(grade.the_geom, uso.geom)) 
from teste2.uso_2012 as uso 
   inner join teste.recorte_grade as grade on ST_Intersects(grade.the_geom, uso.geom) = 't' 
   order by grade.id

然而，當我決定取消它的執行時，這個查詢執行了大約**16 個小時，沒有任何結果。**如果子樣本花了這麼長時間，想像一下完整的數據集。

兩個表之前都進行了真空分析。

EXPLAIN對於慢查詢：http ://explain.depesz.com/s/PEV

gridcode我認為每次將其分成多個查詢可能是一個好主意。這就是我創建雜湊索引的原因。

這是teste2.uso_2012表中的數據分佈：

+----------+---------------+---------------+
| Gridcode | Polygon Count |  Total Area   |
+----------+---------------+---------------+
|        1 |          4100 |   40360812499 |
|        2 |         16992 |  516217687499 |
|        3 |         22745 |  955870062499 |
|        4 |         32243 |  802054562500 |
|        5 |          4286 |   69461437500 |
|        6 |         16081 | 3200491312500 |
|        7 |         40704 |  447186874999 |
|        8 |          1776 |   89474187499 |
|        9 |          1894 |   41834437499 |
|       10 |         15918 | 1765555312500 |
|       11 |          5158 |  306742062499 |
|       12 |         15715 |  274680250000 |
|       14 |           275 |    5606687500 |
+----------+---------------+---------------+

以下是個人的一些查詢結果gridcodes：

Select grade.id, uso.gridcode, st_area(st_intersection(grade.the_geom, uso.geom)) from teste.recorte_2012 as uso inner join teste.recorte_grade as grade on ST_Intersects(grade.the_geom, uso.geom) = 't' where uso.gridcode = 1
   --11 seconds
   --10,069 rows retrieved
   --http://explain.depesz.com/s/tZV1

   Select grade.id, uso.gridcode, st_area(st_intersection(grade.the_geom, uso.geom)) from teste.recorte_2012 as uso inner join teste.recorte_grade as grade on ST_Intersects(grade.the_geom, uso.geom) = 't' where uso.gridcode = 2
   --3275 seconds
   --200,682 rows retrieved

Select grade.id, uso.gridcode, st_area(st_intersection(grade.the_geom, uso.geom)) from teste2.uso_2012 as uso inner join teste.recorte_grade as grade on ST_Intersects(grade.the_geom, uso.geom) = 't' where uso.gridcode = 2
--Total query runtime: 3333 seconds
--200,682 rows retrieved.

   Select grade.id, uso.gridcode, st_area(st_intersection(grade.the_geom, uso.geom)) from teste.recorte_2012 as uso inner join teste.recorte_grade as grade on ST_Intersects(grade.the_geom, uso.geom) = 't' where uso.gridcode = 10
   --5 hours without result

teste.recorte_2012並且teste2.uso_2012幾乎是同一張表，其中uso_2012少了 1 列。

如您所見，這似乎不太有希望。有什麼建議可以加快這個過程嗎？

我正在考慮創建一個儲存過程來循環 177,888 行並直接獲取每一個的交叉點和麵積。這是一個好主意嗎？

配置：

• 共享緩衝區：1920 MB
• 工作記憶體：36 MB
• 有效記憶體大小：5632 MB

伺服器資訊：

• PostgreSQL 9.2.14
• CENTOS 6.4 版
• 8GB SRAM
• 儲存 V7000
• 英特爾(R) 至強(R) CPU E5-2620 2 GHZ
• POSTGIS=“2.0.2 r10789” GEOS=“3.3.6-CAPI-1.7.6” PROJ=“Rel. 4.8.0，2012 年 3 月 6 日” GDAL=“GDAL 1.9.2，2012/10/08 發布” LIBXML =“2.7.6” 光柵

該伺服器在其他數據庫之間共享，但在我執行查詢的同時沒有並行執行繁重的程序。

我有一些非常複雜的特殊功能，幾乎有 100k 個頂點。關於 Postgres 版本，只有 DBA 可以更新基礎設施，我不是其中之一。

更新：

自從發布以來，問題不斷發展，現在我們處理了 140kk+ 行。然而，Postgis 也在發展，現在可以“修復”特徵表。不再需要Dice來自 ArcMap。

我使用ST_VoronoiPolygons方法做到了。我創建了一個工作要點，其功能可以將原始表上的特徵分解為可行的大小以進行處理。

原答案：

有關資訊，我可以使用其他工具並拆分工作負載來做到這一點：

• 為了一切正常，我不得不將每個特徵限制在最大 800 個頂點和最大 15 平方公里內。我在plsql上嘗試了一些工具，甚至是一個重複的過程，但沒有成功。我唯一嚐試並正確拆分所有內容的是 ArcMap 的Dice功能；
• 我將我的 170k+ 行分成 20 行的塊，並並行執行 6 個查詢實例，以使用 .net 控制台應用程序計算區域；
• 使用儲存的區域，我能夠進行一些處理，也可以分塊和並行計算，以計算每個 9kk+ 單元格的值。

這個過程現在“只”需要 3 個小時來完成。

---

ST_Intersects很輕。問題是這個計算：

st_area(st_intersection(grade.the_geom, uso.geom)).

建構複雜特徵的交集併計算面積是一項艱鉅的任務。

引用自：https://dba.stackexchange.com/questions/133312