Skip to content

Instantly share code, notes, and snippets.

@sfc-gh-vsekar

sfc-gh-vsekar/DEMO_BATCH_GEOCODING_SPROC.ipynb

Created September 27, 2025 17:49
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save sfc-gh-vsekar/59bf11691d279739f7b2f6fb26a660bf to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save sfc-gh-vsekar/59bf11691d279739f7b2f6fb26a660bf to your computer and use it in GitHub Desktop.
Download ZIP
Series of Snowflake notebook demonstrating using ArcGIS python SDK natively in Snowflake
Display the source blob
Display the rendered blob
Raw
DEMO_BATCH_GEOCODING_SPROC.ipynb
{
"metadata": {
"language_info": {
"name": "python"
},
"lastEditStatus": {
"notebookId": "33r7ziql6ty6f56kr",
"lastEditTime": 1758994857618
}
},
"nbformat_minor": 2,
"nbformat": 4,
"cells": [
{
"cell_type": "markdown",
"metadata": {
"name": "cell1",
"collapsed": false
},
"source": "# ArcGIS Batch Geocoding with Snowflake Stored Procedures\n\n## 🎯 Executive Summary\n\nThis notebook demonstrates a powerful approach to **batch geocoding** by integrating **ArcGIS Geocoding Services** directly within **Snowflake** using stored procedures. This implementation eliminates the need for separate processing servers and prevents data exfiltration, while enabling efficient address-to-coordinate conversion at scale.\n\nArcGIS Batch Geocoding Guide: https://developers.arcgis.com/python/latest/guide/part4-batch-geocoding/\n\n## 🏗️ Architecture Overview\n\n### Traditional vs. Snowpark Approach\n\n**Traditional Architecture Challenges:**\n- Requires separate application servers for geocoding processing\n- Data must be extracted from Snowflake for geocoding\n- Complex data synchronization between systems\n- Security concerns with data leaving the enterprise boundary\n- Additional infrastructure costs and maintenance overhead\n\n**Snowpark-Powered Solution:**\n- **No intermediary external data movement**: Complete spatial analysis originating from Snowflake to ArcGIS\n- **Serverless Architecture**: No need for dedicated geocoding infrastructure\n- **Native Integration**: Direct access to ArcGIS geocoding services from Snowflake stored procedures\n\n### Key Innovation Benefits\n\n1. **💰 Cost Efficiency**: Eliminates need for separate geocoding servers\n3. **⚡ Performance**: Reduced latency by eliminating data transfer overhead\n4. **🔄 Simplified Architecture**: Single platform for data storage and geocoding\n\n## 🎯 Use Cases and Applications\n\n### Primary Use Cases\n- **Address Standardization**: Clean and standardize address data\n- **Location Intelligence**: Convert addresses to precise coordinates\n- **Spatial Analysis Preparation**: Prepare data for spatial operations\n- **Data Enrichment**: Add geographic context to business data\n- **Mapping and Visualization**: Enable address-based mapping\n\n### Industry Applications\n- **Real Estate**: Property location analysis and mapping\n- **Retail**: Store location optimization and customer analysis\n- **Logistics**: Delivery route planning and optimization\n- **Healthcare**: Patient location analysis and service area planning\n- **Government**: Census data processing and demographic analysis\n\n## 🔧 Technical Architecture\n\n### Core Components\n\n1. **Snowflake Snowpark**: Python runtime environment within Snowflake\n2. **ArcGIS API for Python**: Geocoding services integration\n3. **External Access Integrations**: Secure connectivity to ArcGIS services\n4. **Stored Procedures**: Encapsulated geocoding processing logic\n\n### Data Flow Architecture\n\n```\n┌─────────────────┐ ┌──────────────────┐ ┌─────────────────┐\n│ Source Data │───▶│ Snowflake │───▶│ Geocoded Data │\n│ (Addresses) │ │ Stored Proc │ │ (Lat/Long + │\n│ │ │ Batch Geocoding │ │ Enhanced Info) │\n└─────────────────┘ └──────────────────┘ └─────────────────┘\n │\n ▼\n ┌──────────────────┐\n │ ArcGIS Online │\n │ Geocoding Service│\n │ │\n └──────────────────┘\n```\n\n## 📋 Prerequisites and Requirements\n\n### Snowflake Requirements\n- Snowflake account with Snowpark enabled\n- External access integration capabilities\n- Appropriate compute warehouse for geocoding processing\n\n### ArcGIS Requirements\n- ArcGIS Online or Portal for ArcGIS account\n- Geocoding service access\n- Valid user credentials with geocoding permissions\n\n### Python Dependencies\n- `arcgis` - ArcGIS API for Python\n- `pandas` - Data manipulation and analysis\n\n---\n",
"id": "ce110000-1111-2222-3333-ffffff000000"
},
{
"cell_type": "markdown",
"metadata": {
"name": "cell2",
"collapsed": false
},
"source": "## 1. 🔧 Environment Setup and Configuration\n\nThis section initializes the Snowflake Snowpark session and configures the connection parameters for ArcGIS geocoding services. The configuration includes:\n\n### Key Configuration Elements\n\n- **Development Role**: Defines the Snowflake role for development operations\n- **ArcGIS Portal URL**: Target ArcGIS Online or Portal for ArcGIS instance\n- **Authentication Credentials**: User credentials for ArcGIS service access\n- **Session Context**: Active Snowpark session for data operations\n\n**Note:**\n- add package :\n - python-dotenv\n- create a '.env' and configure the following keys and value:\n\n```\n# session role\ndev_role = 'public'\n#\n# ArcGIS Portal configuration\narcgis_portal_url = 'ABCD.arcgis.com'\n#\n# ArcGIS authentication credentials\narcgis_portal_user_name = '---'\narcgis_portal_password = '---'\n```",
"id": "ce110000-1111-2222-3333-ffffff000001"
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"name": "initialization",
"language": "python"
},
"outputs": [],
"source": "# ==========================================\n# LIBRARY IMPORTS AND CONFIGURATION\n# ==========================================\n\n# Core data processing and Snowflake libraries\nimport streamlit as st\nimport pandas as pd\nimport os\nimport snowflake.snowpark.types as T\nimport snowflake.snowpark.functions as F\nfrom snowflake.snowpark import Session\n\nfrom pathlib import Path\n# Check if the file exists\nif Path('.env').is_file():\n from dotenv import load_dotenv\n load_dotenv() # take environment variables\n\n# ==========================================\n# ENVIRONMENT CONFIGURATION\n# ==========================================\n\n# session role\ndev_role = os.getenv('dev_role','public')\n\n# ArcGIS Portal configuration\narcgis_portal_url = os.getenv('arcgis_portal_url','abc.maps.arcgis.com')\n\n# ArcGIS authentication credentials\narcgis_portal_user_name = os.getenv('arcgis_portal_user_name','john.doe')\narcgis_portal_password = os.getenv('arcgis_portal_password','blah-blah')\n\nprint(f\"🔧 Configuration loaded:\")\nprint(f\" 🌐 ArcGIS Portal: {arcgis_portal_url}\")\n\n# ==========================================\n# SESSION INITIALIZATION\n# ==========================================\n\n# Initialize Snowpark session\n# This gets the active session from the notebook environment\nsp_session = get_active_session()\nsp_session.use_role(dev_role)\n\n# Display session context for verification\n_spdf = sp_session.sql(\"\"\"\n SELECT \n current_role() as CURRENT_ROLE,\n current_warehouse() as CURRENT_WAREHOUSE,\n current_database() as CURRENT_DATABASE,\n current_schema() as CURRENT_SCHEMA,\n current_user() as CURRENT_USER\n\"\"\")\n_spdf\n",
"id": "ce110000-1111-2222-3333-ffffff000002"
},
{
"cell_type": "markdown",
"metadata": {
"name": "cell4"
},
"source": [
"## 2. 🗄️ Secure Storage Infrastructure\n",
"\n",
"### Stage Creation for Geocoding Processing Artifacts\n",
"\n",
"This section creates a secure Snowflake stage to store Python scripts, libraries, and other components required for batch geocoding processing. The stage serves as a secure repository for:\n",
"\n",
"- **Python Scripts**: Custom geocoding processing logic\n",
"- **Library Dependencies**: Required Python packages and modules \n",
"- **Configuration Files**: Processing parameters and settings\n",
"- **Temporary Artifacts**: Intermediate processing results\n"
],
"id": "ce110000-1111-2222-3333-ffffff000003"
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"vscode": {
"languageId": "sql"
},
"name": "define_stage",
"language": "sql"
},
"outputs": [],
"source": [
"-- NOTE: Please switch this to SQL cell.\n",
"\n",
"-- Switch to the development role for stage creation\n",
"USE ROLE {{dev_role}};\n",
"\n",
"-- Create secure library stage for artifacts\n",
"-- This stage will store Python scripts, libraries, and other components\n",
"CREATE OR REPLACE STAGE arcgis_lib_stg\n",
" DIRECTORY = (ENABLE = TRUE)\n",
" ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')\n",
" COMMENT = 'Secure stage for ArcGIS geocoding processing artifacts, Python scripts, and dependencies';\n",
"\n",
"-- Verify stage creation\n",
"SHOW STAGES LIKE 'arcgis_lib_stg';\n"
],
"id": "ce110000-1111-2222-3333-ffffff000004"
},
{
"cell_type": "markdown",
"metadata": {
"name": "cell6"
},
"source": [
"## 3. 🌐 External Access Integrations\n",
"\n",
"Configure secure external access integrations to allow Snowflake to communicate with external services. This section sets up network rules, secrets, integrations, and utility functions for PyPI and ArcGIS services.\n",
"\n",
"### Integration Overview\n",
"\n",
"**ArcGIS Integration**: Enables secure communication with ArcGIS geocoding services\n",
"- Network access to ArcGIS Online/Portal endpoints\n",
"- Secure credential storage using Snowflake secrets\n",
"- Support for geocoding and address standardization services\n",
"\n",
"### Security Architecture\n",
"\n",
"1. **Network Rules**: Define allowed external endpoints and protocols\n",
"2. **Secrets**: Securely store authentication credentials\n",
"3. **External Access Integrations**: Combine network rules and secrets for controlled access\n",
"4. **Role-based Permissions**: Grant access only to authorized roles\n",
"\n",
"### 3.1 🗺️ ArcGIS External Access Integration\n",
"\n",
"Configure secure external access to ArcGIS geocoding services, including encrypted credential storage and network rules. This enables the geocoding workloads to access ArcGIS geocoding services.\n"
],
"id": "ce110000-1111-2222-3333-ffffff000005"
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"vscode": {
"languageId": "sql"
},
"name": "create_eai_arcgis",
"language": "sql"
},
"outputs": [],
"source": [
"-- NOTE: Please switch this to SQL cell.\n",
"\n",
"USE ROLE {{dev_role}};\n",
"\n",
"CREATE OR REPLACE SECRET arcgis_api_key\n",
" TYPE = PASSWORD\n",
" USERNAME = '{{arcgis_portal_user_name}}'\n",
" PASSWORD = '{{arcgis_portal_password}}'\n",
" COMMENT = 'API key used for connecting to ArcGIS Portal';\n",
"\n",
"GRANT USAGE ON SECRET arcgis_api_key\n",
" TO ROLE {{dev_role}};\n",
"\n",
"CREATE OR REPLACE NETWORK RULE nw_esri_api\n",
" MODE = EGRESS\n",
" TYPE = HOST_PORT\n",
" VALUE_LIST = ('*.arcgis.com', '{{arcgis_portal_url}}')\n",
" COMMENT = 'Network rule for ArcGIS API access';\n",
"\n",
"USE ROLE ACCOUNTADMIN;\n",
"\n",
"CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION eai_esri_api\n",
" ALLOWED_NETWORK_RULES = (nw_esri_api)\n",
" ALLOWED_AUTHENTICATION_SECRETS = (arcgis_api_key)\n",
" ENABLED = TRUE\n",
" COMMENT = 'External access integration for ArcGIS services';\n",
"\n",
"GRANT USAGE ON INTEGRATION eai_esri_api\n",
" TO ROLE {{dev_role}};\n",
"\n",
"USE ROLE {{dev_role}};\n"
],
"id": "ce110000-1111-2222-3333-ffffff000006"
},
{
"cell_type": "markdown",
"metadata": {
"name": "cell8"
},
"source": [
"## 4. 📊 Sample Data Loading and Preparation\n",
"\n",
"This section loads field technician data into Snowflake tables for batch geocoding processing. The demo uses field technician address data that needs to be geocoded.\n",
"\n",
"### 4.1 👷 Field Technician Data\n",
"\n",
"Contains address information for field service technicians that needs to be geocoded:\n",
"- **Technician ID**: Unique identifier for each technician\n",
"- **Address Information**: Street addresses that need to be converted to coordinates\n",
"- **Existing Coordinates**: Will be removed to demonstrate pure geocoding from addresses\n",
"\n",
"**Note**: The original data contains longitude and latitude columns, but these will be removed to demonstrate geocoding from addresses only.\n"
],
"id": "ce110000-1111-2222-3333-ffffff000007"
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"name": "load_data_field_technician",
"language": "sql"
},
"outputs": [],
"source": "drop table field_technician;\n\nCREATE OR REPLACE TRANSIENT TABLE field_technician (\n FIELD_TECHNICIAN_ID VARCHAR(50),\n FIELD_TECHNICIAN_ADDRESS VARCHAR(200)\n);\n\nINSERT INTO field_technician VALUES\n('a70b', '1030 Norwood Park Austin TX 78753'),\n('8f2d','1200 Barbara Jordan Austin TX 78723'),\n('b14c', '11301 Lakeline Austin TX 78613'),\n('8e47', '8000 Shoal Creek Austin TX 78757'),\n('848f', '5017 Highway 290 Austin TX 78749'),\n('9772', '9700 Capital Of Texas Austin TX 78759'),\n('94f8', '2525 Anderson Austin TX 78757'),\n('b280', '10900 Lakeline Mall Austin TX 78613'),\n('a08e', '5017 Highway 290 Austin TX 78749'),\n('a74e', '9300 Interstate 35 Austin TX 78748')\n;\n",
"id": "ce110000-1111-2222-3333-ffffff000008"
},
{
"cell_type": "markdown",
"metadata": {
"name": "cell10",
"collapsed": false
},
"source": "## 5. 🔧 Core Implementation: Batch Geocoding Stored Procedure\n\nThis section contains the complete implementation of batch geocoding using ArcGIS Geocoding Services within a Snowflake stored procedure.\n\n### 5.1 📋 Implementation Overview\n\nThe stored procedure implements a comprehensive batch geocoding workflow:\n\n1. **Authentication & Connection**: Secure connection to ArcGIS services using stored credentials\n2. **Data Loading**: Retrieval of address data from Snowflake tables\n3. **Batch Geocoding**: ArcGIS geocoding services for address-to-coordinate conversion\n4. **Table Updates**: Overwrite input table (to keep demo simple)\n\n### 5.2 🏗️ Architecture Components\n\n#### ArcGISProcessorBase Class\n- **Purpose**: Base class providing common ArcGIS connectivity functionality\n- **Features**: Credential management, secure authentication, connection handling\n- **Security**: Leverages Snowflake's internal secret management APIs\n\n#### BatchGeocodingProcessor Class\n- **Purpose**: Specialized processor for batch geocoding operations\n- **Capabilities**: \n - Batch address processing and geocoding\n \n### 5.3 🔍 Key Technical Features\n\n#### Batch Geocoding Implementation\n- **Algorithm**: ArcGIS `batch_geocode()` geocoding service\n- **Address Processing**: Handles various address formats and standards\n- **Quality Control**: Match scores and accuracy assessment\n- **Scalability**: Processes multiple addresses efficiently in batches\n\n#### Data Processing Pipeline\n- **Input**: Snowflake tables with address information\n- **Processing**: Batch geocoding with quality validation\n- **Output**: Enhanced tables with coordinates and geocoding metadata",
"id": "ce110000-1111-2222-3333-ffffff000009"
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"name": "define_spatial_processing_functionality",
"language": "python"
},
"outputs": [],
"source": "\"\"\"\nBatch Geocoding Demo: ArcGIS Geocoding Services Integration\n\nThis script demonstrates the implementation of ArcGIS Geocoding Services for batch address\nprocessing within Snowflake. It converts street addresses to geographic coordinates using\nArcGIS Online geocoding services.\n\nOverview:\n---------\nThe script implements a complete workflow for batch geocoding:\n1. Loads address data from Snowflake tables\n2. Uses ArcGIS Geocoding Services to convert addresses to coordinates\n3. Processes geocoding results with quality assessment\n4. Rewrite original tables with geocoded coordinates.\n\nReference Documentation:\n------------------------\nArcGIS Batch Geocoding Guide:\nhttps://developers.arcgis.com/python/latest/guide/part4-batch-geocoding/\n\"\"\"\n\nimport sys\nfrom datetime import datetime\nimport pandas as pd\nimport json\nimport datetime as dt\n\nclass ArcGISProcessorBase:\n \"\"\"\n A base class, with common functionality, this will be used by all the classes that \n will be used to demonstrate the capabilities of interfacing with ArcGIS Online geocoding services.\n \"\"\"\n \n def __init__(self ,p_session: Session ,p_arcgis_portal: str):\n # Initialize instance variables\n self.portal_url = p_arcgis_portal\n self.session = p_session\n self.credentials = {}\n self.gis = None\n \n def get_arcgis_credentials(self):\n \"\"\"Get ArcGIS credentials from stored secrets\"\"\"\n print('Getting ArcGIS credentials from Snowflake secrets...')\n\n import _snowflake\n \n try:\n username_password_object = _snowflake.get_username_password('esri_api_key');\n self.credentials[\"Username\"] = username_password_object.username\n self.credentials[\"Password\"] = username_password_object.password\n print(\"Successfully retrieved credentials from secrets\")\n except Exception as e:\n print(f\"Failed to get credentials from secrets: {e}\")\n raise Exception(f\"Failed to get credentials from secrets: {e}\")\n \n return self.credentials\n \n def connect_to_arcgis(self):\n from arcgis.gis import GIS\n \n \"\"\"Connect to ArcGIS Online/Portal\"\"\"\n user_name = self.credentials['Username']\n user_pass = self.credentials['Password']\n print(f\"Connecting to ArcGIS at {self.portal_url}...\")\n print(f\"Using username: {user_name}\")\n \n try:\n self.gis = GIS(url=self.portal_url, username=user_name, password=user_pass)\n print(f\"Successfully connected to {self.portal_url}\")\n print(f\"Logged in to ArcGIS as {user_name}\")\n return self.gis\n except Exception as e:\n print(f\"ArcGIS login failed: {e}\")\n print(f\"Login failed: {e}\")\n print(\"Please check your credentials and URL\")\n self.gis = None\n return self.gis\n \nclass BatchGeocodingProcessor(ArcGISProcessorBase):\n \"\"\"\n A class to handle batch geocoding processing with comprehensive logging and quality assessment.\n \"\"\"\n \n def __init__(self,p_session: Session,p_arcgis_portal: str):\n super().__init__(p_session,p_arcgis_portal)\n print(\"BatchGeocodingProcessor initialized\")\n \n def load_address_data(self, table_name='field_technician'):\n \"\"\"Load address data from Snowflake for geocoding\"\"\"\n print(f\"Starting to load address data from table: {table_name}...\")\n \n try:\n df = self.session.table(table_name).to_pandas()\n print(f\"Successfully loaded {len(df)} records for geocoding\")\n \n # Verify required columns exist\n if 'FIELD_TECHNICIAN_ADDRESS' not in df.columns:\n raise ValueError(\"Required column 'FIELD_TECHNICIAN_ADDRESS' not found in data\")\n \n return df\n \n except Exception as e:\n print(f\"Error loading address data: {e}\")\n raise\n \n def perform_batch_geocoding(self, df):\n \"\"\"Perform batch geocoding using ArcGIS services\"\"\"\n from arcgis.geocoding import batch_geocode\n \n print(\"Starting batch geocoding process...\")\n print(f\"Processing {len(df)} addresses...\")\n \n try:\n # Prepare addresses for geocoding\n addresses = df['FIELD_TECHNICIAN_ADDRESS'].tolist()\n \n # Perform batch geocoding\n print(\"Calling ArcGIS batch geocoding service...\")\n geocoding_results = batch_geocode(\n addresses=addresses,\n as_featureset=False # Return as list of dictionaries\n )\n \n print(f\"✅ Batch geocoding completed successfully!\")\n print(f\"Processed {len(geocoding_results)} addresses\")\n \n return geocoding_results\n \n except Exception as e:\n print(f\"❌ Error in batch geocoding: {e}\")\n raise\n \n def process_geocoding_results(self, df, geocoding_results):\n \"\"\"Process and validate geocoding results\"\"\"\n print(\"Processing geocoding results...\")\n \n try:\n # Create results dataframe\n results_data = []\n \n for i, result in enumerate(geocoding_results):\n original_address = df.iloc[i]['FIELD_TECHNICIAN_ADDRESS']\n technician_id = df.iloc[i]['FIELD_TECHNICIAN_ID']\n \n if result and 'location' in result:\n location = result['location']\n attributes = result.get('attributes', {})\n \n results_data.append({\n 'FIELD_TECHNICIAN_ID': technician_id,\n 'ORIGINAL_ADDRESS': original_address,\n 'GEOCODED_LONGITUDE': location.get('x'),\n 'GEOCODED_LATITUDE': location.get('y'),\n 'ADDRESS_TYPE': attributes.get('Addr_type', ''),\n 'GEOCODING_STATUS': 'SUCCESS'\n })\n else:\n # Failed geocoding\n results_data.append({\n 'FIELD_TECHNICIAN_ID': technician_id,\n 'ORIGINAL_ADDRESS': original_address,\n 'GEOCODED_LONGITUDE': None,\n 'GEOCODED_LATITUDE': None,\n 'ADDRESS_TYPE': '',\n 'GEOCODING_STATUS': 'FAILED'\n })\n \n results_df = pd.DataFrame(results_data)\n return results_df\n \n except Exception as e:\n print(f\"Error processing geocoding results: {e}\")\n raise\n \n def update_table_with_geocoded_results(self, results_df, table_name='field_technician_for_geocoding'):\n \"\"\"Update the original table with geocoded results using ALTER commands\"\"\"\n print(f\"Updating table {table_name} with geocoded results...\")\n\n # Keeping this demo simple; so just overwrite the input table\n try:\n self.session.write_pandas(\n results_df,\n table_name,\n quote_identifiers=False, # Use original column names\n auto_create_table=True, # Create table automatically\n overwrite=True, # Replace existing data\n table_type=\"transient\", # Transient table for demo\n )\n \n print(f\"✅ Successfully updated {table_name} with geocoded results\")\n \n return True\n \n except Exception as e:\n print(f\"Error updating table with geocoded results: {e}\")\n raise\n \n\ndef perform_batch_geocoding(p_session: Session, p_arcgis_portal: str):\n \"\"\"Main function to perform batch geocoding\"\"\"\n print(\"Starting Batch Geocoding Processing Application\")\n ret = {}\n \n # Create and use the BatchGeocodingProcessor class\n processor = BatchGeocodingProcessor(p_session, p_arcgis_portal)\n \n try:\n # Get credentials and connect to ArcGIS\n processor.get_arcgis_credentials()\n processor.connect_to_arcgis()\n ret['connected_to_arcgis'] = True\n \n # Load address data\n address_df = processor.load_address_data()\n ret['loaded_data'] = True\n \n # Perform batch geocoding\n geocoding_results = processor.perform_batch_geocoding(address_df)\n ret['performed_geocoding'] = True\n \n # Process results\n results_df = processor.process_geocoding_results(address_df, geocoding_results)\n ret['processed_results'] = True\n \n # Update table with results\n processor.update_table_with_geocoded_results(results_df)\n ret['updated_table'] = True\n \n print(\"Application completed successfully!\")\n ret['success'] = True\n \n except Exception as e:\n ret['success'] = False\n print(f\"Application failed: {e}\")\n raise\n\n print(\"Finished!!!\")\n return ret\n\n# -----------------------------\n# Register the stored procedure\n# Doc: https://docs.snowflake.com/en/developer-guide/snowpark/reference/python/1.29.0/snowpark/api/snowflake.snowpark.functions.sproc\n#\nsproc_batch_geocoding = sp_session.sproc.register(\n func = perform_batch_geocoding\n ,return_type = T.VariantType()\n ,name = 'batch_geocoding_sproc' \n ,replace = True, is_permanent=True, stage_location='@arcgis_lib_stg'\n ,packages=[\"arcgis\", \"pandas\",\"streamlit\"]\n ,external_access_integrations=[\"eai_esri_api\"]\n ,artifact_repository = 'snowflake.snowpark.pypi_shared_repository'\n ,secrets = {\n 'esri_api_key' : 'arcgis_api_key'\n }\n)\n",
"id": "ce110000-1111-2222-3333-ffffff000010"
},
{
"cell_type": "markdown",
"metadata": {
"name": "cell12",
"collapsed": false
},
"source": "## 6. 🚀 Execution: Running the Batch Geocoding\n\nThis section demonstrates how to invoke the stored procedure to perform batch geocoding. The execution includes data preparation, geocoding execution, and result verification.\n\n### 6.1 📤 Stored Procedure Invocation\n\nThe stored procedure call executes the complete workflow:\n- Loads address data from Snowflake tables\n- Performs batch geocoding using ArcGIS services\n- Processes geocoding results\n\n### 6.3 ⚡ Expected Processing Flow\n\n1. **Authentication**: Secure connection to ArcGIS geocoding services\n2. **Data Loading**: Retrieval of address data from Snowflake\n3. **Batch Geocoding**: Address-to-coordinate conversion\n4. **Table Overwrite**: Overwrite the input table with the additional columns of geocoding\n",
"id": "ce110000-1111-2222-3333-ffffff000011"
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"name": "invoke_spatial_processing_job",
"language": "sql"
},
"outputs": [],
"source": "call batch_geocoding_sproc('https://{{arcgis_portal_url}}/')",
"id": "ce110000-1111-2222-3333-ffffff000013"
},
{
"cell_type": "markdown",
"metadata": {
"name": "cell15",
"collapsed": false
},
"source": "## 7. 📊 Results Analysis and Data Exploration\n\nExplore the results generated by the batch geocoding job. The job updates the original table with geocoded coordinates and quality metrics.\n\n### Output Columns Added\n\nThe batch geocoding process adds the following columns to the original table:\n\n1. **`GEOCODED_LONGITUDE`**: Longitude coordinate from geocoding\n2. **`GEOCODED_LATITUDE`**: Latitude coordinate from geocoding \n3. **`ADDRESS_TYPE`**: Type of address match (e.g., PointAddress, StreetAddress)\n4. **`GEOCODING_STATUS`**: SUCCESS or FAILED status",
"id": "ce110000-1111-2222-3333-ffffff000014"
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"vscode": {
"languageId": "sql"
},
"name": "sample_1",
"language": "sql"
},
"outputs": [],
"source": "-- NOTE: Please switch this to SQL cell.\n\n-- ==========================================\n-- GEOCODED RESULTS OVERVIEW\n-- ==========================================\n\n-- Explore the geocoded results with all new columns\nSELECT *\nFROM field_technician_for_geocoding\n;\n",
"id": "ce110000-1111-2222-3333-ffffff000015"
},
{
"cell_type": "markdown",
"metadata": {
"name": "cell21",
"collapsed": false
},
"source": "---\n\n# 🎉 Conclusion\n\nThis demonstration showcases the powerful integration of ArcGIS Geocoding Services directly within Snowflake using stored procedures. The solution provides enterprise-grade batch geocoding capabilities while maintaining data security and operational efficiency.",
"id": "ce110000-1111-2222-3333-ffffff000020"
}
]
}
Display the source blob
Display the rendered blob
Raw
DEMO_FEATURE_LAYER_SPROC.ipynb
Loading
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
Display the source blob
Display the rendered blob
Raw
DEMO_FIND_CLOSEST_FACILITY_SPROC.ipynb
Loading
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment