Base Station Positions¶

import verticapy as vp
vp.__version__

vp.connect("VerticaDSN")

# Creating the schema
vp.drop("shanghai", method="schema")
vp.create_schema("shanghai")

# Libraries import
import matplotlib
from verticapy.geo import *
from verticapy.datasets import load_world

# Increasing video limit
matplotlib.rcParams['animation.embed_limit'] = 2**128

#######
# CDR #
#######
try:
    cdr = vp.read_csv("data/cdr.csv", 
                      schema="shanghai", 
                      table_name="cdr", 
                      sep=',',
                      parse_nrows=1000,)
except:
    cdr = vp.vDataFrame("shanghai.cdr")
# Unique Row id: It will be used to compute the Polygons intersection
cdr["row_id"] = "ROW_NUMBER() OVER(ORDER BY user_id, start_time)"

######################
# Shanghai Districts #
######################
try:
    shanghai_districts = vp.read_csv("shanghai/districts.csv", 
                                     schema="shanghai", 
                                     table_name="districts", 
                                     sep=',',)
except:
    shanghai_districts = vp.vDataFrame("shanghai.districts")
# Converting the districts to Geometry
shanghai_districts = shanghai_districts["geometry"].apply("ST_GeomFromText({})")
# Creating Shanghai 
shanghai_districts["district_level"] = st.case_when(shanghai_districts["number"] <= 7, 'Downtown',
                                                    shanghai_districts["number"] <= 11, 'Suburb1',
                                                    shanghai_districts["number"] <= 15, 'Suburb2',
                                                    'Suburb3')

cdr

shanghai_districts

# Outliers
world = load_world()
china = world[world["country"] == "China"]
ax = china["geometry"].geo_plot(color = "white",
                                edgecolor='black',)
cdr.groupby(["longitude", "latitude"]).scatter(["longitude", "latitude"], ax=ax)
ax.set_title("Shanghai's Base Stations with Outliers")

# Dropping Outliers
cdr["longitude"].drop_outliers(threshold=2.0)
cdr["latitude"].drop_outliers(threshold=2.0)

ax = china["geometry"].geo_plot(color = "white",
                                edgecolor='black',)
cdr.groupby(["longitude", "latitude"]).scatter(["longitude", "latitude"], ax=ax)
ax.set_title("Shanghai's Base Stations without Outliers")

ax = shanghai_districts["geometry"].geo_plot(column = "district_level",
                                             edgecolor='white',)
# Finding Centroids
centroids = shanghai_districts.select(["name", 
                                       "ST_X(ST_CENTROID(geometry))", 
                                       "ST_Y(ST_CENTROID(geometry))"]).to_list()
# Plotting the suburb names
for c in centroids[7:]:
    ax.text(c[1], c[2], c[0], va="center", ha="center")
ax.set_title("Shanghai's Districts")
ax.text(121.43, 31.25, 'Downtown', va="center", ha="center")
ax2 = shanghai_districts.search("number <= 7")["geometry"].geo_plot(color="#CCCCCC",
                                                                    edgecolor='white',)
# Plotting the downtown names
for c in centroids[:7]:
    ax2.text(c[1], c[2], c[0], va="center", ha="center")
ax2.set_title("Shanghai's Downtown")
ax2

# Finding the intersections between each ping and each district
create_index(shanghai_districts, 
             gid="number", 
             g="geometry", 
             index="shanghai_districts",
             overwrite=True,)
intersect_districts_cdr = intersect(cdr,
                                    index="shanghai_districts",
                                    gid="row_id",
                                    x="longitude",
                                    y="latitude",)
intersect_districts_cdr