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
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_FEATURE_LAYER_SPROC.ipynb
{
"metadata": {
"language_info": {
"name": "python"
},
"lastEditStatus": {
"notebookId": "5g47336i3cz6dc2dlw",
"lastEditTime": 1758994892215
}
},
"nbformat_minor": 2,
"nbformat": 4,
"cells": [
{
"cell_type": "markdown",
"id": "ce110000-1111-2222-3333-ffffff000000",
"metadata": {
"collapsed": false,
"name": "cell1"
},
"source": "# Working with ArcGIS FeatureLayer using Snowflake Stored Procedures\n\n## 🎯 Executive Summary\n\nThis notebook demonstrates how to leverage **Snowflake** stored procedures to run **ArcGIS Feature Layer operations** in a scalable, non-interactive manner. \n\nArcGIS Documentation: https://developers.arcgis.com/python/latest/guide/working-with-feature-layers-and-features/\n\n## 🎯 Use Case: Bidirectional Data Integration\n\n**Business Problem**: Organizations need seamless data integration between Snowflake data warehouses and ArcGIS Online feature services for spatial data sharing, visualization, and collaborative analysis.\n\n**Solution**: This notebook implements a bidirectional feature layer integration that:\n- 📊 Loads spatial data from Snowflake tables as spatial dataframes\n- 🌐 Publishes Snowflake data as feature layers to ArcGIS Online\n- 📥 Retrieves published feature layers back from ArcGIS Online\n- 💾 Stores retrieved feature layer data into new Snowflake tables\n- 🔄 Demonstrates complete round-trip data integration workflow\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 feature layer processing\n- Stage for storing Python artifacts and dependencies\n\n### ArcGIS Requirements\n- ArcGIS Online or Portal for ArcGIS account\n- Feature layer publishing permissions\n- Valid user credentials with content creation privileges\n\n---\n"
},
{
"cell_type": "markdown",
"id": "ce110000-1111-2222-3333-ffffff000001",
"metadata": {
"collapsed": false,
"name": "cell2"
},
"source": [
"## 1. 🔧 Environment Setup and Configuration\n",
"\n",
"This section initializes the Snowflake Snowpark session and configures the connection parameters for ArcGIS feature layer 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 dependency: `python-dotenv`\n",
"- Create a `.env` file and configure the following keys and values:\n",
"\n",
"```\n",
"# Session role\n",
"dev_role = 'public'\n",
"\n",
"# ArcGIS Portal configuration\n",
"arcgis_portal_url = 'ABCD.arcgis.com'\n",
"\n",
"# ArcGIS authentication credentials\n",
"arcgis_portal_user_name = '---'\n",
"arcgis_portal_password = '---'\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "ce110000-1111-2222-3333-ffffff000002",
"metadata": {
"language": "python",
"name": "initialization"
},
"outputs": [],
"source": [
"# ==========================================\n",
"# LIBRARY IMPORTS AND CONFIGURATION\n",
"# ==========================================\n",
"\n",
"# Core data processing and Snowflake libraries\n",
"import streamlit as st\n",
"import pandas as pd\n",
"import os\n",
"import snowflake.snowpark.types as T\n",
"import snowflake.snowpark.functions as F\n",
"from snowflake.snowpark import Session\n",
"\n",
"from pathlib import Path\n",
"# Check if the file exists\n",
"if 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\n",
"dev_role = os.getenv('dev_role','public')\n",
"\n",
"# ArcGIS Portal configuration\n",
"arcgis_portal_url = os.getenv('arcgis_portal_url','abc.maps.arcgis.com')\n",
"\n",
"# ArcGIS authentication credentials\n",
"arcgis_portal_user_name = os.getenv('arcgis_portal_user_name','john.doe')\n",
"arcgis_portal_password = os.getenv('arcgis_portal_password','blah-blah')\n",
"\n",
"print(f\"🔧 Configuration loaded:\")\n",
"print(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\n",
"sp_session = get_active_session()\n",
"sp_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"
]
},
{
"cell_type": "markdown",
"id": "ce110000-1111-2222-3333-ffffff000003",
"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"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "ce110000-1111-2222-3333-ffffff000004",
"metadata": {
"codeCollapsed": false,
"collapsed": false,
"language": "sql",
"name": "define_stage",
"vscode": {
"languageId": "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"
]
},
{
"cell_type": "markdown",
"id": "ce110000-1111-2222-3333-ffffff000005",
"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 feature layer services\n",
"- Network access to ArcGIS Online/Portal endpoints\n",
"- Secure credential storage using Snowflake secrets\n",
"- Support for feature layer publishing and retrieval operations\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 feature layer services, including encrypted credential storage and network rules. This enables the feature layer operations to access ArcGIS Online/Portal services.\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "ce110000-1111-2222-3333-ffffff000006",
"metadata": {
"codeCollapsed": true,
"collapsed": true,
"language": "sql",
"name": "create_eai_arcgis",
"vscode": {
"languageId": "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"
]
},
{
"cell_type": "markdown",
"id": "ce110000-1111-2222-3333-ffffff000007",
"metadata": {
"name": "cell8"
},
"source": [
"## 4. 📊 Sample Data Loading and Preparation\n",
"\n",
"This section loads field technician data into Snowflake tables for feature layer demonstration. The demo uses field technician data with existing coordinates for publishing as feature layers.\n",
"\n",
"### 4.1 👷 Field Technician Data\n",
"\n",
"Contains spatial information for field service technicians for feature layer operations:\n",
"- **Technician ID**: Unique identifier for each technician\n",
"- **Coordinates**: Longitude and latitude for spatial visualization\n",
"- **Address Information**: Street addresses for context and labeling\n",
"\n",
"**Note**: The data contains longitude and latitude columns that will be used to create spatial dataframes for feature layer publishing.\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "ce110000-1111-2222-3333-ffffff000008",
"metadata": {
"codeCollapsed": true,
"collapsed": true,
"language": "sql",
"name": "load_data_field_technician"
},
"outputs": [],
"source": [
"CREATE OR REPLACE TRANSIENT TABLE field_technician (\n",
" FIELD_TECHNICIAN_ID VARCHAR(50),\n",
" LONGITUDE FLOAT,\n",
" LATITUDE FLOAT,\n",
" FIELD_TECHNICIAN_ADDRESS VARCHAR(200)\n",
");\n",
"\n",
"INSERT INTO field_technician VALUES\n",
"('a70b', -97.6911692, 30.3391586, '1030 Norwood Park Austin TX 78753'),\n",
"('8f2d', -97.7092389, 30.3073893, '1200 Barbara Jordan Austin TX 78723'),\n",
"('b14c', -97.7993718, 30.4794069, '11301 Lakeline Austin TX 78613'),\n",
"('8e47', -97.7409877, 30.3647362, '8000 Shoal Creek Austin TX 78757'),\n",
"('848f', -97.8239203, 30.232782, '5017 Highway 290 Austin TX 78749'),\n",
"('9772', -97.742009, 30.3916, '9700 Capital Of Texas Austin TX 78759'),\n",
"('94f8', -97.7335442, 30.3554777, '2525 Anderson Austin TX 78757'),\n",
"('b280', -97.7965281, 30.4760066, '10900 Lakeline Mall Austin TX 78613'),\n",
"('a08e', -97.8239203, 30.232782, '5017 Highway 290 Austin TX 78749'),\n",
"('a74e', -97.7919894, 30.1659125, '9300 Interstate 35 Austin TX 78748')\n",
";\n"
]
},
{
"cell_type": "markdown",
"metadata": {
"name": "cell3",
"collapsed": false
},
"source": "## 5. 🐍 Python Implementation: Feature Layer Processing Classes\n\nThis section contains the core Python implementation for ArcGIS feature layer operations within Snowflake stored procedures. \n\n### 5.1 📋 Architecture Overview\n\nThe implementation follows a layered architecture:\n\n- **Base Class (`ArcGISProcessorBase`)**: Provides common functionality for ArcGIS authentication and connection management\n- **Feature Layer Processor (`FeatureLayerProcessor`)**: Specialized class for feature layer operations\n- **Main Function (`perform_feature_layer_demo`)**: Orchestrates the complete workflow\n- **Stored Procedure Registration**: Configures the function as a Snowflake stored procedure\n\n### 5.2 🔐 ArcGISProcessorBase Class\n\n**Purpose**: Base class providing common ArcGIS connectivity functionality\n\n**Key Methods**:\n- `get_arcgis_credentials()`: Retrieves ArcGIS credentials from Snowflake secrets\n- `connect_to_arcgis()`: Establishes secure connection to ArcGIS Online/Portal\n\n**Security Features**:\n- Uses Snowflake's secure secret management (`_snowflake.get_username_password()`)\n- Implements error handling for authentication failures\n- Supports both ArcGIS Online and Portal for ArcGIS\n\n### 5.3 🗺️ FeatureLayerProcessor Class\n\n**Purpose**: Specialized processor for bidirectional feature layer operations\n\n**Core Capabilities**:\n\n#### Data Loading (`load_data_as_spatial_dataframe()`)\n- Loads data from Snowflake `field_technician` table\n- Converts to pandas DataFrame with spatial geometry\n- Creates spatial dataframes using ArcGIS Python API\n- Handles coordinate system transformations\n\n#### Feature Layer Publishing (`publish_technicians_as_feature_layer()`)\n- Publishes spatial dataframes as ArcGIS feature layers\n- Generates unique layer names with timestamps\n- Configures layer metadata (title, tags, description)\n- Sets appropriate spatial extents for visualization\n- Returns published feature layer object with URL\n\n#### Feature Layer Retrieval (`retrieve_feature_layer_and_save_as_snowflake_table()`)\n- Retrieves published feature layers from ArcGIS Online\n- Converts feature layer data back to pandas DataFrames\n- Saves retrieved data to new Snowflake tables\n- Maintains data integrity throughout the round-trip process\n\n### 5.4 🚀 Main Workflow Function\n\n**Function**: `perform_feature_layer_demo(p_session, p_arcgis_portal)`\n\n**Workflow Steps**:\n1. **Initialize Processor**: Create FeatureLayerProcessor instance\n2. **Authenticate**: Retrieve credentials and connect to ArcGIS\n3. **Load Data**: Convert Snowflake data to spatial dataframes\n4. **Publish**: Upload spatial data as feature layers to ArcGIS Online\n5. **Retrieve**: Download published feature layers back to Snowflake\n6. **Return Results**: Provide comprehensive status and metadata\n\n**Return Value**: Dictionary containing:\n- `success`: Boolean indicating overall success\n- `connected_to_arcgis`: Authentication status\n- `loaded_data`: Data loading status\n- `feature_layer_uploaded`: Publishing status\n- `feature_layer_url`: URL of published feature layer\n- `feature_layer_downloaded`: Retrieval status\n- `downloaded_table_name`: Name of created Snowflake table\n\n### 5.5 ⚙️ Stored Procedure Configuration\n\n**Registration Parameters**:\n- **Function**: `perform_feature_layer_demo`\n- **Name**: `feature_layer_demo_sproc`\n- **Return Type**: `VariantType()` for flexible JSON responses\n- **Packages**: `[\"arcgis\", \"pyshp\", \"pandas\", \"streamlit\"]`\n- **External Access**: `[\"pypi_eai\", \"eai_esri_api\"]` for PyPI and ArcGIS access\n- **Secrets**: `arcgis_api_key` for secure credential access\n- **Storage**: `@arcgis_lib_stg` stage for artifacts\n\n**Key Features**:\n- Permanent stored procedure for reusability\n- Secure external access integrations\n- Comprehensive package dependencies\n- Artifact repository for shared libraries\n",
"id": "ce110000-1111-2222-3333-ffffff000009"
},
{
"cell_type": "code",
"execution_count": null,
"id": "ce110000-1111-2222-3333-ffffff000010",
"metadata": {
"language": "python",
"name": "define_spatial_processing_functionality"
},
"outputs": [],
"source": "\"\"\"\nThis script demonstrates varies capabilities of interfacing with feature layer of ArcGIS Online.\nhttps://developers.arcgis.com/python/latest/guide/working-with-feature-layers-and-features/\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 feature layer 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 FeatureLayerProcessor(ArcGISProcessorBase):\n \"\"\"\n A class to demonstrate interfacing with feature layer of ArcGIS Online.\n \"\"\"\n \n def __init__(self,p_session: Session,p_arcgis_portal: str):\n super().__init__(p_session,p_arcgis_portal)\n print(\"FeatureLayerProcessor initialized\")\n \n def load_data_as_spatial_dataframe(self):\n \"\"\"Load sample data from Snowflake as spatial dataframe\"\"\"\n print(\"Starting to load field_technician data from Snowflake...\")\n \n try:\n df = self.session.table('field_technician').to_pandas()\n df['ID'] = df['FIELD_TECHNICIAN_ID']\n df['NAME'] = df['FIELD_TECHNICIAN_ID']\n \n # Convert to spatial dataframe\n # Ref doc: https://developers.arcgis.com/python/latest/guide/introduction-to-the-spatially-enabled-dataframe/\n field_technician_sdf = pd.DataFrame.spatial.from_xy(\n df, \n x_column='longitude'.upper(), \n y_column='latitude'.upper()\n )\n \n print(f\"Created spatial dataframe with {len(field_technician_sdf)} technicians\")\n except Exception as e:\n print(f\"Error loading technician data: {e}\")\n raise\n return field_technician_sdf\n \n def publish_technicians_as_feature_layer(self, field_technician_sdf):\n \"\"\"Publish technicians layer\"\"\"\n print(\"Publishing technicians as featurelayer ...\")\n\n # Ref doc: https://developers.arcgis.com/python/latest/guide/introduction-to-the-spatially-enabled-dataframe/#saving-spatially-enabled-dataframes\n \n # suffix the feature layer name with the current date and time'\n feature_layer_name = f\"Field_Technicians_Demo_{datetime.now().strftime('%Y%m%d_%H%M%S')}\"\n feature_layer = None\n try:\n # Convert to feature layer\n # Ref: https://developers.arcgis.com/python/latest/api-reference/arcgis.features.toc.html?highlight=to_featurelayer#arcgis.features.GeoAccessor.to_featurelayer\n feature_layer = field_technician_sdf.spatial.to_featurelayer(\n gis=self.gis,\n title= feature_layer_name,\n tags=[\"Snowflake_demo\"],\n description=f\"Demonstration of publishing a Snowflake table as a feature layer\",\n sanitize_columns=True, # For handling scenarios where column name size > 10\n )\n\n # ----- Optional: Set the extent of the feature layer -----\n # set the center of the feature layer to the first point\n # Define a new, small extent around the first point\n # Adjust the buffer (0.001) as needed for your data's scale\n first_point_geom = field_technician_sdf.iloc[0].SHAPE\n x, y = first_point_geom.x, first_point_geom.y\n extent_array = [x - 0.001, y - 0.001, x + 0.001, y + 0.001]\n feature_layer.update({'extent': extent_array})\n\n print(f\"✅ Successfully published {feature_layer_name} as feature layer\")\n print(f\" Layer ID: {feature_layer.id}\")\n print(f\" URL: {feature_layer.url}\")\n return feature_layer\n \n except Exception as e:\n print(f\"❌ Failed to publish {feature_layer_name}: {e}\")\n raise\n\n return feature_layer\n\n def retrieve_feature_layer_and_save_as_snowflake_table(self, feature_layer_url):\n \"\"\"Retrieve a feature layer and save it as a Snowflake table\"\"\"\n from arcgis.features import FeatureLayer\n\n print(f\"Retrieving feature layer {feature_layer_url} and saving it as a Snowflake table...\")\n\n # Create FeatureLayer object directly from URL\n # Note: We need to append '/0' to get the first layer, or use the full layer URL\n feature_layer_direct = FeatureLayer(feature_layer_url + '/0', gis=self.gis)\n\n print(f\"✅ Feature layer created directly from URL\")\n print(f\"Layer properties: {feature_layer_direct.properties.name}\")\n\n # Convert the feature layer to a pandas dataframe using direct URL approach\n print(\"Querying feature layer directly from URL and converting to pandas DataFrame...\")\n\n try:\n # Query the feature layer directly - much cleaner approach!\n # No need for .layers[0] since we're working directly with the FeatureLayer\n feature_layer_df = feature_layer_direct.query().df\n \n print(f\"✅ Feature layer converted to pandas DataFrame successfully\")\n print(f\"📊 DataFrame shape: {feature_layer_df.shape}\")\n print(f\"📋 Columns: {list(feature_layer_df.columns)}\")\n except Exception as e:\n print(f\"❌ Error querying feature layer: {e}\")\n print(\"This might happen if:\")\n print(\"- The feature layer URL is incorrect\")\n print(\"- You don't have permission to access the layer\")\n print(\"- The layer is empty or has no queryable features\")\n raise\n\n # save the pandas dataframe to a Snowflake table\n table_name = \"field_technician_sourced_from_feature_layer\"\n try:\n print(f\"Writing pandas dataframe to Snowflake table {table_name}...\")\n _spdf = self.session.write_pandas(\n feature_layer_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 print(\" ✅ Data successfully written to Snowflake\")\n \n # Verify table creation\n row_count = self.session.table(table_name).count()\n print(f\" 📊 Verified: {row_count} records in '{table_name}' table\")\n \n except Exception as e:\n print(f\" ❌ Error writing to Snowflake: {str(e)}\")\n raise\n return table_name\n \n\ndef perform_feature_layer_demo(p_session: Session, p_arcgis_portal: str):\n \"\"\"Main function to perform feature layer demo operations\"\"\"\n print(\"Starting Feature Layer Processing Application\")\n ret = {}\n \n # Create and use the FeatureLayerProcessor class\n processor = FeatureLayerProcessor(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 the data\n field_technician_sdf = processor.load_data_as_spatial_dataframe()\n ret['loaded_data'] = True\n\n # Publish the data as a feature layer to ArcGIS Online\n feature_layer = processor.publish_technicians_as_feature_layer(field_technician_sdf)\n ret['feature_layer_uploaded'] = True\n ret['feature_layer_url'] = feature_layer.url \n \n\n # Retrieve the feature layer and save it as a Snowflake table\n downloaded_table_name = processor.retrieve_feature_layer_and_save_as_snowflake_table(feature_layer.url)\n ret['feature_layer_downloaded'] = True\n ret['downloaded_table_name'] = downloaded_table_name\n \n print(\"Application completed successfully!\")\n ret['success'] = True\n \n except Exception as e:\n ret['success'] = False\n ret['error'] = f\"Application failed: {e}\"\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_feature_layer_demo = sp_session.sproc.register(\n func = perform_feature_layer_demo\n ,return_type = T.VariantType()\n ,name = 'feature_layer_demo_sproc' \n ,replace = True, is_permanent=True, stage_location='@arcgis_lib_stg'\n ,packages=[\"arcgis\", \"pyshp\" ,\"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"
},
{
"cell_type": "markdown",
"id": "ce110000-1111-2222-3333-ffffff000011",
"metadata": {
"collapsed": false,
"name": "cell12"
},
"source": [
"## 6. 🚀 Execution: Running the Feature Layer Demo\n",
"\n",
"This section demonstrates how to invoke the stored procedure to perform feature layer operations. The execution includes data preparation, feature layer publishing, and retrieval workflow.\n",
"\n",
"### 6.1 📤 Stored Procedure Invocation\n",
"\n",
"The stored procedure call executes the complete workflow:\n",
"- Loads spatial data from Snowflake tables\n",
"- Publishes data as feature layers to ArcGIS Online\n",
"- Retrieves published feature layers back to Snowflake\n",
"\n",
"### 6.2 ⚡ Expected Processing Flow\n",
"\n",
"1. **Authentication**: Secure connection to ArcGIS Online/Portal services\n",
"2. **Data Loading**: Retrieval of spatial data from Snowflake as spatial dataframes\n",
"3. **Feature Layer Publishing**: Upload spatial data as feature layers to ArcGIS Online\n",
"4. **Feature Layer Retrieval**: Download published feature layers back to new Snowflake tables\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "ce110000-1111-2222-3333-ffffff000013",
"metadata": {
"language": "sql",
"name": "invoke_spatial_processing_job"
},
"outputs": [],
"source": [
"call feature_layer_demo_sproc('https://{{arcgis_portal_url}}/')"
]
},
{
"cell_type": "markdown",
"id": "ce110000-1111-2222-3333-ffffff000014",
"metadata": {
"collapsed": false,
"name": "cell15"
},
"source": "## 7. 📊 Results Analysis and Data Exploration\n\nExplore the results generated by the feature layer demo. \n\n### Output Table Structure\n\nThe feature layer retrieval process creates a new table `field_technician_sourced_from_feature_layer` with the following structure:\n\n1. **Original Data Columns**: All original columns from the source data\n2. **Spatial Geometry**: SHAPE column containing spatial geometry information\n3. **Feature Layer Metadata**: Additional columns added by ArcGIS feature layer processing\n4. **Object IDs**: System-generated identifiers from the feature layer"
},
{
"cell_type": "code",
"execution_count": null,
"id": "ce110000-1111-2222-3333-ffffff000015",
"metadata": {
"language": "sql",
"name": "sample_1",
"vscode": {
"languageId": "sql"
}
},
"outputs": [],
"source": [
"-- NOTE: Please switch this to SQL cell.\n",
"\n",
"SELECT *\n",
"FROM field_technician_sourced_from_feature_layer\n",
";\n"
]
},
{
"cell_type": "markdown",
"id": "ce110000-1111-2222-3333-ffffff000020",
"metadata": {
"collapsed": false,
"name": "cell21"
},
"source": [
"---\n",
"\n",
"# 🎉 Conclusion\n",
"\n",
"This demonstration showcases the powerful integration of ArcGIS Feature Layer Services directly within Snowflake using stored procedures. The solution provides enterprise-grade bidirectional data integration capabilities between Snowflake and ArcGIS Online, enabling seamless spatial data sharing while maintaining data security and operational efficiency.\n",
"\n",
"## Key Achievements\n",
"\n",
"✅ **Bidirectional Integration**: Successfully demonstrated complete round-trip data flow between Snowflake and ArcGIS Online \n",
"✅ **Spatial Data Publishing**: Published Snowflake spatial data as feature layers to ArcGIS Online \n",
"✅ **Feature Layer Retrieval**: Retrieved published feature layers back to Snowflake tables \n",
"✅ **Secure Operations**: Implemented secure external access integrations for ArcGIS services \n",
"✅ **Scalable Architecture**: Leveraged Snowflake stored procedures for enterprise-scale processing"
]
}
]
}
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