def mactrac():
query = """ SELECT
-- Combine metadata with building info
buildings.cartodb_id,
apdata.jacksqft,
apdata.sqftjack,
apdata.buildingarea,
apdata.orig_area,
apdata.jackcount,
buildings.name,
ST_AsGeoJSON(buildings.the_geom) AS the_geom,
ST_AsGeoJSON(st_centroid(buildings.the_geom)) AS centroid,
buildings.building_n
FROM
buildings
LEFT JOIN
(
-- Get a ratio of room area to jack count
SELECT
binfo.building,
binfo.buildingarea,
binfo.orig_area,
jinfo.jackcount,
(jinfo.jackcount / binfo.buildingarea) AS jacksqft,
(binfo.buildingarea / jinfo.jackcount ) AS sqftjack
FROM
(
-- Get the area of all the floors with
SELECT
b.building_n AS building,
CASE count(fc.floors)
WHEN 0 THEN 0
ELSE sum(b.shape_area)
END AS orig_area,
CASE count(fc.floors)
WHEN 0 THEN 0
ELSE sum(ST_Area(ST_Transform(b.the_geom,3857)))
END AS buildingarea
FROM
buildings b
LEFT JOIN (
SELECT DISTINCT
split_part(jack,'-',2) AS building,
split_part(jack,'-',3) AS floors
FROM access_points
WHERE
split_part(jack,'-',2) ~ '^[0-9]+$'
) fc ON (fc.building = b.building_n)
GROUP BY b.building_n
) binfo,
(
-- Get a jack count of each building
SELECT
split_part(jack,'-',2) AS building,
count(jack) AS jackcount
FROM access_points
WHERE
split_part(jack,'-',2) ~ '^[0-9]+$'
GROUP BY split_part(jack,'-',2)
) jinfo
WHERE
binfo.building = jinfo.building
) apdata ON (apdata.building = buildings.building_n)
ORDER BY apdata.jacksqft
"""
# Extract just the jacks/area and jenks it
rows = dbconn.run_query(query).fetchall()
jacksqft = []
for row in rows:
if(row['jacksqft'] != None):
jacksqft.append(row['jacksqft'])
breaks = jenks.getJenksBreaks(jacksqft, 16)
# Add that info into the resulting data
for row in rows:
if(row['jacksqft'] == None):
row['jenks'] = None
else:
row['jenks'] = jenks.classify(row['jacksqft'], breaks)
geojson = dbconn.array_to_geojson(rows)
dbconn.send_array_as_json(geojson)
FROM access_points
WHERE
split_part(jack,'-',2) ~ '^[0-9]+$'
) fc ON (fc.building = b.building_n)
GROUP BY b.building_n
) binfo,
(
-- Get a jack count of each building
SELECT
split_part(jack,'-',2) AS building,
count(jack) AS jackcount
FROM access_points
WHERE
split_part(jack,'-',2) ~ '^[0-9]+$'
GROUP BY split_part(jack,'-',2)
) jinfo
WHERE
binfo.building = jinfo.building
"""
# Extract just the jacks/area and jenks it
rows = dbconn.run_query(q).fetchall()
jacksqft = []
buildings = []
for row in rows:
if (row['jacksqft'] != None):
jacksqft.append(row['jacksqft'])
breaks = jenks.getJenksBreaks(jacksqft, 5)
dbconn.send_array_as_json(breaks)
FROM access_points
WHERE
split_part(jack,'-',2) ~ '^[0-9]+$'
) fc ON (fc.building = b.building_n)
GROUP BY b.building_n
) binfo,
(
-- Get a jack count of each building
SELECT
split_part(jack,'-',2) AS building,
count(jack) AS jackcount
FROM access_points
WHERE
split_part(jack,'-',2) ~ '^[0-9]+$'
GROUP BY split_part(jack,'-',2)
) jinfo
WHERE
binfo.building = jinfo.building
"""
# Extract just the jacks/area and jenks it
rows = dbconn.run_query(q).fetchall();
jacksqft = []
buildings = []
for row in rows:
if(row['jacksqft'] != None):
jacksqft.append(row['jacksqft'])
breaks = jenks.getJenksBreaks(jacksqft,5)
dbconn.send_array_as_json(breaks)
.format(projectId=projectId))
segmentLengths = {}
permeabilityValues = {}
for segment in cursor.fetchall():
segmentId = segment[0]
permeabilityValues[segmentId] = 0
longitudes = segment[1].split(',')
latitudes = segment[2].split(',')
length = haversine.haversine(float(longitudes[0]), float(latitudes[0]),
float(longitudes[1]),
float(latitudes[1])) # km
segmentLengths[segmentId] = length
# compute permeability
for origin, paths in shortestPaths.iteritems():
for destination, path in paths.iteritems():
multipliedDistance = segmentLengths[int(path[0])] * segmentLengths[int(
path[-1])]
for segment in path:
permeabilityValues[int(segment)] = permeabilityValues[int(
segment)] + multipliedDistance
breaks = jenks.getJenksBreaks(permeabilityValues.values(), 5)
permeabilityValues = [{
'segment_id': segment,
'breakNum': jenks.classify(permeability, breaks),
'permeability': permeability
} for segment, permeability in permeabilityValues.items()]
print json.dumps(permeabilityValues)
firstRow = True
for row in datacsv:
if firstRow:
firstRow = False
for col in row:
headers.append(col)
else:
for col in row:
if is_number(col):
values.append(col)
except IOError:
print("Could not open input file")
# Calculate breaks
jenksBreaks = jenks.getJenksBreaks(values, numberOfJenksBreaks)
print "JenkBreaks:",
print jenksBreaks # [0, '0.308', '0.396', '0.489', '0.584', '0.674', '0.755', '0.843', 0.955]
#Loop through all rows and cols and convert any numerical values to a color
bmap = brewer2mpl.get_map(colorMap, 'sequential', numberOfJenksBreaks)
colors = bmap.hex_colors
#bmap.colorbrewer2()
try:
with open(inputFile, 'rb') as csvfile:
datacsv = csv.reader(csvfile, delimiter=',', quotechar='"')
firstRow = True
for row in datacsv:
if firstRow:
firstRow = False
projectId=projectId
)
)
segmentLengths = {}
permeabilityValues = {}
for segment in cursor.fetchall():
segmentId = segment[0]
permeabilityValues[segmentId] = 0
longitudes = segment[1].split(",")
latitudes = segment[2].split(",")
length = haversine.haversine(
float(longitudes[0]), float(latitudes[0]), float(longitudes[1]), float(latitudes[1])
) # km
segmentLengths[segmentId] = length
# compute permeability
for origin, paths in shortestPaths.iteritems():
for destination, path in paths.iteritems():
multipliedDistance = segmentLengths[int(path[0])] * segmentLengths[int(path[-1])]
for segment in path:
permeabilityValues[int(segment)] = permeabilityValues[int(segment)] + multipliedDistance
breaks = jenks.getJenksBreaks(permeabilityValues.values(), 5)
permeabilityValues = [
{"segment_id": segment, "breakNum": jenks.classify(permeability, breaks), "permeability": permeability}
for segment, permeability in permeabilityValues.items()
]
print json.dumps(permeabilityValues)
firstRow = True
for row in datacsv:
if firstRow:
firstRow = False
for col in row:
headers.append(col)
else:
for col in row:
if is_number(col):
values.append(col)
except IOError:
print ("Could not open input file")
# Calculate breaks
jenksBreaks = jenks.getJenksBreaks(values, numberOfJenksBreaks)
print "JenkBreaks:",
print jenksBreaks # [0, '0.308', '0.396', '0.489', '0.584', '0.674', '0.755', '0.843', 0.955]
# Loop through all rows and cols and convert any numerical values to a color
bmap = brewer2mpl.get_map(colorMap, "sequential", numberOfJenksBreaks)
colors = bmap.hex_colors
# bmap.colorbrewer2()
try:
with open(inputFile, "rb") as csvfile:
datacsv = csv.reader(csvfile, delimiter=",", quotechar='"')
firstRow = True
for row in datacsv:
if firstRow:
firstRow = False
