postgis-workshop/indexing — Chinese (Simplified) @ OSGeo Weblate: `ST_Intersects <http://postgis.net/docs/ST_Intersects.html>`

Translation

English

Chinese (Simplified)

Needs editing

Skip

	English	Chinese (Simplified)	Actions
	Our first step is to remove the index.	第一步，先移除空间索引。
	The ``DROP INDEX`` statement drops an existing index from the database system. For more information, see the PostgreSQL `documentation <http://www.postgresql.org/docs/current/interactive/sql-dropindex.html>`_.	``DROP INDEX`` 表达式移除数据库中现有的索引。更多信息参考 PostgreSQL `文档 <http://www.postgresql.org/docs/current/interactive/sql-dropindex.html>`_。
	Now, watch the "Timing" meter at the lower right-hand corner of the pgAdmin query window and run the following. Our query searches through every single census block in order to identify blocks that contain subway stops that start with "B".	执行命令后，观察 pgAdmin 查询窗口右下角的 “Timing” 面板。这个查询搜索的是每个名称以 “B” 开头的地铁站所在地区。
	The ``nyc_census_blocks`` table is very small (only a few thousand records) so even without an index, the query only takes 300 ms on my test computer.	``nyc_census_blocks`` 数据表很小（大概千把个记录），所以它甚至都不需要索引，这个查询在我的电脑上只用了 300 毫秒。
	Now add the spatial index back in and run the query again.	现在重新添加空间索引并再次运行查询。
	The ``USING GIST`` clause tells PostgreSQL to use the generic index structure (GIST) when building the index. If you receive an error that looks like ``ERROR: index row requires 11340 bytes, maximum size is 8191`` when creating your index, you have likely neglected to add the ``USING GIST`` clause.	``USING GIST`` 子句告诉 PostgreSQL 在创建索引时，使用通用的索引结构（GIST）。若你在创建索引时，报类似于 ``ERROR: index row requires 11340 bytes, maximum size is 8191`` 的错误，你可能就是少写了 ``USING GIST`` 子句。
	On my test computer the time drops to 50 ms. The larger your table, the larger the relative speed improvement of an indexed query will be.	在我的测试机上，查询时间降到了 50 毫秒。你的数据表越大，查询性能提升越明显。
	How Spatial Indexes Work	空间索引是如何起作用的
	Standard database indexes create a hierarchical tree based on the values of the column being indexed. Spatial indexes are a little different -- they are unable to index the geometric features themselves and instead index the bounding boxes of the features.	标准的数据库索引，是根据被索引的列的值去创建树结构的。空间索引略不同，因为数据库并不能索引几何字段的值 —— 也就是几何对象本身，我们改索引要素的范围边界框。
	In the figure above, the number of lines that intersect the yellow star is one, the red line. But the bounding boxes of features that intersect the yellow box is two, the red and blue ones.	上图中，和黄色星星相交的线的数量是 1，即红色那条线。但是与黄色框相交的范围框有红色和蓝色，共 2 个。
	The way the database efficiently answers the question "what lines intersect the yellow star" is to first answer the question "what boxes intersect the yellow box" using the index (which is very fast) and then do an exact calculation of "what lines intersect the yellow star" only for those features returned by the first test.	数据库求解 “什么线与黄色星相交” 这个问题，是先用空间索引求解 “什么范围框与黄色范围框相交” 这个问题的（速度非常快），然后才是 “什么线与黄色的星星相交”。上述过程仅对于第一次测试的空间要素而言。
	For a large table, this "two pass" system of evaluating the approximate index first, then carrying out an exact test can radically reduce the amount of calculations necessary to answer a query.	对于数量庞大的数据表，这种索引先行，然后局部精确计算的 “两遍法” 可以在根本上减少查询计算量。
	Both PostGIS and Oracle Spatial share the same "R-Tree" [#RTree]_ spatial index structure. R-Trees break up data into rectangles, and sub-rectangles, and sub-sub rectangles, etc. It is a self-tuning index structure that automatically handles variable data density, differing amounts of object overlap, and object size.	PostGIS 和 Oracle spatial 都用了 “R-Tree” [#RTree]_ 空间索引结构。R-Tree 把数据描述成一簇簇的矩形，它是一种自调整的索引结构，可以自动处理数据的数量、密度和大小等。
	Spatially Indexed Functions	空间索引函数
	Only a subset of functions will automatically make use of a spatial index, if one is available.	不是所有函数都会使用空间索引的，如果存在空间索引，那么支持使用空间索引的函数会自动使用它。
	`ST_Intersects <http://postgis.net/docs/ST_Intersects.html>`_	`ST_Intersects <http://postgis.net/docs/ST_Intersects.html>`_
	`ST_Contains <http://postgis.net/docs/ST_Contains.html>`_	`ST_Contains <http://postgis.net/docs/ST_Contains.html>`_
	`ST_Within <http://postgis.net/docs/ST_Within.html>`_	`ST_Within <http://postgis.net/docs/ST_Within.html>`_
	`ST_DWithin <http://postgis.net/docs/ST_DWithin.html>`_	`ST_DWithin <http://postgis.net/docs/ST_DWithin.html>`_
	`ST_ContainsProperly <http://postgis.net/docs/ST_ContainsProperly.html>`_	`ST_ContainsProperly <http://postgis.net/docs/ST_ContainsProperly.html>`_
	`ST_CoveredBy <http://postgis.net/docs/ST_CoveredBy.html>`_	`ST_CoveredBy <http://postgis.net/docs/ST_CoveredBy.html>`_
	`ST_Covers <http://postgis.net/docs/ST_Covers.html>`_	`ST_Covers <http://postgis.net/docs/ST_Covers.html>`_
	`ST_Overlaps <http://postgis.net/docs/ST_Overlaps.html>`_	`ST_Overlaps <http://postgis.net/docs/ST_Overlaps.html>`_
	`ST_Crosses <http://postgis.net/docs/ST_Crosses.html>`_	`ST_Crosses <http://postgis.net/docs/ST_Crosses.html>`_
	`ST_DFullyWithin <http://postgis.net/docs/ST_DFullyWithin.html>`_	`ST_DFullyWithin <http://postgis.net/docs/ST_DFullyWithin.html>`_
	`ST_3DIntersects <http://postgis.net/docs/ST_3DIntersects.html>`_	`ST_3DIntersects <http://postgis.net/docs/ST_3DIntersects.html>`_
	`ST_3DDWithin <http://postgis.net/docs/ST_3DDWithin.html>`_	`ST_3DDWithin <http://postgis.net/docs/ST_3DDWithin.html>`_
	`ST_3DDFullyWithin <http://postgis.net/docs/ST_3DDFullyWithin.html>`_	`ST_3DDFullyWithin <http://postgis.net/docs/ST_3DDFullyWithin.html>`_
	`ST_LineCrossingDirection <http://postgis.net/docs/ST_LineCrossingDirection.html>`_	`ST_LineCrossingDirection <http://postgis.net/docs/ST_LineCrossingDirection.html>`_
	`ST_OrderingEquals <http://postgis.net/docs/ST_OrderingEquals.html>`_	`ST_OrderingEquals <http://postgis.net/docs/ST_OrderingEquals.html>`_
	`ST_Equals <http://postgis.net/docs/ST_Equals.html>`_	`ST_Equals <http://postgis.net/docs/ST_Equals.html>`_