--- title: "Climbing Route Topos" description: "Climbing-route topo registry API: photo uploads with thumbnails, grayscale copies, grade bands and index-backed crag lookups" url: "https://www.pixeltable.com/recipes/climbing-routes" source: "https://github.com/pierrebrunelle/pixeltable-climbing-routes" --- # Climbing Route Topos Climbing-route topo registry API: photo uploads with thumbnails, grayscale copies, grade bands and index-backed crag lookups One `app.py` with 1 table, 6 computed columns, 7 HTTP routes. Needs `pixeltable[serve]>=0.7.16`. ## Tables ### routes (table) | Column | Kind | Type or expression | | --- | --- | --- | | route_id | primary key | `pxtf.uuid.uuid7()` | | name | stored | String | | crag | stored | String | | grade | stored | String | | style | stored | String | | bolts | stored | Int (optional) | | length_m | stored | Float (optional) | | topo | stored | Image | | thumb | computed on insert | `topo.resize((96, 128))` | | gray | computed on insert | `topo.convert('L')` | | aspect | computed on insert | `aspect_band(topo)` | | channel | computed on insert | `dominant_channel(topo)` | | band | computed on insert | `grade_band(grade)` | | tag | computed on insert | `topo_tag(aspect, channel, style)` | - B-tree index on `crag` - B-tree index on `grade` - B-tree index on `style` - B-tree index on `band` ## Routes (service `climbing_api`) - `POST /routes`: inserts into `routes` (takes name, crag, grade, style, bolts, length_m; returns route_id, aspect, channel, band, tag) - `POST /analyze`: computes `routes` columns without storing (takes grade, style; returns aspect, channel, band, tag) - `POST /routes/delete`: deletes from `routes` - `GET /routes/by-crag`: runs the query `by_crag` (takes crag) - `GET /routes/by-band`: runs the query `by_grade_band` (takes band, style) - `GET /routes/thumb`: runs the query `route_thumb` (takes name) - `GET /routes/detail`: runs the query `route_detail` (takes name) ## Run it locally ```sh git clone https://github.com/pierrebrunelle/pixeltable-climbing-routes cd pixeltable-climbing-routes uv sync uv run pxt schema update app.py climbing uv run python seed.py climbing uv run pxt service run app.py climbing --port 8000 ``` ## Run it on Pixeltable Cloud ```sh uv run pxt new # a free database, no account # put the pxt:// address it prints in pixeltable.toml uv run pxt db update pxt://:main -f uv run pxt schema update app.py pxt://:main -f uv run pxt service update app.py pxt://:main -f ``` ## app.py At commit `4437f390` (https://github.com/pierrebrunelle/pixeltable-climbing-routes/blob/4437f390075179c0af7d443374810063f4a831c8/app.py): ```python """Climbing Route Topo Registry API built with Pixeltable. Upload a route topo photo with crag, grade and style. Pixeltable stores the image, derives a thumbnail and grayscale copy as media computed columns, and tags aspect, dominant color and a combined topo label via UDFs. B-tree indexes on crag, grade and style back the lookup queries the API exposes. Layout: UDFs live in udfs.py; this file holds table models, @pxt.query functions and routes. Run locally: pxt schema update app.py climbing pxt service run app.py climbing """ import pixeltable as pxt import pixeltable.functions as pxtf from pixeltable.serving import FastAPIRouter from udfs import aspect_band, dominant_channel, grade_band, topo_tag # ---- tables ---- TableModel = pxt.model_base() class Routes(TableModel, name='routes', has_default_idxs=False): route_id = pxt.Column(value=pxtf.uuid.uuid7(), primary_key=True) name: pxt.String crag: pxt.String grade: pxt.String # e.g. 5.10a style: pxt.String # sport / trad / boulder / alpine bolts: pxt.Int | None length_m: pxt.Float | None topo: pxt.Image # generated media thumb = topo.resize((96, 128)) gray = topo.convert('L') # UDF chain aspect = aspect_band(topo) channel = dominant_channel(topo) band = grade_band(grade) tag = topo_tag(aspect, channel, style) __indexes__ = [ pxt.BtreeIndex(crag), pxt.BtreeIndex(grade), pxt.BtreeIndex(style), pxt.BtreeIndex(band), ] # ---- queries ---- @pxt.query def by_crag(crag: str): """Routes at one crag (crag index), hardest grade first.""" return ( Routes.where(Routes.crag == crag) .select(Routes.route_id, Routes.name, Routes.grade, Routes.style, Routes.tag, Routes.band) .order_by(Routes.grade, asc=False) ) @pxt.query def by_grade_band(band: str, style: str): """Routes in a grade band and style (band + style indexes).""" return ( Routes.where((Routes.band == band) & (Routes.style == style)) .select(Routes.route_id, Routes.name, Routes.crag, Routes.grade, Routes.tag) .order_by(Routes.name) ) @pxt.query def route_thumb(name: str): """Thumbnail for a route name (served as an image file).""" return ( Routes.where(Routes.name == name) .order_by(Routes.route_id, asc=False) .limit(1) .select(Routes.thumb) ) @pxt.query def route_detail(name: str): """One route's metadata and media tags.""" return Routes.where(Routes.name == name).select( Routes.route_id, Routes.name, Routes.crag, Routes.grade, Routes.style, Routes.bolts, Routes.length_m, Routes.aspect, Routes.channel, Routes.band, Routes.tag, ) # ---- routes ---- climbing_api = FastAPIRouter(name='climbing_api') climbing_api.add_insert_route( Routes, path='/routes', inputs=[Routes.name, Routes.crag, Routes.grade, Routes.style, Routes.bolts, Routes.length_m], uploadfile_inputs=[Routes.topo], outputs=[Routes.route_id, Routes.aspect, Routes.channel, Routes.band, Routes.tag], ) climbing_api.add_compute_route( Routes, path='/analyze', inputs=[Routes.grade, Routes.style], uploadfile_inputs=[Routes.topo], outputs=[Routes.aspect, Routes.channel, Routes.band, Routes.tag], ) climbing_api.add_delete_route(Routes, path='/routes/delete') climbing_api.add_query_route(path='/routes/by-crag', query=by_crag, method='get') climbing_api.add_query_route(path='/routes/by-band', query=by_grade_band, method='get') climbing_api.add_query_route( path='/routes/thumb', query=route_thumb, method='get', one_row=True, return_fileresponse=True ) climbing_api.add_query_route(path='/routes/detail', query=route_detail, method='get', one_row=True) ```