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

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

    Details

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

      Background

      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.

       

      Steps

      Step 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.

      Step 2. Adapt arcgis_demo.sql

      If you are using EXASOL 6 or later.

      Step 3. Run arcgis_cities.sql

      In order to create the arcgis schema and the input data.

      Step 4. Run arcgis_demo.sql

      In order to see how you can enrich your address information with GPS coordinates (geocoding) and vice versa (reverse geocoding).

      Additional Notes

      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.

      Additional References

      Relates:  SOL-341 Use Google Maps API with EXASOL

      Uses: SOL-503 How to create an EXABucketFS service and bucket

       

       

      Show
      Background 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.   Steps Step 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. Step 2. Adapt arcgis_demo.sql If you are using EXASOL 6 or later. Step 3. Run arcgis_cities.sql In order to create the arcgis schema and the input data. Step 4. Run arcgis_demo.sql In order to see how you can enrich your address information with GPS coordinates (geocoding) and vice versa (reverse geocoding). Additional Notes 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. Additional References Relates:   SOL-341  Use Google Maps API with EXASOL Uses:  SOL-503  How to create an EXABucketFS service and bucket    
    • 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: