/ Check-in [07506694]
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

 ```459 460 461 462 463 464 465 466 467 468 469 470 471 472 ... 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 .... 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 ``` ``` p->a[ii*2+1] = y1; } sqlite3_result_blob(context, p->hdr, 4+8*p->nVertex, SQLITE_TRANSIENT); sqlite3_free(p); } } /* ** Implementation of the geopoly_area(X) function. ** ** If the input is a well-formed Geopoly BLOB then return the area ** enclosed by the polygon. If the polygon circulates clockwise instead ** of counterclockwise (as it should) then return the negative of the ................................................................................ static void geopolyAreaFunc( sqlite3_context *context, int argc, sqlite3_value **argv ){ GeoPoly *p = geopolyFuncParam(context, argv[0], 0); if( p ){ double rArea = 0.0; int ii; for(ii=0; iinVertex-1; ii++){ rArea += (p->a[ii*2] - p->a[ii*2+2]) /* (x0 - x1) */ * (p->a[ii*2+1] + p->a[ii*2+3]) /* (y0 + y1) */ * 0.5; } rArea += (p->a[ii*2] - p->a[0]) /* (xN - x0) */ * (p->a[ii*2+1] + p->a[1]) /* (yN + y0) */ * 0.5; sqlite3_result_double(context, rArea); sqlite3_free(p); } } /* ** Implementation of the geopoly_reverse(X) function. ** ** Reverse the order of the vertexes in polygon X. This can be used ** to convert an historical polygon that uses a clockwise rotation into ** a well-formed GeoJSON polygon that uses counter-clockwise rotation. */ static void geopolyReverseFunc( sqlite3_context *context, int argc, sqlite3_value **argv ){ GeoPoly *p = geopolyFuncParam(context, argv[0], 0); if( p ){ int ii, jj; for(ii=2, jj=p->nVertex*2 - 4; iia[ii]; p->a[ii] = p->a[jj]; p->a[jj] = t; t = p->a[ii+1]; p->a[ii+1] = p->a[jj+1]; p->a[jj+1] = t; } sqlite3_result_blob(context, p->hdr, 4+8*p->nVertex, SQLITE_TRANSIENT); sqlite3_free(p); } } ................................................................................ { geopolyWithinFunc, 2, 1, "geopoly_within" }, { geopolyContainsPointFunc, 3, 1, "geopoly_contains_point" }, { geopolyOverlapFunc, 2, 1, "geopoly_overlap" }, { geopolyDebugFunc, 1, 0, "geopoly_debug" }, { geopolyBBoxFunc, 1, 1, "geopoly_bbox" }, { geopolyXformFunc, 7, 1, "geopoly_xform" }, { geopolyRegularFunc, 4, 1, "geopoly_regular" }, { geopolyReverseFunc, 1, 1, "geopoly_reverse" }, }; static const struct { void (*xStep)(sqlite3_context*,int,sqlite3_value**); void (*xFinal)(sqlite3_context*); const char *zName; } aAgg[] = { { geopolyBBoxStep, geopolyBBoxFinal, "geopoly_group_bbox" }, ``` ``` > > > > > > > > > > > > > > > > > > > > > < < < < < < < < < < | | > > | < < > > > > > > | > | | | | | | | | | | ``` ```459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 ... 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 .... 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 ``` ``` p->a[ii*2+1] = y1; } sqlite3_result_blob(context, p->hdr, 4+8*p->nVertex, SQLITE_TRANSIENT); sqlite3_free(p); } } /* ** Compute the area enclosed by the polygon. ** ** This routine can also be used to detect polygons that rotate in ** the wrong direction. Polygons are suppose to be counter-clockwise (CCW). ** This routine returns a negative value for clockwise (CW) polygons. */ static double geopolyArea(GeoPoly *p){ double rArea = 0.0; int ii; for(ii=0; iinVertex-1; ii++){ rArea += (p->a[ii*2] - p->a[ii*2+2]) /* (x0 - x1) */ * (p->a[ii*2+1] + p->a[ii*2+3]) /* (y0 + y1) */ * 0.5; } rArea += (p->a[ii*2] - p->a[0]) /* (xN - x0) */ * (p->a[ii*2+1] + p->a[1]) /* (yN + y0) */ * 0.5; return rArea; } /* ** Implementation of the geopoly_area(X) function. ** ** If the input is a well-formed Geopoly BLOB then return the area ** enclosed by the polygon. If the polygon circulates clockwise instead ** of counterclockwise (as it should) then return the negative of the ................................................................................ static void geopolyAreaFunc( sqlite3_context *context, int argc, sqlite3_value **argv ){ GeoPoly *p = geopolyFuncParam(context, argv[0], 0); if( p ){ sqlite3_result_double(context, geopolyArea(p)); sqlite3_free(p); } } /* ** Implementation of the geopoly_ccw(X) function. ** ** If the rotation of polygon X is clockwise (incorrect) instead of ** counter-clockwise (the correct winding order according to RFC7946) ** then reverse the order of the vertexes in polygon X. ** ** In other words, this routine returns a CCW polygon regardless of the ** winding order of its input. ** ** Use this routine to sanitize historical inputs that that sometimes ** contain polygons that wind in the wrong direction. */ static void geopolyCcwFunc( sqlite3_context *context, int argc, sqlite3_value **argv ){ GeoPoly *p = geopolyFuncParam(context, argv[0], 0); if( p ){ if( geopolyArea(p)<0.0 ){ int ii, jj; for(ii=2, jj=p->nVertex*2 - 4; iia[ii]; p->a[ii] = p->a[jj]; p->a[jj] = t; t = p->a[ii+1]; p->a[ii+1] = p->a[jj+1]; p->a[jj+1] = t; } } sqlite3_result_blob(context, p->hdr, 4+8*p->nVertex, SQLITE_TRANSIENT); sqlite3_free(p); } } ................................................................................ { geopolyWithinFunc, 2, 1, "geopoly_within" }, { geopolyContainsPointFunc, 3, 1, "geopoly_contains_point" }, { geopolyOverlapFunc, 2, 1, "geopoly_overlap" }, { geopolyDebugFunc, 1, 0, "geopoly_debug" }, { geopolyBBoxFunc, 1, 1, "geopoly_bbox" }, { geopolyXformFunc, 7, 1, "geopoly_xform" }, { geopolyRegularFunc, 4, 1, "geopoly_regular" }, { geopolyCcwFunc, 1, 1, "geopoly_ccw" }, }; static const struct { void (*xStep)(sqlite3_context*,int,sqlite3_value**); void (*xFinal)(sqlite3_context*); const char *zName; } aAgg[] = { { geopolyBBoxStep, geopolyBBoxFinal, "geopoly_group_bbox" }, ```