# Mashkof (משקוף) SQL API — complete reference for LLMs and agents

> Read-only SQL over every Israeli real-estate transaction reported to the Israel Tax Authority since 1998, plus precomputed statistics and public enrichment data. Schema 1.2, deals through 2026-09-17, statistics windows end at 2026-06-30. Generated 2026-09-28T14:59:55Z from the live schema and 100 verified examples.

Contents: 1. How to call the API · 2. Rules for correct answers · 3. Common mistakes · 4. Schema (every table and column) · 5. meta keys · 6. Example queries · 7. Attribution

## 1. How to call the API

Endpoint: `https://mashkof.pov.sh/api/sql` — public, no key, CORS enabled.

```
GET  https://mashkof.pov.sh/api/sql?q=<url-encoded SQL>&format=json|csv|md&limit=N
POST https://mashkof.pov.sh/api/sql   Content-Type: application/json   {"sql": "...", "params": [...] | {...}, "format": "json", "limit": 1000}
POST https://mashkof.pov.sh/api/sql   Content-Type: text/plain         <the SQL itself>
```

Parameters:

- `q` (GET query, string, required): The SQL statement (URL-encoded). Alias: sql.
- `sql` (POST JSON body, string, required): The SQL statement. A text/plain POST body is also accepted as the SQL itself.
- `params` (POST body / GET query (JSON), array | object): Bound parameters: an array for ? / ?NNN placeholders, or an object for :name / @name / $name placeholders (on GET: URL-encoded JSON). Prefer them over string concatenation.
- `format` (GET query / POST body, json | csv | md): Response format. json (default) is structured; csv is text/csv with a header row; md is a Markdown table — the most token-efficient for LLMs.
- `limit` (GET query / POST body, integer): Maximum rows returned (default 1,000, max 10,000). Extra rows are cut and truncated = true. Your own LIMIT still applies.

JSON response (200):

- `columns` ({name, type}[]): Result columns in order; type is the declared SQLite type when the column maps to a table column, else null.
- `rows` (any[][]): Rows as arrays, aligned with columns. NULL → null. Integers beyond 2^53 are returned as strings.
- `row_count` (number): Number of rows in rows.
- `truncated` (boolean): true when more rows existed than limit.
- `limit` (number): The row limit that was applied.
- `elapsed_ms` (number): Query execution time in milliseconds.
- `cached` (boolean): true when the result came from the server's result cache (identical GET requests).
- `schema_version` (string): meta.schema_version of the database (e.g. "1.2").
- `data_as_of` (string): Date of the latest deal in the database, YYYY-MM-DD (meta.max_date).
- `warnings` (string[]?): Optional advice about the query (e.g. a full scan of deals). Read it.

Example: `{"columns":[{"name":"name","type":"TEXT"},{"name":"median_ppsqm_12m","type":"INTEGER"}],"rows":[["תל אביב-יפו",56882]],"row_count":1,"truncated":false,"limit":1000,"elapsed_ms":0.4,"schema_version":"1.2","data_as_of":"2026-09-17"}`

format=md returns a Markdown table followed by a footer line such as `_12 rows · 3 ms_` (the most compact for LLM context); format=csv returns CSV with a header row. Both expose X-Row-Count, X-Truncated and X-Elapsed-Ms headers.

Errors are JSON `{"error": {"code", "message", "hint"?}}`:

- 400 `BAD_REQUEST`: Missing SQL, invalid JSON body, bad format / limit / params.
- 400 `TOO_LONG`: The SQL is longer than 20,000 characters.
- 400 `MULTIPLE_STATEMENTS`: More than one statement. Send exactly one (a trailing ; is fine).
- 400 `NOT_READ_ONLY`: The statement would write. Only SELECT / WITH / VALUES / EXPLAIN are accepted.
- 400 `FORBIDDEN`: A forbidden construct: ATTACH, PRAGMA, load_extension(), writes, temp objects, etc.
- 400 `SQL_ERROR`: SQLite rejected the query (syntax error, unknown table/column…). message has SQLite's text; hint often suggests a fix.
- 408 `TIMEOUT`: The query ran longer than 10 s and was killed. Filter deals through an index or use the agg_* tables.
- 429 `RATE_LIMITED`: Too many requests from your IP. Wait for the Retry-After header (seconds) before retrying.
- 503 `BUSY`: All query workers are busy. Retry after a few seconds (Retry-After).
- 503 `DB_UNAVAILABLE`: The database is being rebuilt or is temporarily unavailable. Retry later.
- 500 `INTERNAL_ERROR`: Unexpected server error. Retry once; if it persists, simplify the query.

Limits:

- Access: Public, read-only, no API key. CORS: Access-Control-Allow-Origin: *
- Statements: Exactly one read-only statement: SELECT, WITH … SELECT, VALUES, EXPLAIN [QUERY PLAN]
- Timeout: 10 seconds per query (the query is killed)
- Rows: default 1,000, max 10,000 per response (truncated: true beyond it)
- Size: SQL up to 20,000 characters; responses up to 8 MB; cells up to 100,000 characters
- Rate limit: Per IP: 60 requests / minute (bursts of 20), 30 s of query time / minute, 6 queries in flight. On 429 wait Retry-After seconds. Cached GET results are free — batch questions into one query where you can.
- Introspection: PRAGMA statements are blocked; use pragma_table_info('<table>'), pragma_index_list(…) etc. inside a SELECT, or GET /api/sql/schema.
- Caching: GET responses are cacheable for 5 minutes (Cache-Control: public, max-age=300). Prefer GET for repeated reads.

Other endpoints:

- `GET https://mashkof.pov.sh/api/sql/schema` — this schema as JSON (`?format=md` for Markdown).
- `GET https://mashkof.pov.sh/api/sql/examples` — the examples of section 6 as JSON.
- `GET https://mashkof.pov.sh/openapi.json` — OpenAPI 3.1.

curl (GET):

```bash
curl -s "https://mashkof.pov.sh/api/sql?format=md&q=SELECT%20name%2C%20median_ppsqm_12m%2C%20n_ppsqm_12m%20FROM%20settlements%20WHERE%20rank_ppsqm%20IS%20NOT%20NULL%20ORDER%20BY%20rank_ppsqm%20LIMIT%205"
```

Python:

```python
import requests

API = "https://mashkof.pov.sh/api/sql"

def sql(query, params=None, fmt="json", limit=1000):
    r = requests.post(API, json={"sql": query, "params": params or [], "format": fmt, "limit": limit}, timeout=30)
    body = r.json() if fmt == "json" else r.text
    if r.status_code != 200:
        raise RuntimeError(body["error"] if fmt == "json" else body)
    return body

res = sql("""
    SELECT s.name, a.year, a.median_ppsqm, a.n_ppsqm
    FROM agg_settlement_year a JOIN settlements s ON s.code = a.settlement_code
    WHERE a.settlement_code IN (5000, 3000, 4000)
      AND a.property_group = 'apartment' AND a.year >= 2015 AND a.is_incomplete = 0
    ORDER BY s.name, a.year
""")
cols = [c["name"] for c in res["columns"]]
for row in res["rows"]:
    print(dict(zip(cols, row)))
```

Suggested system prompt for an agent using this API:

```text
You can query Mashkof (משקוף), a read-only SQLite database of every Israeli real-estate transaction reported to the Israel Tax Authority since 1998 (3.18M deals) plus prices, neighbourhoods, streets, transit, schools, rents and macro data.

Before writing any SQL, fetch and read https://mashkof.pov.sh/llms-full.txt (API, rules, full schema, 100 verified examples).

Run queries with:
  GET  https://mashkof.pov.sh/api/sql?q=<url-encoded SQL>&format=md
  POST https://mashkof.pov.sh/api/sql  {"sql": "...", "params": [...], "format": "json"}

Rules that matter:
- One read-only statement per request; 10 s timeout; max 10,000 rows. Always add LIMIT. Rate limit: 60 requests/min per IP; on HTTP 429 wait Retry-After seconds.
- deals has 3.18M rows: filter it by an indexed column (settlement_code, deal_date, property_group, gush+chelka) or use the precomputed agg_* / settlements / gushim / neighborhoods tables for medians and trends.
- Price statistics use in_stats = 1 (₪/m² also needs price_per_sqm IS NOT NULL). Never average medians.
- Time windows end at meta.stats_anchor_date (2026-06-30), not today; agg_* rows with is_incomplete = 1 are partial months.
- Money is ₪ (INTEGER), areas m², dates 'YYYY-MM-DD'. Place names are Hebrew; look settlements up by code (Tel Aviv 5000, Jerusalem 3000, Haifa 4000) or via settlements_fts.
- Cite numbers with their n (deal count) and period, and say the data comes from the Israel Tax Authority via Mashkof.
```

## 2. Rules for correct answers

### Engine & dialect

SQLite 3.53.4 (better-sqlite3), opened read-only. Standard SQLite SQL: CTEs, window functions, json_each()/json_extract(), FTS5 MATCH/bm25(), R*Tree. Math functions (sqrt, pow, sin, cos, radians, …) are available; median(x), percentile(x, p) and percentile_cont(x, f) aggregates exist in this build (not in stock SQLite, so keep queries portable when you can); there is no REGEXP. Only one read-only statement (SELECT / WITH / EXPLAIN / VALUES) per request; use POST for long SQL (GET URLs over ~16 KB are rejected with HTTP 431); PRAGMA statements are blocked — introspect with SELECT * FROM pragma_table_info('deals') (also pragma_index_list, pragma_index_info, pragma_table_list) or sqlite_schema.

### Types, units and NULLs

Money is INTEGER shekels (₪, ILS). Areas are m². Price per m² (ppsqm) is ₪/m². Distances are metres (straight line). Booleans are INTEGER 0/1. *_pct columns are percent points (3.8 = +3.8%). NULL means unknown or not applicable — never 0.

### Dates and periods

Dates are TEXT 'YYYY-MM-DD' and compare correctly as strings (deal_date >= '2025-07-01'). Months are 'YYYY-MM', quarters 'YYYY-Qn' (sort correctly). Deals cover 1998-01-01 … 2026-09-17. Use substr(deal_date, 1, 7) for the month.

### Hebrew text

Place names are Hebrew (UTF-8, logical order) in the raw registry spelling: ASCII " and ' are used as gershayim/geresh ('ניר ח"ן', 'ג'ת'), e.g. 'תל אביב-יפו', 'ירושלים', 'חיפה', 'באר שבע', 'קריית שמונה', 'הרצלייה' (note: many names use קריית, some קרית; Herzliya is spelled הרצלייה). Match exact names with =, or search with the FTS5 tables. settlements.name_en has English names. English → code shortcuts: Tel Aviv-Yafo 5000, Jerusalem 3000, Haifa 4000, Rishon LeZion 8300, Petah Tikva 7900, Ashdod 70, Netanya 7400, Be'er Sheva 9000, Holon 6600, Bnei Brak 6100, Ramat Gan 8600, Rehovot 8400, Herzliya 6400, Kfar Saba 6900, Ra'anana 8700, Modi'in 1200, Eilat 2600.

### Identifiers and join keys

settlement_code = CBS locality code (settlements.code). A property is located by (gush, chelka, sub_chelka): gush = cadastral block, chelka = parcel, sub_chelka = unit (0 = none). (gush, chelka) joins deals ↔ parcels and every per-parcel enrichment table. nbhd_id joins neighbourhoods, streets.id joins streets. parcels.id is a build-local rowid used only for parcels_rtree.

### The stat rule (in_stats)

Residential price statistics use only deals.in_stats = 1: residential group, not an outlier, not multi-unit, not a discount lottery project, a full deal (portion = 1) and deal_amount ≥ ₪100k (₪50k before 2005). ₪/m² statistics additionally need price_per_sqm IS NOT NULL. Counts (deals, deals_12m, …) include EVERY row. For non-residential groups in_stats is 0; apply is_outlier = 0 AND is_multi_unit = 0 AND is_full_deal = 1 AND deal_amount >= floor (10k; commercial 50k) yourself.

### ppsqm = price per square metre

price_per_sqm (deals) and median_ppsqm / median_ppsqm_* (summaries) are ₪ per m² of the unit's registered area, residential full deals only. They are the standard comparison metric between places. Hide a median when its n (n_ppsqm*) is < 5 and flag 'few deals' when < 20.

### Time windows end at the stats anchor

meta.stats_anchor_date = 2026-06-30 (the last complete month). Window columns: 12m = 2025-07-01…2026-06-30, prev12m = 2024-07-01…2025-06-30, 24m = 2024-07-01…2026-06-30, prev24m = 2022-07-01…2024-06-30, 5y = 2021-07-01…2026-06-30. Never compute 'last 12 months' from today or from max_date; read bounds from meta: (SELECT value FROM meta WHERE key = 'window_12m_start').

### Incomplete months (is_incomplete)

Deals are reported with a lag, so months after the anchor (from 2026-07; quarter 2026-Q3; year 2026) are incomplete and geographically biased. Every agg_* row in that period has is_incomplete = 1: filter is_incomplete = 0 for trends, or label them partial. They are not a price or volume drop.

### Price changes are existing-stock changes

ppsqm_change_pct (settlements, districts, neighborhoods) and change_pct (gushim) compare existing-stock (is_new_build = 0) medians between windows and are NULL unless both windows have ≥ 50 (gushim: 20) such deals. Use them instead of dividing pooled medians, which swing with the new-build mix. Never average medians across cells or years.

### Partial deals, outliers, multi-unit rows

deal_amount is the price of the sold share: when is_full_deal = 0 (portion < 1) it is not comparable with full prices. is_outlier (with outlier_reason) and is_multi_unit (with multi_unit_kind) rows are kept for completeness but excluded from statistics; for 'most expensive / cheapest deal' lists add is_outlier = 0 AND is_multi_unit = 0 AND is_full_deal = 1.

### Discount lottery projects

is_discount_project = 1 marks units sold at a subsidised price in lottery programmes (מחיר למשתכן, מחיר מטרה, דירה בהנחה): real sales, not in statistics. discount_source = 'official' rows link to discount_projects via discount_project_id; 'heuristic' rows were found by price rules. settlements.n_discount_12m / gushim.n_discount_24m count them.

### Geo precision

Every deal of a (gush, chelka) shares one point (deals.lat/lon = parcels.lat/lon). geo_precision: 'parcel' (91%; parcels.geo_source 'parcel' = exact, 'cancelled'/'shuma' = approximate), 'gush' (8.8%, the gush centroid) or 'settlement' (0.02%, the town centre — not a location, excluded from parcels_rtree). Addresses come from OpenStreetMap (parcel_address): street_source = 'osm_nearest_street' is only a nearby street.

### Full-text search (FTS5) with Hebrew

settlements_fts (name, aliases; rowid = settlements.code), streets_fts (street, settlement, aliases; rowid = streets.id; contentless) and neighborhoods_fts (name, settlement, aliases; rowid = nbhd_id; contentless). Tokenizer unicode61 remove_diacritics 2 with prefix indexes. Syntax: WHERE settlements_fts MATCH '"באר"*' (quoted token + * = prefix), AND-ed tokens: '"רמת" "גן"'; column filter: 'street:"הרצל" AND settlement:"רחובות"'. Remove ASCII " and ' from user text first (ת"א → תא) — a bare quote is FTS5 syntax. Order with bm25(settlements_fts, 5.0, 1.0) (lower is better). Contentless tables return NULL columns: join the base table.

### Bounding boxes (R*Tree)

SELECT p.gush, p.chelka, p.lat, p.lon FROM parcels_rtree r JOIN parcels p ON p.id = r.id WHERE r.minLat >= :south AND r.maxLat <= :north AND r.minLon >= :west AND r.maxLon <= :east. For deals in a box join deals ON d.gush = p.gush AND d.chelka = p.chelka (or use the deals_rtree view). Keep boxes small (a few km) and add a date filter.

### Performance

Queries time out after 10 s and results are capped (default 1,000 rows, max 10,000). deals has 3.18M rows: always filter it by an indexed column and add LIMIT. Indexes on deals: ix_deals_amount(deal_amount); ix_deals_date(deal_date, id, settlement_code, …); ix_deals_discount_project(discount_project_id); ix_deals_group_amount(property_group, deal_amount); ix_deals_group_date(property_group, deal_date, id, …); ix_deals_gush_chelka(gush, chelka, deal_date); ix_deals_ppsqm(price_per_sqm, id, settlement_code, …); ix_deals_settlement_amount(settlement_code, deal_amount); ix_deals_settlement_date(settlement_code, deal_date, id, …); ix_deals_settlement_group_date(settlement_code, property_group, deal_date, …); ix_deals_settlement_ppsqm(settlement_code, price_per_sqm). Prefer agg_settlement_quarter/year, agg_national_*, agg_district_*, agg_gush_year, agg_neighborhood_year and the summary columns of settlements / gushim / parcels / neighborhoods / streets — they answer most statistics questions in < 1 ms. count(*) over all deals is fine (~30 ms); a GROUP BY over all deals without an indexed filter takes seconds. Check plans with EXPLAIN QUERY PLAN.

### Pseudo-groups and buckets in agg_* tables

property_group has the 9 real groups plus 'all_residential' (the 4 residential groups) and 'all' (every deal; medians NULL). rooms_bucket has '1-2', '3', '4', '5', '6+' and 'all' (totals incl. unknown rooms). Always pin both dimensions (e.g. property_group = 'all_residential' AND rooms_bucket = 'all') or sums will double count. Cells without deals are absent.

### Real (inflation-adjusted) prices

macro_month.real_factor converts a nominal ₪ amount of that month to June-2026 ₪: JOIN macro_month m ON m.month = substr(d.deal_date, 1, 7) and multiply. For yearly series use the year's mean factor.

### Attribution

Source: Israel Tax Authority real-estate transactions (public). Enrichment: OpenStreetMap contributors (ODbL), CBS, Bank of Israel, government ministries (see meta.sources). Data is provided as-is without warranty; it is not an appraisal.

Current window bounds (inclusive):

- 12m: 2025-07-01 … 2026-06-30
- prev12m: 2024-07-01 … 2025-06-30
- 24m: 2024-07-01 … 2026-06-30
- prev24m: 2022-07-01 … 2024-06-30
- 5y: 2021-07-01 … 2026-06-30

## 3. Common mistakes

### Medians come precomputed

An average of medians is not a median. Read the yearly cell, or compute from deals (in_stats = 1) for a custom slice.

Avoid:

```sql
SELECT avg(median_ppsqm) FROM agg_settlement_quarter WHERE settlement_code = 5000 AND quarter LIKE '2025-%'
```

Prefer:

```sql
SELECT median_ppsqm, n_ppsqm FROM agg_settlement_year
WHERE settlement_code = 5000 AND property_group = 'all_residential' AND year = 2025
```

### Windows end at the stats anchor

Recent months are incomplete (reporting lag). Every statistic in the DB uses windows that end at meta.stats_anchor_date.

Avoid:

```sql
SELECT count(*) FROM deals WHERE settlement_code = 5000 AND deal_date >= date('now', '-12 months')
```

Prefer:

```sql
SELECT count(*) FROM deals
WHERE settlement_code = 5000
  AND deal_date BETWEEN (SELECT value FROM meta WHERE key = 'window_12m_start')
                    AND (SELECT value FROM meta WHERE key = 'window_12m_end')
```

### Pin both aggregate dimensions

agg_* tables contain pseudo-groups ('all', 'all_residential') and a rooms_bucket 'all' total — summing every row double counts.

Avoid:

```sql
SELECT sum(deals) FROM agg_national_quarter WHERE quarter = '2025-Q4'
```

Prefer:

```sql
SELECT deals FROM agg_national_quarter
WHERE quarter = '2025-Q4' AND property_group = 'all' AND rooms_bucket = 'all'
```

### Filter deals through an index

A full scan of 3.18M deals takes seconds and can time out. Start from an indexed column or an aggregate table.

Avoid:

```sql
SELECT settlement, count(*) FROM deals WHERE rooms = 4 GROUP BY settlement
```

Prefer:

```sql
SELECT s.name, a.deals FROM agg_settlement_year a JOIN settlements s ON s.code = a.settlement_code
WHERE a.property_group = 'apartment' AND a.year = 2025 ORDER BY a.deals DESC LIMIT 20
```

### Use the stat rule for prices

Without in_stats the 'cheapest apartment' is a ₪1 transfer, a 1% share or a subsidised lottery unit. A deal_date range (not year) lets the index do the work.

Avoid:

```sql
SELECT min(deal_amount) FROM deals WHERE settlement_code = 5000 AND property_group = 'apartment' AND year = 2025
```

Prefer:

```sql
SELECT min(deal_amount) FROM deals
WHERE settlement_code = 5000 AND property_group = 'apartment'
  AND deal_date BETWEEN '2025-01-01' AND '2025-12-31' AND in_stats = 1
```

### Strip quotes before FTS5 MATCH

A bare " is FTS5 syntax. Remove gershayim / geresh from user text, quote each token and add * for prefix search.

Avoid:

```sql
SELECT rowid FROM settlements_fts WHERE settlements_fts MATCH 'ת"א'
```

Prefer:

```sql
SELECT s.code, s.name FROM settlements_fts f JOIN settlements s ON s.code = f.rowid
WHERE settlements_fts MATCH '"תא"*'
ORDER BY bm25(settlements_fts, 5.0, 1.0) - 2 * log(1 + s.deals_total) LIMIT 5
```

## 4. Schema

49 tables and views (701 columns). SQLite 3.53.4. No foreign keys are declared; the joins listed are logical. FTS5 shadow tables (*_fts_data, …) and rtree node tables are internal and omitted.

Key relationships:

- deals.settlement_code → settlements.code (many-to-one)
- deals.(gush, chelka) → parcels.(gush, chelka) (many-to-one)
- deals.gush → gushim.gush (many-to-one)
- deals.property_group → property_groups.key (many-to-one)
- deals.discount_project_id → discount_projects.project_id (many-to-one)
- settlements.district → districts.name (many-to-one)
- gushim.settlement_code → settlements.code (many-to-one)
- parcels.gush → gushim.gush (many-to-one)
- parcels.id → parcels_rtree.id (one-to-one)
- settlements_fts.rowid → settlements.code (one-to-one)
- agg_settlement_*.settlement_code → settlements.code (many-to-one)
- agg_gush_year.gush → gushim.gush (many-to-one)
- agg_district_*.district → districts.name (many-to-one)
- neighborhoods.settlement_code → settlements.code (many-to-one)
- parcel_neighborhood.(gush, chelka) → parcels.(gush, chelka) (one-to-one)
- parcel_neighborhood.nbhd_id → neighborhoods.nbhd_id (many-to-one)
- agg_neighborhood_year.nbhd_id → neighborhoods.nbhd_id (many-to-one)
- streets.settlement_code → settlements.code (many-to-one)
- street_parcels.street_id → streets.id (many-to-one)
- street_parcels.(gush, chelka) → parcels.(gush, chelka) (many-to-one)
- parcel_address.(gush, chelka) → parcels.(gush, chelka) (one-to-one)
- parcel_address.street_id → streets.id (many-to-one)
- loc_metrics_parcel.(gush, chelka) → parcels.(gush, chelka) (one-to-one)
- loc_metrics_gush.gush → gushim.gush (one-to-one)
- loc_metrics_*.rail_station_id → poi_stations.id (many-to-one)
- settlement_socio.settlement_code → settlements.code (one-to-one)
- parcel_stat_area_socio.stat_area_id → stat_area_socio.stat_area_id (many-to-one)
- crime_settlement_year.settlement_code → settlements.code (many-to-one)
- rent_city.settlement_code → settlements.code (many-to-one)
- gross_yield.settlement_code → settlements.code (many-to-one)
- agg_national_month.month → macro_month.month (many-to-one)
- renewal_compounds.settlement_code → settlements.code (many-to-one)
- parcel_renewal.compound_id → renewal_compounds.compound_id (many-to-one)
- gush_renewal.gush → gushim.gush (one-to-one)
- discount_projects.settlement_code → settlements.code (many-to-one)
- discount_project_parcels.project_id → discount_projects.project_id (many-to-one)

| table | kind | rows | description |
|---|---|---|---|
| deals | table | 3,175,970 | One row per unique real-estate transaction reported to the Israel Tax Authority, 1998-01-01 … meta.max_date (3.18M rows) |
| settlements | table | 1,139 | One row per CBS settlement (locality) that has deals (1,139): names, admin hierarchy, geography and precomputed deal counts and price statistics over windows that end at the stats anchor |
| districts | table | 7 | The 7 CBS districts (מחוזות) with deal counts and residential medians over the 12m / prev12m windows. |
| gushim | table | 12,408 | One row per cadastral block (gush) with deals (12,408): dominant settlement, centroid, and deal counts / residential ₪/m² medians over 24m, prev24m and 5y windows. |
| parcels | table | 381,177 | One row per (gush, chelka) with deals (381k): the map point shared by all its deals, point precision, deal counts, the last deal and 5y residential medians. |
| agg_settlement_quarter | table | 439,396 | Precomputed per settlement × property_group × rooms_bucket × quarter: deal counts, median price, median ₪/m², median area, money volume |
| agg_settlement_year | table | 98,509 | Precomputed per settlement × property_group × year (no rooms dimension): counts, medians, volume. |
| agg_national_month | table | 3,708 | National monthly series per property_group: counts, medians, volume |
| agg_national_quarter | table | 3,875 | National quarterly series per property_group × rooms_bucket. |
| agg_national_year | table | 1,007 | National yearly series per property_group × rooms_bucket. |
| agg_district_quarter | table | 763 | Per district × quarter, RESIDENTIAL ONLY (all_residential) — there is no property_group column |
| agg_district_year | table | 2,009 | Per district × property_group × year (all groups plus all_residential and all). |
| agg_gush_year | table | 191,306 | Per gush × year: deals counts ALL groups; the n/median columns are residential stats |
| agg_neighborhood_year | table | 32,280 | Per neighbourhood × year (1998–2026): deal counts and residential medians, including an existing-stock median. |
| settlements_fts | fts5 | 1,139 | FTS5 full-text index over settlement names and aliases (spelling variants, abbreviations like תא / בש / פת, historic names, English names) |
| streets_fts | fts5 | 31,687 | Contentless FTS5 index over streets (columns street, settlement, aliases) |
| neighborhoods_fts | fts5 | 1,277 | Contentless FTS5 index over neighbourhood names (name, settlement, aliases) |
| parcels_rtree | rtree | 380,764 | R*Tree spatial index of located parcel points (geo_precision 'parcel' or 'gush'; settlement-centre points excluded) |
| deals_rtree | view | 3,175,302 | VIEW behaving like an rtree over deals: parcels_rtree ⋈ parcels ⋈ deals, id = deals.id |
| streets | table | 31,687 | One row per (settlement, street) derived from OpenStreetMap (31,687 streets in 663 settlements) with house-number range, extent and deal statistics of the street's parcels (addressed or nearby). |
| street_parcels | table | 259,266 | Every DB parcel of a street (259k rows) |
| parcel_address | table | 245,790 | OpenStreetMap address (or nearby named street) per DB parcel (245,790 rows) |
| parcel_buildings | table | 226,621 | OSM building summary for every DB parcel with at least one building (226,621 rows), including parcels without an address. |
| neighborhoods | table | 1,277 | 1,277 neighbourhoods in 182 settlements (municipal polygons for Tel Aviv, Jerusalem, Petah Tikva; CBS 2011 statistical areas elsewhere; OSM for small towns) with deal counts and residential price statistics like settlements. |
| parcel_neighborhood | table | 288,727 | Neighbourhood containing each parcel's DB point (288,727 rows). |
| loc_metrics_parcel | table | 381,177 | Location metrics for every DB parcel (381,177 rows): distances to rail / light rail / schools / sea, bus service, transit score, elevation, airport noise |
| loc_metrics_gush | table | 12,407 | The same location metrics computed at each gush centroid (12,407 rows). |
| poi_stations | table | 371 | 371 heavy-rail and light-rail stations (operating, planned, under construction) with weekday service from GTFS. |
| poi_schools | table | 28,312 | 28,312 educational institutions (Ministry of Education) with type, sector, supervision and location |
| poi_bus_stops | table | 35,085 | 35,085 GTFS bus stops with weekday trips and number of lines |
| settlement_socio | table | 1,110 | CBS 2021 socio-economic index per settlement (1,110 rows): cluster 1 (lowest) … 10 (highest), index value and rank. |
| stat_area_socio | table | 1,641 | CBS 2021 socio-economic index per statistical area (1,641 areas in 81 cities / local councils). |
| parcel_stat_area_socio | table | 233,599 | Statistical-area socio-economic cluster per parcel (233,599 parcel-precision points inside an indexed area) |
| crime_settlement_year | table | 1,235 | Israel Police crime case files per settlement and year, 2021–2025 (217 settlements), with per-1,000 rates and category counts. |
| crime_station_year | table | 448 | Crime case files per police station and year (2021–2025), including cases without a settlement (roads, open areas). |
| macro_month | table | 345 | Monthly macro series 1998-01 … 2026-09: CPI and the real-price factor, CBS dwelling price indices, rent index, Bank of Israel rate, prime, mortgage rates, average wage. |
| macro_district_month | table | 630 | CBS dwelling price index per district and month (from 2017-10), linked to the national index. |
| rent_city | table | 4,354 | CBS average monthly free-market rent of CURRENT tenancies (not asking rents) for the country, 6 districts and 18 large cities; quarterly 2019-Q1 … 2026-Q2 and yearly 2018–2025. |
| gross_yield | table | 922 | Indicative gross rental yield per city / district / nation: median existing-stock apartment price vs 12 × average rent, for the 12m window and years 2019–2025. |
| affordability | table | 237 | Median apartment price ÷ national average monthly wage, national and per district, yearly 1998–2025 plus the 12m window. |
| renewal_compounds | table | 978 | 978 declared urban-renewal compounds (מתחמי התחדשות עירונית: פינוי-בינוי / עיבוי) with status, plan, units and location |
| parcel_renewal | table | 26,239 | Parcels inside a renewal compound (26,239 rows). |
| gush_renewal | table | 953 | Urban-renewal summary per gush (953 rows): compounds, approvals and units allocated by area share. |
| discount_projects | table | 2,087 | 2,087 official subsidised-housing lottery projects (מחיר למשתכן / מחיר מטרה / דירה בהנחה) from the Housing ministry tracker and Israel Land Authority tenders, with units, official ₪/m², lottery dates, location and deals attributed. |
| discount_project_parcels | table | 7,275 | Parcels (or whole gushim when chelka is NULL) of each discount project (7,275 rows). |
| discount_match | table | 8,157 | Validation table of the discount-project flag per gush × deal year × official project: flagged vs unflagged counts and medians. |
| property_groups | table | 9 | The 9 normalised property groups (deals.property_group) with Hebrew labels, residential flag, display order, icon name and deal count. |
| deal_natures | table | 47 | Mapping of the 47 raw Hebrew transaction types (deals.deal_nature, מהות) to a property_group, with deal counts. |
| meta | table | 89 | Key/value build metadata (all values TEXT; CAST as needed, some are JSON): schema_version, built_at, min_date/max_date, stats_anchor_date, window_*_start/end, national_* headline statistics, thresholds, counts and data sources. |

### Core: deals & places

Transactions and the entities they belong to.

#### deals

Table · 3,175,970 rows · primary key (id)

One row per unique real-estate transaction reported to the Israel Tax Authority, 1998-01-01 … meta.max_date (3.18M rows). Duplicates are already removed and split-share rows merged, so never de-duplicate. The registry has no street addresses: a location is settlement + gush/chelka/sub_chelka plus one map point per parcel. This is the only big table — always filter it through an index (see indexes) and prefer the agg_* / summary tables for statistics.

