[postgis-tickets] [SCM] PostGIS branch master updated. 3.1.0rc1-1-g5a3d39b
git at osgeo.org
git at osgeo.org
Mon Dec 14 13:29:17 PST 2020
This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "PostGIS".
The branch, master has been updated
via 5a3d39b13622fc6186609bf67f7bb4b886f6cc9e (commit)
from 2b98577932cc6cb3f0445e3977e07fd7c0f75fce (commit)
Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.
- Log -----------------------------------------------------------------
commit 5a3d39b13622fc6186609bf67f7bb4b886f6cc9e
Author: Martin Davis <mtnclimb at gmail.com>
Date: Mon Dec 14 13:29:12 2020 -0800
Improve doc Using Indexes section
diff --git a/doc/reference_operator.xml b/doc/reference_operator.xml
index 0ae2539..26c7166 100644
--- a/doc/reference_operator.xml
+++ b/doc/reference_operator.xml
@@ -2,7 +2,7 @@
<sect1 id="Operators">
<title>Operators</title>
- <sect2>
+ <sect2 id="operators-bbox">
<title>Bounding Box Operators</title>
<refentry id="geometry_overlaps">
@@ -1855,7 +1855,7 @@ select 'LINESTRING(0 0, 1 1)'::geometry ~= 'LINESTRING(0 1, 1 0)'::geometry as e
</sect2>
<!-- ============================================================================== -->
- <sect2>
+ <sect2 id="operators-distance">
<title>Distance Operators</title>
@@ -2290,4 +2290,3 @@ Index only kicks in if one of the geometries is a constant e.g. ORDER BY
</sect2>
</sect1>
-
diff --git a/doc/using_postgis_query.xml b/doc/using_postgis_query.xml
index ae04158..5150688 100644
--- a/doc/using_postgis_query.xml
+++ b/doc/using_postgis_query.xml
@@ -2,8 +2,8 @@
<sect1 id="using_postgis_query">
<title>Spatial Queries</title>
- <para>The <emphasis>raison d'etre</emphasis> of spatial database
- functionality is performing queries inside the database which would
+ <para>The <emphasis>raison d'etre</emphasis> of spatial databases
+ is to perform queries inside the database which would
ordinarily require desktop GIS functionality. Using PostGIS effectively
requires knowing what spatial functions are available,
how to use them in queries, and ensuring that
@@ -329,7 +329,7 @@ FROM city JOIN state ON ST_Intersects(city.geom, state.geom);
<tgroup cols="1">
<tbody>
<row>
- <entry><para><informalfigure float="1" floatstyle="left">
+ <entry><para><informalfigure float="1" floatstyle="right">
<graphic align="left" fileref="images/de9im01.png" />
</informalfigure></para><para>For example, consider a linear
dataset representing a road network. It may be required
@@ -454,44 +454,58 @@ WHERE a.geom && b.geom
</sect2>
<sect2>
- <title>Taking Advantage of Indexes</title>
-
- <para>When constructing a query it is important to remember that only
- the bounding-box-based operators such as && can take advantage
- of the GiST spatial index. Functions such as
- <varname>ST_Distance()</varname> cannot use the index to optimize their
- operation. For example, the following query would be quite slow on a
- large table:</para>
+ <title>Taking Advantage of Indexes</title>
+
+ <para>When constructing queries using spatial conditions it is important to
+ ensure that a spatial index is used, if one exists (see <xref linkend="build-indexes" />).
+ To do this, an index-aware spatial operator or function must be used
+ in the <code>WHERE</code> or <code>ON</code> clause.
+ Spatial operators include
+ the bounding box-based operators
+ (of which the most commonly used is <xref linkend="geometry_overlaps" />)
+ and the distance operators used in nearest-neighbour queries
+ (the most common being <xref linkend="geometry_distance_knn" />.)
+ Index-aware functions include most of the named spatial predicates
+ (such as <xref linkend="ST_Intersects" />), and most of the distance predicates such as
+ <xref linkend="ST_DWithin" />.)
+ </para>
+ <para>
+ Functions such as
+ <xref linkend="ST_Distance" /> do not use indexes to optimize their
+ operation. For example, the following query would be quite slow on a
+ large table:</para>
<programlisting>SELECT the_geom
FROM geom_table
WHERE ST_Distance(the_geom, 'SRID=312;POINT(100000 200000)') < 100</programlisting>
- <para>This query is selecting all the geometries in geom_table which are
- within 100 units of the point (100000, 200000). It will be slow because
- it is calculating the distance between each point in the table and our
- specified point, ie. one <varname>ST_Distance()</varname> calculation
- for each row in the table. We can avoid this by using the single step
- index accelerated function ST_DWithin to reduce the number of distance
- calculations required:</para>
+ <para>This query selects all the geometries in <code>geom_table</code> which are
+ within 100 units of the point (100000, 200000). It will be slow because
+ it is calculating the distance between each point in the table and the
+ specified point, ie. one <varname>ST_Distance()</varname> calculation
+ is computed for <emphasis role="bold">every</emphasis> row in the table.
+ </para>
+ <para>
+ We can reduce the number of distance calculations required by using the
+ index-aware function <xref linkend="ST_DWithin" />:</para>
<programlisting>SELECT the_geom
FROM geom_table
WHERE ST_DWithin(the_geom, 'SRID=312;POINT(100000 200000)', 100)
</programlisting>
- <para>This query selects the same geometries, but it does it in a more
- efficient way. Assuming there is a GiST index on the_geom, the query
- planner will recognize that it can use the index to reduce the number of
- rows before calculating the result of the <varname>ST_Distance()</varname>
- function. Notice that the <varname>ST_MakeEnvelope</varname> geometry which is
- used in the && operation is a 200 unit square box centered on
- the original point - this is our "query box". The && operator
- uses the index to quickly reduce the result set down to only those
- geometries which have bounding boxes that overlap the "query box".
- Assuming that our query box is much smaller than the extents of the
- entire geometry table, this will drastically reduce the number of
- distance calculations that need to be done.</para>
+ <para>This query selects the same geometries, but it does it in a more
+ efficient way. If there is a GiST index on the_geom, the query
+ planner will recognize that it can use the index to reduce the number of
+ rows before calculating the distance.
+ This is enabled by <varname>ST_DWithin()</varname> using the
+ <varname>&&</varname> operator internally on an expanded bounding box
+ of the query geometry.
+ That uses the spatial index to find only records with geometries
+ whose bounding boxes overlap the expanded extent
+ (or in other words, skips records with geometries which are guaranteed to be too far away.)
+ </para>
+
</sect2>
<sect2 id="examples_spatial_sql">
-----------------------------------------------------------------------
Summary of changes:
doc/reference_operator.xml | 5 ++-
doc/using_postgis_query.xml | 74 +++++++++++++++++++++++++++------------------
2 files changed, 46 insertions(+), 33 deletions(-)
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list