notebooks/examples/sql/MosaicAndSedona.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "bf98136c-9276-4388-8eef-b567621fe1a4",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "# Mosaic & Sedona\n",
    "\n",
    "> You can combine the usage of [Mosaic](https://databrickslabs.github.io/mosaic/index.html) with other geospatial libraries. In this example we combine it with [Sedona](https://sedona.apache.org).\n",
    "\n",
    "## Setup\n",
    "\n",
    "This notebook will run if you have both Mosaic and Sedona installed on your cluster as described below.\n",
    "\n",
    "### Install Sedona\n",
    "\n",
    "To install Sedona, follow the [official Sedona instructions](https://sedona.apache.org/1.5.0/setup/databricks/).\n",
    "\n",
    "E.g. Add the following maven coordinates to a non-photon cluster [[1](https://docs.databricks.com/en/libraries/package-repositories.html)]. This is showing DBR 12.2 LTS.  \n",
    "\n",
    "```\n",
    "org.apache.sedona:sedona-spark-shaded-3.0_2.12:1.5.0\n",
    "org.datasyslab:geotools-wrapper:1.5.0-28.2\n",
    "```\n",
    "\n",
    "### Install Mosaic\n",
    "\n",
    "Download Mosaic JAR to your local machine (e.g. from [here](https://github.com/databrickslabs/mosaic/releases/download/v_0.3.12/mosaic-0.3.12-jar-with-dependencies.jar) for 0.3.12) and then UPLOAD to your cluster [[1](https://docs.databricks.com/en/libraries/cluster-libraries.html#install-a-library-on-a-cluster)]. \n",
    "\n",
    "### Notes\n",
    "\n",
    "* This is for [SPARK SQL](https://www.databricks.com/glossary/what-is-spark-sql#:~:text=Spark%20SQL%20is%20a%20Spark,on%20existing%20deployments%20and%20data.) which is different from [DBSQL](https://www.databricks.com/product/databricks-sql); __The best way to combine is to not register mosaic SQL functions since Sedona is primarily SQL.__\n",
    "* See instructions for `SedonaContext.create(spark)` [[1](https://sedona.apache.org/1.5.0/tutorial/sql/?h=sedonacontext#initiate-sedonacontext)]. \n",
    "* And, Sedona identifies that it might have issues if executed on a [Photon](https://www.databricks.com/product/photon) cluster; again this example is showing DBR 12.2 LTS on the Mosaic 0.3 series.\n",
    "\n",
    "--- \n",
    " __Last Update__ 01 DEC 2023 [Mosaic 0.3.12]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "27dd2429-1135-457b-912f-931e7aaa447e",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Prior to Setup\n",
    "\n",
    "> Notice that even in DBR 12.2 LTS, Databricks initially has gated functions, meaning they will not execute on the runtime but are there. However, we will see that after registering functions, e.g. from Sedona, those then become available (in DBR)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "80dcd1b7-5f05-47a9-a5e5-f8361811cec4",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>function</th></tr></thead><tbody><tr><td>st_area</td></tr><tr><td>st_astext</td></tr><tr><td>st_geogfromtext</td></tr><tr><td>st_geogfromwkt</td></tr><tr><td>st_geometrytype</td></tr><tr><td>st_geomfromtext</td></tr><tr><td>st_geomfromwkt</td></tr><tr><td>st_isempty</td></tr><tr><td>st_length</td></tr><tr><td>st_ndims</td></tr><tr><td>st_npoints</td></tr><tr><td>st_point</td></tr><tr><td>st_setsrid</td></tr><tr><td>st_srid</td></tr><tr><td>st_xmax</td></tr><tr><td>st_xmin</td></tr><tr><td>st_ymax</td></tr><tr><td>st_ymin</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         "st_area"
        ],
        [
         "st_astext"
        ],
        [
         "st_geogfromtext"
        ],
        [
         "st_geogfromwkt"
        ],
        [
         "st_geometrytype"
        ],
        [
         "st_geomfromtext"
        ],
        [
         "st_geomfromwkt"
        ],
        [
         "st_isempty"
        ],
        [
         "st_length"
        ],
        [
         "st_ndims"
        ],
        [
         "st_npoints"
        ],
        [
         "st_point"
        ],
        [
         "st_setsrid"
        ],
        [
         "st_srid"
        ],
        [
         "st_xmax"
        ],
        [
         "st_xmin"
        ],
        [
         "st_ymax"
        ],
        [
         "st_ymin"
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "function",
         "type": "\"string\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql \n",
    "-- before we do anything\n",
    "-- have gated product functions\n",
    "show system functions like 'st_*'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "b7cac536-773b-47a4-90ce-5a2c77bdca8e",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "_The following exception will be thrown if you attempt to execute the gated functions:_\n",
    "\n",
    "```\n",
    "AnalysisException: [DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE] Cannot resolve \"st_area(POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10)))\" due to data type mismatch: parameter 1 requires (\"GEOMETRY\" or \"GEOGRAPHY\") type, however, \"POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))\" is of \"STRING\" type.; line 1 pos 7;\n",
    "'Project [unresolvedalias(st_area(POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))), None)]\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "85cd6a7a-dd6d-4cf6-8f65-0ebf640c2ab2",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql \n",
    "-- assumes you are in DBR 12.2 LTS\n",
    "-- so this will not execute\n",
    "-- uncomment to verify\n",
    "-- select st_area('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "a4a24590-8542-4a71-a1c9-03690da5316e",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>function_desc</th></tr></thead><tbody><tr><td>Function: st_area</td></tr><tr><td>Class: com.databricks.sql.catalyst.expressions.st.ST_Area</td></tr><tr><td>Usage: st_area(geo) - Returns the area of the input GEOGRAPHY or GEOMETRY value.</td></tr><tr><td>Extended Usage:\n",
       "    Examples:\n",
       "  \n",
       "    Since: 3.3.0\n",
       "</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         "Function: st_area"
        ],
        [
         "Class: com.databricks.sql.catalyst.expressions.st.ST_Area"
        ],
        [
         "Usage: st_area(geo) - Returns the area of the input GEOGRAPHY or GEOMETRY value."
        ],
        [
         "Extended Usage:\n    Examples:\n  \n    Since: 3.3.0\n"
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "function_desc",
         "type": "\"string\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql \n",
    "-- notice, e.g. these are initially gated product functions\n",
    "describe function extended st_area"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "46dcda8a-cd24-4016-acf9-6ede54978d2f",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Setup\n",
    "\n",
    "> We are installing Mosaic without SQL functions registered (via Scala) and are installing Sedona SQL as normal."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "c91dd7bf-319c-489c-9715-6c512f027d64",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Menlo\", \"Monaco\", \"Consolas\", \"Ubuntu Mono\", \"Source Code Pro\", monospace;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\">import org.apache.spark.sql.functions._\n",
       "import com.databricks.labs.mosaic.functions.MosaicContext\n",
       "import com.databricks.labs.mosaic.H3\n",
       "import com.databricks.labs.mosaic.JTS\n",
       "mosaicContext: com.databricks.labs.mosaic.functions.MosaicContext = com.databricks.labs.mosaic.functions.MosaicContext@70e1f779\n",
       "import mosaicContext.functions._\n",
       "import org.apache.sedona.spark.SedonaContext\n",
       "sedona: org.apache.spark.sql.SparkSession = org.apache.spark.sql.SparkSession@6daae4a9\n",
       "</div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "arguments": {},
       "data": "<div class=\"ansiout\">import org.apache.spark.sql.functions._\nimport com.databricks.labs.mosaic.functions.MosaicContext\nimport com.databricks.labs.mosaic.H3\nimport com.databricks.labs.mosaic.JTS\nmosaicContext: com.databricks.labs.mosaic.functions.MosaicContext = com.databricks.labs.mosaic.functions.MosaicContext@70e1f779\nimport mosaicContext.functions._\nimport org.apache.sedona.spark.SedonaContext\nsedona: org.apache.spark.sql.SparkSession = org.apache.spark.sql.SparkSession@6daae4a9\n</div>",
       "datasetInfos": [],
       "metadata": {},
       "removedWidgets": [],
       "type": "html"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%scala\n",
    "\n",
    "// -- spark functions\n",
    "import org.apache.spark.sql.functions._\n",
    "\n",
    "// -- mosaic functions\n",
    "import com.databricks.labs.mosaic.functions.MosaicContext\n",
    "import com.databricks.labs.mosaic.H3\n",
    "import com.databricks.labs.mosaic.JTS\n",
    "\n",
    "val mosaicContext = MosaicContext.build(H3, JTS)\n",
    "import mosaicContext.functions._\n",
    "\n",
    "// ! don't register SQL functions !\n",
    "// - this allows sedona to be the main spatial SQL provider\n",
    "//mosaicContext.register()\n",
    "\n",
    "// -- sedona functions\n",
    "import org.apache.sedona.spark.SedonaContext\n",
    "val sedona = SedonaContext.create(spark)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "a446841d-9ce1-4b0c-97e8-b705ab06caee",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "_Now when we list user functions, we see all the Sedona provided ones._"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "0394a8a2-dcfd-49c0-a2df-85ecd0272029",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>function</th></tr></thead><tbody><tr><td>hive_metastore.default.st_geohash</td></tr><tr><td>st_3ddistance</td></tr><tr><td>st_addpoint</td></tr><tr><td>st_affine</td></tr><tr><td>st_angle</td></tr><tr><td>st_area</td></tr><tr><td>st_areaspheroid</td></tr><tr><td>st_asbinary</td></tr><tr><td>st_asewkb</td></tr><tr><td>st_asewkt</td></tr><tr><td>st_asgeojson</td></tr><tr><td>st_asgml</td></tr><tr><td>st_askml</td></tr><tr><td>st_astext</td></tr><tr><td>st_azimuth</td></tr><tr><td>st_boundary</td></tr><tr><td>st_boundingdiagonal</td></tr><tr><td>st_buffer</td></tr><tr><td>st_buildarea</td></tr><tr><td>st_centroid</td></tr><tr><td>st_closestpoint</td></tr><tr><td>st_collect</td></tr><tr><td>st_collectionextract</td></tr><tr><td>st_concavehull</td></tr><tr><td>st_contains</td></tr><tr><td>st_convexhull</td></tr><tr><td>st_coorddim</td></tr><tr><td>st_coveredby</td></tr><tr><td>st_covers</td></tr><tr><td>st_crosses</td></tr><tr><td>st_degrees</td></tr><tr><td>st_difference</td></tr><tr><td>st_dimension</td></tr><tr><td>st_disjoint</td></tr><tr><td>st_distance</td></tr><tr><td>st_distancesphere</td></tr><tr><td>st_distancespheroid</td></tr><tr><td>st_dump</td></tr><tr><td>st_dumppoints</td></tr><tr><td>st_endpoint</td></tr><tr><td>st_envelope</td></tr><tr><td>st_envelope_aggr</td></tr><tr><td>st_equals</td></tr><tr><td>st_exteriorring</td></tr><tr><td>st_flipcoordinates</td></tr><tr><td>st_force3d</td></tr><tr><td>st_force_2d</td></tr><tr><td>st_frechetdistance</td></tr><tr><td>st_geogfromtext</td></tr><tr><td>st_geogfromwkt</td></tr><tr><td>st_geohash</td></tr><tr><td>st_geometricmedian</td></tr><tr><td>st_geometryn</td></tr><tr><td>st_geometrytype</td></tr><tr><td>st_geomfromewkt</td></tr><tr><td>st_geomfromgeohash</td></tr><tr><td>st_geomfromgeojson</td></tr><tr><td>st_geomfromgml</td></tr><tr><td>st_geomfromkml</td></tr><tr><td>st_geomfromtext</td></tr><tr><td>st_geomfromwkb</td></tr><tr><td>st_geomfromwkt</td></tr><tr><td>st_h3celldistance</td></tr><tr><td>st_h3cellids</td></tr><tr><td>st_h3kring</td></tr><tr><td>st_h3togeom</td></tr><tr><td>st_hausdorffdistance</td></tr><tr><td>st_interiorringn</td></tr><tr><td>st_intersection</td></tr><tr><td>st_intersection_aggr</td></tr><tr><td>st_intersects</td></tr><tr><td>st_isclosed</td></tr><tr><td>st_iscollection</td></tr><tr><td>st_isempty</td></tr><tr><td>st_isring</td></tr><tr><td>st_issimple</td></tr><tr><td>st_isvalid</td></tr><tr><td>st_length</td></tr><tr><td>st_lengthspheroid</td></tr><tr><td>st_linefrommultipoint</td></tr><tr><td>st_linefromtext</td></tr><tr><td>st_lineinterpolatepoint</td></tr><tr><td>st_linemerge</td></tr><tr><td>st_linestringfromtext</td></tr><tr><td>st_linesubstring</td></tr><tr><td>st_makeline</td></tr><tr><td>st_makepoint</td></tr><tr><td>st_makepolygon</td></tr><tr><td>st_makevalid</td></tr><tr><td>st_minimumboundingcircle</td></tr><tr><td>st_minimumboundingradius</td></tr><tr><td>st_mlinefromtext</td></tr><tr><td>st_mpolyfromtext</td></tr><tr><td>st_multi</td></tr><tr><td>st_ndims</td></tr><tr><td>st_normalize</td></tr><tr><td>st_npoints</td></tr><tr><td>st_nrings</td></tr><tr><td>st_numgeometries</td></tr><tr><td>st_numinteriorrings</td></tr><tr><td>st_numpoints</td></tr><tr><td>st_orderingequals</td></tr><tr><td>st_overlaps</td></tr><tr><td>st_point</td></tr><tr><td>st_pointfromtext</td></tr><tr><td>st_pointn</td></tr><tr><td>st_pointonsurface</td></tr><tr><td>st_pointz</td></tr><tr><td>st_polygon</td></tr><tr><td>st_polygonfromenvelope</td></tr><tr><td>st_polygonfromtext</td></tr><tr><td>st_reduceprecision</td></tr><tr><td>st_removepoint</td></tr><tr><td>st_reverse</td></tr><tr><td>st_s2cellids</td></tr><tr><td>st_setpoint</td></tr><tr><td>st_setsrid</td></tr><tr><td>st_simplifypreservetopology</td></tr><tr><td>st_split</td></tr><tr><td>st_srid</td></tr><tr><td>st_startpoint</td></tr><tr><td>st_subdivide</td></tr><tr><td>st_subdivideexplode</td></tr><tr><td>st_symdifference</td></tr><tr><td>st_touches</td></tr><tr><td>st_transform</td></tr><tr><td>st_translate</td></tr><tr><td>st_union</td></tr><tr><td>st_union_aggr</td></tr><tr><td>st_voronoipolygons</td></tr><tr><td>st_within</td></tr><tr><td>st_x</td></tr><tr><td>st_xmax</td></tr><tr><td>st_xmin</td></tr><tr><td>st_y</td></tr><tr><td>st_ymax</td></tr><tr><td>st_ymin</td></tr><tr><td>st_z</td></tr><tr><td>st_zmax</td></tr><tr><td>st_zmin</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         "hive_metastore.default.st_geohash"
        ],
        [
         "st_3ddistance"
        ],
        [
         "st_addpoint"
        ],
        [
         "st_affine"
        ],
        [
         "st_angle"
        ],
        [
         "st_area"
        ],
        [
         "st_areaspheroid"
        ],
        [
         "st_asbinary"
        ],
        [
         "st_asewkb"
        ],
        [
         "st_asewkt"
        ],
        [
         "st_asgeojson"
        ],
        [
         "st_asgml"
        ],
        [
         "st_askml"
        ],
        [
         "st_astext"
        ],
        [
         "st_azimuth"
        ],
        [
         "st_boundary"
        ],
        [
         "st_boundingdiagonal"
        ],
        [
         "st_buffer"
        ],
        [
         "st_buildarea"
        ],
        [
         "st_centroid"
        ],
        [
         "st_closestpoint"
        ],
        [
         "st_collect"
        ],
        [
         "st_collectionextract"
        ],
        [
         "st_concavehull"
        ],
        [
         "st_contains"
        ],
        [
         "st_convexhull"
        ],
        [
         "st_coorddim"
        ],
        [
         "st_coveredby"
        ],
        [
         "st_covers"
        ],
        [
         "st_crosses"
        ],
        [
         "st_degrees"
        ],
        [
         "st_difference"
        ],
        [
         "st_dimension"
        ],
        [
         "st_disjoint"
        ],
        [
         "st_distance"
        ],
        [
         "st_distancesphere"
        ],
        [
         "st_distancespheroid"
        ],
        [
         "st_dump"
        ],
        [
         "st_dumppoints"
        ],
        [
         "st_endpoint"
        ],
        [
         "st_envelope"
        ],
        [
         "st_envelope_aggr"
        ],
        [
         "st_equals"
        ],
        [
         "st_exteriorring"
        ],
        [
         "st_flipcoordinates"
        ],
        [
         "st_force3d"
        ],
        [
         "st_force_2d"
        ],
        [
         "st_frechetdistance"
        ],
        [
         "st_geogfromtext"
        ],
        [
         "st_geogfromwkt"
        ],
        [
         "st_geohash"
        ],
        [
         "st_geometricmedian"
        ],
        [
         "st_geometryn"
        ],
        [
         "st_geometrytype"
        ],
        [
         "st_geomfromewkt"
        ],
        [
         "st_geomfromgeohash"
        ],
        [
         "st_geomfromgeojson"
        ],
        [
         "st_geomfromgml"
        ],
        [
         "st_geomfromkml"
        ],
        [
         "st_geomfromtext"
        ],
        [
         "st_geomfromwkb"
        ],
        [
         "st_geomfromwkt"
        ],
        [
         "st_h3celldistance"
        ],
        [
         "st_h3cellids"
        ],
        [
         "st_h3kring"
        ],
        [
         "st_h3togeom"
        ],
        [
         "st_hausdorffdistance"
        ],
        [
         "st_interiorringn"
        ],
        [
         "st_intersection"
        ],
        [
         "st_intersection_aggr"
        ],
        [
         "st_intersects"
        ],
        [
         "st_isclosed"
        ],
        [
         "st_iscollection"
        ],
        [
         "st_isempty"
        ],
        [
         "st_isring"
        ],
        [
         "st_issimple"
        ],
        [
         "st_isvalid"
        ],
        [
         "st_length"
        ],
        [
         "st_lengthspheroid"
        ],
        [
         "st_linefrommultipoint"
        ],
        [
         "st_linefromtext"
        ],
        [
         "st_lineinterpolatepoint"
        ],
        [
         "st_linemerge"
        ],
        [
         "st_linestringfromtext"
        ],
        [
         "st_linesubstring"
        ],
        [
         "st_makeline"
        ],
        [
         "st_makepoint"
        ],
        [
         "st_makepolygon"
        ],
        [
         "st_makevalid"
        ],
        [
         "st_minimumboundingcircle"
        ],
        [
         "st_minimumboundingradius"
        ],
        [
         "st_mlinefromtext"
        ],
        [
         "st_mpolyfromtext"
        ],
        [
         "st_multi"
        ],
        [
         "st_ndims"
        ],
        [
         "st_normalize"
        ],
        [
         "st_npoints"
        ],
        [
         "st_nrings"
        ],
        [
         "st_numgeometries"
        ],
        [
         "st_numinteriorrings"
        ],
        [
         "st_numpoints"
        ],
        [
         "st_orderingequals"
        ],
        [
         "st_overlaps"
        ],
        [
         "st_point"
        ],
        [
         "st_pointfromtext"
        ],
        [
         "st_pointn"
        ],
        [
         "st_pointonsurface"
        ],
        [
         "st_pointz"
        ],
        [
         "st_polygon"
        ],
        [
         "st_polygonfromenvelope"
        ],
        [
         "st_polygonfromtext"
        ],
        [
         "st_reduceprecision"
        ],
        [
         "st_removepoint"
        ],
        [
         "st_reverse"
        ],
        [
         "st_s2cellids"
        ],
        [
         "st_setpoint"
        ],
        [
         "st_setsrid"
        ],
        [
         "st_simplifypreservetopology"
        ],
        [
         "st_split"
        ],
        [
         "st_srid"
        ],
        [
         "st_startpoint"
        ],
        [
         "st_subdivide"
        ],
        [
         "st_subdivideexplode"
        ],
        [
         "st_symdifference"
        ],
        [
         "st_touches"
        ],
        [
         "st_transform"
        ],
        [
         "st_translate"
        ],
        [
         "st_union"
        ],
        [
         "st_union_aggr"
        ],
        [
         "st_voronoipolygons"
        ],
        [
         "st_within"
        ],
        [
         "st_x"
        ],
        [
         "st_xmax"
        ],
        [
         "st_xmin"
        ],
        [
         "st_y"
        ],
        [
         "st_ymax"
        ],
        [
         "st_ymin"
        ],
        [
         "st_z"
        ],
        [
         "st_zmax"
        ],
        [
         "st_zmin"
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "function",
         "type": "\"string\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql \n",
    "show user functions like 'st_*'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "c87fd220-d78e-402a-9452-e15191128a1b",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "_Notice that the prior system registered functions have been replaced, e.g. `ST_Area`._"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "7d5f73c2-d7c1-4e61-bf15-41d51a1d3829",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>function_desc</th></tr></thead><tbody><tr><td>Function: ST_Area</td></tr><tr><td>Class: org.apache.spark.sql.sedona_sql.expressions.ST_Area</td></tr><tr><td>Usage: N/A.</td></tr><tr><td>Extended Usage:\n",
       "    No example/argument for ST_Area.\n",
       "</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         "Function: ST_Area"
        ],
        [
         "Class: org.apache.spark.sql.sedona_sql.expressions.ST_Area"
        ],
        [
         "Usage: N/A."
        ],
        [
         "Extended Usage:\n    No example/argument for ST_Area.\n"
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "function_desc",
         "type": "\"string\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql \n",
    "-- notice, e.g. the provided function now are available\n",
    "describe function extended st_area"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "1805f461-ecab-4a03-980d-fb403a3a028e",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Queries\n",
    "\n",
    "> Showing how Sedona (registered Spark SQL) and Mosaic (Scala) can co-exist on the same cluster. Not shown here, but the could also be Mosaic Python bindings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "c1e4ac30-daf0-423c-8117-b7c3c4c06e52",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>wkt</th></tr></thead><tbody><tr><td>POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         "POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))"
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "wkt",
         "type": "\"string\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "CREATE OR REPLACE TEMPORARY VIEW sample AS (\n",
    "  SELECT 'POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))' AS wkt\n",
    ");\n",
    "\n",
    "SELECT * FROM sample"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "fbb24b11-f88d-46fb-a365-773d35923704",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "_Here is a Spark SQL call to use the Sedona functions._"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "6f44c258-6919-43bc-9b52-a9167ce48078",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>sedona_area</th></tr></thead><tbody><tr><td>550.0</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         550.0
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "sedona_area",
         "type": "\"double\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "SELECT ST_Area(ST_GeomFromText(wkt)) AS sedona_area FROM sample"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "3a484604-c4bc-4234-acf0-32994de54554",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "_Here is Scala call to the same Mosaic-provided `ST_Area` function._"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "ccf12e8d-82ff-47d9-ab5e-f64b2c487223",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>mosaic_area</th></tr></thead><tbody><tr><td>550.0</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         550.0
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "mosaic_area",
         "type": "\"double\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%scala\n",
    "// verify scala functions registered\n",
    "display(\n",
    "  spark\n",
    "  .table(\"sample\")\n",
    "    .select(st_area($\"wkt\").as(\"mosaic_area\"))\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "6dd1e21d-7a84-4c5e-b5f6-b02831d846b0",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "_Mosaic + Sedona_\n",
    "\n",
    "> Showing blending Mosaic calls (in Scala) with Sedona (Spark SQL) calls."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "e0602e02-01ec-45cd-8c17-aa30e0d0d969",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>mosaic_area</th><th>sedona_area</th><th>wkt</th></tr></thead><tbody><tr><td>550.0</td><td>550.0</td><td>POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         550.0,
         550.0,
         "POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))"
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "mosaic_area",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "sedona_area",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "wkt",
         "type": "\"string\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%scala\n",
    "display(\n",
    "  spark.table(\"sample\")\n",
    "    .select(\n",
    "      st_area($\"wkt\").as(\"mosaic_area\"),                    // <- mosaic (scala)\n",
    "      expr(\"ST_Area(ST_GeomFromText(wkt)) AS sedona_area\"), // <- sedona (spark sql)\n",
    "      $\"wkt\"\n",
    "    )\n",
    ")"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "language": "python",
   "notebookMetadata": {
    "mostRecentlyExecutedCommandWithImplicitDF": {
     "commandId": 1148550101113066,
     "dataframes": [
      "_sqldf"
     ]
    },
    "pythonIndentUnit": 2
   },
   "notebookName": "MosaicAndSedona",
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}