- Price statistics: residential medians use in_stats = 1 (and price_per_sqm IS NOT NULL for ₪/m²). Counts include every row.
- deal_amount is the price of the SOLD SHARE; when is_full_deal = 0 do not compare it with full prices.
- Time windows end at meta.stats_anchor_date (2026-06-30), not at max_date or today; months after the anchor are incomplete.
- Filter by settlement_code / deal_date / property_group / (gush, chelka) / discount_project_id so an index is used; a full scan takes ~1–3 s.
- Prefer the precomputed medians in agg_* / settlements / gushim / neighborhoods. For custom slices this SQLite build has median(x) and percentile(x, p) aggregates (wrap in round() to match the stored integer medians); always report n alongside.

Joins: settlement_code → settlements.code; property_group → property_groups.key; deal_nature → deal_natures.raw; discount_project_id → discount_projects.project_id; gush → gushim.gush; gush, chelka → parcels.(gush, chelka)

| column | type | null | description | example |
|---|---|---|---|---|
| id | INTEGER | no | Primary key = the source CSV line number (stable across rebuilds, not dense: gaps where duplicates were removed or rows merged). Safe for URLs. | 3011433 |
| settlement_code | INTEGER | yes | CBS locality code → settlements.code. NULL for 1,065 deals (unresolved regional-council labels): group by code, display settlement. | 5000 |
| settlement | TEXT | yes | Canonical Hebrew locality name (Tax Authority spelling), identical for every row of a code; for NULL-code rows the cleaned raw label. NULL for 190 rows. Raw registry spelling with ASCII " and ' as gershayim (e.g. 'ניר ח"ן'). | תל אביב-יפו |
| settlement_raw | TEXT | yes | Original label only when the row was re-assigned to another locality (historic alias like 'קרית חיים' → חיפה, inference from the gush, or a look-alike label fixed by the gush polygon). NULL otherwise (~99%). | צהל |
| gush | INTEGER | no | Cadastral block (גוש). Indexed with chelka (ix_deals_gush_chelka). | 6768 |
| chelka | INTEGER | no | Parcel (חלקה) within the gush. (gush, chelka) → parcels. | 5 |
| sub_chelka | INTEGER | no | Sub-parcel (תת-חלקה, roughly the unit/apartment). 0 = none. | 108 |
| deal_date | TEXT | no | Transaction date, TEXT 'YYYY-MM-DD' (1998-01-01 … 2026-09-17). Compare as strings: deal_date >= '2025-07-01'. | 2026-05-01 |
| year | INTEGER | no | Year of deal_date (INTEGER). | 2026 |
| quarter | TEXT | no | Quarter of deal_date, TEXT 'YYYY-Qn' (e.g. '2025-Q4'); sorts correctly as a string. | 2026-Q2 |
| deal_amount | INTEGER | no | Deal value (שווי מכירה) in ₪ for the SOLD SHARE (see portion). 1 … 8.9e9. For merged rows the sum of the merged shares. | 7555000 |
| declared_amount | INTEGER | yes | Declared value (שווי מוצהר) in ₪. Equals deal_amount in ~92% of rows. NULL when the source said 0 (4,464 rows). | 7555000 |
| deal_nature | TEXT | no | Raw Hebrew transaction type (מהות), one of 47 values → deal_natures.raw (mapped to property_group). | דירה בבית קומות |
| property_group | TEXT | no | Normalised property type key → property_groups.key: apartment, garden_apartment, penthouse, house (residential) or land, commercial, agriculture, parking, other. Values: 'apartment', 'garden_apartment', 'penthouse', 'house', 'land', 'commercial', 'agriculture', 'parking', 'other'. | apartment |
| is_residential | INTEGER | no | 1 for apartment, garden_apartment, penthouse and house; else 0. | 1 |
| portion | REAL | yes | Share of the property sold (חלק נמכר), 0.001–1. 1 = whole unit. NULL = unknown (77k rows; the source said 0). | 1 |
| is_full_deal | INTEGER | no | 1 when portion = 1 (a whole-unit sale). | 1 |
| year_built | INTEGER | yes | Year of construction, 1850 … deal year + 5 (off-plan sales can be in the future). NULL when unknown (~793k rows). | 2031 |
| is_new_build | INTEGER | no | 1 when residential and year_built >= year − 1 (new or off-plan, 'דירה חדשה'); 0 otherwise, including unknown year_built. | 1 |
| area | REAL | yes | Area in m². For apartments the registered (usually net) unit area; for land the WHOLE parcel, not the sold share. NULL when unknown or implausible (~636k rows). | 139 |
| rooms | REAL | yes | Number of rooms (half rooms allowed), 1–20. NULL when unknown (~943k rows). Ignore on non-residential rows. | 5 |
| rooms_bucket | TEXT | yes | '1-2' (< 3), '3' (3–3.5), '4' (4–4.5), '5' (5–5.5), '6+' (≥ 6). NULL for non-residential rows or unknown rooms. Values: '1-2', '3', '4', '5', '6+'. | 5 |
| price_per_sqm | INTEGER | yes | ₪ per m² (INTEGER), only for residential FULL deals with a plausible area and price (~1.8M rows); NULL otherwise (always NULL for land/commercial/other). | 54353 |
| lat | REAL | yes | Latitude (WGS84) of the parcel point shared by every deal of this (gush, chelka); never NULL. See geo_precision. | 32.110498 |
| lon | REAL | yes | Longitude (WGS84) of the parcel point; never NULL. | 34.797737 |
| geo_precision | TEXT | yes | How precise the point is: 'parcel' (91%: the parcel's own point, exact or approximate — see parcels.geo_source), 'gush' (8.8%: gush centroid) or 'settlement' (0.02%: settlement centre; not a location). Values: 'parcel', 'gush', 'settlement'. | parcel |
| is_outlier | INTEGER | no | 1 when an outlier rule fired (58,558 rows). Outliers are never in_stats. | 0 |
| outlier_reason | TEXT | yes | Comma-separated outlier rules. Price/area level: ppsqm_iqr, ppsqm_out_of_band, price_iqr. Amount not credible: low_residential_amount, nominal_amount, amount_vs_declared, declared_nominal, uncorroborated_amount, implied_value, implausible_amount, sentinel_amount. NULL when not an outlier. | ppsqm_out_of_band |
| is_multi_unit | INTEGER | no | 1 for a bulk / non-market group deal (71,985 rows): several units sharing gush, date and amount, portfolios, kibbutz privatisations. Never in_stats. | 0 |
| multi_unit_kind | TEXT | yes | Kind of multi-unit row: same_price_units (mostly genuine per-unit prices), pair_total_price, flat_price_batch, portfolio, privatization (kibbutz שיוך דירות at nominal prices). NULL otherwise. Values: 'same_price_units', 'pair_total_price', 'flat_price_batch', 'portfolio', 'privatization'. | same_price_units |
| multi_unit_n | INTEGER | yes | How many distinct units share (gush, date, amount). 1 = unique; max 181. | 1 |
| in_stats | INTEGER | no | 1 = the row enters residential price medians: residential, not outlier, not multi-unit, not discount project, full deal (portion = 1) and deal_amount ≥ ₪100k (≥ ₪50k before 2005). Always 0 for non-residential groups. ~1.79M rows. | 1 |
| first_seen | TEXT | yes | ISO-8601 UTC time the scraper first saw the row (all 2026-09-18/19). Useless for 'new deals' — use deal_date. | 2026-09-18T10:41:08Z |
| is_discount_project | INTEGER | no | 1 = a unit sold in a subsidised lottery project (דירה בהנחה / מחיר למשתכן / מחיר מטרה), found by official lottery data or price rules (86,927 rows). Real sales at a reduced price; never in_stats. Badge them 'reduced price'. | 0 |
| merged_rows | INTEGER | yes | Number of source rows (2–4 split shares of one sale) merged into this full deal; NULL for ordinary rows. | 2 |
| discount_source | TEXT | yes | Why is_discount_project = 1: 'official' (matched to an official lottery project, see discount_project_id) or 'heuristic' (price rules only). NULL when not flagged. Values: 'official', 'heuristic'. | official |
| discount_project_id | TEXT | yes | discount_projects.project_id for 'official' discount rows (partial index ix_deals_discount_project). NULL otherwise. | moch:10 |

Indexes: ix_deals_amount(deal_amount); ix_deals_date(deal_date, id, settlement_code, property_group, rooms, deal_amount, price_per_sqm, is_outlier, is_multi_unit); ix_deals_discount_project(discount_project_id) WHERE discount_project_id IS NOT NULL; ix_deals_group_amount(property_group, deal_amount); ix_deals_group_date(property_group, deal_date, id, rooms, deal_amount, price_per_sqm, is_outlier, is_multi_unit); ix_deals_gush_chelka(gush, chelka, deal_date); ix_deals_ppsqm(price_per_sqm, id, settlement_code, property_group, deal_date, rooms, deal_amount, is_outlier, is_multi_unit) WHERE price_per_sqm IS NOT NULL; ix_deals_settlement_amount(settlement_code, deal_amount); ix_deals_settlement_date(settlement_code, deal_date, id, property_group, rooms, deal_amount, price_per_sqm, is_outlier, is_multi_unit); ix_deals_settlement_group_date(settlement_code, property_group, deal_date, id, rooms, deal_amount, price_per_sqm, is_outlier, is_multi_unit); ix_deals_settlement_ppsqm(settlement_code, price_per_sqm) WHERE price_per_sqm IS NOT NULL

#### settlements

Table · 1,139 rows · primary key (code)

One row per CBS settlement (locality) that has deals (1,139): names, admin hierarchy, geography and precomputed deal counts and price statistics over windows that end at the stats anchor. The fastest way to answer 'price level / change in city X'.

- Look settlements up by code, or by name via settlements_fts (names are raw registry spellings: 'תל אביב-יפו', 'קריית שמונה', 'ניר ח"ן').
- median_* columns cover ALL residential groups (stat rule). Apartment-only values are *_apartment_12m.
- ppsqm_change_pct is an existing-stock change gated at ≥ 50 deals in both windows — NULL for small places. Never compute median_ppsqm_12m / median_ppsqm_prev12m yourself.
- Windows: 12m = 2025-07-01…2026-06-30, prev12m = 2024-07-01…2025-06-30, 5y = 2021-07-01…2026-06-30 (meta.window_*).

Joins: district → districts.name; code → settlements_fts.rowid

| column | type | null | description | example |
|---|---|---|---|---|
| code | INTEGER | no | Primary key: CBS locality code (סמל יישוב), e.g. 5000 = Tel Aviv-Yafo, 3000 = Jerusalem, 4000 = Haifa. Also the rowid of settlements_fts. | 5000 |
| name | TEXT | no | Canonical Hebrew name (same as deals.settlement), raw registry spelling (ASCII " and ' possible). | תל אביב-יפו |
| slug | TEXT | no | URL slug (UNIQUE): spaces/dashes → '-', quotes/geresh/parentheses/dots removed, e.g. 'תל-אביב-יפו'. | תל-אביב-יפו |
| name_en | TEXT | yes | English name (CBS), e.g. 'Tel Aviv - Yafo'. NULL when unknown. | Tel Aviv - Yafo |
| district | TEXT | yes | District (מחוז) in CBS form: 'הצפון', 'חיפה', 'המרכז', 'תל אביב', 'ירושלים', 'הדרום', 'יהודה והשומרון' → districts.name. NULL for a few small West Bank localities. Values: 'הצפון', 'הדרום', 'המרכז', 'חיפה', 'ירושלים', 'יהודה והשומרון', 'תל אביב'. | תל אביב |
| subdistrict | TEXT | yes | Sub-district (נפה), Hebrew. | תל אביב |
| lat | REAL | yes | Official settlement centre latitude (WGS84). Never NULL. | 32.084449 |
| lon | REAL | yes | Official settlement centre longitude (WGS84). Never NULL. | 34.791693 |
| bbox_min_lat | REAL | yes | Map extent (south). Polygon box when has_polygon = 1, else deal-point box; always contains lat/lon. | 32.029336 |
| bbox_min_lon | REAL | yes | Map extent (west). | 34.739149 |
| bbox_max_lat | REAL | yes | Map extent (north). | 32.146967 |
| bbox_max_lon | REAL | yes | Map extent (east). | 34.852262 |
| has_polygon | INTEGER | yes | 1 when a boundary polygon exists (used by the choropleth). | 1 |
| muni_type | TEXT | yes | Municipal type: 'עירייה' (city), 'מועצה מקומית' (local council), 'מועצה אזורית' (regional council), 'מועצה מקומית תעשייתית', 'ללא שיפוט'. NULL for some. Values: 'מועצה אזורית', 'מועצה מקומית', 'עירייה', 'ללא שיפוט', 'מועצה מקומית תעשייתית'. | עירייה |
| settlement_kind | TEXT | yes | Settlement kind: 'יישוב עירוני' (urban), 'יישוב כפרי' (rural), 'מוקד תעסוקה' (employment zone), 'מקום'. Values: 'יישוב כפרי', 'יישוב עירוני', 'מקום', 'מוקד תעסוקה'. | יישוב עירוני |
| regional_council | TEXT | yes | Regional council name for rural localities; NULL for independent municipalities. | לכיש |
| municipality | TEXT | yes | Name of the local authority the locality belongs to. | תל אביב - יפו |
| muni_code | INTEGER | yes | Code of the local authority. | 5000 |
| population | INTEGER | yes | Current population (Population Authority, else CBS 2023). NULL for 20. | 601640 |
| area_km2 | REAL | yes | Polygon area in km². NULL without a polygon. | 57.178 |
| year_founded | INTEGER | yes | Year founded. May be NULL. | 1909 |
| elevation_m | INTEGER | yes | Elevation of the settlement in metres. May be NULL. | 17 |
| deals_total | INTEGER | no | All unique deals, all property groups, all time. | 244806 |
| deals_residential_total | INTEGER | no | Residential deals, all time. | 178358 |
| first_deal | TEXT | yes | Date of the first deal, 'YYYY-MM-DD'. | 1998-01-01 |
| last_deal | TEXT | yes | Date of the latest deal, 'YYYY-MM-DD' (can be after the stats anchor). | 2026-07-26 |
| deals_12m | INTEGER | no | Deals (all groups) in the 12m window ending at the stats anchor. | 7038 |
| deals_residential_12m | INTEGER | no | Residential deals in the 12m window. | 6020 |
| deals_prev12m | INTEGER | no | Deals (all groups) in the prev12m window. Don't present deals_12m / deals_prev12m as an activity change (late reports still arrive). | 8927 |
| n_stats_12m | INTEGER | no | Residential stat deals (in_stats = 1) in 12m behind median_price_12m. | 3504 |
| median_price_12m | INTEGER | yes | Median residential deal price in ₪, 12m, all residential groups. NULL when n_stats_12m = 0. | 4350000 |
| n_ppsqm_12m | INTEGER | no | Residential stat deals with price_per_sqm in 12m. | 3476 |
| median_ppsqm_12m | INTEGER | yes | Headline median ₪/m², all residential groups, 12m (pooled new + existing; discount projects excluded). NULL for ~684 small places. | 56882 |
| n_ppsqm_prev12m | INTEGER | no | Residential stat deals with ppsqm in prev12m. | 3406 |
| median_ppsqm_prev12m | INTEGER | yes | Median residential ₪/m² in prev12m (pooled). | 55800 |
| ppsqm_change_pct | REAL | yes | Existing-stock ₪/m² change in percent points (12m vs prev12m). Set only when both windows have ≥ 50 existing-stock deals (81 settlements); NULL otherwise. | 1.2 |
| median_ppsqm_5y | INTEGER | yes | Median residential ₪/m² over the 5y window. | 54128 |
| n_apartment_12m | INTEGER | no | Apartment-group (property_group = 'apartment') stat deals, 12m. | 3453 |
| median_price_apartment_12m | INTEGER | yes | Median apartment price in ₪, 12m. | 4320194 |
| median_ppsqm_apartment_12m | INTEGER | yes | Median apartment ₪/m², 12m. | 56882 |
| n_4rooms_12m | INTEGER | no | Apartments with 4–4.5 rooms, stat rule, 12m. | 936 |
| median_price_4rooms_12m | INTEGER | yes | Median price in ₪ of 4–4.5-room apartments, 12m. | 5550643 |
| n_house_12m | INTEGER | no | House-group (private houses / cottages) stat deals, 12m. | 8 |
| median_price_house_12m | INTEGER | yes | Median house price in ₪, 12m. | 7250000 |
| n_new_build_12m | INTEGER | no | In-stats residential new-build deals, 12m. | 1789 |
| total_volume_12m | INTEGER | no | Sum of deal_amount in ₪, 12m, all groups, credible amounts only. | 25615885169 |
| avg_area | INTEGER | yes | Mean area (m²) of in-stats residential deals, 12m. NULL when none. | 89 |
| rank_ppsqm | INTEGER | yes | 1 = highest median_ppsqm_12m among settlements with n_ppsqm_12m ≥ 30 from ≥ 5 parcels, none holding > 50% (92 ranked). NULL = unranked. Ties share a rank. | 1 |
| ppsqm_change_method | TEXT | yes | 'existing' when ppsqm_change_pct is set, else NULL. Values: 'existing'. | existing |
| n_ppsqm_new_12m | INTEGER | no | New-build (is_new_build = 1) stat deals with ppsqm, 12m. | 1775 |
| median_ppsqm_new_12m | INTEGER | yes | Median ₪/m² of new builds, 12m. NULL when none. | 59107 |
| n_ppsqm_existing_12m | INTEGER | no | Existing-stock stat deals with ppsqm, 12m. | 1701 |
| median_ppsqm_existing_12m | INTEGER | yes | Median ₪/m² of existing stock (is_new_build = 0), 12m. Show next to median_ppsqm_12m when they differ by > ~10%. | 51471 |
| median_ppsqm_existing_prev12m | INTEGER | yes | Median ₪/m² of existing stock, prev12m (base of ppsqm_change_pct). | 50862 |
| n_discount_12m | INTEGER | no | Discount-project deals in 12m — NOT in the medians. | 173 |
| deals_after_anchor | INTEGER | no | Deals dated after meta.stats_anchor_date reported so far (incomplete). | 36 |
| n_parcels_12m | INTEGER | no | Distinct parcels behind the 12m ppsqm median (rank gate). | 1414 |
| max_parcel_share_12m | REAL | yes | Largest single parcel's share (0–1) of the 12m ppsqm deals (rank gate: must be ≤ 0.5). | 0.155 |

Indexes: ix_settlements_district(district); ix_settlements_rank(rank_ppsqm) WHERE rank_ppsqm IS NOT NULL

#### districts

Table · 7 rows · primary key (name)

The 7 CBS districts (מחוזות) with deal counts and residential medians over the 12m / prev12m windows.

- 'יהודה והשומרון' has only a few hundred deals: the Tax Authority register barely covers the West Bank.

| column | type | null | description | example |
|---|---|---|---|---|
| name | TEXT | no | Primary key: district name in CBS form ('הצפון', 'חיפה', 'המרכז', 'תל אביב', 'ירושלים', 'הדרום', 'יהודה והשומרון'). Joins settlements.district, agg_district_*.district. Values: 'הדרום', 'המרכז', 'הצפון', 'חיפה', 'יהודה והשומרון', 'ירושלים', 'תל אביב'. | תל אביב |
| n_settlements | INTEGER | yes | Number of settlements with deals in the district. | 14 |
| deals_total | INTEGER | yes | All deals, all time. | 662777 |
| deals_12m | INTEGER | yes | Deals (all groups) in the 12m window. | 17755 |
| median_price_12m | INTEGER | yes | Median residential price in ₪, 12m, stat rule. | 3200000 |
| n_ppsqm_12m | INTEGER | yes | Residential stat deals with ppsqm, 12m. | 9477 |
| median_ppsqm_12m | INTEGER | yes | Median residential ₪/m², 12m. | 37500 |
| median_ppsqm_prev12m | INTEGER | yes | Median residential ₪/m², prev12m (pooled). | 36036 |
| ppsqm_change_pct | REAL | yes | Existing-stock ₪/m² change, percent points, 12m vs prev12m; NULL unless both windows have ≥ 50 existing-stock deals. | -0.8 |
| lat | REAL | yes | Mean parcel point latitude (label anchor). | 32.072238 |
| lon | REAL | yes | Mean parcel point longitude (label anchor). | 34.799767 |
| ppsqm_change_method | TEXT | yes | 'existing' when ppsqm_change_pct is set, else NULL. Values: 'existing'. | existing |

#### gushim

Table · 12,408 rows · primary key (gush)

One row per cadastral block (gush) with deals (12,408): dominant settlement, centroid, and deal counts / residential ₪/m² medians over 24m, prev24m and 5y windows.

- 24m = 2024-07-01…2026-06-30, prev24m = 2022-07-01…2024-06-30. change_pct is gated at ≥ 20 existing-stock deals in both windows.

Joins: settlement_code → settlements.code

| column | type | null | description | example |
|---|---|---|---|---|
| gush | INTEGER | no | Primary key: block number. | 6212 |
| settlement_code | INTEGER | yes | Dominant settlement (most coded deals) → settlements.code. NULL for 96. | 5000 |
| lat | REAL | yes | Gush centroid latitude (WGS84). NULL for 1. | 32.094248 |
| lon | REAL | yes | Gush centroid longitude. | 34.787909 |
| geo_source | TEXT | yes | 'gush' (polygon centroid) or 'parcels_mean' (mean of its parcel points when no polygon). Values: 'gush'. | gush |
| has_polygon | INTEGER | no | 1 when the gush polygon exists. | 1 |
| area_m2 | INTEGER | yes | Gush polygon area in m². | 698386 |
| deals_total | INTEGER | no | All deals, all time, all groups. | 5832 |
| deals_24m | INTEGER | no | Deals (all groups) in the 24m window. | 493 |
| deals_5y | INTEGER | no | Deals (all groups) in the 5y window. | 1477 |
| n_ppsqm_24m | INTEGER | no | Residential stat deals with ppsqm, 24m. | 303 |
| median_ppsqm_24m | INTEGER | yes | Median residential ₪/m², 24m. NULL when n = 0. | 65833 |
| n_ppsqm_5y | INTEGER | no | Residential stat deals with ppsqm, 5y. | 745 |
| median_ppsqm_5y | INTEGER | yes | Median residential ₪/m², 5y. | 64964 |
| n_ppsqm_prev24m | INTEGER | no | Residential stat deals with ppsqm, prev24m. | 206 |
| median_ppsqm_prev24m | INTEGER | yes | Median residential ₪/m², prev24m. | 66709 |
| change_pct | REAL | yes | Existing-stock median ₪/m² change, percent points, 24m vs prev24m. Set only when both windows have ≥ 20 existing-stock deals (1,148 gushim). | 1 |
| median_price_24m | INTEGER | yes | Median residential price in ₪, 24m. | 5419000 |
| first_deal | TEXT | yes | First deal date 'YYYY-MM-DD'. | 1998-01-01 |
| last_deal | TEXT | yes | Latest deal date 'YYYY-MM-DD'. | 2026-06-22 |
| change_method | TEXT | yes | 'existing' when change_pct is set, else NULL. Values: 'existing'. | existing |
| n_ppsqm_new_24m | INTEGER | no | New-build residential stat deals with ppsqm, 24m. | 229 |
| n_ppsqm_existing_24m | INTEGER | no | Existing-stock residential stat deals with ppsqm, 24m. | 74 |
| n_discount_24m | INTEGER | no | Discount-project deals in 24m (not in the medians). | 0 |

Indexes: ix_gushim_settlement(settlement_code)

#### parcels

Table · 381,177 rows · primary key (id)

One row per (gush, chelka) with deals (381k): the map point shared by all its deals, point precision, deal counts, the last deal and 5y residential medians.

- (gush, chelka) is UNIQUE and the natural key; id is a build-local rowid used only to join parcels_rtree.
- geo_source 'cancelled' / 'shuma' are approximate points even though geo_precision = 'parcel'.

Joins: settlement_code → settlements.code; gush → gushim.gush; last_deal_id → deals.id; id → parcels_rtree.id

| column | type | null | description | example |
|---|---|---|---|---|
| id | INTEGER | no | Rowid (INTEGER PK) = parcels_rtree.id. Build-local: do not store it; use (gush, chelka). | 91722 |
| gush | INTEGER | no | Block number. | 6212 |
| chelka | INTEGER | no | Parcel number. UNIQUE (gush, chelka). | 418 |
| settlement_code | INTEGER | yes | Dominant settlement of the parcel's deals → settlements.code. NULL for 346. | 5000 |
| lat | REAL | yes | Parcel point latitude (WGS84) — the point every deal of this parcel carries. | 32.093693 |
| lon | REAL | yes | Parcel point longitude (WGS84). | 34.783113 |
| geo_precision | TEXT | yes | 'parcel', 'gush' (gush centroid) or 'settlement' (settlement centre, not a location; excluded from parcels_rtree). Values: 'parcel', 'gush', 'settlement'. | parcel |
| geo_source | TEXT | yes | 'parcel' (current cadastre, exact), 'cancelled' (cancelled parcel placed at its successors, approximate), 'shuma' (unsettled tax parcel, ±70 m), 'gush' or 'settlement'. Values: 'parcel', 'gush', 'cancelled', 'shuma', 'settlement'. | parcel |
| uncertainty_m | INTEGER | yes | Estimated error in metres of a parcel-level point. NULL for gush / settlement points. | 12 |
| deals_total | INTEGER | no | All deals on the parcel, all time. | 5 |
| deals_5y | INTEGER | no | Deals in the 5y window. | 0 |
| deals_residential | INTEGER | no | Residential deals, all time. | 5 |
| n_units | INTEGER | no | Distinct sub_chelka values seen ≈ number of units in the building. | 3 |
| first_deal_date | TEXT | yes | First deal date 'YYYY-MM-DD'. | 2006-09-07 |
| last_deal_date | TEXT | yes | Latest deal date 'YYYY-MM-DD'. | 2015-01-05 |
| last_price | INTEGER | yes | deal_amount (₪) of the most recent deal (the sold share; see last_portion). | 1500000 |
| last_group | TEXT | yes | property_group of the most recent deal. Values: 'apartment', 'land', 'house', 'commercial', 'garden_apartment', 'agriculture', 'other', 'parking', 'penthouse'. | apartment |
| last_portion | REAL | yes | portion of the most recent deal (show when < 1). NULL = unknown. | 1 |
| last_deal_id | INTEGER | yes | deals.id of the most recent deal. | 2580110 |
| n_ppsqm_5y | INTEGER | no | Residential stat deals with ppsqm, 5y. | 0 |
| median_ppsqm_5y | INTEGER | yes | Median residential ₪/m², 5y. NULL for most parcels. | 18182 |
| median_price_5y | INTEGER | yes | Median residential price in ₪, 5y. | 2600000 |
| dominant_group | TEXT | yes | property_group with the most deals on the parcel. Values: 'apartment', 'land', 'house', 'commercial', 'agriculture', 'other', 'garden_apartment', 'parking', 'penthouse'. | apartment |

Indexes: ix_parcels_settlement(settlement_code)

### Precomputed aggregates

Medians, counts and volume by place and period. Use these instead of scanning deals.

#### agg_settlement_quarter

Table · 439,396 rows · primary key (settlement_code, property_group, rooms_bucket, quarter)

Precomputed per settlement × property_group × rooms_bucket × quarter: deal counts, median price, median ₪/m², median area, money volume. The right source for any settlement time series. Cells with no deals are absent.

- Always constrain property_group AND rooms_bucket (rooms_bucket = 'all' for totals).
- Mark or drop is_incomplete = 1 rows (2026-Q3).

Joins: settlement_code → settlements.code; property_group → property_groups.key

| column | type | null | description | example |
|---|---|---|---|---|
| settlement_code | INTEGER | no | CBS locality code (סמל יישוב). Join key → settlements.code. | 5000 |
| quarter | TEXT | no | Quarter 'YYYY-Qn' (1998-Q1 … 2026-Q3). | 2025-Q4 |
| property_group | TEXT | no | Property group key: apartment, garden_apartment, penthouse, house, land, commercial, agriculture, parking, other, plus the pseudo-groups 'all_residential' (the 4 residential groups) and 'all' (every deal; counts/volume only, medians NULL). Values: 'all_residential', 'apartment', 'house', 'all', 'land', 'garden_apartment', 'commercial', 'penthouse', 'agriculture', 'parking', 'other'. | apartment |
| rooms_bucket | TEXT | no | Rooms bucket: '1-2' (rooms < 3), '3' (3–3.5), '4' (4–4.5), '5' (5–5.5), '6+' (≥ 6), or 'all' (every row incl. unknown rooms). Non-residential groups only have 'all'. ALWAYS filter it (use 'all' for the total) or you will double count. Values: 'all', '5', '4', '6+', '3', '1-2'. | all |
| deals | INTEGER | no | Count of ALL unique deals in the cell (partial, outlier, multi-unit and discount-project rows included) = transaction volume. Never NULL. | 1592 |
| n_stats | INTEGER | yes | Number of deals passing the stat rule (in_stats = 1 for residential; group floor rule for non-residential) behind median_price. NULL for property_group = 'all'. | 854 |
| median_price | INTEGER | yes | True median deal_amount in ₪ of the n_stats deals (computed in DuckDB, rounded). NULL when n_stats = 0 or property_group = 'all'. Never average medians across cells. | 4133559 |
| n_ppsqm | INTEGER | yes | Number of stat deals that also have price_per_sqm, behind median_ppsqm. 0 for non-residential groups; NULL for 'all'. | 849 |
| median_ppsqm | INTEGER | yes | True median price per m² (₪/m²) of the n_ppsqm deals. NULL for non-residential groups, for 'all', and when n_ppsqm = 0. Gate on n_ppsqm (hide when < 5, 'few deals' when < 20). | 57447 |
| median_area | INTEGER | yes | Median area (m²) of the stat deals. NULL when none. | 77 |
| total_volume | INTEGER | no | Sum of deal_amount in ₪ over rows with a credible amount (no amount-based outlier reason; not portfolio / pair-total rows). Includes partial deals (the amount actually paid). | 6061499136 |
| is_incomplete | INTEGER | no | 1 when the period ends after meta.stats_anchor_date (2026-07…09, 2026-Q3, year 2026): reporting lag, NOT a real drop in prices or volume. Exclude (is_incomplete = 0) or flag these rows in any trend. | 0 |

#### agg_settlement_year

Table · 98,509 rows · primary key (settlement_code, property_group, year)

Precomputed per settlement × property_group × year (no rooms dimension): counts, medians, volume.

- Year 2026 is incomplete (is_incomplete = 1).

Joins: settlement_code → settlements.code; property_group → property_groups.key

| column | type | null | description | example |
|---|---|---|---|---|
| settlement_code | INTEGER | no | CBS locality code (סמל יישוב). Join key → settlements.code. | 5000 |
| year | INTEGER | no | Calendar year (1998 … 2026). | 2025 |
| property_group | TEXT | no | Property group key: apartment, garden_apartment, penthouse, house, land, commercial, agriculture, parking, other, plus the pseudo-groups 'all_residential' (the 4 residential groups) and 'all' (every deal; counts/volume only, medians NULL). Values: 'all', 'land', 'all_residential', 'house', 'apartment', 'commercial', 'agriculture', 'garden_apartment', 'other', 'parking', 'penthouse'. | apartment |
| deals | INTEGER | no | Count of ALL unique deals in the cell (partial, outlier, multi-unit and discount-project rows included) = transaction volume. Never NULL. | 6253 |
| n_stats | INTEGER | yes | Number of deals passing the stat rule (in_stats = 1 for residential; group floor rule for non-residential) behind median_price. NULL for property_group = 'all'. | 3269 |
| median_price | INTEGER | yes | True median deal_amount in ₪ of the n_stats deals (computed in DuckDB, rounded). NULL when n_stats = 0 or property_group = 'all'. Never average medians across cells. | 4208000 |
| n_ppsqm | INTEGER | yes | Number of stat deals that also have price_per_sqm, behind median_ppsqm. 0 for non-residential groups; NULL for 'all'. | 3225 |
| median_ppsqm | INTEGER | yes | True median price per m² (₪/m²) of the n_ppsqm deals. NULL for non-residential groups, for 'all', and when n_ppsqm = 0. Gate on n_ppsqm (hide when < 5, 'few deals' when < 20). | 56707 |
| median_area | INTEGER | yes | Median area (m²) of the stat deals. NULL when none. | 80 |
| total_volume | INTEGER | no | Sum of deal_amount in ₪ over rows with a credible amount (no amount-based outlier reason; not portfolio / pair-total rows). Includes partial deals (the amount actually paid). | 23210399012 |
| is_incomplete | INTEGER | no | 1 when the period ends after meta.stats_anchor_date (2026-07…09, 2026-Q3, year 2026): reporting lag, NOT a real drop in prices or volume. Exclude (is_incomplete = 0) or flag these rows in any trend. | 0 |

#### agg_national_month

Table · 3,708 rows · primary key (property_group, month)

National monthly series per property_group: counts, medians, volume. month joins macro_month.month.

Joins: month → macro_month.month; property_group → property_groups.key

| column | type | null | description | example |
|---|---|---|---|---|
| month | TEXT | no | Month 'YYYY-MM' (1998-01 … 2026-09). | 2025-12 |
| property_group | TEXT | no | Property group key: apartment, garden_apartment, penthouse, house, land, commercial, agriculture, parking, other, plus the pseudo-groups 'all_residential' (the 4 residential groups) and 'all' (every deal; counts/volume only, medians NULL). Values: 'all', 'all_residential', 'apartment', 'land', 'commercial', 'house', 'parking', 'agriculture', 'other', 'garden_apartment', 'penthouse'. | all_residential |
| deals | INTEGER | no | Count of ALL unique deals in the cell (partial, outlier, multi-unit and discount-project rows included) = transaction volume. Never NULL. | 9136 |
| n_stats | INTEGER | yes | Number of deals passing the stat rule (in_stats = 1 for residential; group floor rule for non-residential) behind median_price. NULL for property_group = 'all'. | 6164 |
| median_price | INTEGER | yes | True median deal_amount in ₪ of the n_stats deals (computed in DuckDB, rounded). NULL when n_stats = 0 or property_group = 'all'. Never average medians across cells. | 2230000 |
| n_ppsqm | INTEGER | yes | Number of stat deals that also have price_per_sqm, behind median_ppsqm. 0 for non-residential groups; NULL for 'all'. | 6075 |
| median_ppsqm | INTEGER | yes | True median price per m² (₪/m²) of the n_ppsqm deals. NULL for non-residential groups, for 'all', and when n_ppsqm = 0. Gate on n_ppsqm (hide when < 5, 'few deals' when < 20). | 22477 |
| median_area | INTEGER | yes | Median area (m²) of the stat deals. NULL when none. | 101 |
| total_volume | INTEGER | no | Sum of deal_amount in ₪ over rows with a credible amount (no amount-based outlier reason; not portfolio / pair-total rows). Includes partial deals (the amount actually paid). | 20450103635 |
| is_incomplete | INTEGER | no | 1 when the period ends after meta.stats_anchor_date (2026-07…09, 2026-Q3, year 2026): reporting lag, NOT a real drop in prices or volume. Exclude (is_incomplete = 0) or flag these rows in any trend. | 0 |

#### agg_national_quarter

Table · 3,875 rows · primary key (property_group, rooms_bucket, quarter)

National quarterly series per property_group × rooms_bucket.

Joins: property_group → property_groups.key

| column | type | null | description | example |
|---|---|---|---|---|
| quarter | TEXT | no | Quarter 'YYYY-Qn'. | 2025-Q4 |
| property_group | TEXT | no | Property group key: apartment, garden_apartment, penthouse, house, land, commercial, agriculture, parking, other, plus the pseudo-groups 'all_residential' (the 4 residential groups) and 'all' (every deal; counts/volume only, medians NULL). Values: 'all_residential', 'apartment', 'house', 'garden_apartment', 'penthouse', 'agriculture', 'all', 'commercial', 'land', 'other', 'parking'. | apartment |
| rooms_bucket | TEXT | no | Rooms bucket: '1-2' (rooms < 3), '3' (3–3.5), '4' (4–4.5), '5' (5–5.5), '6+' (≥ 6), or 'all' (every row incl. unknown rooms). Non-residential groups only have 'all'. ALWAYS filter it (use 'all' for the total) or you will double count. Values: 'all', '5', '4', '6+', '3', '1-2'. | all |
| deals | INTEGER | no | Count of ALL unique deals in the cell (partial, outlier, multi-unit and discount-project rows included) = transaction volume. Never NULL. | 20765 |
| n_stats | INTEGER | yes | Number of deals passing the stat rule (in_stats = 1 for residential; group floor rule for non-residential) behind median_price. NULL for property_group = 'all'. | 13776 |
| median_price | INTEGER | yes | True median deal_amount in ₪ of the n_stats deals (computed in DuckDB, rounded). NULL when n_stats = 0 or property_group = 'all'. Never average medians across cells. | 2150000 |
| n_ppsqm | INTEGER | yes | Number of stat deals that also have price_per_sqm, behind median_ppsqm. 0 for non-residential groups; NULL for 'all'. | 13518 |
| median_ppsqm | INTEGER | yes | True median price per m² (₪/m²) of the n_ppsqm deals. NULL for non-residential groups, for 'all', and when n_ppsqm = 0. Gate on n_ppsqm (hide when < 5, 'few deals' when < 20). | 22065 |
| median_area | INTEGER | yes | Median area (m²) of the stat deals. NULL when none. | 100 |
| total_volume | INTEGER | no | Sum of deal_amount in ₪ over rows with a credible amount (no amount-based outlier reason; not portfolio / pair-total rows). Includes partial deals (the amount actually paid). | 44121174310 |
| is_incomplete | INTEGER | no | 1 when the period ends after meta.stats_anchor_date (2026-07…09, 2026-Q3, year 2026): reporting lag, NOT a real drop in prices or volume. Exclude (is_incomplete = 0) or flag these rows in any trend. | 0 |

#### agg_national_year

Table · 1,007 rows · primary key (property_group, rooms_bucket, year)

National yearly series per property_group × rooms_bucket.

Joins: property_group → property_groups.key

| column | type | null | description | example |
|---|---|---|---|---|
| year | INTEGER | no | Calendar year. | 2025 |
| property_group | TEXT | no | Property group key: apartment, garden_apartment, penthouse, house, land, commercial, agriculture, parking, other, plus the pseudo-groups 'all_residential' (the 4 residential groups) and 'all' (every deal; counts/volume only, medians NULL). Values: 'all_residential', 'apartment', 'house', 'garden_apartment', 'penthouse', 'agriculture', 'all', 'commercial', 'land', 'other', 'parking'. | apartment |
| rooms_bucket | TEXT | no | Rooms bucket: '1-2' (rooms < 3), '3' (3–3.5), '4' (4–4.5), '5' (5–5.5), '6+' (≥ 6), or 'all' (every row incl. unknown rooms). Non-residential groups only have 'all'. ALWAYS filter it (use 'all' for the total) or you will double count. Values: 'all', '4', '3', '5', '6+', '1-2'. | all |
| deals | INTEGER | no | Count of ALL unique deals in the cell (partial, outlier, multi-unit and discount-project rows included) = transaction volume. Never NULL. | 88371 |
| n_stats | INTEGER | yes | Number of deals passing the stat rule (in_stats = 1 for residential; group floor rule for non-residential) behind median_price. NULL for property_group = 'all'. | 59310 |
| median_price | INTEGER | yes | True median deal_amount in ₪ of the n_stats deals (computed in DuckDB, rounded). NULL when n_stats = 0 or property_group = 'all'. Never average medians across cells. | 2120000 |
| n_ppsqm | INTEGER | yes | Number of stat deals that also have price_per_sqm, behind median_ppsqm. 0 for non-residential groups; NULL for 'all'. | 58512 |
| median_ppsqm | INTEGER | yes | True median price per m² (₪/m²) of the n_ppsqm deals. NULL for non-residential groups, for 'all', and when n_ppsqm = 0. Gate on n_ppsqm (hide when < 5, 'few deals' when < 20). | 22044 |
| median_area | INTEGER | yes | Median area (m²) of the stat deals. NULL when none. | 99 |
| total_volume | INTEGER | no | Sum of deal_amount in ₪ over rows with a credible amount (no amount-based outlier reason; not portfolio / pair-total rows). Includes partial deals (the amount actually paid). | 182276628050 |
| is_incomplete | INTEGER | no | 1 when the period ends after meta.stats_anchor_date (2026-07…09, 2026-Q3, year 2026): reporting lag, NOT a real drop in prices or volume. Exclude (is_incomplete = 0) or flag these rows in any trend. | 0 |

#### agg_district_quarter

Table · 763 rows · primary key (district, quarter)

Per district × quarter, RESIDENTIAL ONLY (all_residential) — there is no property_group column. Other groups: agg_district_year.

Joins: district → districts.name

| column | type | null | description | example |
|---|---|---|---|---|
| district | TEXT | no | District name (CBS form) → districts.name. Values: 'הדרום', 'המרכז', 'הצפון', 'חיפה', 'ירושלים', 'תל אביב', 'יהודה והשומרון'. | תל אביב |
| quarter | TEXT | no | Quarter 'YYYY-Qn'. | 2025-Q4 |
| deals | INTEGER | no | Count of ALL unique deals in the cell (partial, outlier, multi-unit and discount-project rows included) = transaction volume. Never NULL. | 4229 |
| n_stats | INTEGER | yes | Number of deals passing the stat rule (in_stats = 1 for residential; group floor rule for non-residential) behind median_price. NULL for property_group = 'all'. | 2620 |
| median_price | INTEGER | yes | True median deal_amount in ₪ of the n_stats deals (computed in DuckDB, rounded). NULL when n_stats = 0 or property_group = 'all'. Never average medians across cells. | 3256810 |
| n_ppsqm | INTEGER | yes | Number of stat deals that also have price_per_sqm, behind median_ppsqm. 0 for non-residential groups; NULL for 'all'. | 2535 |
| median_ppsqm | INTEGER | yes | True median price per m² (₪/m²) of the n_ppsqm deals. NULL for non-residential groups, for 'all', and when n_ppsqm = 0. Gate on n_ppsqm (hide when < 5, 'few deals' when < 20). | 38116 |
| median_area | INTEGER | yes | Median area (m²) of the stat deals. NULL when none. | 89 |
| total_volume | INTEGER | no | Sum of deal_amount in ₪ over rows with a credible amount (no amount-based outlier reason; not portfolio / pair-total rows). Includes partial deals (the amount actually paid). | 13550109042 |
| is_incomplete | INTEGER | no | 1 when the period ends after meta.stats_anchor_date (2026-07…09, 2026-Q3, year 2026): reporting lag, NOT a real drop in prices or volume. Exclude (is_incomplete = 0) or flag these rows in any trend. | 0 |

#### agg_district_year

Table · 2,009 rows · primary key (district, property_group, year)

Per district × property_group × year (all groups plus all_residential and all).

Joins: district → districts.name; property_group → property_groups.key

| column | type | null | description | example |
|---|---|---|---|---|
| district | TEXT | no | District name (CBS form) → districts.name. Values: 'ירושלים', 'המרכז', 'הצפון', 'הדרום', 'חיפה', 'תל אביב', 'יהודה והשומרון'. | תל אביב |
| year | INTEGER | no | Calendar year. | 2025 |
| property_group | TEXT | no | Property group key: apartment, garden_apartment, penthouse, house, land, commercial, agriculture, parking, other, plus the pseudo-groups 'all_residential' (the 4 residential groups) and 'all' (every deal; counts/volume only, medians NULL). Values: 'all', 'apartment', 'all_residential', 'land', 'house', 'commercial', 'parking', 'other', 'agriculture', 'garden_apartment', 'penthouse'. | all_residential |
| deals | INTEGER | no | Count of ALL unique deals in the cell (partial, outlier, multi-unit and discount-project rows included) = transaction volume. Never NULL. | 17459 |
| n_stats | INTEGER | yes | Number of deals passing the stat rule (in_stats = 1 for residential; group floor rule for non-residential) behind median_price. NULL for property_group = 'all'. | 10368 |
| median_price | INTEGER | yes | True median deal_amount in ₪ of the n_stats deals (computed in DuckDB, rounded). NULL when n_stats = 0 or property_group = 'all'. Never average medians across cells. | 3150000 |
| n_ppsqm | INTEGER | yes | Number of stat deals that also have price_per_sqm, behind median_ppsqm. 0 for non-residential groups; NULL for 'all'. | 10058 |
| median_ppsqm | INTEGER | yes | True median price per m² (₪/m²) of the n_ppsqm deals. NULL for non-residential groups, for 'all', and when n_ppsqm = 0. Gate on n_ppsqm (hide when < 5, 'few deals' when < 20). | 36885 |
| median_area | INTEGER | yes | Median area (m²) of the stat deals. NULL when none. | 88 |
| total_volume | INTEGER | no | Sum of deal_amount in ₪ over rows with a credible amount (no amount-based outlier reason; not portfolio / pair-total rows). Includes partial deals (the amount actually paid). | 52298333365 |
| is_incomplete | INTEGER | no | 1 when the period ends after meta.stats_anchor_date (2026-07…09, 2026-Q3, year 2026): reporting lag, NOT a real drop in prices or volume. Exclude (is_incomplete = 0) or flag these rows in any trend. | 0 |

#### agg_gush_year

Table · 191,306 rows · primary key (gush, year)

Per gush × year: deals counts ALL groups; the n/median columns are residential stats. No median_area / total_volume columns.

Joins: gush → gushim.gush

| column | type | null | description | example |
|---|---|---|---|---|
| gush | INTEGER | no | Block number → gushim.gush. | 6212 |
| year | INTEGER | no | Calendar year. | 2025 |
| deals | INTEGER | no | All deals (all groups) in the gush that year. | 258 |
| n_stats | INTEGER | yes | Residential stat deals behind median_price. | 151 |
| median_price | INTEGER | yes | Median residential price in ₪. | 5534000 |
| n_ppsqm | INTEGER | yes | Residential stat deals with ppsqm. | 150 |
| median_ppsqm | INTEGER | yes | Median residential ₪/m². | 65617 |
| is_incomplete | INTEGER | no | 1 when the period ends after meta.stats_anchor_date (2026-07…09, 2026-Q3, year 2026): reporting lag, NOT a real drop in prices or volume. Exclude (is_incomplete = 0) or flag these rows in any trend. | 0 |

#### agg_neighborhood_year

Table · 32,280 rows · primary key (nbhd_id, year)

Per neighbourhood × year (1998–2026): deal counts and residential medians, including an existing-stock median.

Joins: nbhd_id → neighborhoods.nbhd_id

| column | type | null | description | example |
|---|---|---|---|---|
| nbhd_id | INTEGER | no | Neighbourhood id → neighborhoods.nbhd_id. | 50005953 |
| year | INTEGER | no | Calendar year. | 2025 |
| deals | INTEGER | no | All deals (all groups) on the neighbourhood's parcels (same-settlement deals only). | 171 |
| deals_residential | INTEGER | no | Residential deals. | 123 |
| n_stats | INTEGER | no | Residential stat deals behind median_price. | 86 |
| median_price | INTEGER | yes | Median residential price in ₪. NULL when n_stats = 0. | 2812500 |
| n_ppsqm | INTEGER | no | Residential stat deals with ppsqm. | 86 |
| median_ppsqm | INTEGER | yes | Median residential ₪/m² (pooled new + existing). | 54284 |
| n_ppsqm_existing | INTEGER | no | Existing-stock stat deals with ppsqm. | 60 |
| median_ppsqm_existing | INTEGER | yes | Median ₪/m² of existing stock. | 51688 |
| is_incomplete | INTEGER | no | 1 for 2026 (after the stats anchor). | 0 |

### Search & spatial indexes

FTS5 name search and R*Tree bounding-box lookups.

#### settlements_fts

FTS5 virtual table · 1,139 rows · primary key (rowid)

FTS5 full-text index over settlement names and aliases (spelling variants, abbreviations like תא / בש / פת, historic names, English names). rowid = settlements.code. Tokenizer unicode61 remove_diacritics 2, prefix indexes 1–3.

- Usage: SELECT s.code, s.name FROM settlements_fts f JOIN settlements s ON s.code = f.rowid WHERE settlements_fts MATCH '"באר"*' ORDER BY bm25(settlements_fts, 5.0, 1.0) - 2*log(1 + s.deals_total) LIMIT 10.
- Strip ASCII quotes/geresh from user input before MATCH (ת"א → תא) — a bare " is FTS5 syntax and raises an error.

Joins: rowid → settlements.code

| column | type | null | description | example |
|---|---|---|---|---|
| name | TEXT | yes | Canonical Hebrew name (indexed column; weight it higher in bm25). | תל אביב-יפו |
| aliases | TEXT | yes | ' \| '-joined search aliases: spelling variants, parts of composite names, raw Tax Authority names, CBS names, English names, abbreviations. | Tel Aviv - Yafo \| tel aviv \| tel aviv - yafo \| tlv \| אביב \| יפו \| ת"א \| ת"א יפו \| ת"א-י… |

Definition: `CREATE VIRTUAL TABLE settlements_fts USING fts5( name, aliases, tokenize = 'unicode61 remove_diacritics 2', prefix = '1 2 3')`

#### streets_fts

FTS5 virtual table · 31,687 rows · primary key (rowid)

Contentless FTS5 index over streets (columns street, settlement, aliases). rowid = streets.id. Columns read back as NULL: join streets to get values.

- Column filters: street:"הרצל" matches the street name only; settlement:"תא"* limits the city. Example: WHERE streets_fts MATCH '{street aliases}:"רוטשילד" AND settlement:"תא"*'.

Joins: rowid → streets.id

| column | type | null | description | example |
|---|---|---|---|---|
| street | TEXT | yes | Street name (indexed only; contentless → NULL when selected). |  |
| settlement | TEXT | yes | Settlement name plus its search aliases (indexed only). |  |
| aliases | TEXT | yes | Street spelling variants without quotes/geresh, 'רחוב'/'שדרות'/'דרך' prefixes (indexed only). |  |

Definition: `CREATE VIRTUAL TABLE streets_fts USING fts5(street, settlement, aliases, content='', tokenize='unicode61 remove_diacritics 2', prefix='1 2 3')`

#### neighborhoods_fts

FTS5 virtual table · 1,277 rows · primary key (rowid)

Contentless FTS5 index over neighbourhood names (name, settlement, aliases). rowid = neighborhoods.nbhd_id; join neighborhoods for values.

Joins: rowid → neighborhoods.nbhd_id

| column | type | null | description | example |
|---|---|---|---|---|
| name | TEXT | yes | Neighbourhood name (indexed only; NULL when selected). |  |
| settlement | TEXT | yes | Settlement name and aliases (indexed only). |  |
| aliases | TEXT | yes | Spelling variants (indexed only). |  |

Definition: `CREATE VIRTUAL TABLE neighborhoods_fts USING fts5(name, settlement, aliases, content='', tokenize='unicode61 remove_diacritics 2', prefix='1 2 3')`

#### parcels_rtree

R*Tree virtual table · 380,764 rows · primary key (id)

R*Tree spatial index of located parcel points (geo_precision 'parcel' or 'gush'; settlement-centre points excluded). id = parcels.id. Coordinates are float32 boxes (rounded outward ~1 m).

- Bounding-box query: SELECT p.* FROM parcels_rtree r JOIN parcels p ON p.id = r.id WHERE r.minLat >= :south AND r.maxLat <= :north AND r.minLon >= :west AND r.maxLon <= :east.

Joins: id → parcels.id

| column | type | null | description | example |
|---|---|---|---|---|
| id | INT | yes | = parcels.id. | 91722 |
| minLat | REAL | yes | Box south edge (latitude, float32). | 32.093693 |
| maxLat | REAL | yes | Box north edge (latitude). | 32.093697 |
| minLon | REAL | yes | Box west edge (longitude). | 34.783112 |
| maxLon | REAL | yes | Box east edge (longitude). | 34.783119 |

Definition: `CREATE VIRTUAL TABLE parcels_rtree USING rtree(id, minLat, maxLat, minLon, maxLon)`

#### deals_rtree

View · 3,175,302 rows

VIEW behaving like an rtree over deals: parcels_rtree ⋈ parcels ⋈ deals, id = deals.id. Same bbox query shape as parcels_rtree; joining parcels_rtree → parcels → deals yourself is slightly faster.

Joins: id → deals.id

| column | type | null | description | example |
|---|---|---|---|---|
| id | INTEGER | yes | = deals.id. | 3411747 |
| minLat | REAL | yes | Parcel point box south edge. | 32.070705 |
| maxLat | REAL | yes | Parcel point box north edge. | 32.070709 |
| minLon | REAL | yes | Parcel point box west edge. | 34.780403 |
| maxLon | REAL | yes | Parcel point box east edge. | 34.780407 |

Definition: `CREATE VIEW deals_rtree AS SELECT d.id AS id, r.minLat AS minLat, r.maxLat AS maxLat, r.minLon AS minLon, r.maxLon AS maxLon FROM parcels_rtree r CROSS JOIN parcels p ON p.id = r.id CROSS JOIN deals d ON d.gush = p.gush AND d.chelka = p.chelka`

### Streets, addresses & neighbourhoods

OpenStreetMap addresses and neighbourhood polygons joined to parcels.

#### streets

Table · 31,687 rows · primary key (id)

One row per (settlement, street) derived from OpenStreetMap (31,687 streets in 663 settlements) with house-number range, extent and deal statistics of the street's parcels (addressed or nearby).

- Street statistics include parcels merely NEAR the street (street_parcels.kind = 'near'). Gate medians on n ≥ 5.
- © OpenStreetMap contributors (ODbL).

Joins: settlement_code → settlements.code; id → streets_fts.rowid

| column | type | null | description | example |
|---|---|---|---|---|
| id | INTEGER | no | Primary key, stable: settlement_code × 100000 + crc32(street_key) % 100000. → parcel_address.street_id, street_parcels.street_id, streets_fts.rowid. | 500040063 |
| settlement_code | INTEGER | no | Settlement → settlements.code. | 5000 |
| settlement | TEXT | yes | Settlement canonical name. | תל אביב-יפו |
| street | TEXT | no | Street display name (most common OSM spelling), Hebrew. | אבן גבירול |
| street_key | TEXT | no | Normalised street key (UNIQUE per settlement). | אבנ גבירול |
| slug | TEXT | no | URL slug, UNIQUE per settlement. | אבן-גבירול |
| n_parcels | INTEGER | yes | Parcels on the street (address or nearby-street match). | 216 |
| n_parcels_with_deals | INTEGER | yes | Of which parcels with deals. | 180 |
| n_parcels_addressed | INTEGER | yes | Of which parcels with an OSM address on this street. | 199 |
| n_addresses | INTEGER | yes | OSM address points on the street. | 286 |
| house_min | INTEGER | yes | Lowest house number. NULL when no addresses. | 1 |
| house_max | INTEGER | yes | Highest house number. NULL when no addresses. | 502 |
| lat | REAL | yes | Median address point latitude (else parcel point). | 32.084388 |
| lon | REAL | yes | Median address point longitude. | 34.78182 |
| bbox_min_lat | REAL | yes | Parcel-point bbox south. | 32.071287 |
| bbox_min_lon | REAL | yes | Parcel-point bbox west. | 34.779922 |
| bbox_max_lat | REAL | yes | Parcel-point bbox north. | 32.136711 |
| bbox_max_lon | REAL | yes | Parcel-point bbox east. | 34.7955 |
| gushim | TEXT | yes | Comma-separated gush numbers along the street. | 6111,6212,6213,6214,6215,6216,6217,6620,6632,6634,6635,6798,6951,6952,6953,7085,7111 |
| deals_total | INTEGER | no | Deals on the street's parcels, all time (a corner parcel counts on each of its streets). | 2730 |
| deals_5y | INTEGER | no | Deals in the 5y window. | 335 |
| deals_12m | INTEGER | no | Deals in the 12m window. | 39 |
| n_ppsqm_5y | INTEGER | no | Residential stat deals with ppsqm, 5y. | 137 |
| median_ppsqm_5y | INTEGER | yes | Median residential ₪/m², 5y. NULL when n = 0. | 60930 |
| n_ppsqm_12m | INTEGER | no | Residential stat deals with ppsqm, 12m. | 18 |
| median_ppsqm_12m | INTEGER | yes | Median residential ₪/m², 12m. | 57072 |
| last_deal_date | TEXT | yes | Latest deal date 'YYYY-MM-DD'. NULL when none. | 2026-06-03 |

Indexes: ix_streets_settlement_deals(settlement_code, deals_total DESC); ux_streets_key(settlement_code, street_key) UNIQUE; ux_streets_slug(settlement_code, slug) UNIQUE

#### street_parcels

Table · 259,266 rows · primary key (street_id, gush, chelka)

Every DB parcel of a street (259k rows). kind = 'address' (an OSM address of the parcel is on the street) or 'near' (only the nearest-street rule, ≤ 25 m).

Joins: street_id → streets.id; gush, chelka → parcels.(gush, chelka)

| column | type | null | description | example |
|---|---|---|---|---|
| street_id | INTEGER | no | Street → streets.id. | 500040063 |
| gush | INTEGER | no | Block number. | 6111 |
| chelka | INTEGER | no | Parcel number. Index (gush, chelka) lets you go parcel → streets. | 69 |
| kind | TEXT | no | 'address' or 'near' (a nearby street, not the parcel's address). Values: 'near', 'address'. | address |

Indexes: ix_street_parcels_parcel(gush, chelka)

#### parcel_address

Table · 245,790 rows · primary key (gush, chelka)

OpenStreetMap address (or nearby named street) per DB parcel (245,790 rows). address_label is a real address only when street_source = 'osm_address'; 'osm_nearest_street' rows are 'near street X', never an address.

- Coverage: ~31.6% of deals have a real address, ~73.5% at least a nearby street. © OpenStreetMap contributors (ODbL).

Joins: gush, chelka → parcels.(gush, chelka); street_id → streets.id; settlement_code → settlements.code

| column | type | null | description | example |
|---|---|---|---|---|
| gush | INTEGER | no | Block number. | 6212 |
| chelka | INTEGER | no | Parcel number. PK (gush, chelka). | 418 |
| settlement_code | INTEGER | yes | Settlement of the address points → settlements.code. | 5000 |
| street | TEXT | yes | Primary street name (most OSM addresses in the parcel), or the nearest named road ≤ 25 m for osm_nearest_street rows. | אבן גבירול |
| street_key | TEXT | yes | Normalised street key. | אבנ גבירול |
| street_id | INTEGER | yes | → streets.id. NULL for 20 rows. | 500040063 |
| house_numbers | TEXT | yes | Compact sorted house numbers on the primary street, e.g. '12-16, 18'. NULL for nearby-street rows. | 183 |
| n_addresses | INTEGER | yes | Distinct (street, number) OSM addresses in the parcel. | 1 |
| n_streets | INTEGER | yes | Distinct streets with addresses in the parcel (corner parcels > 1). | 1 |
| other_streets | TEXT | yes | 'street numbers \| …' for non-primary streets. NULL when none. | אריאל שרון 50 |
| address_label | TEXT | yes | Display address, e.g. 'רחוב אבן גבירול 183'. NULL when only a nearby street is known. | רחוב אבן גבירול 183 |
| street_source | TEXT | no | 'osm_address' (real address), 'osm_addr_place' (addr:place villages) or 'osm_nearest_street' (a NEARBY street, not an address). Values: 'osm_nearest_street', 'osm_address', 'osm_addr_place'. | osm_address |
| street_dist_m | REAL | yes | Parcel → road distance in metres for osm_nearest_street rows; NULL otherwise. | 0 |
| n_buildings | INTEGER | yes | OSM buildings whose representative point is in the parcel. | 1 |
| max_levels | INTEGER | yes | Max OSM building:levels in the parcel (sparse, lower bound). NULL when unknown. | 4 |
| max_height_m | REAL | yes | Max OSM building height in metres (sparse). NULL when unknown. | 5 |

Indexes: ix_parcel_address_settlement(settlement_code, street_key); ix_parcel_address_street(street_id)

#### parcel_buildings

Table · 226,621 rows · primary key (gush, chelka)

OSM building summary for every DB parcel with at least one building (226,621 rows), including parcels without an address.

Joins: gush, chelka → parcels.(gush, chelka)

| column | type | null | description | example |
|---|---|---|---|---|
| gush | INTEGER | no | Block number. | 6212 |
| chelka | INTEGER | no | Parcel number. PK (gush, chelka). | 418 |
| n_buildings | INTEGER | no | OSM buildings in the parcel. | 1 |
| max_levels | INTEGER | yes | Max building:levels (sparse). NULL when unknown. | 4 |
| max_height_m | REAL | yes | Max building height in metres (sparse). NULL when unknown. | 2 |

#### neighborhoods

Table · 1,277 rows · primary key (nbhd_id)

1,277 neighbourhoods in 182 settlements (municipal polygons for Tel Aviv, Jerusalem, Petah Tikva; CBS 2011 statistical areas elsewhere; OSM for small towns) with deal counts and residential price statistics like settlements.

- Statistics count only deals whose settlement is the neighbourhood's settlement.
- Small n is common: gate medians (n ≥ 5 to show, 'few deals' < 20); prefer median_ppsqm_existing_12m when one new project dominates (max_parcel_share_12m).

Joins: settlement_code → settlements.code; nbhd_id → neighborhoods_fts.rowid

| column | type | null | description | example |
|---|---|---|---|---|
| nbhd_id | INTEGER | no | Primary key, stable: settlement_code × 10000 + crc32(name) % 10000. → parcel_neighborhood.nbhd_id, agg_neighborhood_year.nbhd_id, neighborhoods_fts.rowid. | 50005953 |
| settlement_code | INTEGER | no | Settlement → settlements.code. | 5000 |
| settlement | TEXT | yes | Settlement name. | תל אביב-יפו |
| name | TEXT | no | Neighbourhood name as published (Hebrew; some Arabic, 'שכונה N'). | פלורנטין |
| slug | TEXT | no | URL slug, UNIQUE per settlement. | פלורנטין |
| source | TEXT | no | Polygon source: tlv_neighborhoods, jlm_neighborhoods, pt_neighborhoods, cbs_stat2011_nbhd, osm_polygon, osm_point_voronoi. Values: 'cbs_stat2011_nbhd', 'jlm_neighborhoods', 'osm_point_voronoi', 'tlv_neighborhoods', 'pt_neighborhoods', 'osm_polygon'. | tlv_neighborhoods |
| area_km2 | REAL | yes | Area in km². | 0.4476 |
| lat | REAL | yes | Label point latitude (pole of inaccessibility). | 32.057525 |
| lon | REAL | yes | Label point longitude. | 34.769864 |
| bbox_min_lat | REAL | yes | Polygon bbox south. | 32.054745 |
| bbox_min_lon | REAL | yes | Polygon bbox west. | 34.764037 |
| bbox_max_lat | REAL | yes | Polygon bbox north. | 32.060676 |
| bbox_max_lon | REAL | yes | Polygon bbox east. | 34.773932 |
| n_parcels | INTEGER | no | DB parcels mapped to the neighbourhood. | 608 |
| deals_total | INTEGER | no | All deals (all property groups, all time) on the neighbourhood's parcels whose settlement equals the neighbourhood's settlement. | 8001 |
| deals_residential_total | INTEGER | no | Residential deals, all time. | 5379 |
| first_deal | TEXT | yes | Date of the first deal, 'YYYY-MM-DD'. NULL when the neighbourhood has no deals. | 1998-01-07 |
| last_deal | TEXT | yes | Date of the latest deal, 'YYYY-MM-DD' (may be after the stats anchor). NULL when no deals. | 2026-07-21 |
| deals_12m | INTEGER | no | Deals (all groups) in the 12m window (meta.window_12m_start … window_12m_end = stats anchor). | 117 |
| deals_residential_12m | INTEGER | no | Residential deals in the 12m window. | 102 |
| deals_prev12m | INTEGER | no | Deals (all groups) in the prev12m window. Do not headline deals_12m vs deals_prev12m as an activity change (late reports). | 204 |
| n_stats_12m | INTEGER | no | Residential stat deals (in_stats = 1) in 12m behind median_price_12m. | 85 |
| median_price_12m | INTEGER | yes | Median residential deal price in ₪, 12m, stat rule. NULL when n_stats_12m = 0. | 2900000 |
| n_ppsqm_12m | INTEGER | no | Residential stat deals with price_per_sqm in 12m behind median_ppsqm_12m. | 84 |
| median_ppsqm_12m | INTEGER | yes | Median residential ₪/m², 12m (pooled new + existing stock, discount projects excluded). NULL when n = 0; gate on n_ppsqm_12m ≥ 5. | 57143 |
| n_ppsqm_prev12m | INTEGER | no | Same as n_ppsqm_12m for the prev12m window. | 89 |
| median_ppsqm_prev12m | INTEGER | yes | Median residential ₪/m², prev12m window (pooled). | 54808 |
| n_ppsqm_existing_12m | INTEGER | no | Existing-stock (is_new_build = 0) stat deals with ppsqm, 12m. | 45 |
| median_ppsqm_existing_12m | INTEGER | yes | Median ₪/m² of existing stock, 12m. | 53125 |
| n_ppsqm_existing_prev12m | INTEGER | no | Existing-stock stat deals with ppsqm, prev12m. | 62 |
| median_ppsqm_existing_prev12m | INTEGER | yes | Median ₪/m² of existing stock, prev12m (base of ppsqm_change_pct). | 51953 |
| n_ppsqm_new_12m | INTEGER | no | New-build (is_new_build = 1) stat deals with ppsqm, 12m. | 39 |
| median_ppsqm_new_12m | INTEGER | yes | Median ₪/m² of new builds, 12m. NULL when n = 0. | 63627 |
| ppsqm_change_pct | REAL | yes | Existing-stock price change in percent points: (median_ppsqm_existing_12m / median_ppsqm_existing_prev12m − 1)·100, 1 decimal. Set only when both windows have ≥ 50 existing-stock deals; NULL otherwise. | -1 |
| ppsqm_change_method | TEXT | yes | 'existing' when ppsqm_change_pct is set, else NULL. Values: 'existing'. | existing |
| n_ppsqm_5y | INTEGER | no | Residential stat deals with ppsqm in the 5y window (2021-07-01 … 2026-06-30). | 488 |
| median_ppsqm_5y | INTEGER | yes | Median residential ₪/m², 5y window — better coverage for quiet areas. | 56392 |
| median_price_5y | INTEGER | yes | Median residential price in ₪, 5y window. | 2950000 |
| n_4rooms_12m | INTEGER | no | Apartments with 4–4.5 rooms, stat rule, 12m. | 8 |
| median_price_4rooms_12m | INTEGER | yes | Median price in ₪ of 4–4.5-room apartments, 12m. | 5100000 |
| n_discount_12m | INTEGER | no | Discount-project deals (is_discount_project = 1) in 12m — NOT in the medians. | 0 |
| deals_after_anchor | INTEGER | no | Deals dated after meta.stats_anchor_date reported so far (incomplete months). | 1 |
| n_parcels_12m | INTEGER | no | Distinct parcels behind the 12m ₪/m² median. | 42 |
| max_parcel_share_12m | REAL | yes | Share (0–1) of the 12m ppsqm deals held by the single largest parcel. High values (> 0.5) mean one project drives the median. | 0.107 |
| rank_ppsqm_in_settlement | INTEGER | yes | Rank by median_ppsqm_12m among the settlement's neighbourhoods with ≥ 20 ppsqm deals from ≥ 5 parcels (none > 50%), industrial zones excluded. 1 = most expensive. NULL = unranked. | 10 |
| ranked_in_settlement | INTEGER | yes | How many neighbourhoods of the settlement were ranked. NULL when unranked. | 29 |
| ppsqm_vs_settlement_pct | REAL | yes | median_ppsqm_12m vs the settlement's, in percent (only when the neighbourhood has ≥ 20 and the settlement ≥ 30 deals). | 0.5 |

Indexes: ix_neighborhoods_settlement(settlement_code, deals_12m DESC); ux_neighborhoods_slug(settlement_code, slug) UNIQUE

#### parcel_neighborhood

Table · 288,727 rows · primary key (gush, chelka)

Neighbourhood containing each parcel's DB point (288,727 rows).

- same_settlement = 0 parcels sit in a neighbouring settlement's neighbourhood and are NOT in its statistics.

Joins: gush, chelka → parcels.(gush, chelka); nbhd_id → neighborhoods.nbhd_id

| column | type | null | description | example |
|---|---|---|---|---|
| gush | INTEGER | no | Block number. | 6212 |
| chelka | INTEGER | no | Parcel number. PK (gush, chelka). | 418 |
| nbhd_id | INTEGER | no | → neighborhoods.nbhd_id (index (nbhd_id, gush, chelka) for neighbourhood → parcels). | 50000117 |
| point_source | TEXT | no | 'db_parcel' (parcel point) or 'db_gush' (parcel sits at its gush centroid: approximate). Values: 'db_parcel', 'db_gush'. | db_parcel |
| same_settlement | INTEGER | no | 1 when the neighbourhood belongs to the parcel's own settlement; 0 otherwise (6,968 parcels). | 1 |

Indexes: ix_parcel_neighborhood_nbhd(nbhd_id, gush, chelka)

### Location metrics & POIs

Transit, schools, sea, elevation and noise per parcel and gush.

#### loc_metrics_parcel

Table · 381,177 rows · primary key (gush, chelka)

Location metrics for every DB parcel (381,177 rows): distances to rail / light rail / schools / sea, bus service, transit score, elevation, airport noise. Distances are straight-line metres.

Joins: gush, chelka → parcels.(gush, chelka); rail_station_id → poi_stations.id; lrt_station_id → poi_stations.id; lrt_planned_station_id → poi_stations.id

| column | type | null | description | example |
|---|---|---|---|---|
| gush | INTEGER | no | Block number. | 6212 |
| chelka | INTEGER | no | Parcel number. PK (gush, chelka). | 418 |
| geo_precision | TEXT | yes | Precision of the point the metrics were computed at: 'parcel', 'gush' (gush centroid) or 'settlement' (settlement centre) — same as parcels.geo_precision. Values: 'parcel', 'gush', 'settlement'. | parcel |
| dist_rail_m | INTEGER | yes | Straight-line distance in metres to the nearest heavy-rail station with service on the representative weekday (meta.representative_weekday_gtfs). | 1807 |
| rail_station_id | TEXT | yes | Id of that rail station → poi_stations.id (e.g. 'rail:17108'); name in poi_stations.name. | rail:17038 |
| rail_weekday_trips | INTEGER | yes | Trains stopping at that station on the representative weekday (both directions). | 464 |
| dist_lrt_m | INTEGER | yes | Straight-line distance in metres to the nearest OPERATING light-rail station (Tel Aviv Red/Purple, Jerusalem lines per GTFS). | 1848 |
| lrt_station_id | TEXT | yes | Id of that LRT station → poi_stations.id (e.g. 'lrt:35962-35962'). | lrt:20711-20711 |
| lrt_weekday_trips | INTEGER | yes | LRT trips stopping at that station on the representative weekday. | 619 |
| dist_lrt_planned_m | INTEGER | yes | Straight-line distance in metres to the nearest PLANNED / under-construction LRT station (ministry lrt_stat table; not in GTFS). | 34 |
| lrt_planned_station_id | TEXT | yes | Id of that planned station → poi_stations.id (e.g. 'lrt:planned136'). | lrt:planned078 |
| bus_trips_500m | INTEGER | yes | Weekday bus trips serving stops within 500 m (per route variant the max over its stops, summed). | 2124 |
| bus_lines_500m | INTEGER | yes | Distinct bus lines with a served stop within 500 m. | 16 |
| bus_stops_500m | INTEGER | yes | Served bus stops within 500 m. | 15 |
| transit_score | INTEGER | yes | 0–100 transit accessibility score: 100·ln(1+X)/ln(1+P99), capped, where X = bus trips ≤ 500 m + rail trips if a station ≤ 1 km + LRT trips if a station ≤ 600 m. | 88 |
| schools_1km | INTEGER | yes | Schools within 1 km (kindergartens and institutions of unknown type excluded; placeholder-located institutions excluded). | 11 |
| elementary_1km | INTEGER | yes | Elementary or combined schools within 1 km. | 5 |
| dist_elementary_m | INTEGER | yes | Straight-line distance in metres to the nearest elementary / combined school. | 214 |
| kindergartens_500m | INTEGER | yes | Kindergartens within 500 m. | 6 |
| dist_sea_m | INTEGER | yes | Straight-line distance in metres to the OSM coastline (Mediterranean or Red Sea; lakes excluded). | 993 |
| sea | TEXT | yes | Which coastline is nearest: 'mediterranean' or 'red_sea'. Values: 'mediterranean', 'red_sea'. | mediterranean |
| elevation_m | INTEGER | yes | Surface elevation in metres above the EGM2008 geoid (Copernicus GLO-30; includes roofs/trees in dense blocks). | 13 |
| noise_zone | TEXT | yes | Airport noise zone of an ACTIVE airport containing the point, e.g. 'נתב"ג: LDN 65-70' or '<airport>: תחום רעש מטוסים (תמ"א 35)'. NULL = no zone (≈ 98.4% of parcels). Values: 'נתב"ג: LDN 60-65', 'נתב"ג: LDN 65-70', 'נתב"ג: LDN 70-75', 'נתב"ג: תחום רעש מטוסים (תמ"א 35)', 'חיפה: תחום רעש מטוסים (תמ"א 35)', 'מחניים (ראש פינה): תחום רעש מטוסים (תמ"א 35)', 'קריית שמונה: תחום רעש מטוסים (תמ"א 35)', 'נתב"ג: LDN 75+', 'עין שמר: תחום רעש מטוסים (תמ"א 35)'. | נתב"ג: LDN 60-65 |
| noise_ldn_min | INTEGER | yes | Lower bound of the Ben Gurion LDN band in dB: 60, 65, 70 or 75. NULL outside LDN bands. | 60 |

#### loc_metrics_gush

Table · 12,407 rows · primary key (gush)

The same location metrics computed at each gush centroid (12,407 rows).

Joins: gush → gushim.gush; rail_station_id → poi_stations.id; lrt_station_id → poi_stations.id; lrt_planned_station_id → poi_stations.id

| column | type | null | description | example |
|---|---|---|---|---|
| gush | INTEGER | no | Primary key → gushim.gush. | 6212 |
| geo_source | TEXT | yes | 'gush' (metrics computed at the gush centroid). Values: 'gush'. | gush |
| dist_rail_m | INTEGER | yes | Straight-line distance in metres to the nearest heavy-rail station with service on the representative weekday (meta.representative_weekday_gtfs). | 1522 |
| rail_station_id | TEXT | yes | Id of that rail station → poi_stations.id (e.g. 'rail:17108'); name in poi_stations.name. | rail:17038 |
| rail_weekday_trips | INTEGER | yes | Trains stopping at that station on the representative weekday (both directions). | 464 |
| dist_lrt_m | INTEGER | yes | Straight-line distance in metres to the nearest OPERATING light-rail station (Tel Aviv Red/Purple, Jerusalem lines per GTFS). | 1626 |
| lrt_station_id | TEXT | yes | Id of that LRT station → poi_stations.id (e.g. 'lrt:35962-35962'). | lrt:20711-20711 |
| lrt_weekday_trips | INTEGER | yes | LRT trips stopping at that station on the representative weekday. | 619 |
| dist_lrt_planned_m | INTEGER | yes | Straight-line distance in metres to the nearest PLANNED / under-construction LRT station (ministry lrt_stat table; not in GTFS). | 439 |
| lrt_planned_station_id | TEXT | yes | Id of that planned station → poi_stations.id (e.g. 'lrt:planned136'). | lrt:planned078 |
| bus_trips_500m | INTEGER | yes | Weekday bus trips serving stops within 500 m (per route variant the max over its stops, summed). | 2892 |
| bus_lines_500m | INTEGER | yes | Distinct bus lines with a served stop within 500 m. | 20 |
| bus_stops_500m | INTEGER | yes | Served bus stops within 500 m. | 15 |
| transit_score | INTEGER | yes | 0–100 transit accessibility score: 100·ln(1+X)/ln(1+P99), capped, where X = bus trips ≤ 500 m + rail trips if a station ≤ 1 km + LRT trips if a station ≤ 600 m. | 92 |
| schools_1km | INTEGER | yes | Schools within 1 km (kindergartens and institutions of unknown type excluded; placeholder-located institutions excluded). | 11 |
| elementary_1km | INTEGER | yes | Elementary or combined schools within 1 km. | 6 |
| dist_elementary_m | INTEGER | yes | Straight-line distance in metres to the nearest elementary / combined school. | 183 |
| kindergartens_500m | INTEGER | yes | Kindergartens within 500 m. | 5 |
| dist_sea_m | INTEGER | yes | Straight-line distance in metres to the OSM coastline (Mediterranean or Red Sea; lakes excluded). | 1414 |
| sea | TEXT | yes | Which coastline is nearest: 'mediterranean' or 'red_sea'. Values: 'mediterranean', 'red_sea'. | mediterranean |
| elevation_m | INTEGER | yes | Surface elevation in metres above the EGM2008 geoid (Copernicus GLO-30; includes roofs/trees in dense blocks). | 10 |
| noise_zone | TEXT | yes | Airport noise zone of an ACTIVE airport containing the point, e.g. 'נתב"ג: LDN 65-70' or '<airport>: תחום רעש מטוסים (תמ"א 35)'. NULL = no zone (≈ 98.4% of parcels). Values: 'נתב"ג: LDN 60-65', 'נתב"ג: LDN 65-70', 'נתב"ג: LDN 70-75', 'חיפה: תחום רעש מטוסים (תמ"א 35)', 'נתב"ג: LDN 75+', 'נתב"ג: תחום רעש מטוסים (תמ"א 35)', 'קריית שמונה: תחום רעש מטוסים (תמ"א 35)', 'מחניים (ראש פינה): תחום רעש מטוסים (תמ"א 35)', 'עין שמר: תחום רעש מטוסים (תמ"א 35)'. | נתב"ג: LDN 60-65 |
| noise_ldn_min | INTEGER | yes | Lower bound of the Ben Gurion LDN band in dB: 60, 65, 70 or 75. NULL outside LDN bands. | 60 |

#### poi_stations

Table · 371 rows · primary key (id)

371 heavy-rail and light-rail stations (operating, planned, under construction) with weekday service from GTFS.

| column | type | null | description | example |
|---|---|---|---|---|
| id | TEXT | no | Primary key: 'rail:<GTFS stop_code>', 'rail:asset<n>', 'lrt:<codes>' or 'lrt:planned<n>'. Referenced by loc_metrics_*.*_station_id. | rail:17038 |
| feature_id | INTEGER | no | Integer feature id in the map tiles. | 19 |
| kind | TEXT | no | 'rail' or 'lrt'. Values: 'lrt', 'rail'. | rail |
| name | TEXT | yes | Official Ministry of Transport name when matched, e.g. 'תל אביב - סבידור מרכז'. | תל אביב - סבידור מרכז |
| status | TEXT | yes | 'operating', 'planned', 'under_construction', 'no_service'. Values: 'planned', 'operating', 'under_construction', 'no_service'. | operating |
| lat | REAL | no | Latitude, WGS84 decimal degrees. | 32.083715 |
| lon | REAL | no | Longitude, WGS84 decimal degrees. | 34.798247 |
| weekday_trips | INTEGER | yes | Trips stopping on the representative weekday. NULL when not in GTFS. | 464 |
| lines | TEXT | yes | LRT line names serving the station. NULL for rail. | כפיר 1 |
| name_gtfs | TEXT | yes | Name in the GTFS feed. NULL when not in GTFS. | תל אביב מרכז |
| source | TEXT | yes | Where the station comes from (GTFS, rail_stat, lrt_stat per metropolitan area). Values: 'lrt_stat (מטרופולין תל אביב)', 'lrt_stat (מטרופולין ירושלים)', 'GTFS (route_type 0)', 'GTFS stops.txt + rail_stat', 'lrt_stat (מטרופולין חיפה)'. | GTFS stops.txt + rail_stat |
| official_asset_no | INTEGER | yes | Ministry asset number (rail). NULL otherwise. | 3700 |
| official_status | TEXT | yes | Ministry status text (Hebrew): 'נוסעים', 'בבניה', 'מתוכננת'. Values: 'מתוכננת', 'נוסעים', 'בבניה'. | נוסעים |

Indexes: ix_poi_stations_kind(kind, status)

#### poi_schools

Table · 28,312 rows · primary key (id)

28,312 educational institutions (Ministry of Education) with type, sector, supervision and location. Attributes are 2011–2015.

- loc_placeholder = 1 institutions sit on a placeholder point and are excluded from the loc_metrics_* school counts.

Joins: settlement_code → settlements.code

| column | type | null | description | example |
|---|---|---|---|---|
| id | INTEGER | no | Primary key: Ministry of Education institution code (סמל מוסד). | 110023 |
| name | TEXT | yes | Institution name (Hebrew). | בית יעקב מטרסדורף |
| kind | TEXT | yes | kindergarten, elementary, middle, high, secondary, combined, school_unknown, post_secondary, other, unknown. Values: 'kindergarten', 'unknown', 'elementary', 'other', 'high', 'secondary', 'combined', 'school_unknown', 'middle', 'post_secondary'. | elementary |
| kind_he | TEXT | yes | Hebrew label of kind. Values: 'גן ילדים', 'לא ידוע', 'יסודי', 'מוסד אחר (פנימייה, מתנ"ס, השלמה...)', 'תיכון (חטיבה עליונה)', 'על-יסודי שש-שנתי (ז-יב)', 'רב-שכבתי (א-ט / א-יב)', 'בית ספר (שכבות לא ידועות)', 'חטיבת ביניים', 'על-תיכוני (יג-יד)'. | יסודי |
| kind_source | TEXT | yes | 'mosdot' (≤ 2015 attribute file) or 'name' (keyword in the name). Values: 'mosdot', 'name'. | mosdot |
| sector | TEXT | yes | Sector (Hebrew): יהודי, ערבי, בדואי, דרוזי, צרקסי. NULL when unknown. Values: 'יהודי', 'ערבי', 'בדואי', 'דרוזי', 'צרקסי'. | יהודי |
| supervision | TEXT | yes | Supervision (Hebrew): ממלכתי (מ"מ), ממלכתי-דתי (חמ"ד), חרדי. NULL when unknown. Values: 'מ"מ"', 'חרדי', '"חמ"ד'. | חרדי |
| legal_status | TEXT | yes | Legal status (Hebrew): רשמי, מוכר, פטור, … NULL when unknown. Values: 'רשמי', 'מוכר', 'ת. י', 'פטור'. | מוכר |
| special_ed | INTEGER | yes | 1 = special education. NULL when unknown. | 0 |
| grade_from | INTEGER | yes | Lowest grade (0 = kindergarten). NULL when unknown. | 1 |
| grade_to | INTEGER | yes | Highest grade. NULL when unknown. | 8 |
| students | INTEGER | yes | Number of students (attrs_year). NULL when unknown. | 1057 |
| attrs_year | INTEGER | yes | Year of the attribute data (2011–2015). NULL when attributes came from the name. | 2015 |
| lat | REAL | no | Latitude (converted from the ministry's ITM coordinates). | 31.79595 |
| lon | REAL | no | Longitude. | 35.200045 |
| loc_accuracy | TEXT | yes | Ministry location accuracy (Hebrew): גבוהה מאוד, גבוהה, בינונית, נמוכה. Values: 'גבוהה מאוד', 'גבוהה', 'נמוכה', 'בינונית'. | גבוהה מאוד |
| loc_placeholder | INTEGER | no | 1 = placeholder point (excluded from metrics and tiles). | 0 |
| settlement_code | INTEGER | yes | → settlements.code. | 3000 |
| settlement_name | TEXT | yes | Settlement name as given by the ministry. | ירושלים |

Indexes: ix_poi_schools_settlement(settlement_code, kind)

#### poi_bus_stops

Table · 35,085 rows · primary key (stop_id)

35,085 GTFS bus stops with weekday trips and number of lines. No spatial index: use it for lookups by id; nearby-stop metrics are precomputed in loc_metrics_*.

| column | type | null | description | example |
|---|---|---|---|---|
| stop_id | TEXT | no | Primary key: GTFS stop_id. | 13103 |
| stop_code | TEXT | yes | Public stop code. | 21472 |
| name | TEXT | yes | Stop name (Hebrew). | קניון עזריאלי/דרך מנחם בגין |
| lat | REAL | no | Latitude, WGS84 decimal degrees. | 32.074584 |
| lon | REAL | no | Longitude, WGS84 decimal degrees. | 34.790607 |
| weekday_trips | INTEGER | yes | Trips (all modes) stopping on the representative weekday. | 3179 |
| bus_trips | INTEGER | yes | Bus trips stopping on the representative weekday. | 3179 |
| n_lines | INTEGER | yes | Distinct lines serving the stop. | 98 |
| route_types | TEXT | yes | JSON array of GTFS route_type values (3 = bus, 0 = tram/LRT, 2 = rail, 715 = demand-responsive). Values: '[3]', '[0]', '[3,715]', '[715]', '[2]', '[5]', '[8]'. | [3] |
| city_gtfs | TEXT | yes | City name in GTFS. | תל אביב יפו |

### Socio-economic & crime

CBS socio-economic clusters and Israel Police crime files.

#### settlement_socio

Table · 1,110 rows · primary key (settlement_code)

CBS 2021 socio-economic index per settlement (1,110 rows): cluster 1 (lowest) … 10 (highest), index value and rank.

Joins: settlement_code → settlements.code

| column | type | null | description | example |
|---|---|---|---|---|
| settlement_code | INTEGER | no | Primary key → settlements.code. | 5000 |
| name | TEXT | yes | Name as published by CBS. | תל אביב-יפו |
| level | TEXT | yes | 'local_authority', 'rc_locality' (locality inside a regional council) or 'regional_council_fallback' (13 localities carrying their council's value). Values: 'rc_locality', 'local_authority', 'regional_council_fallback'. | local_authority |
| cluster | INTEGER | yes | Socio-economic cluster 1 (lowest) … 10 (highest). | 8 |
| index_value | REAL | yes | Standardised index value (higher = better-off). | 1.2925 |
| rank | INTEGER | yes | Rank within rank_of (1 = lowest). | 227 |
| rank_of | INTEGER | yes | Size of the ranking the rank belongs to (255 local authorities or 996 rc localities). | 255 |
| population | INTEGER | yes | Population used by CBS. | 466862 |
| cluster_2019 | INTEGER | yes | Cluster in the 2019 release. | 8 |
| regional_council | TEXT | yes | Regional council name (rc localities). NULL otherwise. | לכיש |
| rc_cluster | INTEGER | yes | The regional council's own cluster. NULL otherwise. | 7 |
| year | INTEGER | yes | Index year (2021). | 2021 |
| source | TEXT | yes | CBS table reference. Values: 'CBS socio-economic index 2021, table 8 (https://www.cbs.gov.il/he/publications/DocLib/2025/1955/t08.xlsx)', 'CBS socio-economic index 2021, table 2 (https://www.cbs.gov.il/he/publications/DocLib/2025/1955/t02.xlsx)', 'CBS socio-economic index 2021, table 2 (https://www.cbs.gov.il/he/publications/DocLib/2025/1955/t02.xlsx) - the locality's regional council (no locality index computed)'. | CBS socio-economic index 2021, table 2 (https://www.cbs.gov.il/he/publications/DocLib/2… |

#### stat_area_socio

Table · 1,641 rows · primary key (stat_area_id)

CBS 2021 socio-economic index per statistical area (1,641 areas in 81 cities / local councils).

Joins: settlement_code → settlements.code

| column | type | null | description | example |
|---|---|---|---|---|
| stat_area_id | INTEGER | no | Primary key = settlement_code × 10000 + stat_area (CBS 2011 geography). | 50000113 |
| settlement_code | INTEGER | yes | → settlements.code. | 5000 |
| settlement_name | TEXT | yes | Settlement name. | תל אביב -יפו |
| stat_area | INTEGER | yes | Statistical area number within the settlement. | 113 |
| population | INTEGER | yes | Population of the area. | 12519 |
| index_value | REAL | yes | Index value. | 1.9222 |
| rank | INTEGER | yes | Rank among all 1,641 areas (1 = lowest). | 1566 |
| rank_of | INTEGER | yes | 1641. | 1641 |
| cluster | INTEGER | yes | Cluster 1 … 10. | 9 |
| year | INTEGER | yes | 2021. | 2021 |
| source | TEXT | yes | CBS table reference. Values: 'CBS socio-economic index 2021, table 12 (https://www.cbs.gov.il/he/publications/DocLib/2025/1955/t12.xlsx)'. | CBS socio-economic index 2021, table 12 (https://www.cbs.gov.il/he/publications/DocLib/… |

Indexes: ix_stat_area_socio_settlement(settlement_code)

#### parcel_stat_area_socio

Table · 233,599 rows · primary key (gush, chelka)

Statistical-area socio-economic cluster per parcel (233,599 parcel-precision points inside an indexed area). Elsewhere fall back to settlement_socio.

Joins: gush, chelka → parcels.(gush, chelka); stat_area_id → stat_area_socio.stat_area_id

| column | type | null | description | example |
|---|---|---|---|---|
| gush | INTEGER | no | Block number. | 6212 |
| chelka | INTEGER | no | Parcel number. PK (gush, chelka). | 418 |
| stat_area_id | INTEGER | no | → stat_area_socio.stat_area_id. | 50000314 |
| stat_area_settlement_code | INTEGER | yes | Settlement of the statistical area (differs from the parcel's for ~1.7%: boundary points). | 5000 |
| cluster | INTEGER | yes | Cluster 1 … 10. | 9 |
| index_value | REAL | yes | Index value. | 1.984 |

Indexes: ix_parcel_stat_area_socio_area(stat_area_id)

#### crime_settlement_year

Table · 1,235 rows · primary key (settlement_code, year)

Israel Police crime case files per settlement and year, 2021–2025 (217 settlements), with per-1,000 rates and category counts.

- Compare years with per_1000_index (national = 100 each year): the 2021–2022 files hold about half the cases of 2023–2025.

Joins: settlement_code → settlements.code

| column | type | null | description | example |
|---|---|---|---|---|
| settlement_code | INTEGER | no | → settlements.code. | 5000 |
| year | INTEGER | no | Year (2021–2025). | 2025 |
| settlement_name | TEXT | yes | Settlement name. | תל אביב יפו |
| quarters | INTEGER | yes | Quarters of data in the year (4 = full year). | 4 |
| cases | INTEGER | yes | Distinct case files. | 27484 |
| offenses | INTEGER | yes | Offense records. | 37064 |
| population | INTEGER | yes | Today's population (used for every year). NULL for some. | 601640 |
| population_source | TEXT | yes | Where population came from. Values: 'nadlan.db settlements.population', 'geo_settlements (population_authority)'. | nadlan.db settlements.population |
| per_1000 | REAL | yes | Cases per 1,000 residents. | 45.68 |
| per_1000_index | REAL | yes | per_1000 relative to the national rate of the same year (100 = national). Use this to compare across years. | 186.4 |
| small_population | INTEGER | yes | 1 = population < 2,000 (noisy rates). | 0 |
| top_categories | TEXT | yes | JSON array [{"group", "cases"}] of the top 3 categories (Hebrew group names). | [{"group":"עבירות כלפי הרכוש","cases":17745},{"group":"עבירות סדר ציבורי","cases":6938}… |
| police_station | TEXT | yes | Police station name. | תחנת לב תא ירקון |
| cases_property | INTEGER | yes | Property-crime cases. | 17745 |
| cases_public_order | INTEGER | yes | Public-order cases. | 6938 |
| cases_violence | INTEGER | yes | Violence cases. | 3778 |
| cases_fraud | INTEGER | yes | Fraud cases. | 1270 |
| cases_security | INTEGER | yes | Security-offense cases. | 224 |
| cases_morality_drugs | INTEGER | yes | Morality / drugs cases. | 1372 |
| cases_sexual | INTEGER | yes | Sexual-offense cases. | 396 |
| cases_economic | INTEGER | yes | Economic-offense cases. | 501 |
| cases_traffic | INTEGER | yes | Traffic-offense cases. | 268 |
| cases_licensing | INTEGER | yes | Licensing-offense cases. | 130 |
| cases_against_person | INTEGER | yes | Offenses against a person (other). | 51 |
| cases_administrative | INTEGER | yes | Administrative-offense cases. | 5 |

Indexes: ix_crime_settlement_year_year(year, settlement_code)

#### crime_station_year

Table · 448 rows · primary key (police_station_code, year)

Crime case files per police station and year (2021–2025), including cases without a settlement (roads, open areas).

| column | type | null | description | example |
|---|---|---|---|---|
| police_station_code | INTEGER | no | Police station code (0 = national total row). | 31311000 |
| year | INTEGER | no | Year. | 2023 |
| police_station | TEXT | yes | Police station name (Hebrew). | תחנת באר שבע נגב |
| cases | INTEGER | yes | Case files. | 14431 |
| offenses | INTEGER | yes | Offense records. | 19147 |
| cases_without_settlement | INTEGER | yes | Cases not attributed to a settlement. | 652 |
| settlement_codes | TEXT | yes | JSON array of settlement codes served by the station. | [9000] |

### Macro, rents & yields

CPI, real-price factor, rates, dwelling indices, rents, yields and affordability.

#### macro_month

Table · 345 rows · primary key (month)

Monthly macro series 1998-01 … 2026-09: CPI and the real-price factor, CBS dwelling price indices, rent index, Bank of Israel rate, prime, mortgage rates, average wage.

- Real prices: nominal ₪ × real_factor = June-2026 ₪ (meta.real_price_base_month). Join on month = substr(deal_date, 1, 7) or agg_national_month.month.
- housing_price_index month = first month of CBS's two-month window; last ~6 months provisional.

Joins: month → agg_national_month.month

| column | type | null | description | example |
|---|---|---|---|---|
| month | TEXT | no | Primary key 'YYYY-MM'. | 2026-06 |
| cpi | REAL | yes | Consumer price index, chained, 2024 average = 100. | 104.8 |
| real_factor | REAL | yes | cpi['2026-06'] / cpi[month]: multiply a nominal ₪ amount of that month to get June-2026 ₪. NULL when no CPI yet. | 1 |
| housing_price_index | REAL | yes | CBS dwelling price index (1993 = 100). | 594.8 |
| new_dwellings_index | REAL | yes | CBS new-dwellings price index (from 2017-10). NULL before. | 541.2 |
| rent_index | REAL | yes | CPI rent component (2024 = 100). | 106.4 |
| owner_housing_index | REAL | yes | CPI owner-occupied housing component (2024 = 100). | 107.8 |
| boi_rate | REAL | yes | Bank of Israel policy rate, % (end of month). | 3.75 |
| mortgage_rate_unlinked | REAL | yes | Average rate on new non-indexed fixed-rate housing loans, % (from 2011-07). | 4.73 |
| mortgage_rate_linked | REAL | yes | Average rate on new CPI-indexed housing loans, % real (from 2011-07). | 3.28 |
| mortgage_rate_linked_fixed | REAL | yes | Average rate on new CPI-indexed fixed-rate housing loans, % real (from 2011-07). | 3.27 |
| mortgage_rate_variable_unlinked | REAL | yes | Average rate on new non-indexed variable-rate (prime-track) housing loans, % — 2016-01 … 2024-01 only. | 1.57 |
| prime_rate | REAL | yes | Prime rate = boi_rate + 1.5, %. | 5.25 |
| avg_wage | REAL | yes | Average monthly wage per employee post, nominal ₪, seasonally adjusted. | 14275.4 |
| avg_wage_israeli | REAL | yes | Same for Israeli employees only. | 14605.1 |
| cpi_base_note | TEXT | yes | How the CPI series was chained. Values: 'chained by CBS's linking rule to the latest base '2024 ממוצע' = 100'. | chained by CBS's linking rule to the latest base '2024 ממוצע' = 100 |

#### macro_district_month

Table · 630 rows · primary key (district, month)

CBS dwelling price index per district and month (from 2017-10), linked to the national index.

Joins: district → districts.name

| column | type | null | description | example |
|---|---|---|---|---|
| district | TEXT | no | District (CBS form) → districts.name. Values: 'הדרום', 'המרכז', 'הצפון', 'חיפה', 'ירושלים', 'תל אביב'. | תל אביב |
| month | TEXT | no | 'YYYY-MM' (first month of the CBS two-month window). | 2026-06 |
| housing_price_index | REAL | yes | District dwelling price index (linked to the national scale). | 563.2 |
| pct_change_month | REAL | yes | Change vs previous period, %. | 0.7 |
| national_index | REAL | yes | National index for the same month. | 594.8 |
| cbs_code | INTEGER | yes | CBS series code. | 60400 |

#### rent_city

Table · 4,354 rows · primary key (area_type, area_name, rooms_bucket, period)

CBS average monthly free-market rent of CURRENT tenancies (not asking rents) for the country, 6 districts and 18 large cities; quarterly 2019-Q1 … 2026-Q2 and yearly 2018–2025.

- rooms_bucket here is a rent bucket ('1-2', '2.5-3', '3.5-4', '4.5-6' through 2025 / '4.5+' in 2026, 'all') — not the deals rooms_bucket.

Joins: settlement_code → settlements.code

| column | type | null | description | example |
|---|---|---|---|---|
| area_type | TEXT | no | 'national', 'district' or 'city'. Values: 'city', 'district', 'national'. | city |
| area_name | TEXT | no | Area name (Hebrew). | תל אביב |
| rooms_bucket | TEXT | no | Rent rooms bucket: '1-2', '2.5-3', '3.5-4', '4.5-6', '4.5+' (2026), 'all'. Values: 'all', '3.5-4', '2.5-3', '1-2', '4.5-6', '4.5+'. | all |
| period | TEXT | no | 'YYYY-Qn' for quarters or 'YYYY' for years. | 2026-Q2 |
| period_type | TEXT | no | 'quarter' or 'year'. Values: 'quarter', 'year'. | quarter |
| year | INTEGER | yes | Year of the period. | 2026 |
| settlement_code | INTEGER | yes | City code → settlements.code (city rows only). | 5000 |
| district | TEXT | yes | District name (district rows). Values: 'תל אביב', 'ירושלים', 'חיפה', 'הצפון', 'המרכז', 'הדרום'. | הדרום |
| district_code | INTEGER | yes | CBS district code (district rows). | 6 |
| avg_rent | REAL | yes | Average monthly rent in ₪. | 7421.4 |
| n | INTEGER | yes | Sample size (2019–2021 only). | 70 |
| sampling_error | REAL | yes | Sampling error, ₪ or %. | 59.5 |
| source | TEXT | yes | CBS table reference. Values: 'CBS price statistics bulletin table 4.9 (2026/price08a)', 'CBS price statistics bulletin table 4.9 (2024/price12a)', 'CBS price statistics bulletin table 4.9 (2022/price12a)', 'CBS price statistics bulletin table 4.9 (2023/price12a)', 'CBS price statistics bulletin table 4.9 (2021/price12a)', 'CBS price statistics bulletin table 4.9 (2020/price12a)', 'CBS price statistics bulletin table 4.9 (2025/price12a)'. | CBS price statistics bulletin table 4.9 (2026/price08a) |
| source_file | TEXT | yes | Source publication URL. Values: 'https://www.cbs.gov.il/he/publications/Madad/DocLib/2026/price08a/a4_9_h.xlsx', 'https://www.cbs.gov.il/he/publications/Madad/DocLib/2024/price12a/a4_9_h.xls', 'https://www.cbs.gov.il/he/publications/Madad/DocLib/2022/price12a/a4_9_h.xls', 'https://www.cbs.gov.il/he/publications/Madad/DocLib/2023/price12a/a4_9_h.xls', 'https://www.cbs.gov.il/he/publications/Madad/DocLib/2021/price12a/a4_9_h.xls', 'https://www.cbs.gov.il/he/publications/Madad/DocLib/2020/price12a/a4_9_h.xls', 'https://www.cbs.gov.il/he/publications/Madad/DocLib/2025/price12a/a4_9_h.xlsx'. | https://www.cbs.gov.il/he/publications/Madad/DocLib/2026/price08a/a4_9_h.xlsx |

Indexes: ix_rent_city_settlement(settlement_code, rooms_bucket, period)

#### gross_yield

Table · 922 rows · primary key (level, area_name, window, rooms_bucket)

Indicative gross rental yield per city / district / nation: median existing-stock apartment price vs 12 × average rent, for the 12m window and years 2019–2025.

- Indicative only: a median sale price vs an average rent of other homes.

Joins: settlement_code → settlements.code

| column | type | null | description | example |
|---|---|---|---|---|
| level | TEXT | no | 'city', 'district' or 'national'. Values: 'city', 'district', 'national'. | city |
| area_name | TEXT | no | Area name (Hebrew). | תל אביב |
| window | TEXT | no | '12m' (to the stats anchor) or a year '2019' … '2025'. Values: '2025', '2024', '2023', '12m', '2022', '2021', '2020', '2019'. | 12m |
| rooms_bucket | TEXT | no | Rent rooms bucket ('1-2', '2.5-3', '3.5-4', '4.5-6', 'all'). Values: 'all', '3.5-4', '2.5-3', '4.5-6', '1-2'. | all |
| settlement_code | INTEGER | yes | City code (city rows) → settlements.code. | 5000 |
| district | TEXT | yes | District (district rows). Values: 'תל אביב', 'ירושלים', 'חיפה', 'הצפון', 'המרכז', 'הדרום'. | הדרום |
| window_start | TEXT | yes | Window start 'YYYY-MM-DD'. Values: '2025-07-01', '2025-01-01', '2024-01-01', '2023-01-01', '2022-01-01', '2021-01-01', '2020-01-01', '2019-01-01'. | 2025-07-01 |
| window_end | TEXT | yes | Window end 'YYYY-MM-DD'. Values: '2026-06-30', '2025-12-31', '2024-12-31', '2023-12-31', '2022-12-31', '2021-12-31', '2020-12-31', '2019-12-31'. | 2026-06-30 |
| n_deals | INTEGER | yes | Existing-stock in-stats apartment deals behind median_price. | 1693 |
| median_price | INTEGER | yes | Median price in ₪. | 3720000 |
| avg_rent_monthly | REAL | yes | Average monthly rent in ₪ over rent_periods. | 7329 |
| rent_periods | TEXT | yes | Comma-separated rent quarters averaged. Values: '2025-Q3,2025-Q4,2026-Q1,2026-Q2', '2025-Q1,2025-Q2,2025-Q3,2025-Q4', '2024-Q1,2024-Q2,2024-Q3,2024-Q4', '2023-Q1,2023-Q2,2023-Q3,2023-Q4', '2022-Q1,2022-Q2,2022-Q3,2022-Q4', '2021-Q1,2021-Q2,2021-Q3,2021-Q4', '2020-Q1,2020-Q2,2020-Q3,2020-Q4', '2019-Q1,2019-Q2,2019-Q3,2019-Q4'. | 2025-Q3,2025-Q4,2026-Q1,2026-Q2 |
| annual_rent | INTEGER | yes | 12 × avg_rent_monthly, ₪. | 87943 |
| price_to_rent | REAL | yes | median_price / annual_rent. | 42.3 |
| gross_yield_pct | REAL | yes | annual_rent / median_price × 100, percent (e.g. 3.07). | 2.36 |
| low_n | INTEGER | yes | 1 when n_deals < 20. | 0 |
| rent_note | TEXT | yes | Note about rent bucket changes. NULL usually. Values: '2026 quarters: CBS rent group '4.5+' (includes > 6 rooms) stands in for 4.5-6'. | 2026 quarters: CBS rent group '4.5+' (includes > 6 rooms) stands in for 4.5-6 |

Indexes: ix_gross_yield_settlement(settlement_code, window)

#### affordability

Table · 237 rows · primary key (level, area, period_kind, period, stock)

Median apartment price ÷ national average monthly wage, national and per district, yearly 1998–2025 plus the 12m window.

| column | type | null | description | example |
|---|---|---|---|---|
| level | TEXT | no | 'national' or 'district'. Values: 'district', 'national'. | national |
| area | TEXT | no | 'ישראל' or the district name. Values: 'תל אביב', 'ישראל', 'ירושלים', 'חיפה', 'הצפון', 'המרכז', 'הדרום', 'יהודה והשומרון'. | ישראל |
| period_kind | TEXT | no | 'year' or '12m'. Values: 'year', '12m'. | 12m |
| period | TEXT | no | The year ('2025') or the 12m range ('2025-07-01..2026-06-30'). | 2025-07-01..2026-06-30 |
| stock | TEXT | no | 'all' or 'existing' (existing stock only). Values: 'all', 'existing'. | all |
| median_price_apartment | INTEGER | yes | Median apartment price in ₪. | 2150000 |
| n_deals | INTEGER | yes | Deals behind the median. | 56681 |
| avg_monthly_wage | REAL | yes | National average monthly wage in ₪. | 13987 |
| months_of_wage | REAL | yes | median_price_apartment / avg_monthly_wage. | 153.7 |
| years_of_wage | REAL | yes | months_of_wage / 12. | 12.81 |
| wage_scope | TEXT | yes | 'national' (no district wages exist). Values: 'national'. | national |

### Urban renewal & discount projects

Declared renewal compounds and subsidised lottery projects.

#### renewal_compounds

Table · 978 rows · primary key (compound_id)

978 declared urban-renewal compounds (מתחמי התחדשות עירונית: פינוי-בינוי / עיבוי) with status, plan, units and location. No TAMA 38.

Joins: settlement_code → settlements.code

| column | type | null | description | example |
|---|---|---|---|---|
| compound_id | INTEGER | no | Primary key (Urban Renewal Authority id). → parcel_renewal.compound_id. | 8003420 |
| name | TEXT | yes | Compound name (Hebrew). | הדר יוסף |
| settlement_code | INTEGER | yes | → settlements.code. | 5000 |
| settlement_name | TEXT | yes | Settlement name. | תל אביב יפו |
| track | TEXT | yes | Normalised track: 'פינוי-בינוי', 'עיבוי', 'משולב פינוי-בינוי ועיבוי', 'משולב', 'טרם הוחלט'. NULL for ~30%. Values: 'פינוי-בינוי', 'משולב פינוי-בינוי ועיבוי', 'עיבוי', 'משולב', 'טרם הוחלט'. | פינוי-בינוי |
| track_raw | TEXT | yes | Track as published. Values: 'פינוי בינוי', 'משולב פ"ב-עיבוי', 'בינוי פינוי', 'עיבוי', 'משולב', 'טרם הוחלט'. | פינוי בינוי |
| declaration_track | TEXT | yes | 'מיסוי' (tax track), 'רשויות' (local-authority track) or 'טרם הוכרז'. Values: 'מיסוי', 'רשויות', 'טרם הוכרז'. | רשויות |
| status | TEXT | yes | Planning status (Hebrew), see status_rank. Values: 'תכנית מאושרת לפני מימוש', 'תכנון סטטוטורי', 'תכנית מאושרת - אחרי רישוי', 'תכנון ראשוני', 'תכנית מאושרת במימוש'. | תכנית מאושרת במימוש |
| status_rank | INTEGER | yes | 1 תכנון ראשוני < 2 תכנון סטטוטורי < 3 תכנית מאושרת לפני מימוש < 4 תכנית מאושרת - אחרי רישוי < 5 תכנית מאושרת במימוש. | 5 |
| status_code | INTEGER | yes | Source status code. | 4 |
| status_date | TEXT | yes | Date of the status, 'YYYY-MM-DD'. | 2003-07-01 |
| in_execution | INTEGER | yes | 1 = in execution. | 1 |
| declared_date | TEXT | yes | Declaration date. | 2006-08-20 |
| plan_valid_date | TEXT | yes | Plan approval date. | 2003-07-01 |
| plan_valid_year | INTEGER | yes | Plan approval year. | 2003 |
| plan_number | TEXT | yes | Statutory plan number (e.g. 'גב/490'). | תא/מק/2204/א |
| mavat_url | TEXT | yes | Link to the plan on mavat.iplan.gov.il. | https://mavat.iplan.gov.il/SV4/1/5051121/310 |
| units_existing | INTEGER | yes | Existing housing units. | 666 |
| units_added | INTEGER | yes | Units added by the plan. | 600 |
| units_planned | INTEGER | yes | Total planned units. | 1544 |
| units_permits | INTEGER | yes | Units with building permits. NULL when unknown. | 210 |
| area_m2 | REAL | yes | Compound area in m². | 72382 |
| n_parcels | INTEGER | yes | Parcels in the compound. | 113 |
| gushim | TEXT | yes | Comma-separated gush numbers. | 6636 |
| settlement_code_geo | INTEGER | yes | Settlement code from the polygon location. | 5000 |
| source_match | TEXT | yes | 'list+polygon', 'list_only' or 'polygon_only'. Values: 'list+polygon', 'list_only', 'polygon_only'. | list+polygon |
| gis_source | TEXT | yes | GIS layer the polygon came from. Values: 'מנהל התכנון 1', 'קליטה ידנית', 'מנהל התכנון 2'. | קליטה ידנית |
| lat | REAL | yes | Compound centroid latitude. | 32.108621 |
| lon | REAL | yes | Compound centroid longitude. | 34.821221 |
| has_polygon | INTEGER | no | 1 when a polygon exists (in the map tiles, not the DB). | 1 |

Indexes: ix_renewal_compounds_settlement(settlement_code, status_rank)

#### parcel_renewal

Table · 26,239 rows · primary key (gush, chelka)

Parcels inside a renewal compound (26,239 rows).

Joins: gush, chelka → parcels.(gush, chelka); compound_id → renewal_compounds.compound_id

| column | type | null | description | example |
|---|---|---|---|---|
| gush | INTEGER | no | Block number. | 310 |
| chelka | INTEGER | no | Parcel number. PK (gush, chelka). | 17 |
| compound_id | INTEGER | no | → renewal_compounds.compound_id. | 8002186 |
| overlap_share | REAL | yes | Share of the parcel polygon inside the compound (> 0.5). NULL for point matches. | 1 |
| n_compounds | INTEGER | yes | Number of compounds the parcel touches. | 1 |
| method | TEXT | yes | 'polygon_overlap', 'centroid_shuma' or 'centroid_cancelled'. Values: 'polygon_overlap', 'centroid_shuma', 'centroid_cancelled'. | polygon_overlap |

Indexes: ix_parcel_renewal_compound(compound_id)

#### gush_renewal

Table · 953 rows · primary key (gush)

Urban-renewal summary per gush (953 rows): compounds, approvals and units allocated by area share.

Joins: gush → gushim.gush

| column | type | null | description | example |
|---|---|---|---|---|
| gush | INTEGER | no | Primary key → gushim.gush. | 10444 |
| n_compounds | INTEGER | yes | Compounds overlapping the gush. | 10 |
| compound_ids | TEXT | yes | Comma-separated compound ids. | 4353,4383,4425,38941,41540,5000650,8001305,8001307,8001659,8003057 |
| n_approved | INTEGER | yes | Compounds with status_rank ≥ 3. | 5 |
| n_in_execution | INTEGER | yes | Compounds in execution. | 2 |
| planned_units | INTEGER | yes | Planned units allocated to this gush by area share (additive across gushim). | 10662 |
| existing_units | INTEGER | yes | Existing units allocated by area share. | 2129 |
| added_units | INTEGER | yes | Added units allocated by area share. | 8538 |
| renewal_area_m2 | REAL | yes | Compound area inside the gush, m². | 622795 |
| n_parcels | INTEGER | yes | Renewal parcels in the gush. | 265 |

#### discount_projects

Table · 2,087 rows · primary key (project_id)

2,087 official subsidised-housing lottery projects (מחיר למשתכן / מחיר מטרה / דירה בהנחה) from the Housing ministry tracker and Israel Land Authority tenders, with units, official ₪/m², lottery dates, location and deals attributed.

- Attribution is by place, time and price, not by buyer: say 'deals attributed to the project'.
- Tracker covers 2016-02 … 2025-01.

Joins: settlement_code → settlements.code; gush → gushim.gush

| column | type | null | description | example |
|---|---|---|---|---|
| project_id | TEXT | no | Primary key: 'moch:<ministry project id>' or 'ila:<tender id>:<plot>'. ← deals.discount_project_id. | moch:52 |
| moch_project_id | INTEGER | yes | Housing ministry project id. NULL for ILA-only plots. | 52 |
| program | TEXT | yes | 'מחיר למשתכן', 'מחיר מטרה' or 'דיור במחיר מופחת'. Values: 'מחיר למשתכן', 'מחיר מטרה', 'דיור במחיר מופחת'. | מחיר למשתכן |
| settlement_code | INTEGER | yes | → settlements.code. | 8500 |
| settlement_name | TEXT | yes | Settlement name. | רמלה |
| neighborhood | TEXT | yes | Neighbourhood name as published. NULL often. | מערב |
| name | TEXT | yes | Project name / plot label. | מערב |
| developer | TEXT | yes | Developer name (cut at 35 characters). | פרשקובסקי השקעות ובניין בע"מ |
| units | INTEGER | yes | Units in the first lottery. NULL when unknown. | 572 |
| units_local_residents | INTEGER | yes | Units reserved for local residents. | 135 |
| price_per_sqm | REAL | yes | Official price per m² in ₪ (for ILA-only plots the winning bid). | 8768 |
| lottery_id | INTEGER | yes | Lottery id. | 238 |
| lottery_date | TEXT | yes | Lottery date 'YYYY-MM-DD'. | 2017-07-24 |
| signup_end_date | TEXT | yes | Sign-up end date. | 2017-07-09 |
| last_lottery_date | TEXT | yes | Date of the latest lottery. | 2018-11-19 |
| n_lotteries | INTEGER | yes | Number of lotteries held. | 3 |
| subscribers | INTEGER | yes | Registered subscribers. | 1396 |
| winners_total | INTEGER | yes | Total winners across lotteries. | 572 |
| permit_status | TEXT | yes | Building-permit status (Hebrew). Values: 'היתר מלא', 'החלטת ועדה (היתר בתנאים)', 'טרם הוגשה בקשה', 'הוגשה בקשה', 'היתר מלא לחלק מהמגרשים', 'הוגשה בקשה לחלק מהמגרשים', 'החלטת ועדה (היתר בתנאים) לחלק מהמגרשים'. | היתר מלא |
| project_status | TEXT | yes | Project status (Hebrew). Values: 'בתהליכי הגרלה', 'בחירת דירות', 'בקרת חוזים', 'בקרה לאחר אכלוס'. | בחירת דירות |
| marketing_rep | TEXT | yes | Marketing body: 'משב"ש' or 'רמ"י'. Values: 'משב"ש', 'רמ"י'. | רמ"י |
| lottery_round | TEXT | yes | Lottery round type (Hebrew). | יוני 2017 |
| rmi_tender | TEXT | yes | Israel Land Authority tender number (e.g. '107/2015'). | 180/2016 |
| rmi_tender_id | INTEGER | yes | ILA tender id. | 20160180 |
| rmi_committee_date | TEXT | yes | ILA committee date. | 2016-12-26 |
| winning_bid | REAL | yes | Winning bid (₪ per m² or per unit as published). | 8657 |
| plans | TEXT | yes | Plan and plot references. | לה/6/170 מגרש 201 |
| gush | INTEGER | yes | Main gush. NULL when unlocated. | 7323 |
| chelka | INTEGER | yes | Main chelka. NULL when unknown. | 17 |
| gushim | TEXT | yes | Comma-separated gushim. | 7323,4351 |
| n_parcels | INTEGER | yes | Parcels linked. | 3 |
| lat | REAL | yes | Latitude. NULL for ~289 unlocated projects. | 31.932631 |
| lon | REAL | yes | Longitude. | 34.850064 |
| location_source | TEXT | yes | 'moch_gis_polygon', 'ila_parcels', 'gush_centroid' or 'none'. Values: 'moch_gis_polygon', 'ila_parcels', 'gush_centroid', 'none'. | moch_gis_polygon |
| source | TEXT | yes | Source datasets joined. Values: 'ila_tenders', 'moch_lotteries+ila_tenders', 'moch_lotteries+moch_gis_2019+ila_tenders', 'moch_lotteries+moch_gis_2019', 'moch_lotteries', 'moch_gis_2019', 'moch_gis_2019+ila_tenders'. | moch_lotteries+moch_gis_2019+ila_tenders |
| has_polygon | INTEGER | no | 1 when a project polygon exists. | 1 |
| n_deals_official | INTEGER | no | Deals with deals.discount_project_id = project_id (capped at max(units, winners_total)). | 572 |

Indexes: ix_discount_projects_gush(gush); ix_discount_projects_settlement(settlement_code, lottery_date)

#### discount_project_parcels

Table · 7,275 rows

Parcels (or whole gushim when chelka is NULL) of each discount project (7,275 rows).

Joins: project_id → discount_projects.project_id; gush, chelka → parcels.(gush, chelka)

| column | type | null | description | example |
|---|---|---|---|---|
| project_id | TEXT | no | → discount_projects.project_id. | moch:50845 |
| gush | INTEGER | no | Block number. | 187 |
| chelka | INTEGER | yes | Parcel number; NULL when only the gush is known. | 12 |
| source | TEXT | yes | 'ila_tender', 'moch_gis_overlap' or both. Values: 'ila_tender', 'moch_gis_overlap', 'ila_tender+moch_gis_overlap'. | ila_tender |

Indexes: ix_discount_project_parcels_parcel(gush, chelka); ix_discount_project_parcels_project(project_id)

#### discount_match

Table · 8,157 rows

Validation table of the discount-project flag per gush × deal year × official project: flagged vs unflagged counts and medians.

Joins: gush → gushim.gush; official_project_id → discount_projects.project_id

| column | type | null | description | example |
|---|---|---|---|---|
| gush | INTEGER | no | Block number. | 187 |
| deal_year | INTEGER | no | Deal year. | 2021 |
| official_project_id | TEXT | yes | → discount_projects.project_id. NULL for flagged_no_located_project rows. | moch:50845 |
| component | TEXT | yes | Group of projects sharing gushim (attribution unit). | 2767 |
| n_flagged | INTEGER | yes | Deals flagged is_discount_project. | 109 |
| n_unflagged_newbuild | INTEGER | yes | Unflagged new-build deals. | 21 |
| n_unflagged_near_official_price | INTEGER | yes | Unflagged deals priced near the official ₪/m². | 0 |
| n_other_residential | INTEGER | yes | Other residential deals. | 8 |
| n_in_project_parcels | INTEGER | yes | Deals on the project's own parcels. | 107 |
| n_unflagged_near_price_in_parcels | INTEGER | yes | Unflagged near-price deals on the project's parcels. | 0 |
| median_ppsqm_flagged | REAL | yes | Median ₪/m² of flagged deals. | 11774 |
| median_ppsqm_unflagged_new | REAL | yes | Median ₪/m² of unflagged new builds. | 15899 |
| official_price_per_sqm | REAL | yes | Official project ₪/m². | 8720 |
| match_kind | TEXT | yes | 'official_project' or 'flagged_no_located_project'. Values: 'official_project', 'flagged_no_located_project'. | official_project |
| settlement_code | INTEGER | yes | Settlement (flagged_no_located_project rows). | 1034 |
| settlement_has_official_project | INTEGER | yes | 1 when the settlement has any official project. | 0 |

Indexes: ix_discount_match_gush(gush, deal_year)

### Reference & metadata

Lookup tables and build metadata.

#### property_groups

Table · 9 rows · primary key (key)

The 9 normalised property groups (deals.property_group) with Hebrew labels, residential flag, display order, icon name and deal count.

| column | type | null | description | example |
|---|---|---|---|---|
| key | TEXT | no | Primary key: apartment, garden_apartment, penthouse, house, land, commercial, agriculture, parking, other. Values: 'agriculture', 'apartment', 'commercial', 'garden_apartment', 'house', 'land', 'other', 'parking', 'penthouse'. | apartment |
| label_he | TEXT | no | Hebrew singular label, e.g. 'דירה'. Values: 'קרקע', 'נכס מסחרי', 'נכס חקלאי', 'חניה / מחסן', 'דירת גן', 'דירת גג', 'דירה', 'בית פרטי', 'אחר'. | דירה |
| label_plural_he | TEXT | no | Hebrew plural label, e.g. 'דירות'. Values: 'קרקעות ומגרשים', 'עסקאות אחרות', 'נכסים מסחריים', 'חקלאות ונחלות', 'חניות ומחסנים', 'דירות גן', 'דירות גג ופנטהאוזים', 'דירות', 'בתים פרטיים וקוטג'ים'. | דירות |
| is_residential | INTEGER | no | 1 for apartment, garden_apartment, penthouse, house. | 1 |
| sort | INTEGER | no | Display order (1 = apartment). | 1 |
| icon | TEXT | no | lucide icon name used by the site (kebab-case). Values: 'tractor', 'store', 'square-parking', 'shapes', 'land-plot', 'house', 'flower-2', 'crown', 'building-2'. | building-2 |
| deals | INTEGER | no | Number of deals in the group. | 2261290 |

#### deal_natures

Table · 47 rows · primary key (raw)

Mapping of the 47 raw Hebrew transaction types (deals.deal_nature, מהות) to a property_group, with deal counts.

Joins: property_group → property_groups.key

| column | type | null | description | example |
|---|---|---|---|---|
| raw | TEXT | no | Primary key: raw Hebrew deal nature as in deals.deal_nature, e.g. 'דירה בבית קומות'. | דירה בבית קומות |
| property_group | TEXT | no | Property group it maps to → property_groups.key. Values: 'commercial', 'other', 'land', 'agriculture', 'house', 'parking', 'apartment', 'penthouse', 'garden_apartment'. | apartment |
| deals | INTEGER | no | Number of deals with this raw value. | 1946146 |

#### meta

Table · 89 rows · primary key (key)

Key/value build metadata (all values TEXT; CAST as needed, some are JSON): schema_version, built_at, min_date/max_date, stats_anchor_date, window_*_start/end, national_* headline statistics, thresholds, counts and data sources.

- Read window bounds from here instead of hard-coding dates: (SELECT value FROM meta WHERE key = 'window_12m_start').
- JSON values (sources, enrich_counts, months_completeness, …) can be unpacked with json_each().

| column | type | null | description | example |
|---|---|---|---|---|
| key | TEXT | no | Primary key: metadata key (see the meta key list in the docs). | stats_anchor_date |
| value | TEXT | yes | Value as TEXT (numbers, dates, or JSON). | 2026-06-30 |


## 5. meta keys

All values are TEXT (cast as needed); JSON values can be read with json_each / json_extract.

| key | value | meaning |
|---|---|---|
| built_at | 2026-09-28T08:55:24Z | UTC time the core DB was built. |
| change_method | existing: median ppsqm of existing-stock residential deals (is_new_build = 0), stat rule |  |
| completeness_ratio_threshold | 0.85 |  |
| data_complete_through | 2026-06-30 | Same as stats_anchor_date. |
| discount_heuristic_deals | 25247 |  |
| discount_official_deals | 61680 |  |
| discount_project_deals | 86927 |  |
| districts | ["המרכז", "תל אביב", "הדרום", "חיפה", "הצפון", "ירושלים", "יהודה והשומרון"] | JSON list of district names, busiest first. |
| duplicates_removed | 626608 |  |
| enrich_built_at | 2026-09-28T09:04:23Z | UTC time the enrichment tables were written. |
| enrich_bundles | {"location": "2026-09-28T07:52:19", "amenities": "2026-09-28T07:49:05", "market": "2026-09-28T07:46:55", "projects": "2026-09-28T09:03:51"} |  |
| enrich_counts | {"parcel_address": 245790, "parcel_buildings": 226621, "streets": 31687, "street_parcels": 259266, "neighborhoods": 1277, "parcel_neighbo… |  |
| enrich_coverage | {"address_pct_deals": 31.55, "street_any_pct_deals": 73.51, "neighborhoods": 1277, "neighborhood_settlements": 182, "neighborhood_pct_dea… |  |
| enrich_db_built_at | 2026-09-28T08:55:24Z |  |
| enrich_schema_version | 1.2 |  |
| enrich_tables | ["affordability", "agg_neighborhood_year", "crime_settlement_year", "crime_station_year", "discount_match", "discount_project_parcels", "… |  |
| geo_fallbacks | {"far_parcel_to_gush": {"parcels": 5, "deals": 12}, "far_to_settlement": {"parcels": 412, "deals": 667}, "no_gush_to_settlement": {"parce… |  |
| geo_far_from_own_settlement_deals | 272 |  |
| geo_far_km | 25.0 |  |
| geo_gush_deals | 280269 |  |
| geo_gush_pct | 8.82 |  |
| geo_none_pct | 0.0 |  |
| geo_parcel_approx_deals | 228044 |  |
| geo_parcel_approx_pct | 7.18 |  |
| geo_parcel_deals | 2895033 |  |
| geo_parcel_exact_deals | 2666989 |  |
| geo_parcel_exact_pct | 83.97 |  |
| geo_parcel_pct | 91.15 |  |
| geo_settlement_deals | 668 |  |
| geo_settlement_pct | 0.021 |  |
| geo_source_deals | {"cancelled": 215962, "gush": 280269, "parcel": 2666989, "settlement": 668, "shuma": 12082} |  |
| gushim | 12408 |  |
| in_stats_deals | 1790860 | Rows with in_stats = 1. |
| in_stats_ppsqm_deals | 1669094 | in_stats rows with price_per_sqm. |
| incomplete_from_month | 2026-07 | First incomplete month (is_incomplete = 1 from here on). |
| last_first_seen | 2026-09-19T11:22:31Z |  |
| max_date | 2026-09-17 | Last deal date (NOT the end of the statistics windows). |
| max_parcel_share_rank | 0.5 |  |
| merged_deals | 37403 |  |
| merged_rows_removed | 41622 |  |
| min_date | 1998-01-01 | First deal date. |
| min_n_change_gush | 20 |  |
| min_n_change_neighborhood | 50 |  |
| min_n_change_settlement | 50 |  |
| min_n_rank | 30 |  |
| min_n_rank_neighborhood | 20 |  |
| min_parcels_rank | 5 |  |
| months_completeness | [{"month": "2026-09", "residential": 72, "same_month_prev_year": 7609, "ratio": 0.009}, {"month": "2026-08", "residential": 978, "same_mo… | JSON: monthly residential counts vs the same month a year earlier (anchor rule). |
| multi_unit_deals | 71985 |  |
| national_deals_12m | 107515 | All deals nationally in the 12m window. |
| national_deals_after_anchor | 4594 | Deals reported after the anchor so far. |
| national_median_ppsqm_12m | 21844 | National median residential ₪/m², 12m. |
| national_median_ppsqm_apartment_12m | 21959 | National median apartment ₪/m², 12m. |
| national_median_ppsqm_existing_12m | 20298 | National existing-stock median ₪/m², 12m. |
| national_median_ppsqm_new_12m | 26463 | National new-build median ₪/m², 12m. |
| national_median_ppsqm_prev12m | 22068 | National median residential ₪/m², prev12m (pooled). |
| national_median_price_12m | 2200000 | National median residential price, 12m (₪). |
| national_median_price_apartment_12m | 2167878 | National median apartment price, 12m. |
| national_ppsqm_change_method | existing |  |
| national_ppsqm_change_pct | -1.3 | National existing-stock ₪/m² change, percent points. |
| national_ppsqm_change_pct_pooled | -1.0 |  |
| no_settlement_code_deals | 1065 |  |
| nonres_min_amount | {"parking": 10000, "land": 10000, "agriculture": 10000, "other": 10000, "commercial": 50000} | JSON: non-residential median floors per group (₪). |
| outlier_deals | 58558 |  |
| parcels | 381177 |  |
| partial_year | 2026 | Year with incomplete data. |
| privatization_deals | 718 |  |
| ranked_settlements | 92 |  |
| real_price_base_month | 2026-06 | Month that macro_month.real_factor converts to. |
| representative_weekday_gtfs | 2026-10-13 | GTFS day behind every trip count. |
| rooms_buckets | ["1-2", "3", "4", "5", "6+"] | JSON list of deals.rooms_bucket values. |
| schema_version | 1.2 | Schema version of this DB (additive changes only within a major). |
| settlements | 1139 |  |
| source_file | taxes-nadlan-full-f41fb496_append.csv |  |
| source_rows | 3844200 |  |
| sources | [{"bundle": "location", "name": "OpenStreetMap - Geofabrik extract israel-and-palestine-latest.osm.pbf", "url": "https://download.geofabr… | JSON array of every public data source with URL and licence. |
| stats_anchor_date | 2026-06-30 | End of the last complete month; every statistics window ends here. |
| total_deals | 3175970 | Rows in deals. |
| total_volume_ils | 4116091864997 | Sum of credible deal amounts, all time (₪). |
| window_12m_end | 2026-06-30 | End of the 12m window (= stats anchor). |
| window_12m_start | 2025-07-01 | Start of the 12m window. |
| window_24m_end | 2026-06-30 | End of the 24m window. |
| window_24m_start | 2024-07-01 | Start of the 24m window (gushim). |
| window_5y_end | 2026-06-30 | End of the 5y window. |
| window_5y_start | 2021-07-01 | Start of the 5y window. |
| window_prev12m_end | 2025-06-30 | End of the prev12m window. |
| window_prev12m_start | 2024-07-01 | Start of the prev12m window. |
| window_prev24m_end | 2024-06-30 | End of the prev24m window. |
| window_prev24m_start | 2022-07-01 | Start of the prev24m window. |

## 6. Example queries

### Basics & metadata

#### Example 1: List every table and view

Question: Which tables and views exist in the database?
Hebrew: רשימת כל הטבלאות והתצוגות

```sql
SELECT name, type
FROM sqlite_master
WHERE type IN ('table', 'view')
  AND name NOT LIKE 'sqlite_%'
  AND name NOT GLOB '*_fts_*'
  AND name NOT GLOB '*_rtree_*'
ORDER BY name
```

sqlite_master is the catalogue of every schema object and can be read with a plain SELECT. The GLOB filters hide the internal shadow tables that FTS5 and R*Tree create behind settlements_fts, streets_fts, neighborhoods_fts and parcels_rtree.

Tables: sqlite_master · verified 49 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-1

#### Example 2: Show the CREATE statement of a table

Question: What is the exact DDL of the deals and settlements tables?
Hebrew: הצגת הגדרת טבלה (CREATE)

```sql
SELECT name, sql
FROM sqlite_master
WHERE type = 'table' AND name IN ('deals', 'settlements')
```

The sql column keeps the original CREATE TABLE text, including inline comments that document the deals columns. Use it to see declared types, NOT NULL constraints and primary keys.

Tables: sqlite_master · verified 2 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-2

#### Example 3: List the columns of a table

Question: What columns does the neighborhoods table have, with their types?
Hebrew: רשימת העמודות של טבלה

```sql
SELECT cid, name, type, "notnull" AS not_null, pk
FROM pragma_table_info('neighborhoods')
ORDER BY cid
```

pragma_table_info() is a read-only table-valued function, so it works inside a SELECT. It returns one row per column with its declared type, NOT NULL flag and primary-key position.

Tables: neighborhoods · verified 47 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-3

#### Example 4: List the indexes of the deals table

Question: Which indexes exist on deals, so I can write queries that use them?
Hebrew: רשימת האינדקסים של טבלת העסקאות

```sql
SELECT name, sql
FROM sqlite_master
WHERE type = 'index' AND tbl_name = 'deals' AND sql IS NOT NULL
ORDER BY name
```

Filter deals by settlement_code, (gush, chelka), property_group, deal_date, deal_amount or price_per_sqm so SQLite can use one of these indexes. A filter on area, year_built or in_stats alone forces a scan of 3.18M rows.

Tables: sqlite_master · verified 11 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-4

#### Example 5: Stats anchor date and time windows

Question: What date do the statistics run to, and what are the 12-month, 24-month and 5-year windows?
Hebrew: תאריך העוגן וחלונות הזמן

```sql
SELECT key, value
FROM meta
WHERE key LIKE 'window_%'
   OR key IN ('schema_version', 'stats_anchor_date', 'incomplete_from_month', 'min_date', 'max_date', 'built_at')
ORDER BY key
```

Every precomputed window ends at stats_anchor_date (2026-06-30), not at max_date: later months are still incomplete because of reporting lag. Use these bounds for your own date filters so your numbers match the precomputed columns.

Tables: meta · verified 16 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-5

#### Example 6: Property groups and their deal counts

Question: What property types are there, which are residential, and how many deals does each have?
Hebrew: סוגי נכסים ומספר העסקאות

```sql
SELECT key, label_he, label_plural_he, is_residential, deals
FROM property_groups
ORDER BY sort
```

property_group is the normalised deal type used everywhere (deals, agg_* tables). The agg tables also have two pseudo-groups: all_residential (the 4 residential groups) and all (every deal, counts and volume only).

Tables: property_groups · verified 9 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-6

#### Example 7: Row counts of the main tables

Question: How many rows does each main table have?
Hebrew: מספר השורות בטבלאות העיקריות

```sql
SELECT 'deals' AS table_name, CAST(value AS INTEGER) AS row_count FROM meta WHERE key = 'total_deals'
UNION ALL
SELECT 'settlements', CAST(value AS INTEGER) FROM meta WHERE key = 'settlements'
UNION ALL
SELECT 'gushim', CAST(value AS INTEGER) FROM meta WHERE key = 'gushim'
UNION ALL
SELECT 'parcels', CAST(value AS INTEGER) FROM meta WHERE key = 'parcels'
UNION ALL
SELECT j.key, j.value FROM json_each((SELECT value FROM meta WHERE key = 'enrich_counts')) AS j
```

meta stores counts at build time, which is instant, whereas count(*) over deals walks an index of 3.18M entries. enrich_counts is a JSON object, and json_each() turns it into rows.

Tables: meta · verified 34 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-7

#### Example 8: Data sources and licences

Question: Where does the enrichment data come from and under what licence?
Hebrew: מקורות המידע והרישיונות

```sql
SELECT json_extract(j.value, '$.bundle') AS bundle,
       json_extract(j.value, '$.name') AS source,
       json_extract(j.value, '$.licence') AS licence,
       json_extract(j.value, '$.url') AS url
FROM json_each((SELECT value FROM meta WHERE key = 'sources')) AS j
ORDER BY bundle, source
```

meta.sources is a JSON array with one object per public source. json_each() plus json_extract() turn it into a table. Cite these sources when you publish derived numbers.

Tables: meta · verified 30 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-8

### Settlements & cities

#### Example 9: Settlement headline numbers

Question: What are the key price statistics for Haifa over the last 12 months?
Hebrew: נתוני מפתח של יישוב

```sql
SELECT code, name, name_en, district, population,
       deals_12m, n_stats_12m, median_price_12m,
       n_ppsqm_12m, median_ppsqm_12m,
       median_ppsqm_existing_12m, median_ppsqm_new_12m,
       ppsqm_change_pct, rank_ppsqm, n_discount_12m, deals_after_anchor
FROM settlements
WHERE name = 'חיפה'
```

The settlements table holds precomputed 12-month statistics (2025-07-01 to 2026-06-30) over all residential groups under the in_stats rule. ppsqm_change_pct compares existing-stock prices only and is NULL unless both windows have at least 50 deals.

Tables: settlements · verified 1 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-9

#### Example 10: Most expensive settlements per m²

Question: Which cities have the highest price per square metre?
Hebrew: היישובים היקרים ביותר למ״ר

```sql
SELECT rank_ppsqm, name, name_en, median_ppsqm_12m, n_ppsqm_12m, median_price_12m, ppsqm_change_pct
FROM settlements
WHERE rank_ppsqm IS NOT NULL
ORDER BY rank_ppsqm
LIMIT 20
```

rank_ppsqm is only set for settlements with at least 30 deals with a price per m² in 12 months, spread over at least 5 parcels. That keeps one luxury project from topping the list. The partial index ix_settlements_rank serves this query.

Tables: settlements · verified 20 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-10

#### Example 11: Biggest price risers and fallers

Question: Which cities saw the largest change in price per m² over the last year?
Hebrew: העליות והירידות הגדולות במחיר

```sql
SELECT name, ppsqm_change_pct,
       median_ppsqm_existing_prev12m, median_ppsqm_existing_12m,
       n_ppsqm_existing_12m, ppsqm_change_method
FROM settlements
WHERE ppsqm_change_pct IS NOT NULL
ORDER BY ppsqm_change_pct DESC
LIMIT 20
```

The change compares the existing-stock median (is_new_build = 0) of the 12 months to the anchor with the 12 months before. It is not a pooled median ratio, which would swing with the share of new projects. Sort ASC to get the fallers.

Tables: settlements · verified 20 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-11

#### Example 12: District overview

Question: How do the seven districts compare on price and activity?
Hebrew: סקירת מחוזות

```sql
SELECT name, n_settlements, deals_total, deals_12m,
       median_price_12m, n_ppsqm_12m, median_ppsqm_12m, ppsqm_change_pct
FROM districts
ORDER BY median_ppsqm_12m DESC
```

districts has one row per CBS district with the same 12-month rules as settlements. יהודה והשומרון has very few deals because the Tax Authority register barely covers the West Bank.

Tables: districts · verified 7 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-12

#### Example 13: Busiest settlements in the last 12 months

Question: Where were the most real-estate deals in the last 12 months?
Hebrew: היישובים הפעילים ביותר ב-12 החודשים האחרונים

```sql
SELECT name, deals_12m, deals_residential_12m, deals_prev12m, deals_after_anchor, total_volume_12m
FROM settlements
ORDER BY deals_12m DESC
LIMIT 20
```

deals_12m counts every deal in the window, including partial, outlier and multi-unit rows (transaction volume). deals_after_anchor are deals reported after 2026-06-30 so far; do not read deals_12m vs deals_prev12m as a trend, because late reports keep arriving.

Tables: settlements · verified 20 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-13

#### Example 14: Cities of a district ranked by price

Question: Rank the settlements of the Central district by median price per m².
Hebrew: דירוג ערי מחוז לפי מחיר

```sql
SELECT name, median_ppsqm_12m, n_ppsqm_12m, median_price_12m, median_price_4rooms_12m, ppsqm_change_pct
FROM settlements
WHERE district = 'המרכז' AND n_ppsqm_12m >= 30
ORDER BY median_ppsqm_12m DESC
```

district uses the CBS spelling (המרכז, תל אביב, ירושלים, חיפה, הצפון, הדרום, יהודה והשומרון) and is indexed. The n_ppsqm_12m >= 30 gate hides medians based on a handful of deals.

Tables: settlements · verified 29 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-14

#### Example 15: New-build premium by city

Question: How much more do new apartments cost per m² than existing ones, by city?
Hebrew: הפרמיה על דירות חדשות לפי עיר

```sql
SELECT name, median_ppsqm_new_12m, n_ppsqm_new_12m,
       median_ppsqm_existing_12m, n_ppsqm_existing_12m,
       round(100.0 * median_ppsqm_new_12m / median_ppsqm_existing_12m - 100, 1) AS new_build_premium_pct
FROM settlements
WHERE n_ppsqm_new_12m >= 50 AND n_ppsqm_existing_12m >= 50
ORDER BY new_build_premium_pct DESC
```

New builds are deals with year_built >= deal year - 1, including off-plan sales. Discount-lottery units are already excluded from both medians, so a premium here is the market new-build premium.

Tables: settlements · verified 59 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-15

### Trends over time

#### Example 16: National apartment prices by year

Question: How has the national median apartment price changed since 1998?
Hebrew: מחירי דירות ארציים לפי שנה

```sql
SELECT year, deals, n_stats, median_price, n_ppsqm, median_ppsqm, is_incomplete
FROM agg_national_year
WHERE property_group = 'apartment' AND rooms_bucket = 'all'
ORDER BY year
```

agg_national_year holds precomputed true medians under the in_stats rule, so there is no need to scan deals. The 2026 row has is_incomplete = 1 (reporting lag): drop it or label it partial.

Tables: agg_national_year · verified 29 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-16

#### Example 17: Quarterly price series of a city

Question: Show the quarterly median price per m² of apartments in Tel Aviv since 2015.
Hebrew: סדרת מחירים רבעונית של עיר

```sql
SELECT quarter, deals, n_ppsqm, median_ppsqm, median_price, is_incomplete
FROM agg_settlement_quarter
WHERE settlement_code = 5000 AND property_group = 'apartment' AND rooms_bucket = 'all'
  AND quarter >= '2015-Q1'
ORDER BY quarter
```

The primary key (settlement_code, property_group, rooms_bucket, quarter) makes this a direct range lookup. Quarter strings sort correctly. Quarters with no deals are absent, and 2026-Q3 has is_incomplete = 1.

Tables: agg_settlement_quarter · verified 47 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-17

#### Example 18: Compare cities side by side

Question: Compare the yearly apartment price per m² of Tel Aviv, Jerusalem and Haifa since 2010.
Hebrew: השוואת ערים זו לצד זו

```sql
SELECT year,
       max(CASE WHEN settlement_code = 5000 THEN median_ppsqm END) AS tel_aviv,
       max(CASE WHEN settlement_code = 3000 THEN median_ppsqm END) AS jerusalem,
       max(CASE WHEN settlement_code = 4000 THEN median_ppsqm END) AS haifa,
       max(is_incomplete) AS is_incomplete
FROM agg_settlement_year
WHERE settlement_code IN (5000, 3000, 4000) AND property_group = 'apartment' AND year >= 2010
GROUP BY year
ORDER BY year
```

Conditional aggregation (max(CASE ...)) pivots one row per city-year into one column per city. Look up codes in settlements (5000 Tel Aviv, 3000 Jerusalem, 4000 Haifa).

Tables: agg_settlement_year · verified 17 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-18

#### Example 19: National monthly deal volume

Question: How many deals and how much money changed hands each month over the last three years?
Hebrew: היקף עסקאות ארצי חודשי

```sql
SELECT month, deals, round(total_volume / 1e9, 2) AS volume_billion_ils, is_incomplete
FROM agg_national_month
WHERE property_group = 'all' AND month >= '2023-07'
ORDER BY month
```

Use property_group = 'all' for counts and money volume (its median columns are NULL). total_volume only sums credible amounts. The months after 2026-06 are incomplete, so their drop is reporting lag, not a crash.

Tables: agg_national_month · verified 39 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-19

#### Example 20: Apartment prices by number of rooms

Question: What did apartments cost nationally in 2025 by number of rooms?
Hebrew: מחירי דירות לפי מספר חדרים

```sql
SELECT rooms_bucket, deals, n_stats, median_price, median_ppsqm, median_area
FROM agg_national_year
WHERE property_group = 'apartment' AND year = 2025 AND rooms_bucket <> 'all'
ORDER BY rooms_bucket
```

rooms_bucket is 1-2 (< 3 rooms), 3 (3-3.5), 4 (4-4.5), 5 (5-5.5) and 6+. The 'all' bucket also includes rows with unknown rooms, so the buckets do not add up to it.

Tables: agg_national_year · verified 5 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-20

#### Example 21: District price trend by quarter

Question: How has the residential price per m² moved in each district quarter by quarter since 2023?
Hebrew: מגמת מחירים רבעונית לפי מחוז

```sql
SELECT quarter,
       max(CASE WHEN district = 'תל אביב' THEN median_ppsqm END) AS tel_aviv,
       max(CASE WHEN district = 'ירושלים' THEN median_ppsqm END) AS jerusalem,
       max(CASE WHEN district = 'המרכז' THEN median_ppsqm END) AS center,
       max(CASE WHEN district = 'חיפה' THEN median_ppsqm END) AS haifa,
       max(CASE WHEN district = 'הצפון' THEN median_ppsqm END) AS north,
       max(CASE WHEN district = 'הדרום' THEN median_ppsqm END) AS south,
       max(is_incomplete) AS is_incomplete
FROM agg_district_quarter
WHERE quarter >= '2023-Q1'
GROUP BY quarter
ORDER BY quarter
```

agg_district_quarter is residential only (all_residential) and has no property_group column. For other groups use agg_district_year.

Tables: agg_district_quarter · verified 15 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-21

#### Example 22: Seasonality of deals by calendar month

Question: Which months of the year have the most real-estate deals?
Hebrew: עונתיות העסקאות לפי חודש

```sql
SELECT substr(month, 6, 2) AS calendar_month,
       round(avg(deals)) AS avg_deals,
       min(deals) AS min_deals,
       max(deals) AS max_deals
FROM agg_national_month
WHERE property_group = 'all_residential' AND month BETWEEN '2010-01' AND '2025-12'
GROUP BY calendar_month
ORDER BY calendar_month
```

Averaging deal counts is fine; averaging medians is not. The range stops at complete years, before the incomplete months after the anchor. December usually tops the list because of year-end tax planning.

Tables: agg_national_month · verified 12 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-22

#### Example 23: Biggest 10-year price risers

Question: Which cities had the biggest rise in apartment price per m² from 2015 to 2025?
Hebrew: העליות הגדולות בעשור

```sql
SELECT s.name, a.median_ppsqm AS ppsqm_2015, b.median_ppsqm AS ppsqm_2025,
       round(1.0 * b.median_ppsqm / a.median_ppsqm, 2) AS multiple,
       a.n_ppsqm AS n_2015, b.n_ppsqm AS n_2025
FROM agg_settlement_year a
JOIN agg_settlement_year b
  ON b.settlement_code = a.settlement_code AND b.property_group = a.property_group AND b.year = 2025
JOIN settlements s ON s.code = a.settlement_code
WHERE a.property_group = 'apartment' AND a.year = 2015
  AND a.n_ppsqm >= 100 AND b.n_ppsqm >= 100
ORDER BY multiple DESC
LIMIT 20
```

A self-join of agg_settlement_year pairs each city's 2015 and 2025 rows. The n >= 100 gate in both years keeps small samples out. Part of a periphery rise can be mix: new neighbourhoods replacing old stock in the sales.

Tables: agg_settlement_year, settlements · verified 20 rows in 1 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-23

### Deal lists

#### Example 24: Latest deals nationwide

Question: What are the 25 most recent deals in the registry?
Hebrew: העסקאות האחרונות בארץ

```sql
SELECT id, deal_date, settlement, property_group, rooms, area, deal_amount, price_per_sqm,
       portion, is_outlier, is_multi_unit, is_discount_project
FROM deals
ORDER BY deal_date DESC, id DESC
LIMIT 25
```

ORDER BY deal_date DESC, id DESC walks ix_deals_date backwards and stops after 25 rows. Show portion when it is below 1 (a share was sold), and flag outlier, multi-unit and discount-project rows rather than hiding them.

Tables: deals · verified 25 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-24

#### Example 25: Latest deals in a city

Question: Show the 50 newest deals in Tel Aviv.
Hebrew: העסקאות האחרונות בעיר

```sql
SELECT id, deal_date, property_group, rooms, area, deal_amount, price_per_sqm,
       gush, chelka, portion, is_outlier, is_multi_unit, is_discount_project
FROM deals
WHERE settlement_code = 5000
ORDER BY deal_date DESC, id DESC
LIMIT 50
```

ix_deals_settlement_date (settlement_code, deal_date, id, ...) returns the rows already in order, so the query stops after 50 rows. Always filter by settlement_code (an integer) rather than by the settlement name.

Tables: deals · verified 50 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-25

#### Example 26: Filtered deal search

Question: Find apartments in Tel Aviv with 3 to 4.5 rooms that sold for 2-4 million ILS in the last 12 months.
Hebrew: חיפוש עסקאות עם מסננים

```sql
SELECT id, deal_date, rooms, area, deal_amount, price_per_sqm, gush, chelka, year_built,
       is_outlier, is_multi_unit
FROM deals
WHERE settlement_code = 5000
  AND property_group = 'apartment'
  AND deal_date BETWEEN '2025-07-01' AND '2026-06-30'
  AND rooms BETWEEN 3 AND 4.5
  AND deal_amount BETWEEN 2000000 AND 4000000
  AND is_full_deal = 1
ORDER BY deal_date DESC, id DESC
LIMIT 100
```

The equality filters (settlement_code, property_group) and the date range match ix_deals_settlement_group_date. That index also carries rooms and deal_amount, so those filters run inside the index. is_full_deal = 1 makes the amount the whole unit's price rather than a share.

Tables: deals · verified 100 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-26

#### Example 27: Keyset pagination of a deal list

Question: How do I get the next page of Jerusalem deals after the last row I already have?
Hebrew: דפדוף בעסקאות לפי מפתח (keyset)

```sql
SELECT id, deal_date, property_group, rooms, deal_amount, price_per_sqm
FROM deals
WHERE settlement_code = 3000
  AND (deal_date, id) < ('2026-05-31', 99999999)
ORDER BY deal_date DESC, id DESC
LIMIT 50
```

Pass the (deal_date, id) of the last row of the previous page as the cursor. The row-value comparison continues exactly where the page ended, and each page costs the same however deep you go. OFFSET costs O(offset).

Tables: deals · verified 50 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-27

#### Example 28: Most expensive homes of the last 12 months

Question: What were the most expensive home sales in Israel in the 12 months to June 2026?
Hebrew: הדירות היקרות ביותר ב-12 החודשים האחרונים

```sql
SELECT id, deal_date, settlement, property_group, rooms, area, deal_amount, price_per_sqm, gush, chelka
FROM deals
WHERE deal_date BETWEEN '2025-07-01' AND '2026-06-30'
  AND is_residential = 1
  AND is_full_deal = 1
  AND is_outlier = 0
  AND is_multi_unit = 0
ORDER BY deal_amount DESC
LIMIT 20
```

Exclude outliers and multi-unit rows, or "most expensive" fills up with bulk and non-market rows. Keep full deals only, because a partial deal's amount is for a share. The date range keeps the sort to about 110k rows.

Tables: deals · verified 20 rows in 96 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-28

#### Example 29: Highest price per m² in a city

Question: Which Jerusalem homes sold at the highest price per m² in 2025?
Hebrew: המחיר הגבוה ביותר למ״ר בעיר

```sql
SELECT id, deal_date, property_group, rooms, area, deal_amount, price_per_sqm, gush, chelka
FROM deals
WHERE settlement_code = 3000
  AND price_per_sqm IS NOT NULL
  AND in_stats = 1
  AND deal_date BETWEEN '2025-01-01' AND '2025-12-31'
ORDER BY price_per_sqm DESC
LIMIT 20
```

price_per_sqm is only set for residential full deals with a plausible area. in_stats = 1 drops outliers, multi-unit and discount-project rows. ix_deals_settlement_ppsqm walks the city's prices from the top down.

Tables: deals · verified 20 rows in 3 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-29

#### Example 30: Cheapest 4-room apartments in a city

Question: What were the cheapest 4-room apartments sold in Haifa in the last 12 months?
Hebrew: דירות 4 חדרים הזולות ביותר בעיר

```sql
SELECT id, deal_date, rooms, area, deal_amount, price_per_sqm, gush, chelka, year_built
FROM deals
WHERE settlement_code = 4000
  AND property_group = 'apartment'
  AND deal_date BETWEEN '2025-07-01' AND '2026-06-30'
  AND rooms >= 4 AND rooms < 5
  AND in_stats = 1
ORDER BY deal_amount
LIMIT 20
```

in_stats = 1 removes the non-market rows (₪1 transfers, partial shares, bulk and discount-lottery sales) that would otherwise be the "cheapest". rooms >= 4 AND rooms < 5 is the same as rooms_bucket = '4'.

Tables: deals · verified 20 rows in 1 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-30

#### Example 31: Deal types in a city

Question: What kinds of properties were sold in Haifa in the last 12 months?
Hebrew: סוגי העסקאות בעיר

```sql
SELECT property_group, count(*) AS deals,
       sum(CASE WHEN is_full_deal = 1 THEN 0 ELSE 1 END) AS partial_or_unknown_share
FROM deals
WHERE settlement_code = 4000
  AND deal_date BETWEEN '2025-07-01' AND '2026-06-30'
GROUP BY property_group
ORDER BY deals DESC
```

Counts include every deal (transaction volume). The settlement and date range match ix_deals_settlement_date. is_full_deal = 0 covers partial shares and unknown portions.

Tables: deals · verified 8 rows in 4 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-31

#### Example 32: New-build sales in a city

Question: Which new-build apartments sold in Netanya most recently?
Hebrew: מכירות דירות חדשות בעיר

```sql
SELECT id, deal_date, rooms, area, deal_amount, price_per_sqm, year_built, gush, chelka,
       is_discount_project, discount_source
FROM deals
WHERE settlement_code = 7400
  AND property_group = 'apartment'
  AND deal_date BETWEEN '2025-07-01' AND '2026-06-30'
  AND is_new_build = 1
ORDER BY deal_date DESC, id DESC
LIMIT 50
```

is_new_build = 1 means year_built >= deal year - 1 (new or off-plan). is_new_build is not in the index, so keep the settlement and date range. Discount-lottery units are flagged in is_discount_project.

Tables: deals · verified 50 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-32

### Parcels, gushim & geo

#### Example 33: Deal history of a parcel

Question: What deals were recorded at gush 7104, chelka 289 (a big Tel Aviv building)?
Hebrew: היסטוריית העסקאות בחלקה

```sql
SELECT id, deal_date, sub_chelka, property_group, rooms, area, deal_amount, price_per_sqm,
       portion, year_built, is_outlier, is_multi_unit
FROM deals
WHERE gush = 7104 AND chelka = 289
ORDER BY deal_date DESC, id DESC
LIMIT 200
```

The registry identifies property by gush (block), chelka (parcel) and sub_chelka (unit), not by address. ix_deals_gush_chelka (gush, chelka, deal_date) serves this lookup.

Tables: deals · verified 200 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-33

#### Example 34: Parcel summary with address

Question: Summarise parcel 7104/289: location, activity, prices and street address.
Hebrew: תקציר חלקה כולל כתובת

```sql
SELECT p.gush, p.chelka, p.settlement_code, p.lat, p.lon, p.geo_precision, p.geo_source, p.uncertainty_m,
       p.deals_total, p.deals_5y, p.n_units, p.first_deal_date, p.last_deal_date, p.last_price,
       p.n_ppsqm_5y, p.median_ppsqm_5y, p.dominant_group,
       a.address_label, a.street, a.house_numbers, a.street_source,
       b.n_buildings, b.max_levels
FROM parcels p
LEFT JOIN parcel_address a ON a.gush = p.gush AND a.chelka = p.chelka
LEFT JOIN parcel_buildings b ON b.gush = p.gush AND b.chelka = p.chelka
WHERE p.gush = 7104 AND p.chelka = 289
```

(gush, chelka) is the natural key of parcels and of every parcel-level enrichment table. address_label is NULL when OpenStreetMap only gives a nearby street (street_source = 'osm_nearest_street'): say "near street X", never present it as the address.

Tables: parcels, parcel_address, parcel_buildings · verified 1 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-34

#### Example 35: Yearly price series of a gush

Question: How has the price per m² in Jerusalem gush 30435 evolved year by year?
Hebrew: סדרת מחירים שנתית של גוש

```sql
SELECT year, deals, n_stats, median_price, n_ppsqm, median_ppsqm, is_incomplete
FROM agg_gush_year
WHERE gush = 30435
ORDER BY year
```

agg_gush_year counts all groups in deals but computes the price columns over residential in_stats deals only. Gushim are small, so check n_ppsqm before quoting a year's median.

Tables: agg_gush_year · verified 29 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-35

#### Example 36: Gushim of a city ranked by price

Question: Which blocks (gushim) in Haifa are the most expensive, and how did they change?
Hebrew: גושי העיר מדורגים לפי מחיר

```sql
SELECT gush, deals_24m, n_ppsqm_24m, median_ppsqm_24m, median_ppsqm_prev24m, change_pct, n_discount_24m, lat, lon
FROM gushim
WHERE settlement_code = 4000 AND n_ppsqm_24m >= 20
ORDER BY median_ppsqm_24m DESC
LIMIT 30
```

Gush statistics use a 24-month window (2024-07-01 to 2026-06-30). change_pct compares existing stock with the previous 24 months and is only set when both windows have at least 20 such deals.

Tables: gushim · verified 30 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-36

#### Example 37: Repeat sales of the same unit

Question: Which apartments in gush 6212 were sold more than once, and how much did their price grow per year?
Hebrew: מכירות חוזרות של אותה יחידה

```sql
WITH sales AS (
  SELECT gush, chelka, sub_chelka, deal_date, deal_amount,
         lag(deal_date) OVER w AS prev_date,
         lag(deal_amount) OVER w AS prev_amount
  FROM deals
  WHERE gush = 6212 AND sub_chelka > 0 AND in_stats = 1
  WINDOW w AS (PARTITION BY chelka, sub_chelka ORDER BY deal_date)
)
SELECT chelka, sub_chelka, prev_date, prev_amount, deal_date, deal_amount,
       round((julianday(deal_date) - julianday(prev_date)) / 365.25, 1) AS years_held,
       round(100 * (pow(1.0 * deal_amount / prev_amount, 365.25 / (julianday(deal_date) - julianday(prev_date))) - 1), 1) AS annual_growth_pct
FROM sales
WHERE prev_date IS NOT NULL AND julianday(deal_date) - julianday(prev_date) >= 365
ORDER BY deal_date DESC
LIMIT 50
```

A unit is (gush, chelka, sub_chelka) with sub_chelka > 0. lag() over a window partitioned by unit pairs each sale with the previous one. in_stats = 1 keeps only full, market-priced sales, and pow() gives the compound annual growth.

Tables: deals · verified 50 rows in 6 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-37

#### Example 38: Last sale of every unit in a building

Question: For each apartment unit in parcel 7104/289, what was its most recent sale?
Hebrew: המכירה האחרונה של כל יחידה בבניין

```sql
SELECT sub_chelka, deal_date, rooms, area, deal_amount, price_per_sqm, portion, n_sales
FROM (
  SELECT sub_chelka, deal_date, rooms, area, deal_amount, price_per_sqm, portion,
         row_number() OVER (PARTITION BY sub_chelka ORDER BY deal_date DESC, id DESC) AS rn,
         count(*) OVER (PARTITION BY sub_chelka) AS n_sales
  FROM deals
  WHERE gush = 7104 AND chelka = 289 AND sub_chelka > 0 AND is_residential = 1
)
WHERE rn = 1
ORDER BY deal_date DESC
LIMIT 100
```

row_number() = 1 per partition is the standard "latest row per group" idiom. count(*) OVER the same partition adds how many times each unit was sold.

Tables: deals · verified 100 rows in 4 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-38

#### Example 39: Parcel vs its gush vs its city

Question: Is parcel 6213/1471 in Tel Aviv expensive compared with its block and the city?
Hebrew: חלקה מול הגוש ומול העיר

```sql
SELECT p.gush, p.chelka,
       p.median_ppsqm_5y AS parcel_ppsqm_5y, p.n_ppsqm_5y AS parcel_n,
       g.median_ppsqm_5y AS gush_ppsqm_5y, g.n_ppsqm_5y AS gush_n,
       s.median_ppsqm_5y AS city_ppsqm_5y,
       round(100.0 * p.median_ppsqm_5y / s.median_ppsqm_5y - 100, 1) AS parcel_vs_city_pct
FROM parcels p
JOIN gushim g ON g.gush = p.gush
JOIN settlements s ON s.code = p.settlement_code
WHERE p.gush = 6213 AND p.chelka = 1471
```

The 5-year window (2021-07-01 to 2026-06-30) exists at every level: parcels, gushim and settlements. That makes it the fair common basis for a parcel that trades rarely.

Tables: parcels, gushim, settlements · verified 1 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-39

#### Example 40: Parcels inside a map bounding box

Question: Which parcels with deals are inside a small box in central Tel Aviv?
Hebrew: חלקות בתוך מלבן במפה

```sql
SELECT p.gush, p.chelka, p.lat, p.lon, p.deals_total, p.deals_5y, p.median_ppsqm_5y, p.last_deal_date, p.last_price
FROM parcels_rtree r
CROSS JOIN parcels p ON p.id = r.id
WHERE r.minLat >= 32.0700 AND r.maxLat <= 32.0800
  AND r.minLon >= 34.7700 AND r.maxLon <= 34.7850
ORDER BY p.deals_5y DESC
LIMIT 100
```

parcels_rtree is an R*Tree over parcel points (id = parcels.id). CROSS JOIN pins the loop order so the R*Tree drives the query. Settlement-centre fallback points are not in the tree, so a box never returns fake town-centre rows.

Tables: parcels_rtree, parcels · verified 100 rows in 1 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-40

#### Example 41: Deals inside a bounding box

Question: List the residential deals of the last 12 months inside a box in central Tel Aviv.
Hebrew: עסקאות בתוך מלבן במפה

```sql
SELECT d.id, d.deal_date, d.property_group, d.rooms, d.area, d.deal_amount, d.price_per_sqm,
       d.gush, d.chelka, d.lat, d.lon, d.portion, d.is_outlier, d.is_multi_unit
FROM parcels_rtree r
CROSS JOIN parcels p ON p.id = r.id
CROSS JOIN deals d ON d.gush = p.gush AND d.chelka = p.chelka
WHERE r.minLat >= 32.0700 AND r.maxLat <= 32.0800
  AND r.minLon >= 34.7700 AND r.maxLon <= 34.7850
  AND d.deal_date BETWEEN '2025-07-01' AND '2026-06-30'
  AND d.is_residential = 1
ORDER BY d.deal_date DESC, d.id DESC
LIMIT 200
```

Every deal of a parcel shares the parcel's point, so the fastest path is R*Tree, then parcels, then deals through ix_deals_gush_chelka. The deals_rtree view does the same join for you.

Tables: parcels_rtree, parcels, deals · verified 200 rows in 2 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-41

#### Example 42: Nearest parcels to a point

Question: What are the 20 nearest parcels with deals to Dizengoff Center (32.0753, 34.7748), and how far are they?
Hebrew: החלקות הקרובות לנקודה

```sql
SELECT p.gush, p.chelka, p.deals_total, p.median_ppsqm_5y, a.address_label,
       round(6371000 * 2 * asin(sqrt(
         pow(sin(radians(p.lat - 32.0753) / 2), 2) +
         cos(radians(32.0753)) * cos(radians(p.lat)) * pow(sin(radians(p.lon - 34.7748) / 2), 2)
       ))) AS distance_m
FROM parcels_rtree r
CROSS JOIN parcels p ON p.id = r.id
LEFT JOIN parcel_address a ON a.gush = p.gush AND a.chelka = p.chelka
WHERE r.minLat >= 32.0753 - 0.0045 AND r.maxLat <= 32.0753 + 0.0045
  AND r.minLon >= 34.7748 - 0.0053 AND r.maxLon <= 34.7748 + 0.0053
ORDER BY distance_m
LIMIT 20
```

Prefilter with an R*Tree box (0.0045° of latitude is about 500 m; 0.0053° of longitude is about 500 m at latitude 32), then compute the exact haversine distance with the built-in math functions. Widen the box if you get fewer rows than you need.

Tables: parcels_rtree, parcels, parcel_address · verified 20 rows in 2 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-42

#### Example 43: Median price within a radius

Question: What is the median price per m² of homes sold within 1 km of Dizengoff Center in the last 5 years?
Hebrew: מחיר חציוני ברדיוס מנקודה

```sql
SELECT count(*) AS n_deals,
       round(median(d.price_per_sqm)) AS median_ppsqm,
       round(percentile(d.price_per_sqm, 25)) AS p25_ppsqm,
       round(percentile(d.price_per_sqm, 75)) AS p75_ppsqm
FROM parcels_rtree r
CROSS JOIN parcels p ON p.id = r.id
CROSS JOIN deals d ON d.gush = p.gush AND d.chelka = p.chelka
WHERE r.minLat >= 32.0753 - 0.009 AND r.maxLat <= 32.0753 + 0.009
  AND r.minLon >= 34.7748 - 0.0106 AND r.maxLon <= 34.7748 + 0.0106
  AND 6371000 * 2 * asin(sqrt(pow(sin(radians(p.lat - 32.0753) / 2), 2) + cos(radians(32.0753)) * cos(radians(p.lat)) * pow(sin(radians(p.lon - 34.7748) / 2), 2))) <= 1000
  AND d.deal_date BETWEEN '2021-07-01' AND '2026-06-30'
  AND d.in_stats = 1 AND d.price_per_sqm IS NOT NULL
```

The box is a fast prefilter and the haversine condition turns it into a true circle. This SQLite build includes median() and percentile(). Apply the in_stats rule and a price_per_sqm IS NOT NULL filter to any price-per-m² median.

Tables: parcels_rtree, parcels, deals · verified 1 rows in 7 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-43

#### Example 44: Deals near a train station

Question: How much did homes sell for within 700 m of Tel Aviv Savidor station in the last 12 months?
Hebrew: עסקאות ליד תחנת רכבת

```sql
WITH st AS (SELECT lat, lon FROM poi_stations WHERE id = 'rail:17038')
SELECT count(*) AS n_deals, round(median(d.price_per_sqm)) AS median_ppsqm, round(median(d.deal_amount)) AS median_price
FROM st
CROSS JOIN parcels_rtree r
CROSS JOIN parcels p ON p.id = r.id
CROSS JOIN deals d ON d.gush = p.gush AND d.chelka = p.chelka
WHERE r.minLat >= st.lat - 0.0063 AND r.maxLat <= st.lat + 0.0063
  AND r.minLon >= st.lon - 0.0075 AND r.maxLon <= st.lon + 0.0075
  AND 6371000 * 2 * asin(sqrt(pow(sin(radians(p.lat - st.lat) / 2), 2) + cos(radians(st.lat)) * cos(radians(p.lat)) * pow(sin(radians(p.lon - st.lon) / 2), 2))) <= 700
  AND d.deal_date BETWEEN '2025-07-01' AND '2026-06-30'
  AND d.in_stats = 1 AND d.price_per_sqm IS NOT NULL
```

Take the station's coordinates from poi_stations (rail:17038 is תל אביב - סבידור מרכז; find ids by name first), then use the box-plus-haversine pattern. A one-row CTE avoids repeating the lookup.

Tables: poi_stations, parcels_rtree, parcels, deals · verified 1 rows in 1 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-44

#### Example 45: Which neighbourhood is a point in?

Question: Which settlement and neighbourhood are at coordinates 31.7730, 35.2120?
Hebrew: באיזו שכונה נמצאת נקודה?

```sql
SELECT p.gush, p.chelka, s.name AS settlement, n.name AS neighborhood, n.median_ppsqm_12m AS nbhd_ppsqm_12m,
       round(6371000 * 2 * asin(sqrt(pow(sin(radians(p.lat - 31.7730) / 2), 2) + cos(radians(31.7730)) * cos(radians(p.lat)) * pow(sin(radians(p.lon - 35.2120) / 2), 2)))) AS distance_m
FROM parcels_rtree r
CROSS JOIN parcels p ON p.id = r.id
LEFT JOIN settlements s ON s.code = p.settlement_code
LEFT JOIN parcel_neighborhood pn ON pn.gush = p.gush AND pn.chelka = p.chelka
LEFT JOIN neighborhoods n ON n.nbhd_id = pn.nbhd_id
WHERE r.minLat >= 31.7730 - 0.003 AND r.maxLat <= 31.7730 + 0.003
  AND r.minLon >= 35.2120 - 0.0035 AND r.maxLon <= 35.2120 + 0.0035
ORDER BY distance_m
LIMIT 5
```

The DB has no polygons, but every parcel has a point and a neighbourhood. The nearest parcel is a good proxy for "where is this point". Treat the answer as approximate near borders.

Tables: parcels_rtree, parcels, settlements, parcel_neighborhood, neighborhoods · verified 5 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-45

#### Example 46: Settlements within a radius

Question: Which settlements lie within 15 km of Modiin, and what do homes cost there?
Hebrew: יישובים ברדיוס מנקודה

```sql
WITH c AS (SELECT lat, lon FROM settlements WHERE code = 1200)
SELECT s.name, s.n_ppsqm_12m,
       CASE WHEN s.n_ppsqm_12m >= 5 THEN s.median_ppsqm_12m END AS median_ppsqm_12m,
       CASE WHEN s.n_stats_12m >= 5 THEN s.median_price_12m END AS median_price_12m,
       round(6371 * 2 * asin(sqrt(pow(sin(radians(s.lat - c.lat) / 2), 2) + cos(radians(c.lat)) * cos(radians(s.lat)) * pow(sin(radians(s.lon - c.lon) / 2), 2))), 1) AS distance_km
FROM settlements s, c
WHERE s.code <> 1200
  AND s.lat BETWEEN c.lat - 0.14 AND c.lat + 0.14
  AND s.lon BETWEEN c.lon - 0.16 AND c.lon + 0.16
  AND 6371 * 2 * asin(sqrt(pow(sin(radians(s.lat - c.lat) / 2), 2) + cos(radians(c.lat)) * cos(radians(s.lat)) * pow(sin(radians(s.lon - c.lon) / 2), 2))) <= 15
ORDER BY distance_km
```

settlements has only 1,139 rows, so a scan with a distance filter is instant and no R*Tree is needed. The CASE expressions hide medians based on fewer than 5 deals, which are common in small villages.

Tables: settlements · verified 73 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-46

### Name search (FTS5)

#### Example 47: Search settlements by name prefix

Question: Which settlements match "באר" (e.g. Be'er Sheva)?
Hebrew: חיפוש יישוב לפי תחילת השם

```sql
SELECT s.code, s.name, s.name_en, s.deals_total
FROM settlements_fts f
JOIN settlements s ON s.code = f.rowid
WHERE settlements_fts MATCH '"באר"*'
ORDER BY bm25(settlements_fts, 5.0, 1.0) - 2 * log(1 + s.deals_total)
LIMIT 10
```

settlements_fts indexes names and aliases (spellings, abbreviations, English names), with rowid = settlements.code. Quote each token and add * to the last one for prefix search. The ORDER BY mixes text relevance (name weighted 5×) with activity.

Tables: settlements_fts, settlements · verified 9 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-47

#### Example 48: Search settlements by abbreviation

Question: Which settlement does the abbreviation ת"א refer to?
Hebrew: חיפוש יישוב לפי ראשי תיבות

```sql
SELECT s.code, s.name, s.name_en, s.deals_total
FROM settlements_fts f
JOIN settlements s ON s.code = f.rowid
WHERE settlements_fts MATCH '"תא"*'
ORDER BY bm25(settlements_fts, 5.0, 1.0) - 2 * log(1 + s.deals_total)
LIMIT 10
```

Strip quotes and geresh before matching (ת"א becomes תא, ב"ש becomes בש). The aliases column holds these squashed forms, so תל אביב-יפו ranks first.

Tables: settlements_fts, settlements · verified 3 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-48

#### Example 49: Search settlements in English

Question: Find the settlement called "haifa" in English.
Hebrew: חיפוש יישוב באנגלית

```sql
SELECT s.code, s.name, s.name_en, s.district, s.deals_total
FROM settlements_fts f
JOIN settlements s ON s.code = f.rowid
WHERE settlements_fts MATCH '"haifa"*'
ORDER BY bm25(settlements_fts, 5.0, 1.0) - 2 * log(1 + s.deals_total)
LIMIT 5
```

English CBS names are part of the aliases, and the unicode61 tokenizer is case-insensitive. Use this to map an English city name to its code before querying other tables.

Tables: settlements_fts, settlements · verified 1 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-49

#### Example 50: Search streets by name

Question: Which streets called Herzl (הרצל) exist, and where are they busiest?
Hebrew: חיפוש רחוב לפי שם

```sql
SELECT s.id, s.street, s.settlement, s.deals_total, s.median_ppsqm_5y, s.n_ppsqm_5y
FROM streets_fts f
JOIN streets s ON s.id = f.rowid
WHERE streets_fts MATCH '{street aliases} : "הרצל"*'
ORDER BY s.deals_total DESC
LIMIT 20
```

streets_fts is contentless (its columns read back NULL), so always join streets on rowid = streets.id. The column filter {street aliases} keeps a single word from matching settlement names: הרצל would otherwise return every street in הרצלייה.

Tables: streets_fts, streets · verified 20 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-50

#### Example 51: Search a street within a city

Question: Find Rothschild street in Tel Aviv (query "רוטשילד ת"א").
Hebrew: חיפוש רחוב בתוך עיר

```sql
SELECT s.id, s.street, s.settlement, s.deals_total, s.median_ppsqm_5y, s.house_min, s.house_max
FROM streets_fts f
JOIN streets s ON s.id = f.rowid
WHERE streets_fts MATCH '({street aliases} : "רוטשילד" OR {settlement} : "רוטשילד") AND ({street aliases} : "תא"* OR {settlement} : "תא"*)'
ORDER BY s.deals_total DESC
LIMIT 10
```

For multi-word input, let each word match either the street or the settlement column, and make the last word a prefix. The settlement column holds the city name plus its Hebrew abbreviations (תא, בש, ...).

Tables: streets_fts, streets · verified 4 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-51

#### Example 52: Search neighbourhoods by name

Question: Find the neighbourhood Florentin (פלורנטין) and its prices.
Hebrew: חיפוש שכונה לפי שם

```sql
SELECT n.nbhd_id, n.name, n.settlement, n.deals_12m, n.median_ppsqm_12m, n.n_ppsqm_12m, n.ppsqm_vs_settlement_pct
FROM neighborhoods_fts f
JOIN neighborhoods n ON n.nbhd_id = f.rowid
WHERE neighborhoods_fts MATCH '{name aliases} : "פלורנטין"*'
ORDER BY n.deals_total DESC
LIMIT 10
```

neighborhoods_fts is contentless with rowid = neighborhoods.nbhd_id. Use the returned nbhd_id with parcel_neighborhood and agg_neighborhood_year.

Tables: neighborhoods_fts, neighborhoods · verified 1 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-52

### Neighbourhoods & streets

#### Example 53: Neighbourhoods of a city by price

Question: Rank the neighbourhoods of Tel Aviv by median price per m².
Hebrew: שכונות העיר לפי מחיר

```sql
SELECT nbhd_id, name, deals_12m, n_ppsqm_12m, median_ppsqm_12m, median_ppsqm_existing_12m,
       ppsqm_change_pct, rank_ppsqm_in_settlement, ppsqm_vs_settlement_pct, max_parcel_share_12m
FROM neighborhoods
WHERE settlement_code = 5000 AND n_ppsqm_12m >= 20
ORDER BY median_ppsqm_12m DESC
```

Neighbourhood medians follow the settlement rules (in_stats, 12 months to the anchor). Gate them: n >= 20 here, and show a median at all only when n >= 5. A high max_parcel_share_12m means one building dominates the median.

Tables: neighborhoods · verified 35 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-53

#### Example 54: Yearly series of a neighbourhood

Question: How have prices in Rehavia (Jerusalem) evolved year by year?
Hebrew: סדרה שנתית של שכונה

```sql
SELECT a.year, a.deals, a.n_ppsqm, a.median_ppsqm, a.n_ppsqm_existing, a.median_ppsqm_existing, a.is_incomplete
FROM neighborhoods n
JOIN agg_neighborhood_year a ON a.nbhd_id = n.nbhd_id
WHERE n.settlement_code = 3000 AND n.name = 'רחביה'
ORDER BY a.year
```

Look up the neighbourhood by (settlement_code, name) or by nbhd_id from the FTS search. agg_neighborhood_year also has the existing-stock median, which is steadier when a new project dominates a year.

Tables: neighborhoods, agg_neighborhood_year · verified 29 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-54

#### Example 55: Neighbourhoods with the biggest price change

Question: Which neighbourhoods in Israel had the largest existing-stock price rise over the last year?
Hebrew: השכונות עם השינוי הגדול במחיר

```sql
SELECT settlement, name, ppsqm_change_pct, median_ppsqm_existing_prev12m, median_ppsqm_existing_12m,
       n_ppsqm_existing_12m, n_ppsqm_existing_prev12m
FROM neighborhoods
WHERE ppsqm_change_pct IS NOT NULL
ORDER BY ppsqm_change_pct DESC
LIMIT 20
```

ppsqm_change_pct is only set when both windows have at least 50 existing-stock deals (194 neighbourhoods), so it is never based on a tiny sample. Sort ASC for the biggest falls.

Tables: neighborhoods · verified 20 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-55

#### Example 56: Latest deals in a neighbourhood

Question: Show the 50 latest deals in Florentin, Tel Aviv.
Hebrew: העסקאות האחרונות בשכונה

```sql
SELECT d.id, d.deal_date, d.property_group, d.rooms, d.area, d.deal_amount, d.price_per_sqm,
       d.gush, d.chelka, d.portion, d.is_outlier, d.is_multi_unit
FROM neighborhoods n
CROSS JOIN parcel_neighborhood pn ON pn.nbhd_id = n.nbhd_id
CROSS JOIN deals d ON d.gush = pn.gush AND d.chelka = pn.chelka
WHERE n.settlement_code = 5000 AND n.name = 'פלורנטין'
  AND d.settlement_code = n.settlement_code
ORDER BY d.deal_date DESC, d.id DESC
LIMIT 50
```

parcel_neighborhood maps each parcel to its neighbourhood. CROSS JOIN pins the loop order (neighbourhood, then its parcels, then deals by (gush, chelka)). The settlement_code condition matches how the neighbourhood statistics are computed.

Tables: neighborhoods, parcel_neighborhood, deals · verified 50 rows in 1 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-56

#### Example 57: Most expensive streets of a city

Question: Which streets in Tel Aviv have the highest median price per m² over 5 years?
Hebrew: הרחובות היקרים בעיר

```sql
SELECT id, street, deals_5y, n_ppsqm_5y, median_ppsqm_5y, n_ppsqm_12m, median_ppsqm_12m, last_deal_date
FROM streets
WHERE settlement_code = 5000 AND n_ppsqm_5y >= 20
ORDER BY median_ppsqm_5y DESC
LIMIT 25
```

Street statistics cover the parcels with an OSM address on the street or next to it, so a corner parcel counts on each of its streets. Streets have fewer deals than neighbourhoods, so the 5-year window with an n gate is the safer basis.

Tables: streets · verified 25 rows in 1 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-57

#### Example 58: Deals on a street

Question: What sold on Rothschild street in Tel Aviv recently, with house numbers?
Hebrew: עסקאות ברחוב

```sql
SELECT d.id, d.deal_date, d.property_group, d.rooms, d.area, d.deal_amount, d.price_per_sqm,
       a.house_numbers, sp.kind, d.portion, d.is_outlier
FROM streets s
CROSS JOIN street_parcels sp ON sp.street_id = s.id
CROSS JOIN deals d ON d.gush = sp.gush AND d.chelka = sp.chelka
LEFT JOIN parcel_address a ON a.gush = sp.gush AND a.chelka = sp.chelka
WHERE s.settlement_code = 5000 AND s.street = 'רוטשילד'
ORDER BY d.deal_date DESC, d.id DESC
LIMIT 50
```

street_parcels lists the parcels of each street. kind = 'near' means the parcel is only next to the street, so say "near" rather than "on". Addresses come from OpenStreetMap, not from the registry.

Tables: streets, street_parcels, deals, parcel_address · verified 50 rows in 2 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-58

#### Example 59: Neighbourhood premium vs its city

Question: Which Jerusalem neighbourhoods are most above or below the city's price per m²?
Hebrew: פרמיית השכונה ביחס לעיר

```sql
SELECT name, median_ppsqm_12m, n_ppsqm_12m, ppsqm_vs_settlement_pct, rank_ppsqm_in_settlement, ranked_in_settlement
FROM neighborhoods
WHERE settlement_code = 3000 AND ppsqm_vs_settlement_pct IS NOT NULL
ORDER BY ppsqm_vs_settlement_pct DESC
```

ppsqm_vs_settlement_pct is precomputed only when the neighbourhood has at least 20 and the city at least 30 deals. A NULL rank means unranked: an industrial or business zone (which can still hold new housing) or too few parcels.

Tables: neighborhoods · verified 43 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-59

### Location metrics

#### Example 60: Price vs distance to a train station

Question: In Netanya, do homes closer to a train station sell for more per m²?
Hebrew: מחיר מול מרחק מתחנת רכבת

```sql
SELECT CASE WHEN m.dist_rail_m < 1000 THEN '1: < 1 km'
            WHEN m.dist_rail_m < 2000 THEN '2: 1-2 km'
            WHEN m.dist_rail_m < 3000 THEN '3: 2-3 km'
            ELSE '4: 3+ km' END AS rail_distance,
       count(*) AS n_deals,
       round(median(d.price_per_sqm)) AS median_ppsqm
FROM deals d
JOIN loc_metrics_parcel m ON m.gush = d.gush AND m.chelka = d.chelka
WHERE d.settlement_code = 7400
  AND d.deal_date BETWEEN '2021-07-01' AND '2026-06-30'
  AND d.in_stats = 1 AND d.price_per_sqm IS NOT NULL
  AND d.property_group = 'apartment'
GROUP BY rail_distance
ORDER BY rail_distance
```

loc_metrics_parcel has straight-line distances from each parcel point to the nearest operating station. Compare within one city and one property type, otherwise location effects mix with city price levels. This shows correlation, not causation.

Tables: deals, loc_metrics_parcel · verified 4 rows in 61 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-60

#### Example 61: Price vs distance to the sea

Question: How much more do apartments near the beach cost in Bat Yam?
Hebrew: מחיר מול מרחק מהים

```sql
SELECT CASE WHEN m.dist_sea_m < 500 THEN '1: < 500 m'
            WHEN m.dist_sea_m < 1000 THEN '2: 500 m-1 km'
            WHEN m.dist_sea_m < 2000 THEN '3: 1-2 km'
            ELSE '4: 2+ km' END AS sea_distance,
       count(*) AS n_deals,
       round(median(d.price_per_sqm)) AS median_ppsqm,
       round(median(d.deal_amount)) AS median_price
FROM deals d
JOIN loc_metrics_parcel m ON m.gush = d.gush AND m.chelka = d.chelka
WHERE d.settlement_code = 6200
  AND d.property_group = 'apartment'
  AND d.deal_date BETWEEN '2021-07-01' AND '2026-06-30'
  AND d.in_stats = 1 AND d.price_per_sqm IS NOT NULL
GROUP BY sea_distance
ORDER BY sea_distance
```

dist_sea_m is the distance to the OSM coastline (Mediterranean or Red Sea). Bucketing and taking median() per bucket over deal-level rows is the correct way to compare. Never average precomputed medians.

Tables: deals, loc_metrics_parcel · verified 4 rows in 14 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-61

#### Example 62: Price vs elementary schools nearby

Question: In Jerusalem, how does price per m² vary with the number of elementary schools within 1 km?
Hebrew: מחיר מול מספר בתי ספר יסודיים בקרבת מקום

```sql
SELECT CASE WHEN m.elementary_1km = 0 THEN '0'
            WHEN m.elementary_1km <= 2 THEN '1-2'
            WHEN m.elementary_1km <= 5 THEN '3-5'
            ELSE '6+' END AS elementary_schools_1km,
       count(*) AS n_deals,
       round(median(d.price_per_sqm)) AS median_ppsqm
FROM deals d
JOIN loc_metrics_parcel m ON m.gush = d.gush AND m.chelka = d.chelka
WHERE d.settlement_code = 3000
  AND d.deal_date BETWEEN '2021-07-01' AND '2026-06-30'
  AND d.in_stats = 1 AND d.price_per_sqm IS NOT NULL
GROUP BY elementary_schools_1km
ORDER BY min(m.elementary_1km)
```

School counts leave out institutions on placeholder coordinates (loc_placeholder = 1), so they are lower bounds in some Arab towns and East Jerusalem. School density also tracks population density, so read this as descriptive.

Tables: deals, loc_metrics_parcel · verified 4 rows in 45 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-62

#### Example 63: Homes inside airport noise zones

Question: Which settlements have home sales inside airport noise zones, and at what prices compared with the rest of the town?
Hebrew: דירות באזורי רעש מטוסים

```sql
WITH z AS (
  SELECT d.settlement_code, count(*) AS n_in_zone,
         round(median(d.price_per_sqm)) AS median_ppsqm_in_zone,
         group_concat(DISTINCT m.noise_zone) AS zones
  FROM loc_metrics_parcel m
  CROSS JOIN deals d ON d.gush = m.gush AND d.chelka = m.chelka
  WHERE m.noise_zone IS NOT NULL
    AND d.deal_date BETWEEN '2021-07-01' AND '2026-06-30'
    AND d.in_stats = 1 AND d.price_per_sqm IS NOT NULL
  GROUP BY d.settlement_code
  HAVING count(*) >= 20
)
SELECT s.name, z.zones, z.n_in_zone, z.median_ppsqm_in_zone,
       s.median_ppsqm_5y AS town_median_ppsqm_5y,
       round(100.0 * z.median_ppsqm_in_zone / s.median_ppsqm_5y - 100, 1) AS in_zone_vs_town_pct
FROM z
JOIN settlements s ON s.code = z.settlement_code
ORDER BY z.n_in_zone DESC
```

noise_zone has no index, but loc_metrics_parcel (381k rows) scans in about 40 ms, and only the 1.6% of parcels inside an active airport noise zone then reach their deals through ix_deals_gush_chelka. Compare with the town's 5-year median over the same window, keeping in mind that in-zone parcels can be a very different part of town (in Tel Aviv they are in the south-east: Kfar Shalem, Hatikva).

Tables: loc_metrics_parcel, deals, settlements · verified 8 rows in 43 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-63

#### Example 64: Price by socio-economic cluster

Question: How does the price per m² vary with the CBS socio-economic cluster of the settlement?
Hebrew: מחיר לפי אשכול חברתי-כלכלי

```sql
SELECT ss.cluster, count(DISTINCT ss.settlement_code) AS settlements, count(*) AS n_deals,
       round(median(d.price_per_sqm)) AS median_ppsqm, round(median(d.deal_amount)) AS median_price
FROM settlement_socio ss
JOIN deals d ON d.settlement_code = ss.settlement_code
WHERE d.deal_date BETWEEN '2025-07-01' AND '2026-06-30'
  AND d.in_stats = 1 AND d.price_per_sqm IS NOT NULL
GROUP BY ss.cluster
ORDER BY ss.cluster
```

This takes a true median over deal-level rows per cluster (1 = lowest, 10 = highest, CBS 2021), not an average of settlement medians. Clusters mix very different towns: Bnei Brak and Jerusalem are in cluster 2 but have high prices.

Tables: settlement_socio, deals · verified 9 rows in 76 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-64

#### Example 65: Crime rate vs price in big cities

Question: For cities over 100k residents, how does the 2025 crime rate compare with home prices?
Hebrew: שיעור הפשיעה מול מחירים בערים הגדולות

```sql
SELECT s.name, c.per_1000, c.per_1000_index, c.cases, s.median_ppsqm_12m, ss.cluster AS socio_cluster
FROM crime_settlement_year c
JOIN settlements s ON s.code = c.settlement_code
LEFT JOIN settlement_socio ss ON ss.settlement_code = c.settlement_code
WHERE c.year = 2025 AND s.population >= 100000
ORDER BY c.per_1000_index DESC
```

per_1000_index is the case rate relative to the national rate of the same year (100 = national). Use it to compare across years, because the 2021-2022 police files hold about half the cases of later years.

Tables: crime_settlement_year, settlements, settlement_socio · verified 21 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-65

#### Example 66: Price vs public-transport score

Question: In Jerusalem, do homes with better public transport sell for more?
Hebrew: מחיר מול ציון תחבורה ציבורית

```sql
SELECT (m.transit_score / 10) * 10 AS transit_score_from,
       count(*) AS n_deals,
       round(median(d.price_per_sqm)) AS median_ppsqm
FROM deals d
JOIN loc_metrics_parcel m ON m.gush = d.gush AND m.chelka = d.chelka
WHERE d.settlement_code = 3000
  AND d.deal_date BETWEEN '2021-07-01' AND '2026-06-30'
  AND d.in_stats = 1 AND d.price_per_sqm IS NOT NULL
GROUP BY transit_score_from
HAVING count(*) >= 30
ORDER BY transit_score_from
```

transit_score (0-100) combines weekday bus trips within 500 m with nearby rail and light-rail service. Integer division (score / 10) * 10 makes 10-point bands, and HAVING drops bands with too few deals.

Tables: deals, loc_metrics_parcel · verified 6 rows in 43 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-66

#### Example 67: Parcel quality-of-life panel

Question: For parcel 6212/418 in Tel Aviv, what is its address, neighbourhood, transit, schools, sea distance and socio-economic level?
Hebrew: לוח איכות חיים לחלקה

```sql
SELECT p.gush, p.chelka, a.address_label, a.street_source,
       n.name AS neighborhood, n.median_ppsqm_12m AS nbhd_ppsqm_12m,
       m.transit_score, m.dist_rail_m, rs.name AS rail_station, m.rail_weekday_trips,
       m.dist_lrt_m, ls.name AS lrt_station, m.bus_lines_500m,
       m.elementary_1km, m.dist_elementary_m, m.kindergartens_500m,
       m.dist_sea_m, m.elevation_m, m.noise_zone,
       sa.cluster AS stat_area_cluster, ss.cluster AS settlement_cluster,
       rc.name AS renewal_compound, rc.status AS renewal_status
FROM parcels p
LEFT JOIN parcel_address a ON a.gush = p.gush AND a.chelka = p.chelka
LEFT JOIN parcel_neighborhood pn ON pn.gush = p.gush AND pn.chelka = p.chelka
LEFT JOIN neighborhoods n ON n.nbhd_id = pn.nbhd_id
LEFT JOIN loc_metrics_parcel m ON m.gush = p.gush AND m.chelka = p.chelka
LEFT JOIN poi_stations rs ON rs.id = m.rail_station_id
LEFT JOIN poi_stations ls ON ls.id = m.lrt_station_id
LEFT JOIN parcel_stat_area_socio sa ON sa.gush = p.gush AND sa.chelka = p.chelka
LEFT JOIN settlement_socio ss ON ss.settlement_code = p.settlement_code
LEFT JOIN parcel_renewal r ON r.gush = p.gush AND r.chelka = p.chelka
LEFT JOIN renewal_compounds rc ON rc.compound_id = r.compound_id
WHERE p.gush = 6212 AND p.chelka = 418
```

Every enrichment table keys on (gush, chelka), settlement_code or an id, so one row can carry them all through LEFT JOINs (a missing match is NULL, not an error). Distances are straight lines in metres.

Tables: parcels, parcel_address, parcel_neighborhood, neighborhoods, loc_metrics_parcel, poi_stations, parcel_stat_area_socio, settlement_socio, parcel_renewal, renewal_compounds · verified 1 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-67

### Market, rents & macro

#### Example 68: National prices in today's shekels

Question: What was the national median apartment price each year in June-2026 shekels?
Hebrew: מחירים ארציים בשקלים של היום

```sql
SELECT a.year, a.median_price AS median_price_nominal,
       round(a.median_price * f.rf) AS median_price_real,
       a.median_ppsqm AS median_ppsqm_nominal,
       round(a.median_ppsqm * f.rf) AS median_ppsqm_real
FROM agg_national_year a
JOIN (SELECT CAST(substr(month, 1, 4) AS INTEGER) AS year, avg(real_factor) AS rf
      FROM macro_month GROUP BY 1) f ON f.year = a.year
WHERE a.property_group = 'apartment' AND a.rooms_bucket = 'all' AND a.is_incomplete = 0
ORDER BY a.year
```

macro_month.real_factor converts nominal shekels of a month into June-2026 shekels (CPI based). For yearly figures, use the year's mean factor. The real series shows how much of the nominal rise is inflation.

Tables: agg_national_year, macro_month · verified 28 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-68

#### Example 69: Monthly real vs nominal price

Question: Show the monthly national residential price per m², nominal and inflation-adjusted, since 2016.
Hebrew: מחיר ריאלי מול נומינלי לפי חודש

```sql
SELECT a.month, a.median_ppsqm AS nominal, round(a.median_ppsqm * m.real_factor) AS real_2026_06, a.is_incomplete
FROM agg_national_month a
JOIN macro_month m ON m.month = a.month
WHERE a.property_group = 'all_residential' AND a.month >= '2016-01'
ORDER BY a.month
```

agg_national_month.month and macro_month.month share the 'YYYY-MM' format, so they join directly. real_factor is NULL for months without a CPI yet.

Tables: agg_national_month, macro_month · verified 129 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-69

#### Example 70: Mortgage rates vs prices and activity

Question: How did mortgage rates relate to apartment prices and deal volume year by year?
Hebrew: ריבית משכנתאות מול מחירים ופעילות

```sql
SELECT a.year, a.deals AS apartment_deals, a.median_price,
       round(r.boi_rate, 2) AS boi_rate, round(r.mortgage_unlinked, 2) AS mortgage_rate_unlinked,
       round(r.mortgage_linked, 2) AS mortgage_rate_linked
FROM agg_national_year a
JOIN (SELECT CAST(substr(month, 1, 4) AS INTEGER) AS year,
             avg(boi_rate) AS boi_rate,
             avg(mortgage_rate_unlinked) AS mortgage_unlinked,
             avg(mortgage_rate_linked) AS mortgage_linked
      FROM macro_month GROUP BY 1) r ON r.year = a.year
WHERE a.property_group = 'apartment' AND a.rooms_bucket = 'all' AND a.year BETWEEN 2012 AND 2025
ORDER BY a.year
```

Mortgage rates start in 2011-07, and yearly averages of monthly rates are fine to compute. The prime-track rate (mortgage_rate_variable_unlinked) only covers 2016-01 to 2024-01; use prime_rate as the benchmark after that.

Tables: agg_national_year, macro_month · verified 14 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-70

#### Example 71: Rent series of a city

Question: How has average rent in Tel Aviv changed by quarter and apartment size?
Hebrew: סדרת שכר דירה של עיר

```sql
SELECT period,
       max(CASE WHEN rooms_bucket = '1-2' THEN avg_rent END) AS rooms_1_2,
       max(CASE WHEN rooms_bucket = '2.5-3' THEN avg_rent END) AS rooms_2_5_3,
       max(CASE WHEN rooms_bucket = '3.5-4' THEN avg_rent END) AS rooms_3_5_4,
       max(CASE WHEN rooms_bucket IN ('4.5-6', '4.5+') THEN avg_rent END) AS rooms_4_5_plus,
       max(CASE WHEN rooms_bucket = 'all' THEN avg_rent END) AS all_rooms
FROM rent_city
WHERE settlement_code = 5000 AND period_type = 'quarter'
GROUP BY period
ORDER BY period
```

rent_city holds CBS average rents of current tenancies (not asking rents) for 18 big cities. The top bucket was renamed from 4.5-6 to 4.5+ in 2026, which is why both are mapped to one column.

Tables: rent_city · verified 30 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-71

#### Example 72: Gross rental yield by city

Question: Which big cities have the highest gross rental yield?
Hebrew: תשואה ברוטו משכירות לפי עיר

```sql
SELECT area_name, settlement_code, n_deals, median_price, round(avg_rent_monthly) AS avg_rent_monthly,
       price_to_rent, gross_yield_pct, low_n
FROM gross_yield
WHERE level = 'city' AND window = '12m' AND rooms_bucket = 'all'
ORDER BY gross_yield_pct DESC
```

gross_yield compares the median existing-stock apartment price (12 months to the anchor) with 12 × the CBS average rent. It is indicative only, because the rent comes from other homes than the ones sold.

Tables: gross_yield · verified 18 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-72

#### Example 73: Affordability: years of wages per apartment

Question: How many years of the average wage does a median apartment cost, over time?
Hebrew: נגישות לדיור: שנות שכר לדירה

```sql
SELECT period, median_price_apartment, round(avg_monthly_wage) AS avg_monthly_wage, months_of_wage, years_of_wage
FROM affordability
WHERE level = 'national' AND stock = 'all'
ORDER BY period_kind = '12m', period
```

affordability divides the median apartment price by the national average monthly wage. Rows are yearly plus a '12m' row for the window to the stats anchor. District rows also use the national wage.

Tables: affordability · verified 29 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-73

#### Example 74: CBS price index vs registry medians

Question: Does the CBS dwelling price index move like the registry median? Compare both, rebased to 2015 = 100.
Hebrew: מדד מחירי הדירות של הלמ״ס מול החציון במרשם

```sql
WITH idx AS (
  SELECT CAST(substr(month, 1, 4) AS INTEGER) AS year, avg(housing_price_index) AS hpi
  FROM macro_month GROUP BY 1
),
reg AS (
  SELECT year, median_ppsqm FROM agg_national_year
  WHERE property_group = 'apartment' AND rooms_bucket = 'all' AND is_incomplete = 0
)
SELECT reg.year,
       round(100.0 * reg.median_ppsqm / (SELECT median_ppsqm FROM reg WHERE year = 2015), 1) AS registry_ppsqm_2015_100,
       round(100.0 * idx.hpi / (SELECT hpi FROM idx WHERE year = 2015), 1) AS cbs_index_2015_100
FROM reg JOIN idx ON idx.year = reg.year
WHERE reg.year >= 2008
ORDER BY reg.year
```

The CBS index is quality-adjusted (it controls for what kind of dwellings sold), while the registry median moves with the mix of what sold, so gaps between the two lines are expected. The CBS month label is the first month of a two-month deal window.

Tables: macro_month, agg_national_year · verified 18 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-74

#### Example 75: A parcel's deal history in today's shekels

Question: Show the home sales in parcel 7016/25 (Tel Aviv) since 1998 with their prices converted to June-2026 shekels.
Hebrew: היסטוריית עסקאות בחלקה בשקלים של היום

```sql
SELECT d.id, d.deal_date, d.sub_chelka, d.rooms, d.area, d.deal_amount,
       round(d.deal_amount * m.real_factor) AS deal_amount_real,
       round(d.price_per_sqm * m.real_factor) AS ppsqm_real
FROM deals d
JOIN macro_month m ON m.month = substr(d.deal_date, 1, 7)
WHERE d.gush = 7016 AND d.chelka = 25 AND d.is_residential = 1 AND d.is_full_deal = 1
ORDER BY d.deal_date
```

Join a single deal to its month's factor with substr(deal_date, 1, 7). The macro_month primary key makes this a point lookup per row.

Tables: deals, macro_month · verified 29 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-75

### Renewal & discount projects

#### Example 76: Urban renewal compounds in a city

Question: What declared urban-renewal compounds exist in Bat Yam, and how advanced are they?
Hebrew: מתחמי התחדשות עירונית בעיר

```sql
SELECT compound_id, name, track, status, status_rank, units_existing, units_planned, plan_number, mavat_url
FROM renewal_compounds
WHERE settlement_code = 6200
ORDER BY status_rank DESC, units_planned DESC
```

status_rank orders the planning stages from 1 (initial planning) to 5 (approved plan in execution). Only declared compounds are included (פינוי-בינוי, עיבוי), not תמ"א 38 building projects.

Tables: renewal_compounds · verified 41 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-76

#### Example 77: Largest urban renewal compounds

Question: Which urban renewal compounds in Israel will add the most housing units?
Hebrew: מתחמי ההתחדשות הגדולים ביותר

```sql
SELECT settlement_name, name, track, status, units_existing, units_planned,
       units_planned - units_existing AS net_new_units
FROM renewal_compounds
WHERE units_planned IS NOT NULL AND units_existing IS NOT NULL
ORDER BY net_new_units DESC
LIMIT 20
```

units_planned is the total after renewal and units_existing is what gets demolished or reinforced, so the difference is the net addition. The table has 978 rows, so a scan is instant.

Tables: renewal_compounds · verified 20 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-77

#### Example 78: Prices on renewal parcels vs elsewhere

Question: In Bat Yam, do existing apartments on urban-renewal parcels sell at a premium?
Hebrew: מחירים בחלקות התחדשות מול שאר העיר

```sql
SELECT CASE WHEN r.compound_id IS NULL THEN 'not in a compound'
            WHEN rc.status_rank >= 3 THEN 'compound, plan approved'
            ELSE 'compound, still planning' END AS renewal,
       count(*) AS n_deals,
       round(median(d.price_per_sqm)) AS median_ppsqm,
       round(median(d.deal_amount)) AS median_price
FROM deals d
LEFT JOIN parcel_renewal r ON r.gush = d.gush AND r.chelka = d.chelka
LEFT JOIN renewal_compounds rc ON rc.compound_id = r.compound_id
WHERE d.settlement_code = 6200
  AND d.deal_date BETWEEN '2021-07-01' AND '2026-06-30'
  AND d.property_group = 'apartment' AND d.is_new_build = 0
  AND d.in_stats = 1 AND d.price_per_sqm IS NOT NULL
GROUP BY renewal
ORDER BY median_ppsqm DESC
```

is_new_build = 0 keeps old units that are waiting for renewal, rather than the new towers built on the same parcels. parcel_renewal maps parcels to compounds, and a LEFT JOIN labels the rest.

Tables: deals, parcel_renewal, renewal_compounds · verified 3 rows in 9 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-78

#### Example 79: Discount lottery projects in a city

Question: Which subsidised lottery projects (מחיר למשתכן / דירה בהנחה) were in Beit Shemesh?
Hebrew: פרויקטים של דירה בהנחה בעיר

```sql
SELECT project_id, program, name, developer, units, winners_total, price_per_sqm, lottery_date, n_deals_official
FROM discount_projects
WHERE settlement_code = 2610
ORDER BY lottery_date DESC
LIMIT 50
```

discount_projects lists official lottery projects (2016-2025) with their official price per m². n_deals_official is the number of registry deals attributed to the project, capped at its number of units; recent lotteries show 0 because sales are registered years later.

Tables: discount_projects · verified 50 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-79

#### Example 80: Discount projects vs the market price

Question: How far below the market were the biggest discount-lottery projects sold?
Hebrew: פרויקטי הנחה מול מחיר השוק

```sql
WITH pd AS (
  SELECT d.discount_project_id AS project_id, count(*) AS n, round(median(d.price_per_sqm)) AS ppsqm, min(d.year) AS first_year
  FROM deals d
  WHERE d.discount_project_id IN (SELECT project_id FROM discount_projects WHERE n_deals_official >= 150)
    AND d.price_per_sqm IS NOT NULL
  GROUP BY d.discount_project_id
)
SELECT p.project_id, p.settlement_name, p.name, p.program, pd.n, pd.first_year,
       pd.ppsqm AS project_ppsqm, a.median_ppsqm AS town_ppsqm_same_year,
       round(100.0 * pd.ppsqm / a.median_ppsqm - 100, 1) AS discount_pct
FROM pd
JOIN discount_projects p ON p.project_id = pd.project_id
JOIN agg_settlement_year a ON a.settlement_code = p.settlement_code AND a.property_group = 'all_residential' AND a.year = pd.first_year
ORDER BY discount_pct
LIMIT 30
```

Discount-project deals are never in_stats, so query them through discount_project_id (partial index ix_deals_discount_project). Compare them with the town's in-stats median of the same year, which already excludes those deals.

Tables: deals, discount_projects, agg_settlement_year · verified 30 rows in 9 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-80

#### Example 81: Deals of one discount project

Question: Show the registry deals attributed to discount project moch:52 (מחיר למשתכן in Ramla).
Hebrew: העסקאות של פרויקט הנחה אחד

```sql
SELECT id, deal_date, gush, chelka, rooms, area, deal_amount, price_per_sqm, year_built
FROM deals
WHERE discount_project_id = 'moch:52'
ORDER BY deal_date DESC
LIMIT 100
```

Attribution is by parcel, date window and price, not by buyer, so describe these as "deals attributed to the project". Show them with a "reduced price" badge, never as market prices.

Tables: deals · verified 100 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-81

### Data quality & flags

#### Example 82: Why rows are excluded from statistics

Question: Of all Tel Aviv apartment deals in 2025, how many enter the price statistics, and why are the others excluded?
Hebrew: מדוע שורות לא נכנסות לסטטיסטיקה

```sql
SELECT count(*) AS all_deals,
       sum(in_stats) AS in_stats,
       count(*) FILTER (WHERE is_full_deal = 0) AS partial_or_unknown_share,
       sum(is_outlier) AS outliers,
       sum(is_multi_unit) AS multi_unit,
       sum(is_discount_project) AS discount_project,
       count(*) FILTER (WHERE in_stats = 1 AND price_per_sqm IS NOT NULL) AS in_stats_with_ppsqm
FROM deals
WHERE settlement_code = 5000 AND property_group = 'apartment'
  AND deal_date BETWEEN '2025-01-01' AND '2025-12-31'
```

in_stats = 1 means not an outlier, not multi-unit, not a discount project, a full deal and at least ₪100k. The flags can overlap, so the exclusion columns do not add up exactly. count(*) FILTER (WHERE ...) is a compact conditional count.

Tables: deals · verified 1 rows in 5 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-82

#### Example 83: Outlier reasons

Question: Which outlier rules fired most often in 2025?
Hebrew: סיבות לסימון חריגים

```sql
SELECT outlier_reason, count(*) AS deals, min(deal_amount) AS min_amount, max(deal_amount) AS max_amount
FROM deals
WHERE deal_date BETWEEN '2025-01-01' AND '2025-12-31' AND is_outlier = 1
GROUP BY outlier_reason
ORDER BY deals DESC
```

outlier_reason is a comma-separated list of rule names. Price-level rules (ppsqm_iqr, price_iqr, ppsqm_out_of_band) still count in money volume, while amount-credibility rules (nominal_amount, declared_nominal, ...) do not.

Tables: deals · verified 10 rows in 12 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-83

#### Example 84: Location precision of a city's deals

Question: How precise are the map locations of Haifa's deals?
Hebrew: דיוק המיקום של עסקאות העיר

```sql
SELECT geo_precision, geo_source, count(*) AS parcels, sum(deals_total) AS deals,
       round(100.0 * sum(deals_total) / (SELECT sum(deals_total) FROM parcels WHERE settlement_code = 4000), 1) AS pct_of_deals
FROM parcels
WHERE settlement_code = 4000
GROUP BY geo_precision, geo_source
ORDER BY deals DESC
```

geo_source = 'parcel' is an exact current-cadastre point. cancelled and shuma are approximate parcel points, gush is the block centroid, and settlement is the town centre (not a location). Aggregating parcels (weighted by deals_total) is much cheaper than scanning deals.

Tables: parcels · verified 5 rows in 10 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-84

#### Example 85: Reporting lag in recent months

Question: Why do the statistics stop at June 2026? Show how complete each recent month is.
Hebrew: פיגור בדיווח בחודשים האחרונים

```sql
SELECT json_extract(j.value, '$.month') AS month,
       json_extract(j.value, '$.residential') AS residential_deals,
       json_extract(j.value, '$.same_month_prev_year') AS same_month_prev_year,
       json_extract(j.value, '$.ratio') AS ratio
FROM json_each((SELECT value FROM meta WHERE key = 'months_completeness')) AS j
ORDER BY month
```

Deals are reported months late. The stats anchor is the last month whose residential count reached 85% of the same month a year earlier. Treat anything after it (is_incomplete = 1) as partial data, not a market drop.

Tables: meta · verified 13 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-85

#### Example 86: Discount-project flags in a city

Question: How many Beer Sheva deals in the last 12 months were flagged as discount-lottery units, and on what basis?
Hebrew: סימוני פרויקטי הנחה בעיר

```sql
SELECT coalesce(discount_source, 'not flagged') AS discount_source,
       count(*) AS deals,
       round(median(price_per_sqm)) AS median_ppsqm
FROM deals
WHERE settlement_code = 9000
  AND property_group = 'apartment'
  AND deal_date BETWEEN '2025-07-01' AND '2026-06-30'
  AND is_full_deal = 1 AND is_outlier = 0 AND is_multi_unit = 0
GROUP BY discount_source
ORDER BY deals DESC
```

discount_source = 'official' means the deal was matched to an official lottery project, and 'heuristic' means it was flagged by the price rules only. Both are excluded from in_stats; show them with a "reduced price" badge.

Tables: deals · verified 3 rows in 3 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-86

#### Example 87: Where the headline median misleads

Question: In which cities does the headline median differ by more than 10% from the existing-stock median?
Hebrew: היכן החציון הכללי מטעה

```sql
SELECT name, median_ppsqm_12m, n_ppsqm_12m, median_ppsqm_existing_12m, n_ppsqm_existing_12m,
       median_ppsqm_new_12m, n_ppsqm_new_12m,
       round(100.0 * median_ppsqm_12m / median_ppsqm_existing_12m - 100, 1) AS pooled_vs_existing_pct
FROM settlements
WHERE n_ppsqm_12m >= 30 AND n_ppsqm_existing_12m >= 20
  AND abs(1.0 * median_ppsqm_12m / median_ppsqm_existing_12m - 1) > 0.10
ORDER BY pooled_vs_existing_pct
```

median_ppsqm_12m pools new and existing stock. Where cheap new projects (not flagged as discount) or expensive towers dominate, it can differ a lot from the existing-stock level. Report both in that case.

Tables: settlements · verified 16 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-87

#### Example 88: Partial-share deals

Question: How common are partial-share sales in Tel Aviv in 2025, and at what shares?
Hebrew: עסקאות של חלק מנכס

```sql
SELECT CASE WHEN portion IS NULL THEN 'unknown'
            WHEN portion = 1 THEN '1 (full)'
            WHEN portion >= 0.5 THEN '0.5-0.99'
            WHEN portion >= 0.25 THEN '0.25-0.49'
            ELSE '< 0.25' END AS portion_band,
       count(*) AS deals,
       round(median(deal_amount)) AS median_amount
FROM deals
WHERE settlement_code = 5000 AND property_group = 'apartment'
  AND deal_date BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY portion_band
ORDER BY deals DESC
```

deal_amount is the price of the sold share, so a partial deal's amount cannot be compared with full prices. A lone 50% row may still be a whole-unit sale by two equal co-owners.

Tables: deals · verified 5 rows in 7 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-88

### Custom statistics

#### Example 89: Year-over-year change with LAG()

Question: What was the year-over-year change of Jerusalem's apartment price per m²?
Hebrew: שינוי שנתי עם LAG()

```sql
SELECT year, n_ppsqm, median_ppsqm,
       lag(median_ppsqm) OVER (ORDER BY year) AS prev_year,
       round(100.0 * median_ppsqm / lag(median_ppsqm) OVER (ORDER BY year) - 100, 1) AS yoy_pct,
       is_incomplete
FROM agg_settlement_year
WHERE settlement_code = 3000 AND property_group = 'apartment'
ORDER BY year
```

lag() reads the previous row of the ordered window. The yearly median pools new and existing stock, so a year with an unusual share of new projects can move it; the official change columns use existing stock only.

Tables: agg_settlement_year · verified 29 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-89

#### Example 90: Top 3 cities per district with RANK()

Question: What are the three most expensive cities in each district?
Hebrew: שלוש הערים המובילות בכל מחוז עם RANK()

```sql
SELECT district, rnk, name, median_ppsqm_12m, n_ppsqm_12m
FROM (
  SELECT district, name, median_ppsqm_12m, n_ppsqm_12m,
         rank() OVER (PARTITION BY district ORDER BY median_ppsqm_12m DESC) AS rnk
  FROM settlements
  WHERE n_ppsqm_12m >= 30 AND district IS NOT NULL
)
WHERE rnk <= 3
ORDER BY district, rnk
```

rank() OVER (PARTITION BY ...) numbers rows within each group, and filtering rnk <= 3 in an outer query gives the top-N per group. Apply the n gate before ranking.

Tables: settlements · verified 18 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-90

#### Example 91: Moving average of a quarterly series

Question: Smooth the national quarterly apartment price per m² with a 4-quarter moving average.
Hebrew: ממוצע נע של סדרה רבעונית

```sql
SELECT quarter, median_ppsqm,
       round(avg(median_ppsqm) OVER (ORDER BY quarter ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)) AS ma_4q,
       is_incomplete
FROM agg_national_quarter
WHERE property_group = 'apartment' AND rooms_bucket = 'all' AND quarter >= '2015-Q1'
ORDER BY quarter
```

A ROWS BETWEEN 3 PRECEDING AND CURRENT ROW frame averages each quarter with the 3 before it. This smooths a displayed trend line; it is not a way to combine medians into a new statistic.

Tables: agg_national_quarter · verified 47 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-91

#### Example 92: Price percentiles in a city

Question: What is the price distribution (10th to 90th percentile) of apartments sold in Rishon LeZion in the last 12 months?
Hebrew: אחוזוני מחיר בעיר

```sql
SELECT count(*) AS n,
       round(percentile(deal_amount, 10)) AS p10,
       round(percentile(deal_amount, 25)) AS p25,
       round(median(deal_amount)) AS p50,
       round(percentile(deal_amount, 75)) AS p75,
       round(percentile(deal_amount, 90)) AS p90,
       round(median(price_per_sqm)) AS median_ppsqm
FROM deals
WHERE settlement_code = 8300 AND property_group = 'apartment'
  AND deal_date BETWEEN '2025-07-01' AND '2026-06-30'
  AND in_stats = 1
```

This SQLite build has the percentile extension: median(x), percentile(x, p) with p from 0 to 100, and percentile_cont(x, f) with f from 0 to 1. median() averages the two middle values when n is even; round() it to match the precomputed integer medians.

Tables: deals · verified 1 rows in 2 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-92

#### Example 93: Portable median with window functions

Question: How do I compute a median without a median() function, e.g. Tel Aviv apartments in 2025?
Hebrew: חציון נייד באמצעות פונקציות חלון

```sql
WITH ranked AS (
  SELECT price_per_sqm AS v,
         row_number() OVER (ORDER BY price_per_sqm) AS rn,
         count(*) OVER () AS n
  FROM deals
  WHERE settlement_code = 5000 AND property_group = 'apartment'
    AND deal_date BETWEEN '2025-01-01' AND '2025-12-31'
    AND in_stats = 1 AND price_per_sqm IS NOT NULL
)
SELECT max(n) AS n, round(avg(v)) AS median_ppsqm
FROM ranked
WHERE rn IN ((n + 1) / 2, (n + 2) / 2)
```

Number the sorted values, then average the middle one (odd n) or the two middle ones (even n). Integer division picks the right positions. The result, 56,707, equals agg_settlement_year.median_ppsqm and median().

Tables: deals · verified 1 rows in 7 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-93

#### Example 94: Histogram of price per m²

Question: What is the distribution of price per m² for apartments sold in Haifa in the last 12 months, in 2,500 ILS bins?
Hebrew: היסטוגרמה של מחיר למ״ר

```sql
SELECT (price_per_sqm / 2500) * 2500 AS bin_from,
       (price_per_sqm / 2500) * 2500 + 2499 AS bin_to,
       count(*) AS deals
FROM deals
WHERE settlement_code = 4000 AND property_group = 'apartment'
  AND deal_date BETWEEN '2025-07-01' AND '2026-06-30'
  AND in_stats = 1 AND price_per_sqm IS NOT NULL
GROUP BY bin_from
ORDER BY bin_from
```

price_per_sqm is an INTEGER, so (x / 2500) * 2500 floors it to its bin. Bins with no deals are absent; fill them on the client if you chart the result.

Tables: deals · verified 20 rows in 3 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-94

#### Example 95: Busiest single days on record

Question: On which single days were the most deals recorded from December 2013 to 2025?
Hebrew: הימים העמוסים ביותר

```sql
SELECT deal_date, count(*) AS deals,
       sum(CASE WHEN property_group = 'apartment' THEN 1 ELSE 0 END) AS apartments
FROM deals
WHERE deal_date BETWEEN '2013-12-01' AND '2025-12-31'
GROUP BY deal_date
ORDER BY deals DESC
LIMIT 10
```

This scans about 1.5M entries, but only inside the covering index ix_deals_date (which also carries property_group), so it stays fast. Spikes line up with tax changes, such as 2015-06-23 before the purchase-tax hike and 2013-12-31 before the מס שבח reform.

Tables: deals · verified 10 rows in 228 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-95

### Recipes

#### Example 96: Recipe: 4-room apartments in Rehavia in 2025

Question: How much did 4-room apartments cost in Rehavia (Jerusalem) in 2025?
Hebrew: מתכון: דירות 4 חדרים ברחביה ב-2025

```sql
SELECT n.name AS neighborhood,
       count(*) AS all_deals,
       sum(d.in_stats) AS n_stats,
       CAST(round(median(CASE WHEN d.in_stats = 1 THEN d.deal_amount END)) AS INTEGER) AS median_price,
       CAST(round(median(CASE WHEN d.in_stats = 1 THEN d.price_per_sqm END)) AS INTEGER) AS median_ppsqm,
       min(CASE WHEN d.in_stats = 1 THEN d.deal_amount END) AS min_price,
       max(CASE WHEN d.in_stats = 1 THEN d.deal_amount END) AS max_price
FROM neighborhoods n
CROSS JOIN parcel_neighborhood pn ON pn.nbhd_id = n.nbhd_id
CROSS JOIN deals d ON d.gush = pn.gush AND d.chelka = pn.chelka
WHERE n.settlement_code = 3000 AND n.name = 'רחביה'
  AND d.settlement_code = n.settlement_code
  AND d.property_group = 'apartment'
  AND d.rooms >= 4 AND d.rooms < 5
  AND d.deal_date BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY n.name
```

Resolve the city (settlements) and the neighbourhood (neighborhoods or neighborhoods_fts), walk its parcels, and take median() over in_stats deals only. Report n_stats next to the median: with about 25 deals, this is a small sample.

Tables: neighborhoods, parcel_neighborhood, deals · verified 1 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-96

#### Example 97: Recipe: 3-room apartment prices in a city, recent quarters

Question: What does a 3-room apartment cost in Haifa lately, and is it going up?
Hebrew: מתכון: מחירי דירות 3 חדרים בעיר ברבעונים האחרונים

```sql
SELECT quarter, deals, n_stats, median_price, median_ppsqm, median_area, is_incomplete
FROM agg_settlement_quarter
WHERE settlement_code = (SELECT code FROM settlements WHERE name = 'חיפה')
  AND property_group = 'apartment' AND rooms_bucket = '3'
  AND quarter >= '2023-Q1'
ORDER BY quarter
```

For "how much and which direction" questions, read the precomputed quarterly medians for the right rooms_bucket, and resolve the city code with a subquery. Judge the direction on complete quarters (is_incomplete = 0) with a reasonable n_stats.

Tables: agg_settlement_quarter, settlements · verified 15 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-97

#### Example 98: Recipe: compare two neighbouring cities

Question: Should I buy in Ramat Gan or Givatayim? Compare prices, change, 4-room prices and rental yield.
Hebrew: מתכון: השוואה בין שתי ערים שכנות

```sql
SELECT s.name, s.median_ppsqm_12m, s.n_ppsqm_12m, s.median_ppsqm_existing_12m, s.ppsqm_change_pct,
       s.median_price_4rooms_12m, s.n_4rooms_12m, s.deals_12m,
       ss.cluster AS socio_cluster, gy.gross_yield_pct
FROM settlements s
LEFT JOIN settlement_socio ss ON ss.settlement_code = s.code
LEFT JOIN gross_yield gy ON gy.settlement_code = s.code AND gy.level = 'city' AND gy.window = '12m' AND gy.rooms_bucket = 'all'
WHERE s.code IN (8600, 6300)
```

One row per city with the precomputed 12-month numbers plus enrichment (socio-economic cluster, gross yield). Yield exists only for the 18 big cities, so it is NULL for Givatayim; say so rather than guessing.

Tables: settlements, settlement_socio, gross_yield · verified 2 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-98

#### Example 99: Recipe: real price change over 5 years

Question: Are Jerusalem apartments more expensive now than 5 years ago after inflation?
Hebrew: מתכון: שינוי ריאלי במחיר ב-5 שנים

```sql
WITH y AS (
  SELECT a.year, a.median_ppsqm, a.n_ppsqm,
         round(a.median_ppsqm * (SELECT avg(m.real_factor) FROM macro_month m WHERE m.month BETWEEN a.year || '-01' AND a.year || '-12')) AS median_ppsqm_real
  FROM agg_settlement_year a
  WHERE a.settlement_code = 3000 AND a.property_group = 'apartment' AND a.year IN (2020, 2025)
)
SELECT y20.median_ppsqm AS ppsqm_2020, y25.median_ppsqm AS ppsqm_2025,
       round(100.0 * y25.median_ppsqm / y20.median_ppsqm - 100, 1) AS nominal_change_pct,
       y20.median_ppsqm_real AS ppsqm_2020_real, y25.median_ppsqm_real AS ppsqm_2025_real,
       round(100.0 * y25.median_ppsqm_real / y20.median_ppsqm_real - 100, 1) AS real_change_pct
FROM y y20 JOIN y y25 ON y20.year = 2020 AND y25.year = 2025
```

Compare complete years only (2025, not the partial 2026) and convert each to June-2026 shekels with the year's mean real_factor. The real change is the nominal change with CPI inflation removed.

Tables: macro_month, agg_settlement_year · verified 1 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-99

#### Example 100: Recipe: where can I afford a 4-room apartment?

Question: In which cities can I buy a typical 4-room apartment for up to 2 million ILS?
Hebrew: מתכון: היכן אפשר לקנות דירת 4 חדרים בתקציב?

```sql
SELECT name, district, median_price_4rooms_12m, n_4rooms_12m, median_ppsqm_12m, population
FROM settlements
WHERE median_price_4rooms_12m <= 2000000 AND n_4rooms_12m >= 30
ORDER BY median_price_4rooms_12m DESC
LIMIT 25
```

median_price_4rooms_12m is the median of 4-4.5-room apartments over the 12 months to the anchor (in_stats). Sorting by price descending shows the most expensive places that still fit the budget first; the n >= 30 gate drops thin samples.

Tables: settlements · verified 25 rows in 0 ms on 2026-09-28 · https://mashkof.pov.sh/developers#example-100

## 7. Attribution and terms

Source: Israel Tax Authority real-estate transaction records (public), cleaned and enriched by Mashkof (משקוף) with OpenStreetMap (© OpenStreetMap contributors, ODbL), the Central Bureau of Statistics, the Bank of Israel and government ministries. Provided as-is, without warranty; it is not an appraisal or advice. When you present numbers, say they are medians of reported deals, give the deal count (n) and the period, and credit “Mashkof, based on Israel Tax Authority data”.