Catchment：用一個資料庫回答地點的五個問題

"Can I rent here?" is not a question. It is five questions wearing a trench coat.

Which ward is this? Which zone? How far is the nearest lake? Is there a hospital close enough? Is there a school close enough? In Hyderabad you answer that today by opening a municipal PDF, a ward map, a maps app for distances, and then making a decision on vibes anyway.

Catchment answers the whole stack from a single shape. Tap a point or draw an area, and it comes back with a verdict plus the real metres behind every rule. Then it reverses the question: give it your rules once and it searches the entire city for every place that passes.

This is the technical story of building it, including the four things that behaved differently from how I assumed, because those are the parts worth writing down.

Why a spatial database and not a spreadsheet

The naive version of this app is a JSON file with polygons and a loop. It works for 145 wards. It falls apart the moment you ask "what is within 500 metres of this point" for 2,566 places, because you have turned a query into a computation the browser has to survive.

The whole argument for Catchment is that the distance, the containment and the threshold comparison are questions a database should answer. So the profile of the project is deliberately shaped around the least used corner of Appwrite TablesDB: polygon and point columns, spatial indexes, and the intersects and distanceLessThan operators.

That decision has a cost. It means the app cannot lie about where its numbers come from, because the query that produced them is part of the answer. That turned out to be the best feature in the product.

The data

Real boundaries, real places, rebuilt from source:

• 145 wards, 5 zones, and the city boundary from DataMeet, Greater Hyderabad Municipal Corporation, derived from OpenStreetMap
• 320 water bodies and 2,566 places (hospitals, clinics, schools, colleges, stations) from OpenStreetMap through the Overpass API
• 1,624 candidate cells generated as a grid over the city, each measured against water, hospitals, schools and transit at build time
• Basemap from OpenFreeMap, geocoding from Nominatim, both keyless

npm run fixtures re-fetches all of it, repairs multipart geometry, simplifies rings and rebuilds the grid. The raw API responses are committed so a clone can rebuild offline.

The schema

Seven tables, twelve indexes, five of them spatial.

The load bearing decision is in water. The exact geometry of every lake lives in polys_json, and the boundary column holds a convex hull of all parts. That split exists because of the first bug below.

The three queries that make it work

One: which ward contains this shape. Polygons are queried by intersection, and a drawn area can touch several wards at once, which is the correct answer rather than an edge case.

const rows = await this.rows("wards", [
  Query.intersects("boundary", ring),
  Query.select(["name", "number", "zone", "area_km2"]),
]);

Two: how far is the nearest water. This one had to be asked twice, for reasons covered below.

const ring = bufferRing(point, metres);
const [touching, centred] = await Promise.all([
  this.rows("water", [Query.intersects("boundary", ring), select]),
  this.rows("water", [Query.distanceLessThan("center", point, metres), select]),
]);

const merged = new Map<string, Water>();
for (const row of [...touching, ...centred]) merged.set(String(row.$id), toWater(row));

const inRange = [...merged.values()].filter((w) => distToPolysM(point, w.polys) <= metres);

The database narrows the candidates. The metre figure on screen is then computed from the geometry that came back with the row. That division of labour is the whole design: index for speed, geometry for truth.

Three: the reverse query. Each cell stores the four distances it was measured against, so the city wide search is an ordinary indexed comparison instead of 1,624 geometry tests at request time.

const rows = await this.rows("cells", [
  Query.greaterThanEqual("water_m", rules.waterMinM),
  Query.lessThanEqual("hospital_m", rules.hospitalMaxM),
  Query.lessThanEqual("school_m", rules.schoolMaxM),
  Query.greaterThanEqual("station_m", rules.transitMaxM),
]);

With the default "live somewhere" rules, 546 of 1,624 places pass. That number is in the tests, so if a refactor quietly changes it, the suite says so.

Four things that did not behave as expected

1. A lake with four parts is not a lake with one part

Hussain Sagar is one of the largest lakes in the city, 3.55 km². Its OpenStreetMap geometry is a relation with four polygon parts. The loader stored polys[0][0], the first ring of the first part, which is a 27 point sliver.

The insert succeeded, the row count was right, the index reported available, and every spatial query near the lake returned nothing. The app did the worst possible thing: it looked fine.

The fix is a convex hull over every part for the search column, while the exact geometry stays in polys_json for measurement. A hull is a superset, so the index can over return but never miss, and the metre figure is still exact.

const searchRing = (polys: number[][][][]) => {
  const points: number[][] = [];
  for (const part of polys) for (const point of part[0]) points.push(point);
  return convexHull(points);
};

Osman Sagar's stored ring went from about 200 points to 16, and the queries started answering.

