Use the map from Teradata

Now open your favourite SQL client and connect to your Teradata system.

We will use Teradata Database JSON functions to parse our GeoJson document and extract the most relevant properties and the geometry itself (the coordinates of the city) for each feature (each feature representing a city in this example). We then use the GeomFromGeoJSON function to cast our geometry as a native Teradata geometry data type (ST_GEOMETRY).

For user convenience, will wrap all this SQL code in a view:

REPLACE VIEW cities_geo AS
SEL city_name, country_name, region_name, code_country_isoa3, GeomFromGeoJSON(geom, 4326) city_coord
FROM JSON_Table
(ON (
    SEL
     geojson_nm id
    ,cast(geojson_clob as JSON) jsonCol
    FROM geojson_src where geojson_nm='cities'
)
USING rowexpr('$.features[*]')
               colexpr('[ {"jsonpath" : "$.geometry",
                           "type" : "VARCHAR(32000)"},
                          {"jsonpath" : "$.properties.NAME",
                           "type" : "VARCHAR(50)"},
                          {"jsonpath" : "$.properties.SOV0NAME",
                           "type" : "VARCHAR(50)"},
                          {"jsonpath" : "$.properties.ADM1NAME",
                           "type" : "VARCHAR(50)"},
                          {"jsonpath" : "$.properties.SOV_A3",
                           "type" : "VARCHAR(50)"}]')
) AS JT(id, geom, city_name, country_name, region_name, code_country_isoa3);

That's all, you can now view the prepared geometry data as a table, ready to enrich your analytics:

SEL TOP 5 * FROM cities_geo;

Result:

city_name	country_name	region_name	code_country_isoa3	city_coord
Potenza	Italy	Basilicata	ITA	POINT (15.798996495640267 40.642002130098206)
Mariehamn	Finland	Finström	ALD	POINT (19.949004471869102 60.096996184895431)
Ramallah	Indeterminate		PSE	POINT (35.206209378189556 31.902944751424059)
Poitier	French Republic	Poitou-Charentes	FRA	POINT (0.333276528534554 46.583292255736581)
Clermont-Ferrand	French Republic	Auvergne	FRA	POINT (3.080008095928406 45.779982115759424)

Calculate the distance between two cities:

SEL b.city_coord.ST_SphericalDistance(l.city_coord)
FROM
(SEL city_coord FROM cities_geo WHERE city_name='Bordeaux') b
CROSS JOIN (SEL city_coord FROM cities_geo WHERE city_name='Lvov') l

Result:

city_coord.ST_SPHERICALDISTANCE(city_coord)
1.9265006861079421e+06