Add Spatial Data to a Hyper File


Tableau supports spatial data (geography) in .hyper files. However the Hyper API does not directly accept Well-Known-Text for spatial data. Instead you need to provide a CAST("column_as_text" AS GEOGRAPHY) expression in the inserter to provide the spatial data as text strings. Hyper API’s inserter pushes the CAST("column_as_text" AS GEOGRPAHY) expression down to Hyper where the text strings are converted to spatial data.

Tip: The procedure explained here is for Hyper API v0.0.10309 and later. To add geographic data to a Hyper file using temporary tables, a solution used for previous versions of the Hyper API, see Legacy Method - Add Spatial Data to a Hyper File.

This topic describes how you can use the Hyper API to add geography type data to the Hyper file.


In this section


Overview of inserting spatial data to a hyper file

The basic process for adding spatial data involves defining your inputs to Hyper API’s inserter and specifying how to convert the text strings to geography types using Hyper SQL expressions. Hyper API’s inserter pushes the expression down to Hyper to convert text string to spatial data on the fly during insertion

When you add the text strings into the Hyper file, the text must be in the Well-Known-Text (WKT) format for geography data. The WKT is defined by the Open GIS Consortium, Inc, in the OpenGIS Simple Features Specification For SQL. The types include Point, MultiPoint, LineString, MultiLineString, Polygon, and MultiPolygon.

Create tables for text and spatial data

  1. Define and create a table in the .hyper file to contain the SqlType.geography() data. This is the table that you will use in Tableau. For example, the following Python code snippet creates a table to hold location data. The table is called Extract and is in the Extract namespace (or schema) similar to Hyper files created with Tableau.

    
         connection.catalog.create_schema('Extract')
         geo_table = TableDefinition(TableName('Extract','Extract'), [
             TableDefinition.Column('Name', SqlType.text(), nullability=NOT_NULLABLE),
             TableDefinition.Column('Location',  SqlType.geography(), nullability=NOT_NULLABLE),
          ])
    
         connection.catalog.create_table(geo_table)
    
    
  2. Define your inputs to the inserter as a List of TableDefinition.Column . This definition will be similar to the TableDefinition of Extract table created before except that the columns with SqlType.geography() will be specified as SqlType.text() type

      # Inserter definition contains the column definition for the values that are inserted
      # The data input has two text values Name and Location_as_text
      inserter_definition = [
          TableDefinition.Column(name='Name', type=SqlType.text(), nullability=NOT_NULLABLE),
          TableDefinition.Column(name='Location_as_text', type=SqlType.text(), nullability=NOT_NULLABLE)]
    
    
  3. Specify the conversion of SqlType.text() to SqlType.geography() using CAST expression in Inserter.ColumnMapping. Specify all columns into which data is inserter in Inserter.ColumnMapping list. For columns that do not require any transformations provide only the names

         column_mappings = [
             'Name',
             Inserter.ColumnMapping('Location', f'CAST({escape_name("Location_as_text")} AS GEOGRAPHY)')
         ]
    

Insert the spatial data as text (WKT) into the text table

When you add the text data to the Hyper file, the text must be in the Well Known Text Format (WKT) format for geography data, such as Point, Polygon, etc. For example, to specify location data, you would use point(Longitude Latitude).

The following Python code example inserts two rows of data with location information into a table that is defined to hold geography data.


    data_to_insert = [
        [ 'Seattle', "point(-122.338083 47.647528)" ],
        [ 'Munich' , "point(11.584329 48.139257)"   ]
    ]

    with Inserter(connection, geo_table, column_mappings, inserter_definition = inserter_definition) as inserter:
        inserter.add_rows(rows=data_to_insert)
        inserter.execute()

Complete example code (Python)

The following example code illustrates how you can create a .hyper file that contains location (geography) information by using expressions in the Inserter.


from tableauhyperapi import Connection, HyperProcess, SqlType, TableDefinition, \
    escape_string_literal, escape_name, NOT_NULLABLE, Telemetry, Inserter, CreateMode, TableName


with HyperProcess(Telemetry.SEND_USAGE_DATA_TO_TABLEAU, 'myapp' ) as hyper:
    print("The HyperProcess has started.")

    with Connection(hyper.endpoint, 'TrivialExample_geo.hyper', CreateMode.CREATE_AND_REPLACE) as connection:
        print("The connection to the Hyper file is open.")

    # create geography table
        connection.catalog.create_schema('Extract')
        geo_table = TableDefinition(TableName('Extract','Extract'), [
            TableDefinition.Column('Name', SqlType.text(), nullability=NOT_NULLABLE),
            TableDefinition.Column('Location',  SqlType.geography(), nullability=NOT_NULLABLE),
         ])
        print("The geo_table is defined.")
        connection.catalog.create_table(geo_table)

        # Inserter definition contains the column definition for the values that are inserted
        # The data input has two text values Name and Location_as_text
        inserter_definition = [
            TableDefinition.Column(name='Name', type=SqlType.text(), nullability=NOT_NULLABLE),
            TableDefinition.Column(name='Location_as_text', type=SqlType.text(), nullability=NOT_NULLABLE)]

        # Column 'Name' is inserted into "Extract"."Extract" as-is.
        # Column 'Location' in "Extract"."Extract" of geography type is computed from Column 'Location_as_text' of text type
        # using the expression 'CAST("Location_as_text") AS GEOGRAPHY'.
        # Inserter.ColumnMapping is used for mapping the CAST expression to Column 'Location'.
        column_mappings = [
            'Name',
            Inserter.ColumnMapping('Location', f'CAST({escape_name("Location_as_text")} AS GEOGRAPHY)')
        ]

    # Format the data as well-known text (WKT)
        data_to_insert = [
        [ 'Seattle', "point(-122.338083 47.647528)" ],
        [ 'Munich' , "point(11.584329 48.139257)"  ]
        ]

        # Insert data into "Extract"."Extract" table with CAST expression.
        with Inserter(connection, geo_table, column_mappings, inserter_definition = inserter_definition) as inserter:
            inserter.add_rows(rows=data_to_insert)
            inserter.execute()
        print("The data was added to the table.")

    print("The connection to the Hyper extract file is closed.")
print("The HyperProcess has shut down.")