Skip to content

Instantly share code, notes, and snippets.

@sfc-gh-vsekar

sfc-gh-vsekar/arcgis_geocoding.ipynb

Last active March 13, 2025 02:42
Show Gist options
  • Save sfc-gh-vsekar/672474c5686b3b80181ddcf534fe6a3c to your computer and use it in GitHub Desktop.
Save sfc-gh-vsekar/672474c5686b3b80181ddcf534fe6a3c to your computer and use it in GitHub Desktop.
Download ZIP
Geocoding using ArcGis location services in Snowflake
Display the source blob
Display the rendered blob
Raw
arcgis_geocoding.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Geocoding using ArcGis API in Snowflake\n",
"\n",
"**Dated: March-2025**\n",
"\n",
"Gecoding is the process of converting addresses (like \"1600 Amphitheatre Parkway, Mountain View, CA\") into geographic coordinates (like latitude 37.423021 and longitude -122.083739), which you can use to place markers on a map, or position the map.\n",
"\n",
"The [ArcGis Python](https://developers.arcgis.com/python/latest/) is one of the SDK that is used to interact with ArcGis platform, However this\n",
"is currently not available in the [Snowflake Anaconda Channel](\n",
"https://repo.anaconda.com/pkgs/snowflake/). Also installing the library using PyPI integration is not possible in Snowflake, as there are many dependency\n",
"issues that are not supported in Snowflake. While the SDK offers a simpler approach to various GIS functionality, we can still interact with the ArcGis Mapping and Location services via its REST API. The REST API can be used to perform various GIS operations like geocoding, reverse geocoding, routing, etc. \n",
"\n",
"In this notebook, we will see how to use the ArcGis REST API to geocode addresses in Snowflake. This allows directly invoking the API using Snowflake Snowpark functionality, without any need to data export/import to/from Snowflake.\n",
"\n",
"### Prerequisites\n",
"- To connect to the ArcGis REST API, you have an ArcGis account and also have created an API key with Location & GeoCoding privileges. \n",
"- The notebook establishes [Snowflake session using .toml file](https://docs.snowflake.com/en/developer-guide/snowpark/python/creating-session#connect-by-using-the-connections-toml-file). \n",
"- You have ability to create the following in Snowflake:\n",
" - [Network rule](https://docs.snowflake.com/en/sql-reference/sql/create-network-rule)\n",
" - [Secret](https://docs.snowflake.com/en/sql-reference/sql/create-secret) \n",
" - [External Access Integration](https://docs.snowflake.com/en/developer-guide/external-network-access/creating-using-external-network-access)\n",
" \n",
"#### Python Libraries\n",
"- snowflake-snowpark-python\n",
"\n",
"---\n",
"### 1. Create External Access Integration to ArcGis REST API\n",
"In this step we first create an External Access Integration in Snowflake to connect to the ArcGis REST API. This will be used by\n",
"the UDF, which will invoke the GeoCoding service."
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"/Users/vsekar/Dev/rough/snowflake_osmnx/.venv/lib/python3.11/site-packages/snowflake/connector/options.py:108: UserWarning: You have an incompatible version of 'pyarrow' installed (19.0.0), please install a version that adheres to: 'pyarrow<19.0.0; extra == \"pandas\"'\n",
" warn_incompatible_dep(\n"
]
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"-----------------------------------------------------------------------------------------------------------\n",
"|\"CURRENT_DATABASE()\" |\"CURRENT_SCHEMA()\" |\"CURRENT_ROLE()\" |\"CURRENT_WAREHOUSE()\" |\"CURRENT_USER()\" |\n",
"-----------------------------------------------------------------------------------------------------------\n",
"|VENKAT_DB |PUBLIC |VENKAT_APP_DEV |VENKAT_COMPUTE_WH |VSEKAR |\n",
"-----------------------------------------------------------------------------------------------------------\n",
"\n"
]
}
],
"source": [
"# Initialization & establish Snowpark session\n",
"\n",
"import pandas as pd\n",
"import snowflake.snowpark.functions as F\n",
"import snowflake.snowpark.types as T\n",
"from snowflake.snowpark.session import Session\n",
"\n",
"# Establish Snowflake Session, with connection information from config.toml\n",
"# Ref: https://docs.snowflake.com/en/developer-guide/snowpark/python/creating-session#connect-by-using-the-connections-toml-file\n",
"sp_session = Session.builder.config(\"connection_name\", 'polaris2').create()\n",
"\n",
"# Set up the role, warehouse and database schema context\n",
"sp_session.use_role('venkat_app_dev')\n",
"sp_session.use_database('venkat_db')\n",
"sp_session.use_schema('public')\n",
"sp_session.use_warehouse('venkat_compute_wh')\n",
"\n",
"spdf = sp_session.sql('select current_database() ,current_schema() ,current_role() ,current_warehouse() ,current_user()')\n",
"spdf.show()"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [],
"source": [
"# Create secret for holding ArcGis API Key\n",
"\n",
"#Ref: https://docs.snowflake.com/en/sql-reference/sql/create-secret\n",
"\n",
"# I typically create secrets and other cross usage objects that are used across multiple demo assets\n",
"# in a common database/schema (i.e. account_common_db.public). You would need to change this as\n",
"# appropriate for your environment.\n",
"sql_stmts = [\n",
" 'use role accountadmin'\n",
" ,'use schema account_common_db.public'\n",
" ,'''create or replace secret arcgis_api_key\n",
" type = generic_string\n",
" secret_string = '<< API_KEY >>'\n",
" comment = 'api key used for connecting to arcgis rest api endpoint. profile at: __fillin_arcgis_url__'\n",
" '''\n",
" ,''' grant usage on secret arcgis_api_key\n",
" to role venkat_app_dev\n",
" '''\n",
" ,' use role venkat_app_dev '\n",
"]\n",
"for sql_stmt in sql_stmts:\n",
" sp_session.sql(sql_stmt).collect()"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [],
"source": [
"# Create network rule and EAI to communicate with ArcGIS API\n",
"\n",
"sql_stmts = [\n",
"'use role accountadmin'\n",
",'use schema account_common_db.public'\n",
",'''create or replace network rule account_common_db.public.nw_arcgis_api\n",
" mode = egress\n",
" type = host_port\n",
" value_list = ('*.arcgis.com')\n",
" comment = 'Used for ESRI arcgis needs' '''\n",
",''' create or replace external access integration eai_arcgis_api\n",
" allowed_network_rules = (nw_arcgis_api)\n",
" allowed_authentication_secrets = (arcgis_api_key)\n",
" enabled = true\n",
" comment = 'Used for ESRI arcgis needs' '''\n",
",'''grant usage on integration eai_arcgis_api\n",
" to role venkat_app_dev;\n",
"'''\n",
",' use role venkat_app_dev '\n",
"]\n",
"for sql_stmt in sql_stmts:\n",
" sp_session.sql(sql_stmt).collect()\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### 2. Create a UDF to invoke the ArcGis REST API\n",
"In this step we create a Snowpark Vectorized UDF that will invoke the ArcGis REST API to geocode addresses. The UDF will take an address as input and return the full response from the ArcGis API. To help with batching and parallelization, the implementation is based of Vectorized UDF.\n",
"\n",
"From ArcGis perspective, Since the UDF can potentially be called across multiple rows, we are using the [/geocodeAddresses](https://developers.arcgis.com/rest/geocode/geocode-addresses/) endpoint. The api response, will not necessarily return the geocoded addresses in the same order as the input addresses. To overcome this,\n",
"the UDF will add an attribute 'ObjectID' to each input address, which will be returned in the response. This ObjectID can then be used to join the response back to the original input data. The ObjectID has to be an integer otherwise it will not work.\n",
"\n",
"#### doc links:\n",
"- https://developers.arcgis.com/rest/geocode/geocode-addresses/\n",
"- https://developers.arcgis.com/documentation/mapping-and-location-services/geocoding/batch-geocoding/#what-is-batch-geocoding\n",
"\n",
"**NOTE**: \n",
"- The UDF implementation is a simple one and does not handle all parameterization / possible error cases. You would want to enhance it to handle more error cases, retries, etc. This implementation is just to show case the possibility.\n",
"- Do understand the service limits and the cost associated with invoking the API service.\n",
"- To handle very large volumes, you probably have to batch and throttle the requests.\n",
"- The number of input address also matters, as the REST API has size limits on the parameter.\n",
"- For production implementations, would recommend to register the UTF as a permanent function."
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [],
"source": [
"# Define the Geocoding UDF\n",
"\n",
"import requests\n",
"import pandas as pd\n",
"\n",
"def _invoke_geocode_addresses_api(access_token ,addresses_records):\n",
"\t'''\n",
"\tThis function invokes the geocodeAddresses API'''\n",
"\t_api_url = 'https://geocode.arcgis.com/arcgis/rest/services/World/GeocodeServer/geocodeAddresses'\n",
"\t_addresses = {\n",
"\t\t\t\"records\" : addresses_records\n",
"\t}\n",
"\t_api_url = f'{_api_url}?f=json&token={access_token}&addresses={_addresses}'\n",
"\t_response = requests.get(_api_url)\n",
"\treturn _response\n",
" \n",
"def _format_for_request(df):\n",
"\t'''\n",
"\tThis function formats the input dataframe as per the geocodeAddresses API spec\n",
"\thttps://developers.arcgis.com/rest/geocode/geocode-addresses/#addresses\n",
"\t'''\n",
"\t# NOTE: Ensure Objectid is an integer, preferable sequence. otherwise the responses becomes invalid\n",
"\t_records = []\n",
"\t# df.columns = ['objectid','address']\n",
"\tfor idx ,row in df.iterrows():\n",
"\t\t_r = {\n",
"\t\t\t\"attributes\": {\n",
"\t\t\t\t\t\"objectid\": idx #row['objectid']\n",
"\t\t\t\t\t,\"singleline\": row['address']\n",
"\t\t\t}}\n",
"\t\t_records.extend([_r])\n",
"\treturn _records\n",
"\n",
"def _reorder_response_matching_input(df ,response_payload):\n",
"\t'''\n",
"\tThis function reformats the response from the geocodeAddresses API and returns \n",
"\tthe list of geocoded values.'''\n",
"\n",
"\t# The order of the response is not guaranteed to match the input order. So\n",
"\t# we first build a Map of objectid to the response and then iterate over the\n",
"\t# input dataframe to build the response\n",
"\n",
"\t# Build a dictory that is based of objectid as index and its corresponding geocoded value \n",
"\t_rmap = {\n",
"\t\tx['attributes']['ResultID'] : x\n",
"\t\tfor x in response_payload['locations']\n",
"\t}\n",
"\t\n",
"\t_geocoded_address = []\n",
"\tfor idx ,row in df.iterrows():\n",
"\t\tk = idx #row['objectid']\n",
"\t\tv = _rmap.get(k, {})\n",
"\t\t_geocoded_address.extend([v])\n",
"\n",
"\treturn _geocoded_address\n",
" \n",
"def _geocode_addresses(df :pd.DataFrame):\n",
"\timport _snowflake # This is a private module that will be available during runtime.\n",
"\n",
"\t# Extract the api from the secret\n",
"\t_access_token = _snowflake.get_generic_secret_string('esri_api_key')\n",
"\n",
"\tdf.columns = ['address']\n",
"\t_request_payload = _format_for_request(df)\n",
"\t_response_payload = _invoke_geocode_addresses_api(_access_token ,_request_payload)\n",
"\t_response_payload.raise_for_status() # Raise HTTPError for bad responses (4xx or 5xx)\n",
"\t\n",
"\t# Extract the locations\n",
"\t_geocoded_address = []\n",
"\n",
"\t# If the response is not 200, then we will just return the \n",
"\t# content asis for each input record, so that user can be aware of the error.\n",
"\t# Another option is to log the event and raise an exception\n",
"\tif _response_payload.status_code != 200:\n",
"\t\tfor idx ,row in df.iterrows():\n",
"\t\t\t_geocoded_address.extend([\n",
"\t\t\t\t\t_response_payload.json()\n",
"\t\t\t])\n",
"\telse:\n",
"\t\t_geocoded_address = _reorder_response_matching_input(df ,_response_payload.json())\n",
" \n",
"\t# Convert the list of geocoded values to a pandas series\n",
"\tr_df = pd.Series(_geocoded_address)\n",
"\treturn r_df\n",
"\n",
"# Ensure the current role and schema context\n",
"sp_session.use_role('venkat_app_dev')\n",
"sp_session.use_database('venkat_db')\n",
"sp_session.use_schema('public')\n",
"\n",
"# Register the snowpark UDF\n",
"# # Ref : https://docs.snowflake.com/en/developer-guide/snowpark/reference/python/latest/snowpark/api/snowflake.snowpark.functions.pandas_udf\n",
"fn_geocode_addresses = F.pandas_udf(\n",
" func = _geocode_addresses,\n",
" return_type = T.PandasSeriesType(T.VariantType()),\n",
" input_types=[T.PandasDataFrameType([T.StringType()])],\n",
" name = 'arcgis_geocode_address_vudf',\n",
" replace=True, is_permanent=True,stage_location='@lib_stg',\n",
" packages=[\"pandas\", 'requests'],\n",
" external_access_integrations=['eai_arcgis_api'],\n",
" secrets = {\n",
" 'esri_api_key' : 'account_common_db.public.arcgis_api_key'\n",
" },\n",
" max_batch_size = 100\n",
")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"---\n",
"### 3. Demonstration"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [],
"source": [
"# Create demo table and insert records\n",
"\n",
"sql_stmts = ['''\n",
"create or replace transient table demo_geocode_address_table(\n",
" address_id varchar\n",
" ,property_address varchar\n",
" ) '''\n",
",'''\n",
"insert into demo_geocode_address_table values\n",
" ('187746','5126 E 5 ST, AUSTIN TX')\n",
" ,('230740','2500 RIDGEPOINT DR, AUSTIN TX, 78728')\n",
" ,('729528','1700 S PLEASANT VALLEY RD, AUSTIN TX, 78741')\n",
" ,('R413942','13435 N US 183 HWY, AUSTIN, TX 78729')\n",
" ,('526381','2011 W RUNDBERG LN Austin TX 78758')\n",
" ,('200001','202 W 17 ST, AUSTIN TX, 78701')\n",
" ,('230428','1101 WHEATLEY AVE, AUSTIN TX, 78752')\n",
" ,('587738','8400 BRODIE LA, AUSTIN TX, 00000')\n",
" ,('255057','10201 MCKALLA PL, AUSTIN TX, 78758')\n",
"'''\n",
"]\n",
"for sql_stmt in sql_stmts:\n",
" sp_session.sql(sql_stmt).collect()\n"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"-------------------------------------------------------------------------------------------------------------------------------------------------\n",
"|\"ADDRESS_ID\" |\"PROPERTY_ADDRESS\" |\"GEOCODED_INFO\" |\"ADDRESS_PT\" |\n",
"-------------------------------------------------------------------------------------------------------------------------------------------------\n",
"|187746 |5126 E 5 ST, AUSTIN TX |{ |{ |\n",
"| | | \"address\": \"5126 E 5th St, Austin, Texas, 787... | \"coordinates\": [ |\n",
"| | | \"attributes\": { | -9.769730406706100e+01, |\n",
"| | | \"AddBldg\": \"\", | 3.025197516672900e+01 |\n",
"| | | \"AddNum\": \"5126\", | ], |\n",
"| | | \"AddNumFrom\": \"\", | \"type\": \"Point\" |\n",
"| | | \"AddNumTo\": \"\", |} |\n",
"| | | \"AddRange\": \"\", | |\n",
"| | | \"Addr_type\": \"PointAddress\", | |\n",
"| | | \"BldgName\": \"\", | |\n",
"| | | \"BldgType\": \"\", | |\n",
"| | | \"Block\": \"\", | |\n",
"| | | \"City\": \"Austin\", | |\n",
"| | | \"CntryName\": \"United States\", | |\n",
"| | | \"Country\": \"USA\", | |\n",
"| | | \"DisplayX\": -9.769730406706100e+01, | |\n",
"| | | \"DisplayY\": 3.025197516672900e+01, | |\n",
"| | | \"Distance\": 0, | |\n",
"| | | \"District\": \"\", | |\n",
"| | | \"ExInfo\": \"\", | |\n",
"| | | \"LangCode\": \"ENG\", | |\n",
"| | | \"LevelName\": \"\", | |\n",
"| | | \"LevelType\": \"\", | |\n",
"| | | \"Loc_name\": \"World\", | |\n",
"| | | \"LongLabel\": \"5126 E 5th St, Austin, TX, 78... | |\n",
"| | | \"Match_addr\": \"5126 E 5th St, Austin, Texas... | |\n",
"| | | \"MetroArea\": \"\", | |\n",
"| | | \"Nbrhd\": \"\", | |\n",
"| | | \"Phone\": \"\", | |\n",
"| | | \"PlaceName\": \"\", | |\n",
"| | | \"Place_addr\": \"5126 E 5th St, Austin, Texas... | |\n",
"| | | \"Postal\": \"78702\", | |\n",
"| | | \"PostalExt\": \"5101\", | |\n",
"| | | \"Rank\": 20, | |\n",
"| | | \"Region\": \"Texas\", | |\n",
"| | | \"RegionAbbr\": \"TX\", | |\n",
"| | | \"ResultID\": 0, | |\n",
"| | | \"Score\": 100, | |\n",
"| | | \"Sector\": \"\", | |\n",
"| | | \"ShortLabel\": \"5126 E 5th St\", | |\n",
"| | | \"Side\": \"\", | |\n",
"| | | \"StAddr\": \"5126 E 5th St\", | |\n",
"| | | \"StDir\": \"\", | |\n",
"| | | \"StName\": \"5th\", | |\n",
"| | | \"StPreDir\": \"E\", | |\n",
"| | | \"StPreType\": \"\", | |\n",
"| | | \"StType\": \"St\", | |\n",
"| | | \"Status\": \"M\", | |\n",
"| | | \"StrucDet\": \"Warehouse\", | |\n",
"| | | \"StrucType\": \"Commercial\", | |\n",
"| | | \"SubAddr\": \"\", | |\n",
"| | | \"Subregion\": \"Travis County\", | |\n",
"| | | \"Territory\": \"\", | |\n",
"| | | \"Type\": \"\", | |\n",
"| | | \"URL\": \"\", | |\n",
"| | | \"UnitName\": \"\", | |\n",
"| | | \"UnitType\": \"\", | |\n",
"| | | \"X\": -9.769740414698499e+01, | |\n",
"| | | \"Xmax\": -9.769630406706101e+01, | |\n",
"| | | \"Xmin\": -9.769830406706100e+01, | |\n",
"| | | \"Y\": 3.025176905573000e+01, | |\n",
"| | | \"Ymax\": 3.025297516672900e+01, | |\n",
"| | | \"Ymin\": 3.025097516672900e+01, | |\n",
"| | | \"Zone\": \"\" | |\n",
"| | | }, | |\n",
"| | | \"location\": { | |\n",
"| | | \"x\": -9.769730406706100e+01, | |\n",
"| | | \"y\": 3.025197516672900e+01 | |\n",
"| | | }, | |\n",
"| | | \"score\": 100 | |\n",
"| | |} | |\n",
"|230740 |2500 RIDGEPOINT DR, AUSTIN TX, 78728 |{ |{ |\n",
"| | | \"address\": \"2500 Ridgepoint Dr, Austin, Texas... | \"coordinates\": [ |\n",
"| | | \"attributes\": { | -9.767257200445600e+01, |\n",
"| | | \"AddBldg\": \"\", | 3.032891378753300e+01 |\n",
"| | | \"AddNum\": \"2500\", | ], |\n",
"| | | \"AddNumFrom\": \"\", | \"type\": \"Point\" |\n",
"| | | \"AddNumTo\": \"\", |} |\n",
"| | | \"AddRange\": \"\", | |\n",
"| | | \"Addr_type\": \"PointAddress\", | |\n",
"| | | \"BldgName\": \"\", | |\n",
"| | | \"BldgType\": \"\", | |\n",
"| | | \"Block\": \"\", | |\n",
"| | | \"City\": \"Austin\", | |\n",
"| | | \"CntryName\": \"United States\", | |\n",
"| | | \"Country\": \"USA\", | |\n",
"| | | \"DisplayX\": -9.767257200445600e+01, | |\n",
"| | | \"DisplayY\": 3.032891378753300e+01, | |\n",
"| | | \"Distance\": 0, | |\n",
"| | | \"District\": \"\", | |\n",
"| | | \"ExInfo\": \"78728\", | |\n",
"| | | \"LangCode\": \"ENG\", | |\n",
"| | | \"LevelName\": \"\", | |\n",
"| | | \"LevelType\": \"\", | |\n",
"| | | \"Loc_name\": \"World\", | |\n",
"| | | \"LongLabel\": \"2500 Ridgepoint Dr, Austin, T... | |\n",
"| | | \"Match_addr\": \"2500 Ridgepoint Dr, Austin, ... | |\n",
"| | | \"MetroArea\": \"\", | |\n",
"| | | \"Nbrhd\": \"\", | |\n",
"| | | \"Phone\": \"\", | |\n",
"| | | \"PlaceName\": \"\", | |\n",
"| | | \"Place_addr\": \"2500 Ridgepoint Dr, Austin, ... | |\n",
"| | | \"Postal\": \"78754\", | |\n",
"| | | \"PostalExt\": \"5250\", | |\n",
"| | | \"Rank\": 20, | |\n",
"| | | \"Region\": \"Texas\", | |\n",
"| | | \"RegionAbbr\": \"TX\", | |\n",
"| | | \"ResultID\": 1, | |\n",
"| | | \"Score\": 9.828000000000000e+01, | |\n",
"| | | \"Sector\": \"\", | |\n",
"| | | \"ShortLabel\": \"2500 Ridgepoint Dr\", | |\n",
"| | | \"Side\": \"\", | |\n",
"| | | \"StAddr\": \"2500 Ridgepoint Dr\", | |\n",
"| | | \"StDir\": \"\", | |\n",
"| | | \"StName\": \"Ridgepoint\", | |\n",
"| | | \"StPreDir\": \"\", | |\n",
"| | | \"StPreType\": \"\", | |\n",
"| | | \"StType\": \"Dr\", | |\n",
"| | | \"Status\": \"M\", | |\n",
"| | | \"StrucDet\": \"\", | |\n",
"| | | \"StrucType\": \"Commercial\", | |\n",
"| | | \"SubAddr\": \"\", | |\n",
"| | | \"Subregion\": \"Travis County\", | |\n",
"| | | \"Territory\": \"\", | |\n",
"| | | \"Type\": \"\", | |\n",
"| | | \"URL\": \"\", | |\n",
"| | | \"UnitName\": \"\", | |\n",
"| | | \"UnitType\": \"\", | |\n",
"| | | \"X\": -9.767243889983401e+01, | |\n",
"| | | \"Xmax\": -9.767157200445600e+01, | |\n",
"| | | \"Xmin\": -9.767357200445601e+01, | |\n",
"| | | \"Y\": 3.032809101991800e+01, | |\n",
"| | | \"Ymax\": 3.032991378753300e+01, | |\n",
"| | | \"Ymin\": 3.032791378753300e+01, | |\n",
"| | | \"Zone\": \"\" | |\n",
"| | | }, | |\n",
"| | | \"location\": { | |\n",
"| | | \"x\": -9.767257200445600e+01, | |\n",
"| | | \"y\": 3.032891378753300e+01 | |\n",
"| | | }, | |\n",
"| | | \"score\": 9.828000000000000e+01 | |\n",
"| | |} | |\n",
"|729528 |1700 S PLEASANT VALLEY RD, AUSTIN TX, 78741 |{ |{ |\n",
"| | | \"address\": \"1700 S Pleasant Valley Rd, Austin... | \"coordinates\": [ |\n",
"| | | \"attributes\": { | -9.772213419793300e+01, |\n",
"| | | \"AddBldg\": \"\", | 3.023656885150700e+01 |\n",
"| | | \"AddNum\": \"1700\", | ], |\n",
"| | | \"AddNumFrom\": \"\", | \"type\": \"Point\" |\n",
"| | | \"AddNumTo\": \"\", |} |\n",
"| | | \"AddRange\": \"\", | |\n",
"| | | \"Addr_type\": \"PointAddress\", | |\n",
"| | | \"BldgName\": \"\", | |\n",
"| | | \"BldgType\": \"\", | |\n",
"| | | \"Block\": \"\", | |\n",
"| | | \"City\": \"Austin\", | |\n",
"| | | \"CntryName\": \"United States\", | |\n",
"| | | \"Country\": \"USA\", | |\n",
"| | | \"DisplayX\": -9.772213419793300e+01, | |\n",
"| | | \"DisplayY\": 3.023656885150700e+01, | |\n",
"| | | \"Distance\": 0, | |\n",
"| | | \"District\": \"\", | |\n",
"| | | \"ExInfo\": \"\", | |\n",
"| | | \"LangCode\": \"ENG\", | |\n",
"| | | \"LevelName\": \"\", | |\n",
"| | | \"LevelType\": \"\", | |\n",
"| | | \"Loc_name\": \"World\", | |\n",
"| | | \"LongLabel\": \"1700 S Pleasant Valley Rd, Au... | |\n",
"| | | \"Match_addr\": \"1700 S Pleasant Valley Rd, A... | |\n",
"| | | \"MetroArea\": \"\", | |\n",
"| | | \"Nbrhd\": \"\", | |\n",
"| | | \"Phone\": \"\", | |\n",
"| | | \"PlaceName\": \"\", | |\n",
"| | | \"Place_addr\": \"1700 S Pleasant Valley Rd, A... | |\n",
"| | | \"Postal\": \"78741\", | |\n",
"| | | \"PostalExt\": \"3105\", | |\n",
"| | | \"Rank\": 20, | |\n",
"| | | \"Region\": \"Texas\", | |\n",
"| | | \"RegionAbbr\": \"TX\", | |\n",
"| | | \"ResultID\": 2, | |\n",
"| | | \"Score\": 100, | |\n",
"| | | \"Sector\": \"\", | |\n",
"| | | \"ShortLabel\": \"1700 S Pleasant Valley Rd\", | |\n",
"| | | \"Side\": \"\", | |\n",
"| | | \"StAddr\": \"1700 S Pleasant Valley Rd\", | |\n",
"| | | \"StDir\": \"\", | |\n",
"| | | \"StName\": \"Pleasant Valley\", | |\n",
"| | | \"StPreDir\": \"S\", | |\n",
"| | | \"StPreType\": \"\", | |\n",
"| | | \"StType\": \"Rd\", | |\n",
"| | | \"Status\": \"M\", | |\n",
"| | | \"StrucDet\": \"Retail\", | |\n",
"| | | \"StrucType\": \"Commercial\", | |\n",
"| | | \"SubAddr\": \"\", | |\n",
"| | | \"Subregion\": \"Travis County\", | |\n",
"| | | \"Territory\": \"\", | |\n",
"| | | \"Type\": \"\", | |\n",
"| | | \"URL\": \"\", | |\n",
"| | | \"UnitName\": \"\", | |\n",
"| | | \"UnitType\": \"\", | |\n",
"| | | \"X\": -9.772132542809599e+01, | |\n",
"| | | \"Xmax\": -9.772113419793300e+01, | |\n",
"| | | \"Xmin\": -9.772313419793301e+01, | |\n",
"| | | \"Y\": 3.023605420265200e+01, | |\n",
"| | | \"Ymax\": 3.023756885150700e+01, | |\n",
"| | | \"Ymin\": 3.023556885150700e+01, | |\n",
"| | | \"Zone\": \"\" | |\n",
"| | | }, | |\n",
"| | | \"location\": { | |\n",
"| | | \"x\": -9.772213419793300e+01, | |\n",
"| | | \"y\": 3.023656885150700e+01 | |\n",
"| | | }, | |\n",
"| | | \"score\": 100 | |\n",
"| | |} | |\n",
"-------------------------------------------------------------------------------------------------------------------------------------------------\n",
"\n"
]
}
],
"source": [
"# # Invoke the Geocoding UDF \n",
"\n",
"sql_stmt = '''\n",
"select address_id\n",
" ,property_address\n",
" ,arcgis_geocode_address_vudf(property_address) as geocoded_info\n",
" ,st_makepoint(geocoded_info:location:x ,geocoded_info:location:y) as address_pt\n",
"from demo_geocode_address_table\n",
"limit 3\n",
"'''\n",
"sp_session.sql(sql_stmt).show()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"---\n",
"### Visualize the Geocoded Addresses\n",
"\n",
"Though visualization can be done here programatically; I am deferring this to be done in the ArcGis platform.\n",
"\n",
"## Finished\n",
"You have successfully geocoded addresses using the ArcGis REST API in Snowflake."
]
}
],
"metadata": {
"kernelspec": {
"display_name": ".venv",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.11.11"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment