Documentation Source Text

<h1>Special Functions</h1>

<p>
The geopoly module defines several new SQL functions that are useful for
dealing with polygons.  All polygon arguments to these functions can be
either the GeoJSON format or the internal binary format.


<h2>The geopoly_overlap(P1,P2) Function</h2>

<p>
If P1 and P2 are both polygons, then the geopoly_overlap(P1,P2) function returns
true if there is any overlap between P1 and P2, or it returns false if P1 and P2
completely disjoint.
If either P1 or P2 is not a polygon, this routine returns NULL.
................................................................................
<p>
The geopoly_overlap(P1,p2) function is special in that the geopoly virtual
table knows how to use R*Tree indexes to optimize queries in which the 
WHERE clause uses geopoly_overlap() as a boolean function.  Only the
geopoly_overlap(P1,P2) and geopoly_within(P1,P2) functions have this
capability.


<h2>The geopoly_within(P1,P2) Function</h2>

<p>
If P1 and P2 are both polygons, then the geopoly_within(P1,P2) function returns
true if P2 is completely contained within P1, or it returns false if any part of
P2 is outside of P1.  If P1 and P2 are the same polygon, this routine returns true.
If either P1 or P2 is not a polygon, this routine returns NULL.
................................................................................
<p>
The geopoly_within(P1,p2) function is special in that the geopoly virtual
table knows how to use R*Tree indexes to optimize queries in which the 
WHERE clause uses geopoly_within() as a boolean function.  Only the
geopoly_within(P1,P2) and geopoly_overlap(P1,P2) functions have this
capability.


<h2>The geopoly_area(P) Function</h2>

<p>
If P is a polygon, then geopoly_area(P) returns the area enclosed by
that polygon.  If P is not a polygon, geopoly_area(P) returns NULL.


<h2>The geopoly_blob(P) Function</h2>

<p>
If P is a polygon, then geopoly_blob(P) returns the binary encoding
of that polygon as a BLOB.
If P is not a polygon, geopoly_blob(P) returns NULL.


<h2>The geopoly_json(P) Function</h2>

<p>
If P is a polygon, then geopoly_json(P) returns the GeoJSON representation
of that polygon as a TEPT string.
If P is not a polygon, geopoly_json(P) returns NULL.


<h2>The geopoly_svg(P,...) Function</h2>

