ALTER TABLE "GEO_DATA"."STORES" ADD (COORDINATES ST_POINT(4326) NULL);
UPDATE "GEO_DATA"."STORES" SET COORDINATES = NEW ST_POINT(VL_X, VL_Y);
ALTER TABLE "GEO_DATA"."CLIENTS" ADD (COORDINATES ST_POINT(4326) NULL);
UPDATE "GEO_DATA"."CLIENTS" SET COORDINATES = NEW ST_POINT(VL_X, VL_Y);
ALTER TABLE "GEO_DATA"."STORES" ADD(ISOCRON5MW ST_GEOMETRY(4326) NULL);
ALTER TABLE "GEO_DATA"."STORES" ADD(ISOCRON10MW ST_GEOMETRY(4326) NULL);
ALTER TABLE "GEO_DATA"."STORES" ADD(ISOCRON15MW ST_GEOMETRY(4326) NULL);
ALTER TABLE "GEO_DATA"."STORES" ADD(ISOCRON5MC ST_GEOMETRY(4326) NULL);
ALTER TABLE "GEO_DATA"."STORES" ADD(ISOCRON10MC ST_GEOMETRY(4326) NULL);
ALTER TABLE "GEO_DATA"."STORES" ADD(ISOCRON15MC ST_GEOMETRY(4326) NULL);
from hdbcli import dbapi
from openrouteservice import client
import json
api_key = 'Token obtained from openroute'
clior = client.Client(key=api_key)
con = dbapi.connect(address='HANA host', port=<port number>,
user='user name', password='password')
cur = con.cursor()
cur.execute("""SELECT ID, VL_X, VL_Y FROM "GEO_DATA"."STORES" """)
res = cur.fetchall()
profiles = ['driving-car', 'foot-walking']
#5, 10 and 15 minutes (converted into seconds)
ranges = [300, 600, 900]
polygons = []
for store in res:
# Get isochrones for each row
# Update corresponding columns
c_x = store[1]
c_y = store[2]
polygons = []
for profile in profiles:
for r in ranges:
iso_result = clior.isochrones(locations=[(c_x, c_y)],
profile=profile,
range=[r],
dry_run = False)
geom = iso_result['features'][0]['geometry']
polygons.append(json.dumps(geom))
update_sql = """UPDATE "GEO_DATA"."STORES"
SET ISOCRON5MC = ST_GEOMFROMGEOJSON(?, 4326),
ISOCRON10MC = ST_GEOMFROMGEOJSON(?, 4326),
ISOCRON15MC = ST_GEOMFROMGEOJSON(?, 4326),
ISOCRON5MW = ST_GEOMFROMGEOJSON(?, 4326),
ISOCRON10MW = ST_GEOMFROMGEOJSON(?, 4326),
ISOCRON15MW = ST_GEOMFROMGEOJSON(?, 4326)
WHERE ID = %d """ %(store[0])
cur2 = con.cursor()
cur2.execute(update_sql, polygons)
{'coordinates': [[[-0.446451, 39.476014],
[-0.445327, 39.475588],
[-0.444924, 39.475452],
[-0.438826, 39.473127],
[-0.433999, 39.4713],
[-0.42627, 39.466775],
[-0.421565, 39.463238],
[-0.416286, 39.460046],
...
[-0.446451, 39.476014]]],
'type': 'Polygon'}
update_sql = """UPDATE "GEO_DATA"."STORES"
SET ISOCRON5MC = ST_GEOMFROMGEOJSON(?, 4326),
ISOCRON10MC = ST_GEOMFROMGEOJSON(?, 4326),
ISOCRON15MC = ST_GEOMFROMGEOJSON(?, 4326),
ISOCRON5MW = ST_GEOMFROMGEOJSON(?, 4326),
ISOCRON10MW = ST_GEOMFROMGEOJSON(?, 4326),
ISOCRON15MW = ST_GEOMFROMGEOJSON(?, 4326)
WHERE ID = %d """ %(store[0])
cur.execute("""SELECT ISOCRON5MC.ST_AsGeoJSON(),
VL_X,
VL_Y,
NAME FROM STORES
WHERE ID = 30111 """)
res = cur.fetchall()
import folium
m = folium.Map(location=[res[0][2], res[0][1]], zoom_start=14)
folium.Marker([res[0][2], res[0][1]]).add_to(m)
m
'{"type": "Polygon", "coordinates": [[[-4.634976, 36.546284], [-4.634647, 36.544776], [-4.633848, 36.543983], [-4.628487, 36.534139], [-4.62702, 36.532061], [-4.622769, 36.530737], [-4.617062, 36.536948], [-4.616745, 36.540534], ..., [-4.634976, 36.546284]]]}'
import json
list_coords = json.loads(res[0][0])
list_coords = list_coords['coordinates'][0]
list_coords = [(y,x) for x,y in list_coords]
list_coords
[[-4.634976, 36.546284], [-4.634647, 36.544776], [-4.633848, 36.543983], [-4.628487, 36.534139], [-4.62702, 36.532061], [-4.622769, 36.530737], [-4.617062, 36.536948], [-4.616745, 36.540534], ..., [-4.634976, 36.546284]]
folium.vector_layers.Polygon(list_coords,
color='#ffd600',
fill_color='#ffd600',
fill_opacity=0.5,
weight=3).add_to(m)
m
SELECT MIN(stores.COORDINATES.ST_DISTANCE(clients.COORDINATES, 'kilometer'))
FROM "GEO_DATA"."STORES" stores,
"GEO_DATA"."CLIENTS" clients
WHERE clients."ID" = 475903
| MIN(STORES.COORDI |
| --------------------------------------------------------------------- |
| 0.874822458959487 |
1 row selected (overall time 56.818 msec; server time 31.743 msec)
SELECT stores."ID",
Stores."VL_X",
stores."VL_Y"
FROM "GEO_DATA"."STORES" stores,
(SELECT stores."ID" "ID",
MIN(stores.COORDINATES.ST_DISTANCE(clients.COORDINATES, 'kilometer'))
FROM
"GEO_DATA"."STORES" stores,
"GEO_DATA"."CLIENTS" clients
WHERE clients."ID" = 475903
GROUP BY stores."ID"
ORDER BY 2
LIMIT 1
) closest_stores
WHERE stores."ID" = closest_stores."ID"
| ID | VL_X | VL_Y |
| ----------- | ----------------------- | ----------------------- |
| 30216 | -3.656899 | 40.54659199999999 |
1 row selected (overall time 277.303 msec; server time 198.717 msec)
folium.Marker([res[0][5], res[0][4]],
popup="<i>"+ res[0][1] + "</i>", tooltip="Closest Store",
icon=folium.Icon(color='green')).add_to(m)
SELECT
clients.COORDINATES.ST_CoveredBy(stores.ISOCRON5MW)
as "5 minutes walking",
clients.COORDINATES.ST_CoveredBy(stores.ISOCRON10MW)
as "10 minutes walking",
clients.COORDINATES.ST_CoveredBy(stores.ISOCRON15MW)
as "15 minutes walking",
clients.COORDINATES.ST_CoveredBy(stores.ISOCRON5MC)
as "5 minutes car",
clients.COORDINATES.ST_CoveredBy(stores.ISOCRON10MC)
as "10 minutes car",
clients.COORDINATES.ST_CoveredBy(stores.ISOCRON15MC)
as "15 minutes car"
FROM "GEO_DATA"."STORES" stores, "GEO_DATA"."CLIENTS" clients
WHERE
clients."ID" = 475903 AND
stores."ID" = 30216
| 5 minutes walking | 10 minutes walking | 15 minutes walking | 5 minutes car | 10 minutes car | 15 minutes car |
| ------------------ | ------------------- | ------------------- | -------------- | --------------- | --------------- |
| 0 | 0 | 1 | 1 | 1 | 1 |
1 row selected (overall time 15.151 msec; server time 4983 usec)
SELECT COUNT(*)
FROM
"GEO_DATA"."STORES" stores,
"GEO_DATA"."CLIENTS" clients
WHERE
clients."ID" = 475903 AND
stores.COORDINATES.ST_DISTANCE(clients.COORDINATES, 'kilometer') < 20
| COUNT(*) |
| -------------------- |
| 43 |
1 row selected (overall time 52.003 msec; server time 25.774 msec)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
13 | |
10 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
5 | |
4 |