[postgis-tickets] r16707 - Clarify ST_DWithin usage when table stores a point and a radius.

[Darafei]

Author: komzpa
Date: 2018-08-28 00:51:44 -0700 (Tue, 28 Aug 2018)
New Revision: 16707

Modified:
   branches/2.5/NEWS
   branches/2.5/doc/reference_measure.xml
Log:
Clarify ST_DWithin usage when table stores a point and a radius.

Added after IRC discussion.
Thanks github user Boscop for questions and review.

Closes #4162
Closes https://github.com/postgis/postgis/pull/289


Modified: branches/2.5/NEWS
===================================================================
--- branches/2.5/NEWS	2018-08-28 07:45:03 UTC (rev 16706)
+++ branches/2.5/NEWS	2018-08-28 07:51:44 UTC (rev 16707)
@@ -1,3 +1,9 @@
+PostGIS 2.5.0rc2
+2018/08/xx
+New since PostGIS 2.5.0rc1
+  - #4162, ST_DWithin documentation examples for storing geometry and
+    radius in table (Darafei Praliaskouski, github user Boscop).
+
 PostGIS 2.5.0rc1
 2018/08/19
 New since PostGIS 2.5.0beta2

Modified: branches/2.5/doc/reference_measure.xml
===================================================================
--- branches/2.5/doc/reference_measure.xml	2018-08-28 07:45:03 UTC (rev 16706)
+++ branches/2.5/doc/reference_measure.xml	2018-08-28 07:51:44 UTC (rev 16707)
@@ -3059,7 +3059,7 @@
 		<refname>ST_DWithin</refname>
 
 		<refpurpose>Returns true if the geometries are within the specified
-		distance of one another. For geometry units are in those of spatial reference and For geography units are in meters and measurement is
+		distance of one another. For geometry units are in those of spatial reference and for geography units are in meters and measurement is
 		defaulted to use_spheroid=true (measure around spheroid), for faster check, use_spheroid=false to measure along sphere.</refpurpose>
 	  </refnamediv>
 
@@ -3111,14 +3111,16 @@
 
 		<para>Returns true if the geometries are within the specified distance
 		of one another.</para>
-		<para>For Geometries: The distance is specified in units defined by the
+
+		<para>For <type>geometry</type>: The distance is specified in units defined by the
 		spatial reference system of the geometries.  For this function to make
 		sense, the source geometries must both be of the same coordinate projection,
 		having the same SRID.</para>
 
-		<para>For geography units are in meters and measurement is
-		defaulted to use_spheroid=true, for faster check, use_spheroid=false to measure along sphere.
+		<para>For <type>geography</type> units are in meters and measurement is
+		defaulted to <varname>use_spheroid</varname>=true, for faster check, <varname>use_spheroid</varname>=false to measure along sphere.
 		</para>
+
 		<note>
 		  <para>This function call will automatically include a bounding box
 		  comparison that will make use of any indexes that are available on
@@ -3143,23 +3145,40 @@
 	  <refsection>
 		<title>Examples</title>
 		  <programlisting>
---Find the nearest hospital to each school
---that is within 3000 units of the school.
--- We do an ST_DWithin search to utilize indexes to limit our search list
--- that the non-indexable ST_Distance needs to process
---If the units of the spatial reference is meters then units would be meters
-SELECT DISTINCT ON (s.gid) s.gid, s.school_name, s.the_geom, h.hospital_name
+-- Find the nearest hospital to each school
+-- that is within 3000 units of the school.
+--  We do an ST_DWithin search to utilize indexes to limit our search list
+--  that the non-indexable ST_Distance needs to process
+-- If the units of the spatial reference is meters then units would be meters
+SELECT DISTINCT ON (s.gid) s.gid, s.school_name, s.geom, h.hospital_name
 	FROM schools s
-		LEFT JOIN hospitals h ON ST_DWithin(s.the_geom, h.the_geom, 3000)
-	ORDER BY s.gid, ST_Distance(s.the_geom, h.the_geom);
+		LEFT JOIN hospitals h ON ST_DWithin(s.the_geom, h.geom, 3000)
+	ORDER BY s.gid, ST_Distance(s.geom, h.geom);
 
---The schools with no close hospitals
---Find all schools with no hospital within 3000 units
---away from the school.  Units is in units of spatial ref (e.g. meters, feet, degrees)
+-- The schools with no close hospitals
+-- Find all schools with no hospital within 3000 units
+-- away from the school.  Units is in units of spatial ref (e.g. meters, feet, degrees)
 SELECT s.gid, s.school_name
 	FROM schools s
-		LEFT JOIN hospitals h ON ST_DWithin(s.the_geom, h.the_geom, 3000)
+		LEFT JOIN hospitals h ON ST_DWithin(s.geom, h.geom, 3000)
 	WHERE h.gid IS NULL;
+
+-- Find broadcasting towers that receiver with limited range can receive.
+-- Data is geometry in Spherical Mercator (SRID=3857), ranges are approximate.
+
+-- Create geometry index that will check proximity limit of user to tower
+CREATE INDEX ON broadcasting_towers using gist (geom);
+
+-- Create geometry index that will check proximity limit of tower to user
+CREATE INDEX ON broadcasting_towers using gist (ST_Expand(geom, sending_range));
+
+-- Query towers that 4-kilometer receiver in Minsk Hackerspace can get
+-- Note: two conditions, because shorter LEAST(b.sending_range, 4000) will not use index.
+SELECT b.tower_id, b.geom
+  FROM broadcasting_towers b
+  WHERE ST_DWithin(b.geom, 'SRID=3857;POINT(3072163.4 7159374.1)', 4000)
+	  AND ST_DWithin(b.geom, 'SRID=3857;POINT(3072163.4 7159374.1)', b.sending_range);
+
 			  </programlisting>
 	  </refsection>
 
@@ -3166,7 +3185,7 @@
 	  <refsection>
 		<title>See Also</title>
 
-		<para><xref linkend="ST_Distance"/>, <xref linkend="ST_Expand"/></para>
+		<para><xref linkend="ST_Distance"/>, <xref linkend="ST_Expand"/>, <xref linkend="ST_3DDWithin"/></para>
 	  </refsection>
 	</refentry>