<p>
If P is a polygon, then geopoly_svg(P,...) returns a text string which is a
[https://en.wikipedia.org/wiki/Scalable_Vector_Graphics|Scalable Vector Graphics (SVG)]
representation of that polygon.  If there is more one argument, then second
and subsequent arguments are added as attributes to each SVG glyph.  For example:
................................................................................
with the origin at the lower left, whereas SVG uses a left-handed coordinate
system with the origin at the upper left.  The geopoly_svg() routine makes no
attempt to transform the coordinate system, so the displayed images are shown
in mirror image and rotated.  If that is undesirable, the geopoly_xform() routine
can be used to transform the output from cartesian to SVG coordinates prior to
passing the polygons into geopoly_svg().


<h2>The geopoly_bbox(P) Function</h2>

<p>
If P is a polygon, then geopoly_bbox(P) returns the a new polygon that is
the smallest rectangle completely enclosing P.
If P is not a polygon, geopoly_bbox(P) returns NULL.


<h2>The geopoly_contains_point(P,X,Y) Function</h2>

<p>
If P is a polygon, then geopoly_contains_point(P,X,Y) returns true if and only
if the coordinate X,Y is inside or on the boundary of the polygon P.
If P is not a polygon, geopoly_contains_point(P,X,Y) returns NULL.


<h2>The geopoly_xform(P,A,B,C,D,E,F) Function</h2>

<p>
The geopoly_xform(P,A,B,C,D,E,F) returns a new polygon that is a
linear transformation of the polygon P and where the transformation
is defined by values A,B,C,D,E,F. If P is not a valid polygon, this
routine returns NULL.

<h1>Special Functions</h1>

<p>
The geopoly module defines several new SQL functions that are useful for
dealing with polygons.  All polygon arguments to these functions can be
either the GeoJSON format or the internal binary format.

<tcl>hd_fragment goverlap geopoly_overlap</tcl>
<h2>The geopoly_overlap(P1,P2) Function</h2>

<p>
If P1 and P2 are both polygons, then the geopoly_overlap(P1,P2) function returns
true if there is any overlap between P1 and P2, or it returns false if P1 and P2
completely disjoint.
If either P1 or P2 is not a polygon, this routine returns NULL.
................................................................................
<p>
The geopoly_overlap(P1,p2) function is special in that the geopoly virtual
table knows how to use R*Tree indexes to optimize queries in which the 
WHERE clause uses geopoly_overlap() as a boolean function.  Only the
geopoly_overlap(P1,P2) and geopoly_within(P1,P2) functions have this
capability.

<tcl>hd_fragment gwithin geopoly_within</tcl>
<h2>The geopoly_within(P1,P2) Function</h2>

<p>
If P1 and P2 are both polygons, then the geopoly_within(P1,P2) function returns
true if P2 is completely contained within P1, or it returns false if any part of
P2 is outside of P1.  If P1 and P2 are the same polygon, this routine returns true.
If either P1 or P2 is not a polygon, this routine returns NULL.
................................................................................
<p>
The geopoly_within(P1,p2) function is special in that the geopoly virtual
table knows how to use R*Tree indexes to optimize queries in which the 
WHERE clause uses geopoly_within() as a boolean function.  Only the
geopoly_within(P1,P2) and geopoly_overlap(P1,P2) functions have this
capability.

<tcl>hd_fragment garea geopoly_area</tcl>
<h2>The geopoly_area(P) Function</h2>

<p>
If P is a polygon, then geopoly_area(P) returns the area enclosed by
that polygon.  If P is not a polygon, geopoly_area(P) returns NULL.

<tcl>hd_fragment gblob geopoly_blob</tcl>
<h2>The geopoly_blob(P) Function</h2>

<p>
If P is a polygon, then geopoly_blob(P) returns the binary encoding
of that polygon as a BLOB.
If P is not a polygon, geopoly_blob(P) returns NULL.

<tcl>hd_fragment gjson geopoly_json</tcl>
<h2>The geopoly_json(P) Function</h2>

<p>
If P is a polygon, then geopoly_json(P) returns the GeoJSON representation
of that polygon as a TEPT string.
If P is not a polygon, geopoly_json(P) returns NULL.

<tcl>hd_fragment gsvg geopoly_svg</tcl>
<h2>The geopoly_svg(P,...) Function</h2>

<p>
If P is a polygon, then geopoly_svg(P,...) returns a text string which is a
[https://en.wikipedia.org/wiki/Scalable_Vector_Graphics|Scalable Vector Graphics (SVG)]
representation of that polygon.  If there is more one argument, then second
and subsequent arguments are added as attributes to each SVG glyph.  For example:
................................................................................
with the origin at the lower left, whereas SVG uses a left-handed coordinate
system with the origin at the upper left.  The geopoly_svg() routine makes no
attempt to transform the coordinate system, so the displayed images are shown
in mirror image and rotated.  If that is undesirable, the geopoly_xform() routine
can be used to transform the output from cartesian to SVG coordinates prior to
passing the polygons into geopoly_svg().

<tcl>hd_fragment gbbox geopoly_bbox</tcl>
<h2>The geopoly_bbox(P) Function</h2>

<p>
If P is a polygon, then geopoly_bbox(P) returns the a new polygon that is
the smallest rectangle completely enclosing P.
If P is not a polygon, geopoly_bbox(P) returns NULL.

<tcl>hd_fragment gpoint geopoly_constains_point</tcl>
<h2>The geopoly_contains_point(P,X,Y) Function</h2>

<p>
If P is a polygon, then geopoly_contains_point(P,X,Y) returns true if and only
if the coordinate X,Y is inside or on the boundary of the polygon P.
If P is not a polygon, geopoly_contains_point(P,X,Y) returns NULL.

<tcl>hd_fragment xform geopoly_xform</tcl>
<h2>The geopoly_xform(P,A,B,C,D,E,F) Function</h2>

<p>
The geopoly_xform(P,A,B,C,D,E,F) returns a new polygon that is a
linear transformation of the polygon P and where the transformation
is defined by values A,B,C,D,E,F. If P is not a valid polygon, this
routine returns NULL.

  #define SQLITE_INDEX_CONSTRAINT_GLOB      66  /* 3.10.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_REGEXP    67  /* 3.10.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_NE        68  /* 3.21.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_ISNOT     69  /* 3.21.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_ISNOTNULL 70  /* 3.21.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_ISNULL    71  /* 3.21.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_IS        72  /* 3.21.0 and later */

  #define SQLITE_INDEX_SCAN_UNIQUE           1  /* Scan visits at most 1 row */
</codeblock>

<p>The SQLite core calls the xBestIndex method when it is compiling a query
that involves a virtual table. In other words, SQLite calls this method 
when it is running [sqlite3_prepare()] or the equivalent. 
By calling this method, the 
................................................................................

<p>The main thing that the SQLite core is trying to communicate to 
the virtual table is the constraints that are available to limit 
the number of rows that need to be searched. The aConstraint[] array 
contains one entry for each constraint. There will be exactly 
nConstraint entries in that array.

<p>Each constraint will correspond to a term in the WHERE clause
or in a USING or ON clause that is of the form

<blockquote>
     column  OP  EXPR
</blockquote>

<p>Where "column" is a column in the virtual table, OP is an operator 
................................................................................

<codeblock>
     x &gt;= 10
     x &lt;= 100
     y &lt; 999
</codeblock>

<p>For each constraint, the aConstraint[].iColumn field indicates which 
column appears on the left-hand side of the constraint.
The first column of the virtual table is column 0. 
The rowid of the virtual table is column -1. 
The aConstraint[].op field indicates which operator is used. 
The SQLITE_INDEX_CONSTRAINT_* constants map integer constants 
into operator values.
Columns occur in the order they were defined by the call to
[sqlite3_declare_vtab()] in the [xCreate] or [xConnect] method.
Hidden columns are counted when determining the column index.












<p>The aConstraint[] array contains information about all constraints 
that apply to the virtual table. But some of the constraints might
not be usable because of the way tables are ordered in a join. 
The xBestIndex method must therefore only consider constraints 
that have an aConstraint[].usable flag which is true.

................................................................................
<p>When a function uses a column from a virtual table as its first 
argument, this method is called to see if the virtual table would 
like to overload the function. The first three parameters are inputs: 
the virtual table, the number of arguments to the function, and the 
name of the function. If no overloading is desired, this method
returns 0. To overload the function, this method writes the new 
function implementation into *pxFunc and writes user data into *ppArg 
























and returns 1.







<p>Note that infix functions ([LIKE], [GLOB], [REGEXP], and [MATCH]) reverse 
the order of their arguments. So "like(A,B)" is equivalent to "B like A". 
For the form "B like A" the B term is considered the first argument 
to the function. But for "like(A,B)" the A term is considered the 
first argument.

  #define SQLITE_INDEX_CONSTRAINT_GLOB      66  /* 3.10.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_REGEXP    67  /* 3.10.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_NE        68  /* 3.21.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_ISNOT     69  /* 3.21.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_ISNOTNULL 70  /* 3.21.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_ISNULL    71  /* 3.21.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_IS        72  /* 3.21.0 and later */
  #define SQLITE_INDEX_CONSTRAINT_FUNCTION 150  /* 3.25.0 and later */
  #define SQLITE_INDEX_SCAN_UNIQUE           1  /* Scan visits at most 1 row */
</codeblock>

<p>The SQLite core calls the xBestIndex method when it is compiling a query
that involves a virtual table. In other words, SQLite calls this method 
when it is running [sqlite3_prepare()] or the equivalent. 
By calling this method, the 
................................................................................

<p>The main thing that the SQLite core is trying to communicate to 
the virtual table is the constraints that are available to limit 
the number of rows that need to be searched. The aConstraint[] array 
contains one entry for each constraint. There will be exactly 
nConstraint entries in that array.

<p>Each constraint will usually correspond to a term in the WHERE clause
or in a USING or ON clause that is of the form

<blockquote>
     column  OP  EXPR
</blockquote>

<p>Where "column" is a column in the virtual table, OP is an operator 
................................................................................

<codeblock>
     x &gt;= 10
     x &lt;= 100
     y &lt; 999
</codeblock>

<p>For such each constraint, the aConstraint[].iColumn field indicates which 
column appears on the left-hand side of the constraint.
The first column of the virtual table is column 0. 
The rowid of the virtual table is column -1. 
The aConstraint[].op field indicates which operator is used. 
The SQLITE_INDEX_CONSTRAINT_* constants map integer constants 
into operator values.
Columns occur in the order they were defined by the call to
[sqlite3_declare_vtab()] in the [xCreate] or [xConnect] method.
Hidden columns are counted when determining the column index.

<p>If the [xFindFunction()] method for the virtual table is defined, and 
if xFindFunction() sometimes returns [SQLITE_INDEX_CONSTRAINT_FUNCTION] or
larger, then the constraints might also be of the form:

<blockquote>
     FUNCTION( column, EXPR)
</blockquote>

<p>In this case the aConstraint[].op value is the same as the value
returned by [xFindFunction()] for FUNCTION.

<p>The aConstraint[] array contains information about all constraints 
that apply to the virtual table. But some of the constraints might
not be usable because of the way tables are ordered in a join. 
The xBestIndex method must therefore only consider constraints 
that have an aConstraint[].usable flag which is true.

................................................................................
<p>When a function uses a column from a virtual table as its first 
argument, this method is called to see if the virtual table would 
like to overload the function. The first three parameters are inputs: 
the virtual table, the number of arguments to the function, and the 
name of the function. If no overloading is desired, this method
returns 0. To overload the function, this method writes the new 
function implementation into *pxFunc and writes user data into *ppArg 
and returns either 1 or a number between
[SQLITE_INDEX_CONSTRAINT_FUNCTION] and 255.

<p>Historically, the return value from xFindFunction() was either zero
or one.  Zero means that the function is not overloaded and one means that
it is overload.  The ability to return values of 
[SQLITE_INDEX_CONSTRAINT_FUNCTION] or greater was added in
version 3.25.0 ([dateof:3.25.0]).  If xFindFunction returns
[SQLITE_INDEX_CONSTRAINT_FUNCTION] or greater, than means that the function
takes two arguments and the function
can be used as a boolean in the WHERE clause of a query and that
the virtual table is able to exploit that function to speed up the query
result.  When xFindFunction returns [SQLITE_INDEX_CONSTRAINT_FUNCTION] or 
larger, the value returned becomes the sqlite3_index_info.aConstraint.op
value for one of the constraints passed into [xBestIndex()] and the second
argument becomes the value corresponding to that constraint that is passed
to [xFilter()].  This enables the
xBestIndex()/xFilter implementations to use the function to speed
its search.

<p>The technique of having xFindFunction() return values of
[SQLITE_INDEX_CONSTRAINT_FUNCTION] was initially used in the implementation
of the [Geopoly module].  The xFindFunction() method of that module returns
SQLITE_INDEX_CONSTRAINT_FUNCTION for the [geopoly_overlap()] SQL function
and it returns
SQLITE_INDEX_CONSTRAINT_FUNCTION+1 for the [geopoly_within()] SQL function.
This permits search optimizations for queries such as:

<codeblock>
SELECT * FROM geopolytab WHERE geopoly_overlap(_shape, $query_polygon);
</codeblock>

<p>Note that infix functions ([LIKE], [GLOB], [REGEXP], and [MATCH]) reverse 
the order of their arguments. So "like(A,B)" is equivalent to "B like A". 
For the form "B like A" the B term is considered the first argument 
to the function. But for "like(A,B)" the A term is considered the 
first argument.