geocoding_and_reverse_geocoding.ipynb

{
 "metadata": {
  "kernelspec": {
   "display_name": "Streamlit Notebook",
   "name": "streamlit"
  }
 },
 "nbformat_minor": 5,
 "nbformat": 4,
 "cells": [
  {
   "cell_type": "markdown",
   "id": "3775908f-ca36-4846-8f38-5adca39217f2",
   "metadata": {
    "name": "text_1",
    "collapsed": false
   },
   "source": "Before starting with this lab, complete the preparation steps from `Setup your account` page of [Geospatial Analytics, AI and ML using Snowflake](http://localhost:8000/guide/geo-for-machine-learning/index.html?index=..%2F..index#1) quickstart.\n\nIn this lab, we will demonstrate how to perform geocoding and reverse geocoding using datasets and applications from the Marketplace. You will learn how to:\n- Perform address cleansing\n- Convert an address into a location (geocoding)\n- Convert a location into an address (reverse geocoding)\n\nFor the most precise and reliable geocoding results, we recommend using specialized services like [Mapbox](https://app.snowflake.com/marketplace/listing/GZT0ZIFQPEA/mapbox-mapbox-geocoding-analysis-tools) or [TravelTime](https://app.snowflake.com/marketplace/listing/GZ2FSZKSSH1/traveltime-technologies-ltd-traveltime). While the methods described in this Lab can be useful, they may not always achieve the highest accuracy, especially in areas with sparse data or complex geographic features. If your application demands extremely precise geocoding, consider investing in a proven solution with guaranteed accuracy and robust support.\n\nHowever, many companies seek cost-effective solutions for geocoding large datasets. In such cases, supplementing specialized services with free datasets can be a viable approach. Datasets provided by the [Overture Maps Foundation](https://overturemaps.org/) or [OpenAddresses](https://openaddresses.io/) can be valuable resources for building solutions that are \"good enough\", especially when some accuracy can be compromised in favor of cost-efficiency. It's essential to evaluate your specific needs and constraints before selecting a geocoding approach.\n\n### Step 1. Data acquisition\n\nFor this project you will use a dataset with locations of restaurants and cafes in Berlin from the [CARTO Academy](https://app.snowflake.com/marketplace/listing/GZT0Z4CM1E9J2/carto-carto-academy-data-for-tutorials) Marketplace listing.\n* Navigate to the `Marketplace` screen using the menu on the left side of the window\n* Search for `CARTO Academy` in the search bar\n* Find and click the `CARTO Academy - Data for tutorials` tile\n* Once in the listing, click the big blue `Get` button\n\nOn the `Get` screen, you may be prompted to complete your `user profile` if you have not done so before. Click the link as shown in the screenshot below. Enter your name and email address into the profile screen and click the blue `Save` button. You will be returned to the `Get` screen.\n\nAnother dataset that you will use in this Lab is [Worldwide Address Data](https://app.snowflake.com/marketplace/listing/GZSNZ7F5UT/starschema-worldwide-address-data) and you can also get it from the Snowflake Marketplace. It's a free dataset from the OpenAddresses project that allows Snowflake customers to map lat/long information to address details. \n- Search for `Worldwide Address Data` in the search bar\n- Find and click on the corresponding dataset from Starschema\n- On the `Get Data` screen, don't change the name of the database from `WORLDWIDE_ADDRESS_DATA`.\n\nNice! You have just got two listings that you will need for this project.\n\n### Step 2. Data Preparation\nTo showcase geocoding techniques in this lab, and to evaluate the quality of our approach you will use a table `CARTO_ACADEMY__DATA_FOR_TUTORIALS.CARTO.DATAAPPEAL_RESTAURANTS_AND_CAFES_BERLIN_CPG` with locations of restaurants and cafes in Berlin. If you look into that table you will notice that some records don't have full or correct information in the `STREET_ADDRESS` column. To be able to calculate the correct quality metrics in this lab lets do a simple cleanup of the low quality datapoint. Run the following query to create a table that has only records that have 5-digits postcode and those records are in Berlin."
  },
  {
   "cell_type": "code",
   "id": "0449367b-3ae8-4bc7-8a5a-236ed598d9e6",
   "metadata": {
    "language": "sql",
    "name": "query_1",
    "collapsed": false,
    "codeCollapsed": false
   },
   "outputs": [],
   "source": "CREATE OR REPLACE TABLE ADVANCED_ANALYTICS.PUBLIC.GEOCODING_ADDRESSES AS\nSELECT * \nFROM CARTO_ACADEMY__DATA_FOR_TUTORIALS.CARTO.DATAAPPEAL_RESTAURANTS_AND_CAFES_BERLIN_CPG\nWHERE REGEXP_SUBSTR(street_address, '(\\\\d{5})') is not null\nAND city ILIKE 'berlin';",
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "id": "68aea3d4-2224-4d11-a463-f17f87bf1803",
   "metadata": {
    "name": "text_2",
    "collapsed": false
   },
   "source": "If you check the size of `ADVANCED_ANALYTICS.PUBLIC.GEOCODING_ADDRESSES` table you'll see that it has about 10K rows.\n\nThe Worldwide Address Data dataset contains more than 500M addresses around the world and we will use it for geocoding and reverse geocoding. However some addresses in that dataset contain addresses with coordinates outside of the allowed boundaries for latitude and longitude. Run the following query to create a new table that filters out those \"invalid\" records and includes a new column, `LOCATION`, which stores the locations in the `GEOGRAPHY` type:"
  },
  {
   "cell_type": "code",
   "id": "ca427606-a887-4718-ba0b-498f04826ec1",
   "metadata": {
    "language": "sql",
    "name": "query_2"
   },
   "outputs": [],
   "source": "CREATE OR REPLACE TABLE ADVANCED_ANALYTICS.PUBLIC.OPENADDRESS AS\nSELECT ST_POINT(lon, lat) as location, *\nFROM WORLDWIDE_ADDRESS_DATA.ADDRESS.OPENADDRESS\nWHERE lon between -180 and 180\nAND lat between -90 and 90;",
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "id": "4c65635e-ebe0-4795-8853-c899d419f531",
   "metadata": {
    "name": "text_3",
    "collapsed": false
   },
   "source": "Now when all your data is ready and clean, you can proceed to the actual use cases.\n\n### Step 2. Data Cleansing\nCustomer-provided address data is often incomplete or contains spelling mistakes. If you plan to perform geocoding on that data, it would be a good idea to include address cleansing as a preparation step.\n\nIn this step, you will prepare a prompt to run the data cleansing. For this task, you will use the [CORTEX.COMPLETE()](https://docs.snowflake.com/en/sql-reference/functions/complete-snowflake-cortex) function because it is purpose-built for data processing and data generation tasks. First, let's create a Cortex role. In the query below, replace AA with the username you used to log in to Snowflake."
  },
  {
   "cell_type": "code",
   "id": "8d50cbf4-0c8d-4950-86cb-114990437ac9",
   "metadata": {
    "language": "sql",
    "name": "query_3"
   },
   "source": "CREATE ROLE IF NOT EXISTS cortex_user_role;\nGRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE cortex_user_role;\n\nGRANT ROLE cortex_user_role TO USER GEOLAB;",
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "id": "c695373e-ac74-4b62-a1f1-08206cbd5c81",
   "metadata": {
    "name": "text_4",
    "collapsed": false
   },
   "source": "You are now ready to provide the CORTEX.COMPLETE() function with instructions on how to perform address cleansing. Specifically, using a table of Berlin restaurants, you'll create a new table with an additional column `parsed_address`, which is the result of the `CORTEX.COMPLETE()` function. For complex processing like this, you will use [mistral-8x7b](https://docs.snowflake.com/en/user-guide/snowflake-cortex/llm-functions#availability), a very capable open-source LLM created by Mistral AI. Essentially, we want to parse the address stored as a single string into a JSON object that contains each component of the address as a separate key.\n\nAs a general rule when writing a prompt, the instructions should be simple, clear, and complete. For example, you should clearly define the task as parsing an address into a JSON object. It's important to define the constraints of the desired output; otherwise, the LLM may produce unexpected results. Below, you specifically instruct the LLM to parse the address stored as text and explicitly tell it to respond in JSON format."
  },
  {
   "cell_type": "code",
   "id": "cd7f9b9a-4587-48f1-a137-0f907b25be61",
   "metadata": {
    "language": "sql",
    "name": "query_4",
    "collapsed": false,
    "codeCollapsed": false
   },
   "outputs": [],
   "source": "CREATE OR REPLACE TABLE ADVANCED_ANALYTICS.PUBLIC.GEOCODING_CLEANSED_ADDRESSES as\nSELECT geom, geoid, street_address, name,\n    snowflake.cortex.complete('mixtral-8x7b', \n    concat('Task: Your job is to return a JSON formatted response that normalizes, standardizes, and enriches the following address,\n            filling in any missing information when needed: ', street_address, \n            'Requirements: Return only in valid JSON format (starting with { and ending with }).\n            The JSON response should include the following fields:\n            \"number\": <<house_number>>,\n            \"street\": <<street_name>>,\n            \"city\": <<city_name>>,\n            \"postcode\": <<postcode_value>>,\n            \"country\": <<ISO_3166-1_alpha-2_country_code>>.\n            Values inside <<>> must be replaced with the corresponding details from the address provided.\n            - If a value cannot be determined, use \"Null\".\n            - No additional fields or classifications should be included beyond the five categories listed.\n            - Country code must follow the ISO 3166-1 alpha-2 standard.\n            - Do not add comments or any other non-JSON text.\n            - Use Latin characters for street names and cities, avoiding Unicode alternatives.\n            Examples:\n            Input: \"123 Mn Stret, San Franscico, CA\"\n            Output: {\"number\": \"123\", \"street\": \"Main Street\", \"city\": \"San Francisco\", \"postcode\": \"94105\", \"country\": \"US\"}\n            Input: \"45d Park Avnue, New Yrok, NY 10016\"\n            Output: {\"number\": \"45d\", \"street\": \"Park Avenue\", \"city\": \"New York\", \"postcode\": \"10016\", \"country\": \"US\"}\n            Input: \"10 Downig Stret, Londn, SW1A 2AA, United Knidom\"\n            Output: {\"number\": \"10\", \"street\": \"Downing Street\", \"city\": \"London\", \"postcode\": \"SW1A 2AA\", \"country\": \"UK\"}\n            Input: \"4 Avneu des Champs Elyses, Paris, France\"\n            Output: {\"number\": \"4\", \"street\": \"Avenue des Champs-Élysées\", \"city\": \"Paris\", \"postcode\": \"75008\", \"country\": \"FR\"}\n            Input: \"1600 Amiphiteatro Parkway, Montain View, CA 94043, USA\"\n            Output: {\"number\": \"1600\", \"street\": \"Amphitheatre Parkway\", \"city\": \"Mountain View\", \"postcode\": \"94043\", \"country\": \"US\"}\n            Input: \"Plaza de Espana, 28c, Madird, Spain\"\n            Output: {\"number\": \"28c\", \"street\": \"Plaza de España\", \"city\": \"Madrid\", \"postcode\": \"28008\", \"country\": \"ES\"}\n            Input: \"1d Prinzessinenstrase, Berlín, 10969, Germany\"\n            Output: {\"number\": \"1d\", \"street\": \"Prinzessinnenstraße\", \"city\": \"Berlin\", \"postcode\": \"10969\", \"country\": \"DE\"} ')) as parsed_address \n        FROM ADVANCED_ANALYTICS.PUBLIC.GEOCODING_ADDRESSES;",
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "id": "78a66e28-b6dd-422b-95d7-9e91569f90f0",
   "metadata": {
    "name": "text_5",
    "collapsed": false
   },
   "source": "On a `LARGE` warehouse, which we used in this quickstart, the query completed in about 13 minutes. However, on a smaller warehouse, the completion time is roughly the same. We don't recommend using a warehouse larger than `MEDIUM` for CORTEX LLM functions, as it won't significantly reduce execution time. If you plan to execute complex processing with LLM on a large dataset, it's better to split the dataset into chunks up to 100K rows each and run multiple jobs in parallel using an `X-Small` warehouse. A rule of thumb is that on an `X-Small`, data cleansing of 1,000 rows can be done within 90 seconds, which costs about 5 cents.\n\nNow, you will convert the parsed address into JSON type:"
  },
  {
   "cell_type": "code",
   "id": "975d1ca9-ecd0-4d17-a4d0-ea55808a591f",
   "metadata": {
    "language": "sql",
    "name": "query_5"
   },
   "outputs": [],
   "source": "CREATE OR REPLACE TABLE ADVANCED_ANALYTICS.PUBLIC.GEOCODING_CLEANSED_ADDRESSES AS\nSELECT geoid, geom, street_address, name,\nTRY_PARSE_JSON(parsed_address) AS parsed_address,\nFROM ADVANCED_ANALYTICS.PUBLIC.GEOCODING_CLEANSED_ADDRESSES;",
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "id": "6797ab31-f9b0-48c7-ae51-56c10ed537c2",
   "metadata": {
    "name": "text_6",
    "collapsed": false
   },
   "source": "Run the following query to check what the result of cleansing looks like in the `PARSED_ADDRESS` column and compare it with the actual address in the `STREET_ADDRESS` column."
  },
  {
   "cell_type": "code",
   "id": "cbe0eed5-6258-41f9-bbed-d9ccde55d73b",
   "metadata": {
    "language": "sql",
    "name": "query_6",
    "collapsed": false,
    "codeCollapsed": false
   },
   "outputs": [],
   "source": "ALTER SESSION SET GEOGRAPHY_OUTPUT_FORMAT='WKT';\n\nSELECT TOP 10 * FROM ADVANCED_ANALYTICS.PUBLIC.GEOCODING_CLEANSED_ADDRESSES;",
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "id": "86a21d5d-437e-497c-a974-2f83fdfbe79d",
   "metadata": {
    "name": "text_7",
    "collapsed": false
   },
   "source": "You also can notice that 23 addresses were not correctly parsed, but if you look into the `STREET_ADDRESS` column of those records using the following query, you can understand why they were not parsed: in most cases there are some address elements missing in the initial address."
  },
  {
   "cell_type": "code",
   "id": "ef3bf621-3f68-4422-b489-f288ef36512c",
   "metadata": {
    "language": "sql",
    "name": "query_7"
   },
   "outputs": [],
   "source": "SELECT * FROM ADVANCED_ANALYTICS.PUBLIC.GEOCODING_CLEANSED_ADDRESSES\nWHERE parsed_address IS NULL;",
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "id": "ee45a730-7cec-449c-9d9b-0058498e4c96",
   "metadata": {
    "name": "text_8",
    "collapsed": false
   },
   "source": "### Step3. Geocoding\n\nIn this step, you will use the Worldwide Address Data to perform geocoding. You will join this dataset with your cleansed address data using country, city, postal code, street, and building number as keys. For street name comparison, you will use [Jaro-Winkler distance](https://en.wikipedia.org/wiki/Jaro%E2%80%93Winkler_distance) to measure similarity between the two strings. You should use a sufficiently high similarity threshold but not 100%, which would imply exact matches. Approximate similarity is necessary to account for potential variations in street names, such as \"Street\" versus \"Straße\".\n\nTo the initial table with actual location and address, you will add columns with geocoded and parsed values for country, city, postcode, street, and building number. Run the following query:"
  },
  {
   "cell_type": "code",
   "id": "5143c281-dc8e-4b2a-baf7-5dc66db85fa6",
   "metadata": {
    "language": "sql",
    "name": "query_8"
   },
   "outputs": [],
   "source": "CREATE OR REPLACE TABLE ADVANCED_ANALYTICS.PUBLIC.GEOCODED AS\nSELECT \n    t1.name,\n    t1.geom AS actual_location,\n    t2.location AS geocoded_location, \n    t1.street_address as actual_address,\n    t2.street as geocoded_street, \n    t2.postcode as geocoded_postcode, \n    t2.number as geocoded_number, \n    t2.city as geocoded_city\nFROM ADVANCED_ANALYTICS.PUBLIC.GEOCODING_CLEANSED_ADDRESSES t1\nLEFT JOIN ADVANCED_ANALYTICS.PUBLIC.OPENADDRESS t2\nON t1.parsed_address:postcode::string = t2.postcode\nAND t1.parsed_address:number::string = t2.number\nAND LOWER(t1.parsed_address:country::string) = LOWER(t2.country)\nAND LOWER(t1.parsed_address:city::string) = LOWER(t2.city)\nAND JAROWINKLER_SIMILARITY(LOWER(t1.parsed_address:street::string), LOWER(t2.street)) > 95;",
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "id": "df33c2b4-1470-48ca-a0d5-6ff45b053ee7",
   "metadata": {
    "name": "text_9",
    "collapsed": false
   },
   "source": "Now let's analyze the results of geocoding and compare the locations we obtained after geocoding with the original addresses. First, let's see how many addresses we were not able to geocode using this approach."
  },
  {
   "cell_type": "code",
   "id": "54983b4e-2bc6-4be2-956c-fa807184eaf6",
   "metadata": {
    "language": "sql",
    "name": "query_9"
   },
   "outputs": [],
   "source": "SELECT count(*) FROM ADVANCED_ANALYTICS.PUBLIC.GEOCODED\nWHERE geocoded_location IS NULL;",
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "id": "8341ad21-c7f6-47f3-a0fb-5d64d2b7412b",
   "metadata": {
    "name": "text_10",
    "collapsed": false
   },
   "source": "It turned out that 2,081 addresses were not geocoded, which is around 21% of the whole dataset. Let's see how many geocoded addresses deviate from the original location by more than 200 meters."
  },
  {
   "cell_type": "code",
   "id": "ceff2da4-126b-47a2-a221-9ea776be684f",
   "metadata": {
    "language": "sql",
    "name": "query_10"
   },
   "outputs": [],
   "source": "SELECT COUNT(*) FROM ADVANCED_ANALYTICS.PUBLIC.GEOCODED\nWHERE ST_DISTANCE(actual_location, geocoded_location) > 200;",
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "id": "a8df1810-9882-4a56-bfb2-06afee7fe67a",
   "metadata": {
    "name": "text_11",
    "collapsed": false
   },
   "source": "It seems there are 174 addresses. Let's examine random records from these 174 addresses individually by running the query below. You can visualize coordinates from the table with results using [this](https://clydedacruz.github.io/openstreetmap-wkt-playground/) service (copy-paste `GEOCODED_LOCATION` and `ACTUAL_LOCATION` values). "
  },
  {
   "cell_type": "code",
   "id": "c0975265-8978-42b4-806c-2791e3f9f0c8",
   "metadata": {
    "language": "sql",
    "name": "query_11"
   },
   "outputs": [],
   "source": "SELECT * FROM ADVANCED_ANALYTICS.PUBLIC.GEOCODED\nWHERE ST_DISTANCE(actual_location, geocoded_location) > 200;",
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "id": "58fa2f41-f7df-4529-9138-9dd6af73fcc3",
   "metadata": {
    "name": "text_12",
    "collapsed": false
   },
   "source": "You can see that in many cases, our geocoding provided the correct location for the given address, while the original location point actually corresponds to a different address. Therefore, our approach returned more accurate locations than those in the original dataset. Sometimes, the \"ground truth\" data contains incorrect data points.\n\nIn this exercise, you successfully geocoded more than 78% of the entire dataset. To geocode the remaining addresses that were not geocoded using this approach, you can use paid services such as [Mapbox](https://app.snowflake.com/marketplace/listing/GZT0ZIFQPEA/mapbox-mapbox-geocoding-analysis-tools) or [TravelTime](https://app.snowflake.com/marketplace/listing/GZ2FSZKSSH1/traveltime-technologies-ltd-traveltime). However, you managed to reduce the geocoding cost by more than four times compared to what it would have been if you had used those services for the entire dataset.\n\n### Step 4. Reverse Geocoding\n\nIn the next step, we will do the opposite - for a given location, we will get the address. Often, companies have location information and need to convert it into the actual address. Similar to the previous example, the best way to do reverse geocoding is to use specialized services, such as Mapbox or TravelTime. However, there are cases where you're ready to trade off between accuracy and cost. For example, if you don't need an exact address but a zip code would be good enough. In this case, you can use free datasets to perform reverse geocoding.\n\nTo complete this exercise, we will use the nearest neighbor approach. For locations in our test dataset (`ADVANCED_ANALYTICS.PUBLIC.GEOCODING_ADDRESSES` table), you will find the closest locations from the Worldwide Address Data. Let's first create a procedure that, for each row in the given table with addresses, finds the closest address from the Worldwide Address Data table within the radius of 5km. To speed up the function we will apply an iterative approach to the neighbor search - start from 10 meters and increase the search radius until a match is found or the maximum radius is reached. Run the following query:"
  },
  {
   "cell_type": "code",
   "id": "11a7d4b9-0357-488b-945f-9bcb84779339",
   "metadata": {
    "language": "sql",
    "name": "query_12"
   },
   "outputs": [],
   "source": "CREATE OR REPLACE PROCEDURE GEOCODING_EXACT(\n    NAME_FOR_RESULT_TABLE TEXT,\n    LOCATIONS_TABLE_NAME TEXT,\n    LOCATIONS_ID_COLUMN_NAME TEXT,\n    LOCATIONS_COLUMN_NAME TEXT,\n    WWAD_TABLE_NAME TEXT,\n    WWAD_COLUMN_NAME TEXT\n)\nRETURNS TEXT\nLANGUAGE SQL\nAS $$\nDECLARE\n    -- Initialize the search radius to 10 meters.\n    RADIUS REAL DEFAULT 10.0;\nBEGIN\n    -- **********************************************************************\n    -- Procedure: GEOCODING_EXACT\n    -- Description: This procedure finds the closest point from the Worldwide \n    --              Address Data table for each location in the LOCATIONS_TABLE. \n    --              It iteratively increases the search radius until a match is \n    --              found or the maximum radius is reached.\n    -- **********************************************************************\n\n    -- Create or replace the result table with the required schema but no data.\n    EXECUTE IMMEDIATE '\n        CREATE OR REPLACE TABLE ' || NAME_FOR_RESULT_TABLE || ' AS\n        SELECT\n            ' || LOCATIONS_ID_COLUMN_NAME || ',\n            ' || LOCATIONS_COLUMN_NAME || ' AS LOCATION_POINT,\n            ' || WWAD_COLUMN_NAME || ' AS CLOSEST_LOCATION_POINT,\n            t2.NUMBER,\n            t2.STREET,\n            t2.UNIT,\n            t2.CITY,\n            t2.DISTRICT,\n            t2.REGION,\n            t2.POSTCODE,\n            t2.COUNTRY,\n            0.0::REAL AS DISTANCE\n        FROM\n            ' || LOCATIONS_TABLE_NAME || ' t1,\n            ' || WWAD_TABLE_NAME || ' t2\n        LIMIT 0';\n\n-- Define a sub-query to select locations not yet processed.\n    LET REMAINING_QUERY := '\n        SELECT\n            ' || LOCATIONS_ID_COLUMN_NAME || ',\n            ' || LOCATIONS_COLUMN_NAME || '\n        FROM\n            ' || LOCATIONS_TABLE_NAME || '\n        WHERE\n            NOT EXISTS (\n                SELECT 1\n                FROM ' || NAME_FOR_RESULT_TABLE || ' tmp\n                WHERE ' || LOCATIONS_TABLE_NAME || '.' || LOCATIONS_ID_COLUMN_NAME || ' = tmp.' || LOCATIONS_ID_COLUMN_NAME || '\n            )';\n\n-- Iteratively search for the closest point within increasing radius.\n    FOR I IN 1 TO 10 DO\n-- Insert closest points into the result table for \n-- locations within the current radius.\n        EXECUTE IMMEDIATE '\n            INSERT INTO ' || NAME_FOR_RESULT_TABLE || '\n            WITH REMAINING AS (' || :REMAINING_QUERY || ')\n            SELECT\n                ' || LOCATIONS_ID_COLUMN_NAME || ',\n                ' || LOCATIONS_COLUMN_NAME || ' AS LOCATION_POINT,\n                points.' || WWAD_COLUMN_NAME || ' AS CLOSEST_LOCATION_POINT,\n                points.NUMBER,\n                points.STREET,\n                points.UNIT,\n                points.CITY,\n                points.DISTRICT,\n                points.REGION,\n                points.POSTCODE,\n                points.COUNTRY,\n                ST_DISTANCE(' || LOCATIONS_COLUMN_NAME || ', points.' || WWAD_COLUMN_NAME || ') AS DISTANCE\n            FROM\n                REMAINING\n            JOIN\n                ' || WWAD_TABLE_NAME || ' points\n            ON\n                ST_DWITHIN(\n                    REMAINING.' || LOCATIONS_COLUMN_NAME || ',\n                    points.' || WWAD_COLUMN_NAME || ',\n                    ' || RADIUS || '\n                )\n            QUALIFY\n                ROW_NUMBER() OVER (\n                    PARTITION BY ' || LOCATIONS_ID_COLUMN_NAME || '\n                    ORDER BY DISTANCE\n                ) <= 1';\n\n        -- Double the radius for the next iteration.\n        RADIUS := RADIUS * 2;\n    END FOR;\nEND\n$$;",
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "id": "883bc7da-7dd8-47d9-8c42-392879c03abd",
   "metadata": {
    "name": "text_13",
    "collapsed": false
   },
   "source": "Run the next query to call that procedure and store results of reverse geocoding to `ADVANCED_ANALYTICS.PUBLIC.REVERSE_GEOCODED` table:"
  },
  {
   "cell_type": "code",
   "id": "9d85b461-fc8a-4b31-bc7d-397635c52517",
   "metadata": {
    "language": "sql",
    "name": "query_13",
    "collapsed": false,
    "codeCollapsed": false
   },
   "outputs": [],
   "source": "CALL GEOCODING_EXACT('ADVANCED_ANALYTICS.PUBLIC.REVERSE_GEOCODED', 'ADVANCED_ANALYTICS.PUBLIC.GEOCODING_ADDRESSES', 'GEOID', 'GEOM', 'ADVANCED_ANALYTICS.PUBLIC.OPENADDRESS', 'LOCATION');",
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "id": "59465406-c38d-488b-8217-5dc1719917dd",
   "metadata": {
    "name": "text_14",
    "collapsed": false
   },
   "source": "This query completed in 5.5 minutes on `LARGE` warehouse, which corresponds to about 2 USD. Let's now compare the address we get after the reverse geocoding (`ADVANCED_ANALYTICS.PUBLIC.REVERSE_GEOCODED` table) with the table that has the original address."
  },
  {
   "cell_type": "code",
   "id": "2212d8a4-e611-4552-ba91-73197b6ac9f3",
   "metadata": {
    "language": "sql",
    "name": "query_14",
    "collapsed": false
   },
   "outputs": [],
   "source": "SELECT t1.geoid, \n    t2.street_address AS actual_address,\n    t1.street || ' ' || t1.number || ', ' || t1.postcode || ' ' || t1.city  || ', ' || t1.country AS geocoded_address\nFROM ADVANCED_ANALYTICS.PUBLIC.REVERSE_GEOCODED t1\nINNER JOIN ADVANCED_ANALYTICS.PUBLIC.GEOCODING_CLEANSED_ADDRESSES t2\n    ON t1.geoid = t2.geoid\nWHERE t1.distance < 100;",
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "id": "057ae51d-1e7f-49d5-8366-1079025042cf",
   "metadata": {
    "name": "text_15",
    "collapsed": false
   },
   "source": "For 9830 records, the closest addresses we found are within 100 meters from the original address. This corresponds to 98.7% of cases. As we mentioned earlier, often for analysis you might not need the full address, and knowing a postcode is already good enough. Run the following query to see for how many records the geocoded postcode is the same as the original postcode:"
  },
  {
   "cell_type": "code",
   "id": "05226e15-97bb-4767-a15f-fcdaf9a6bee8",
   "metadata": {
    "language": "sql",
    "name": "query_15"
   },
   "outputs": [],
   "source": "SELECT count(*)\nFROM ADVANCED_ANALYTICS.PUBLIC.REVERSE_GEOCODED t1\nINNER JOIN ADVANCED_ANALYTICS.PUBLIC.GEOCODING_CLEANSED_ADDRESSES t2\n    ON t1.geoid = t2.geoid\nWHERE t2.parsed_address:postcode::string = t1.postcode::string;",
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "id": "e9f467f9-00ce-4001-91a2-9ac757578861",
   "metadata": {
    "name": "text_16",
    "collapsed": false
   },
   "source": "This query returned 9564 records,  about 96% of the dataset, which is quite a good result.\n\nOut of curiosity, let's see, for how many addresses the geocoded and initial address is the same up until the street name. Run the following query:"
  },
  {
   "cell_type": "code",
   "id": "27dcf8a0-c952-4fe0-8a80-c6b283044a6a",
   "metadata": {
    "language": "sql",
    "name": "query16"
   },
   "outputs": [],
   "source": "SELECT count(*)\nFROM ADVANCED_ANALYTICS.PUBLIC.REVERSE_GEOCODED t1\nINNER JOIN ADVANCED_ANALYTICS.PUBLIC.GEOCODING_CLEANSED_ADDRESSES t2\n    ON t1.geoid = t2.geoid\nWHERE t2.parsed_address:postcode::string = t1.postcode\nAND LOWER(t2.parsed_address:country::string) = LOWER(t1.country)\nAND LOWER(t2.parsed_address:city::string) = LOWER(t1.city)\nAND JAROWINKLER_SIMILARITY(LOWER(t2.parsed_address:street::string), LOWER(t1.street)) > 95;",
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "id": "797746f6-dc13-4668-916d-dbe834404c7a",
   "metadata": {
    "name": "text_17",
    "collapsed": false
   },
   "source": "82% of addresses correctly geocoded up to the street name. And to have a full picture, let's see how many records have the fully identical original and geocoded address:"
  },
  {
   "cell_type": "code",
   "id": "55e0081a-13c5-49e2-995b-eb47cd1dec57",
   "metadata": {
    "language": "sql",
    "name": "query_17"
   },
   "outputs": [],
   "source": "SELECT count(*)\nFROM ADVANCED_ANALYTICS.PUBLIC.REVERSE_GEOCODED t1\nINNER JOIN ADVANCED_ANALYTICS.PUBLIC.GEOCODING_CLEANSED_ADDRESSES t2\n    ON t1.geoid = t2.geoid\nWHERE t2.parsed_address:postcode::string = t1.postcode\nAND t2.parsed_address:number::string = t1.number\nAND LOWER(t2.parsed_address:country::string) = LOWER(t1.country)\nAND LOWER(t2.parsed_address:city::string) = LOWER(t1.city)\nAND JAROWINKLER_SIMILARITY(LOWER(t2.parsed_address:street::string), LOWER(t1.street)) > 95;",
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "id": "81cf9084-4457-4e40-a35a-9c6f5051e1ef",
   "metadata": {
    "name": "text_18",
    "collapsed": false
   },
   "source": "For 61% of addresses we were able to do reverse geocoding that matches reference dataset up to the rooftop.\n\n### Conclusion\n\nIn this lab, you have learned how to perform geocoding and reverse geocoding using free datasets and open-source tools. While this approach may not provide the highest possible accuracy, it offers a cost-effective solution for processing large datasets where some degree of inaccuracy is acceptable. It's important to mention that Worldwide Address Data that has more than 500M addresses  for the whole world is one of many free datasets that you can get from Snowflake Marketplace and use for geocoding use cases. There are others, which you might consider for your use cases, here are just some examples:\n- [Overture Maps - Addresses](https://app.snowflake.com/marketplace/listing/GZT0Z4CM1E9NQ/carto-overture-maps-addresses) - if you mainly need to geocode addresses in North America, another good option would be to use this dataset that has more than 200M addresses.\n- [US Addresses & PO](https://app.snowflake.com/marketplace/listing/GZTSZAS2KIA/cybersyn-us-addresses-poi) - has more than 150M rows can be used as a source of information around locations of Points of Interests.\n- [French National Addresses](https://app.snowflake.com/marketplace/listing/GZT1ZQXT8U/atos-french-national-addresses) - contains about 26M addresses in France.\n- [Dutch Addresses & Buildings Registration (BAG)](https://app.snowflake.com/marketplace/listing/GZ1M7Z62O2A/tensing-dutch-addresses-buildings-registration-bag) - includes Dutch Addresses.\n\nThere is a high chance that datasets focused on particular counties have richer and more accurate data for those countries. And by amending queries from this lab you can find the best option for your needs. "
  }
 ]
}