Vantage で地理参照データを使用する方法

import wget
world_cities=wget.download('https://d2ad6b4ur7yvpq.cloudfront.net/naturalearth-3.3.0/ne_50m_populated_places.geojson')
with open(world_cities) as geo_json:
    jmap = jmap = geo_json.read()

import teradatasql
import getpass
tdhost='<Your-Vantage-System-HostName-Here>'
tdUser='<Your-Vantage-User-Name-Here>'

# Create a connection to Teradata Vantage
con = teradatasql.connect(None, host=tdhost, user=tdUser, password=getpass.getpass())

# Create a table named geojson_src and load the JSON map into it as a single CLOB
with con.cursor () as cur:
    cur.execute ("create table geojson_src (geojson_nm VARCHAR(32), geojson_clob CLOB CHARACTER SET UNICODE);")
    r=cur.execute ("insert into geojson_src (?, ?)", ['cities',jmap])

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);

SEL TOP 5 * FROM cities_geo;

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

import wget
countries_geojson=wget.download('https://datahub.io/core/geo-countries/r/countries.geojson')

print(countries_json.keys())
print(countries_json['type'])
print(countries_json['features'][0]['properties'].keys())
print(countries_json['features'][0]['geometry']['coordinates'])

[(f['properties']['ADMIN'], f['properties']['ISO_A3'], f['geometry']) for f in countries_json['features'][:1]]

import teradatasql
import getpass
tdhost='<Your-Vantage-System-HostName-Here>'
tdUser='<Your-Vantage-User-Name-Here>'

# Create a connection to Teradata Vantage
con = teradatasql.connect(None, host=tdhost, user=tdUser, password=tdPassword)

# Create a table and load our country names, codes, and geometries.
with con.cursor () as cur:
    cur.execute ("create table stg_countries_map (country_nm VARCHAR(32), ISO_A3_cd VARCHAR(32), boundaries_geo CLOB CHARACTER SET UNICODE);")
    r=cur.execute ("insert into stg_countries_map (?, ?, ?)", [(f['properties']['ADMIN'], f['properties']['ISO_A3'], str(f['geometry'])) for f in countries_json['features']])

# Now create our final reference table, casting the geometry CLOB as a ST_GEOMETRY object
sql='''
CREATE TABLE ref_countries_map AS
(
SEL
ISO_A3_cd
,country_nm
,GeomFromGeoJSON(boundaries_geo, 4326) boundaries_geo
FROM stg_countries_map
) WITH DATA
'''

WITH con.cursor () AS cur:
    cur.execute (sql)

SEL cty.city_name, cty.city_coord, ctry.country_nm
FROM cities_geo cty
LEFT JOIN ref_countries_map ctry
	ON ctry.boundaries_geo.ST_Contains(cty.city_coord)=1
WHERE cty.city_name LIKE 'a%'