2. Distance operators are for points

Query.distanceLessThan("boundary", point, metres) on a polygon column does not throw. It returns zero rows.

That is worth reading twice, because if your code has a fallback that quietly computes locally when the database returns nothing, your app will be correct on your machine and subtly wrong for everyone else, and no test will notice unless you write one that can.

The right answer is to ask the polygon column something it can answer, which is intersection, and to complement it with a centre distance query on a second spatial index.

3. Intersection is boundary based, not containment

The complement query exists because of this: a query polygon is treated as an outline. A pond that sits entirely inside your 500 metre circle never touches the circle's edge, so intersects alone does not return it. Ask both questions, union the results, then measure exactly.

There is a test for precisely this, and it is small enough to be unambiguous: a 0.026 km² pond called Erra Kunta, a test point 250 metres east of it, and a 500 metre search radius. The intersection returns nothing. The centre query returns the pond at 157 metres. The app gets the union.

4. Small schema details that reject big features

Two Appwrite constraints cost time and are worth knowing before you start:

• A spatial index cannot be built on a nullable column. If your geometry columns are optional, index creation fails with column_index_invalid, and the fix is to mark the column required and repopulate.
• The bulk rows endpoint takes row objects, not { rowId, data } wrappers. My first loader wrote the wrapper, and the API answered "Missing required attribute name" because it saw no attributes at all. The setup script now probes which row shape the server accepts and keeps the one that works.

There is also a cloud level trap: an Appwrite Cloud project is pinned to one region, and the wrong endpoint answers 401 Project is not accessible in this region. npm run detect:region probes every region with the project ID and saves the one that answers, which turns a confusing auth error into one command.

Design constraints, not decoration

The brief was that a 70 year old and a 17 year old should both be able to use this without being taught. That became engineering constraints:

• 17px base type with three text sizes, so enlarging does not break the layout
• 46 to 56px targets, with a hard 44px minimum enforced by scripts/audit.mjs at five viewport widths
• AA contrast, measured rather than eyeballed
• No icon only buttons, no long press, no hidden gesture: every action is a word
• Pass and fail stated in text, never colour alone
• A first visit hint that disappears on first use
• The verdict card scrolls itself into view on a phone, because a result below the fold is a result nobody sees

Accessibility regressions are build failures here, not polish. That is the only way they survive contact with a deadline.

Making the database visible

Every verdict carries a receipt. Under each result there is a Show control that lists the table, the operator and the milliseconds behind that number. It is not a debug mode. It is the argument of the product: the distance is real, the boundary is real, and here is the query that produced it.

That choice also made the project testable in a way that is hard to fake. npm run verify:appwrite runs 13 checks against the live project, each printing a timing, and exits non zero when anything fails:

pass  spatial: water bodies near Hussain Sagar  (118 ms)  1 rows · nearest Hussain Sagar at 0 m (3.55 km²)
pass  spatial: a pond entirely inside the searched circle  (357 ms)  Erra Kunta at 157 m
pass  spatial: hospitals and clinics within 2 km of Charminar  (185 ms)  43 rows
pass  thresholds: cells at least 300 m from water and within 1 km of a hospital  (256 ms)  1031 of 1624 cells
pass  spatial: wards overlapping a drawn ring  (116 ms)  6 rows · Ward 91 Khairatabad, Ward 88 Bholakpur

Those lines are the demo. Not because they prove the app works, but because they show what kind of app it is.

What I would build next

The honest next steps, in order of how much they would teach:

1. Saved rulesets with row permissions. Let people keep "our rules" as a row that belongs to them, not to a server key. That is the natural place to use per user permissions properly.
2. A public audit endpoint. Given the inputs of a shared verdict, recompute it. A result you can hand to someone and have them check is worth more than a screenshot.
3. Offline sync. The last verdict and the rules surviving a train tunnel is a real use case for someone checking a place on site.
4. More rule dimensions. Flood extent, noise, metro proximity. The engine is a list of measurements, so the hard part is data, not code.
5. VectorsDB over place descriptions to answer "somewhere like this neighbourhood", which is the question that follows "which places pass" once people start using it.

Run it

git clone https://github.com/harishkotra/catchment.git
cd catchment
npm install
npm run dev

No keys needed. Add Appwrite credentials and the header switches from Local cache to Appwrite TablesDB, and the same queries run against the live database.

The code is MIT licensed, the data is ODbL, and the parts that took the longest to learn are the parts documented in the README. If you are building anything spatial on TablesDB, start from the four gotchas above and save yourself an evening.

Code & more: https://www.dailybuild.xyz/project/296-catchment