12-05-2020 08:28 PM - last edited on 22-05-2020 01:19 PM by
Tableau doesn't yet support Exasol GEOMETRY data types or functions. A workaround is to save the data in GeoJSON format. The following script will convert GEOMETRY data for POINT and POLYGON geometry types into GeoJSON that you can copy and paste into a .json file.
CREATE OR REPLACE PYTHON SCALAR SCRIPT DASHBOARDS."UDF_GeoToJSON" ("ID" VARCHAR(1000), "Geometry" VARCHAR(2000000), "Properties" VARCHAR(10000)) RETURNS VARCHAR(2000000) AS
#==============================================
# Description: This script converts geometry data in Exasol into GeoJSON features. A comma is added to the end o every result, which will need to be removed from the last record.
# Copy / paste the results to a text file. Add this as a header to the file: {"type": "FeatureCollection","features": [
# Add this as a footer to the file: ]}
#
# Usage:
# ID = a unique ID to associate with feature
# Geometry = a value from a GEOMETRY data type
# Properties = a properly formatted string of properties to include with the feature (e.g. {"property1": "string1", "property2": value2})
#
# Notes: Known to work for POLYGON and POINT geometry types
#
# History:
# [05/12/2020] MJ Original Version
#==============================================
import re
def run(variables):
geo = variables.Geometry
header = '{ "type": "Feature", "id": "' + variables.ID + '", "geometry": '
properties = ', "properties": {}'
if variables.Properties != None:
properties = ', "properties": {}'.format(variables.Properties)
footer = '},'
try:
geometryType = re.search('([A-Z]+)\s',geo).group(1)
geo = geo.replace(geometryType + ' ', geometryType).replace(', ', '], [').replace(geometryType,'{ "type": "' + geometryType.title() + '", "coordinates": ' + ('' if geometryType == 'POINT' else '[')).replace('(','[').replace(')',']') + ('' if geometryType == 'POINT' else ']') + '}'
geo = re.sub(r'(\d)( \d)', r'\1,\2', geo)
geojson = header + geo + properties + footer
return(geojson)
except:
return('')
/
Example usage:
Then it's time to become part of a unique family! Discover helpful tips and support other Community members with your knowledge.
Sign In