Xpert

Description

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('')
/

 

 

Comments
Xpert

We have an alternative implementation in Java:

CREATE OR REPLACE JAVA SCALAR SCRIPT geo_to_json (geo VARCHAR(2000000), srid DECIMAL(9,0))
RETURNS VARCHAR(2000000)
AS
/*
 * Convert the geometry to a GeoJSON representation
 * https://javadoc.io/doc/org.orbisgis/h2gis/latest/org/h2gis/functions/io/geojson/ST_AsGeoJSON.html
 */
%jvmoption -Xms16m -Xmx64m -Xss512k;
%jar /buckets/bfsdefault/javalib/h2gis.jar;
import org.h2gis.functions.spatial.convert.ST_GeomFromText;
import org.h2gis.functions.spatial.edit.ST_FlipCoordinates;
import org.h2gis.functions.io.geojson.ST_AsGeoJSON;
class GEO_TO_JSON {
    static ST_GeomFromText parser       = new ST_GeomFromText();
    static ST_FlipCoordinates flipper   = new ST_FlipCoordinates();
    static ST_AsGeoJSON builder         = new ST_AsGeoJSON();
    static String run(ExaMetadata exa, ExaIterator ctx) throws Exception {
        String  geo_wkt  = ctx.getString(0);
        Integer geo_srid = ctx.getInteger(1);
        try {
            return builder.toGeojson(flipper.flipCoordinates(parser.toGeometry(geo_wkt, geo_srid.intValue())));
        } catch (Exception ex) {
            return null;
        }
    }
}
/

h2gis JAR can be downloaded here: https://mvnrepository.com/artifact/org.orbisgis/h2gis/1.5.0

Please add or remove ST_FlipCoorinates() call depending on your needs. Common SRID 4326 is lat / lon, but GeoJSON is lon / lat, so we do use this flip.