Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-211

Esri: (reverse) geocoding with ArcGIS REST API and EXASolution GEOMETRY data type

    XMLWordPrintable

    Details

    • Type: How To
    • Status: Published
    • Affects Version/s: EXASolution 5.0, EXASOL 6.0.0
    • Fix Version/s: None
    • Labels:
      None
    • Environment:
      You have an EXASolution database and a use case which has advanced geospatial requirements.
    • Solution:
      Hide

      Intro

      One way to do deal with geocoding is to use a GIS (geographic information system) service like ArcGIS (www.arcgis.com) from Esri.

      The following website shows different use cases when such a service might be useful:
      https://developers.arcgis.com

      In this tutorial, we would like to show you how to use the ArcGIS REST API from Python:
      http://resources.arcgis.com/en/help/arcgis-rest-api

      In practice, you might face the following challenges:

      • Connect to a REST API from an EXASolution UDF language (Python, Java, R, Lua).
      • If you want to benefit from EXASolution's GEOMETRY data type (e.g. in order to run geospatial functions), you need to think about converting this data to Python strings and back.

      This solution uses Python and its package requests (python-requests.org) to connect to the REST API.
      As an example, we only demonstrate geocoding and reverse geocoding.

      Nota bene:
      For better performance when working with large datasets, we recommend batch geocoding (https://developers.arcgis.com/rest/geocode/api-reference/geocoding-geocode-addresses.htm) instead of geocoding – but in order to use this, you need to have an ArcGIS online organizational account which you might be charged for.

      Prerequisites

      • You need Python as "Extra Database Feature" in your license.
      • You need Geospatial as "Extra Database Feature" in your license.
        Note: Both features are included in EXASOL 6 advanced edition

      Steps

      1. Install Python package requests.
        • EXASOL 5 or before: Use EXAoperation like in requests.jpg.
        • EXASOL 6 or later: See detailed information about EXABucket FS in SOL-503 and in particular the subsection Expanding script languages using BucketFS in the EXASOL user manual. There you can see, how to adapt arcgis_demo.sql to import python libraries from buckets.
      2. Adapt arcgis_demo.sql if you are using EXASOL 6 or later.
      3. Then, run arcgis_cities.sql in order to create the arcgis schema and the input data.
      4. Finally, run arcgis_demo.sql in order see how you can enrich your address information with GPS coordinates (geocoding) and vice versa (reverse geocoding).
      Show
      Intro One way to do deal with geocoding is to use a GIS (geographic information system) service like ArcGIS (www.arcgis.com) from Esri. The following website shows different use cases when such a service might be useful: https://developers.arcgis.com In this tutorial, we would like to show you how to use the ArcGIS REST API from Python: http://resources.arcgis.com/en/help/arcgis-rest-api In practice, you might face the following challenges: Connect to a REST API from an EXASolution UDF language (Python, Java, R, Lua). If you want to benefit from EXASolution's GEOMETRY data type (e.g. in order to run geospatial functions), you need to think about converting this data to Python strings and back. This solution uses Python and its package requests (python-requests.org) to connect to the REST API. As an example, we only demonstrate geocoding and reverse geocoding. Nota bene: For better performance when working with large datasets, we recommend batch geocoding ( https://developers.arcgis.com/rest/geocode/api-reference/geocoding-geocode-addresses.htm ) instead of geocoding – but in order to use this, you need to have an ArcGIS online organizational account which you might be charged for. Prerequisites You need Python as "Extra Database Feature" in your license. You need Geospatial as "Extra Database Feature" in your license. Note: Both features are included in EXASOL 6 advanced edition Steps Install Python package requests. EXASOL 5 or before: Use EXAoperation like in requests.jpg . EXASOL 6 or later: See detailed information about EXABucket FS in SOL-503 and in particular the subsection Expanding script languages using BucketFS in the EXASOL user manual. There you can see, how to adapt arcgis_demo.sql to import python libraries from buckets. Adapt arcgis_demo.sql if you are using EXASOL 6 or later. Then, run arcgis_cities.sql in order to create the arcgis schema and the input data. Finally, run arcgis_demo.sql in order see how you can enrich your address information with GPS coordinates (geocoding) and vice versa (reverse geocoding).
    • Category 1:
      UDFs and In-Database Analytics
    • Category 2:
      SQL - Data types

      Attachments

        Issue Links

        1. arcgis_cities.sql
          20 kB
        2. arcgis_demo.sql
          5 kB
        3. requests.jpg
          requests.jpg
          71 kB

          Activity

            People

            • Assignee:
              CaptainEXA Captain EXASOL
              Reporter:
              CaptainEXA Captain EXASOL
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated: