Climbing Route Topos

1"""Climbing Route Topo Registry API built with Pixeltable.
2
3Upload a route topo photo with crag, grade and style. Pixeltable stores the image,
4derives a thumbnail and grayscale copy as media computed columns, and tags aspect,
5dominant color and a combined topo label via UDFs. B-tree indexes on crag, grade and
6style back the lookup queries the API exposes.
7
8Layout: UDFs live in udfs.py; this file holds table models, @pxt.query functions and routes.
9
10Run locally:
11    pxt schema update app.py climbing
12    pxt service run app.py climbing
13"""
14import pixeltable as pxt
15import pixeltable.functions as pxtf
16from pixeltable.serving import FastAPIRouter
17
18from udfs import aspect_band, dominant_channel, grade_band, topo_tag
19
20# ---- tables ----
21TableModel = pxt.model_base()
22
23
24class Routes(TableModel, name='routes', has_default_idxs=False):
25    route_id = pxt.Column(value=pxtf.uuid.uuid7(), primary_key=True)
26    name: pxt.String
27    crag: pxt.String
28    grade: pxt.String  # e.g. 5.10a
29    style: pxt.String  # sport / trad / boulder / alpine
30    bolts: pxt.Int | None
31    length_m: pxt.Float | None
32    topo: pxt.Image
33
34    # generated media
35    thumb = topo.resize((96, 128))
36    gray = topo.convert('L')
37
38    # UDF chain
39    aspect = aspect_band(topo)
40    channel = dominant_channel(topo)
41    band = grade_band(grade)
42    tag = topo_tag(aspect, channel, style)
43
44    __indexes__ = [
45        pxt.BtreeIndex(crag),
46        pxt.BtreeIndex(grade),
47        pxt.BtreeIndex(style),
48        pxt.BtreeIndex(band),
49    ]
50
51
52# ---- queries ----
53@pxt.query
54def by_crag(crag: str):
55    """Routes at one crag (crag index), hardest grade first."""
56    return (
57        Routes.where(Routes.crag == crag)
58        .select(Routes.route_id, Routes.name, Routes.grade, Routes.style, Routes.tag, Routes.band)
59        .order_by(Routes.grade, asc=False)
60    )
61
62
63@pxt.query
64def by_grade_band(band: str, style: str):
65    """Routes in a grade band and style (band + style indexes)."""
66    return (
67        Routes.where((Routes.band == band) & (Routes.style == style))
68        .select(Routes.route_id, Routes.name, Routes.crag, Routes.grade, Routes.tag)
69        .order_by(Routes.name)
70    )
71
72
73@pxt.query
74def route_thumb(name: str):
75    """Thumbnail for a route name (served as an image file)."""
76    return (
77        Routes.where(Routes.name == name)
78        .order_by(Routes.route_id, asc=False)
79        .limit(1)
80        .select(Routes.thumb)
81    )
82
83
84@pxt.query
85def route_detail(name: str):
86    """One route's metadata and media tags."""
87    return Routes.where(Routes.name == name).select(
88        Routes.route_id, Routes.name, Routes.crag, Routes.grade, Routes.style,
89        Routes.bolts, Routes.length_m, Routes.aspect, Routes.channel, Routes.band, Routes.tag,
90    )
91
92
93# ---- routes ----
94climbing_api = FastAPIRouter(name='climbing_api')
95
96climbing_api.add_insert_route(
97    Routes,
98    path='/routes',
99    inputs=[Routes.name, Routes.crag, Routes.grade, Routes.style, Routes.bolts, Routes.length_m],
100    uploadfile_inputs=[Routes.topo],
101    outputs=[Routes.route_id, Routes.aspect, Routes.channel, Routes.band, Routes.tag],
102)
103climbing_api.add_compute_route(
104    Routes,
105    path='/analyze',
106    inputs=[Routes.grade, Routes.style],
107    uploadfile_inputs=[Routes.topo],
108    outputs=[Routes.aspect, Routes.channel, Routes.band, Routes.tag],
109)
110climbing_api.add_delete_route(Routes, path='/routes/delete')
111climbing_api.add_query_route(path='/routes/by-crag', query=by_crag, method='get')
112climbing_api.add_query_route(path='/routes/by-band', query=by_grade_band, method='get')
113climbing_api.add_query_route(
114    path='/routes/thumb', query=route_thumb, method='get', one_row=True, return_fileresponse=True
115)
116climbing_api.add_query_route(path='/routes/detail', query=route_detail, method='get', one_row=True)