03 Spatial Viz.ipynb
in JupyterLab.import pandas as pd
from hana_ml import dataframe as dfh
hana_cloud_endpoint="<uuid>.hana.trial-eu10.hanacloud.ondemand.com:443"
hana_cloud_host, hana_cloud_port=hana_cloud_endpoint.split(":")
cchc=dfh.ConnectionContext(port=hana_cloud_port,
address=hana_cloud_host,
user='HANAML',
password='Super$ecr3t!',
encrypt=True
)
cchc.connection.isconnected()
matplotlib
library for plotting spatial data.descartes
supporting plotting in GeoPandas.!pip install geopandas descartes
import geopandas
dfh_countries
from the existing SAP HANA table TM_WORLD_BOARDERS
that we loaded in the previous post. You can see the table()
method in hana_ml
has been extended to support geo_cols
property too.dfh_countries = cchc.table("TM_WORLD_BORDERS", geo_cols={"SHAPE":"4326"})
dfg_
prefixes for that.dfg_countries = geopandas.GeoDataFrame(dfh_countries.collect(),
geometry='SHAPE', crs="EPSG:4326"
)
crs
, is used. It matches SAP HANA's SRID that is based on a dataset maintained by EPSG as well.dfg_countries.dtypes
print(type(dfg_countries))
print(type(dfg_countries.LON))
print(type(dfg_countries.SHAPE))
plot()
to do it!dfg_countries.plot()
import matplotlib.pyplot as plt
print(plt.rcParams.get('figure.figsize'))
boundary
here, as we will need it later as well)...dfg_countries.boundary.plot(figsize=(26, 12))
plt.rcParams["figure.figsize"] = [26, 12]
dfg_countries.plot(column="SUBREGION", cmap='flag')
dfg_countries.plot(column="POP2005", cmap='rainbow', legend=True)
cmap
to get the best use of colors to convey information. You can play with different colormaps available in Matplotlib.legend
in the second example.PORTS
in SAP HANA.dfh_ports = cchc.table("PORTS", geo_cols={"POINT_LON_LAT_GEO":"4326"}).select('CODE','POINT_LON_LAT_GEO')
geopandas.GeoDataFrame(
dfh_ports.collect(),
geometry='POINT_LON_LAT_GEO', crs="EPSG:4326"
).plot()
dfh_ports_pl = (dfh_ports
.alias('P')
.join(dfh_countries.filter("NAME='Poland'").alias('C'),
condition='P.POINT_LON_LAT_GEO.ST_CoveredBy(C.SHAPE)=1',
select=["P.*"]
)
)
dfg_ports_pl = geopandas.GeoDataFrame(dfh_ports_pl.collect(),
geometry='POINT_LON_LAT_GEO', crs="EPSG:4326"
)
dfg_ports_pl.plot()
fig_port_pl
representing the plot.fig_port_pl=dfg_ports_pl.plot()
dfg_ports_pl.apply(
lambda port: fig_port_pl.annotate(
port.CODE,
xy=port.POINT_LON_LAT_GEO.coords[0],
xytext=(-10, 10), textcoords="offset points"
), axis=1
)
TM_WORLD_BORDERS
, would help. Don't you think so?dfg_poland = geopandas.GeoDataFrame(dfh_countries.filter("ISO2='PL'").collect(),
geometry='SHAPE', crs="EPSG:4326"
)
fig_port_pl=dfg_ports_pl.plot(color='w', edgecolor='r', markersize=300)
dfg_ports_pl.apply(lambda port: fig_port_pl.annotate(port.CODE, xy=port.POINT_LON_LAT_GEO.coords[0], xytext=(-10, 15), textcoords="offset points"), axis=1)
dfg_poland.plot(ax=fig_port_pl, alpha=0.6, edgecolor='k', zorder=0)
color
, edgecolor
and markersize
to make airport locations more visually prominent,alpha
parameter to set the transparency of the country shape (we will need it later), andzorder
to add a country shape as a layer below airports.contextily
module.!pip install contextily
import contextily as ctx
source
parameter in the ctx.add_basemap()
method. I am using standard OpenStreetMap here:fig_port_pl=dfg_ports_pl.to_crs(epsg=3857).plot(alpha=1, color='w', edgecolor='r', markersize=300, zorder=2)
dfg_ports_pl.to_crs(epsg=3857).apply(lambda port: fig_port_pl.annotate(port.CODE, xy=port.POINT_LON_LAT_GEO.coords[0], xytext=(-10, 15), textcoords="offset points"), axis=1)
dfg_poland.to_crs(epsg=3857).plot(ax=fig_port_pl, alpha=0.4, edgecolor='k', zorder=1)
fig_port_pl.set_axis_off()
ctx.add_basemap(fig_port_pl, source=ctx.providers.OpenStreetMap.Mapnik)
to_csr()
method we had to apply to convert our datasets to Pseudo-Mercator coordinate spatial reference 3857
. This is the spatial reference that was first introduced in Google Maps, and then adopted by most of the map services, incl. OpenStreetMap.Do you know that Google tried to get EPSG to register this CSR using the number900913
? Because, you know,
country_iso2='PL'
sql_3857='''
SELECT
p."POINT_LON_LAT_GEO".ST_Transform(3857) AS "PORTGEO",
ST_VoronoiCell(p."POINT_LON_LAT_GEO".ST_Srid(0), 30).ST_Srid(4326).ST_Intersection(wb."SHAPE").ST_Transform(3857) OVER () AS "CELL"
FROM "PORTS" p
JOIN "TM_WORLD_BORDERS" wb
ON p."POINT_LON_LAT_GEO".ST_COveredBy(wb."SHAPE")=1
WHERE wb."ISO2" ='{}'
'''.format(country_iso2)
dfp_ports_pl_voronoi = cchc.sql(
sql_3857, geo_cols=['CELL','PORTGEO'],
srid=3857
).collect()
dfg_ports_pl_voronoi = geopandas.GeoDataFrame(
dfp_ports_pl_voronoi,
geometry='CELL', crs="EPSG:3857"
)
zorder
parameter.fig_port_pl=dfg_ports_pl.to_crs(epsg=3857).plot(alpha=1, color='w', edgecolor='r', markersize=300, zorder=3)
dfg_ports_pl.to_crs(epsg=3857).apply(lambda port: fig_port_pl.annotate(port.CODE, xy=port.POINT_LON_LAT_GEO.coords[0], xytext=(-10, 15), textcoords="offset points"), axis=1)
dfg_poland.to_crs(epsg=3857).plot(ax=fig_port_pl, alpha=0.4, edgecolor='k', zorder=1)
fig_port_pl.set_axis_off()
ctx.add_basemap(fig_port_pl, source=ctx.providers.OpenStreetMap.Mapnik)
dfg_ports_pl_voronoi.boundary.plot(ax=fig_port_pl, edgecolor='r',
zorder=2
)
hana_ml
2.6 processing connected data using SAP HANA's graph workspaces!You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
37 | |
25 | |
17 | |
13 | |
7 | |
7 | |
7 | |
6 | |
6 | |
6 |