Public · read-only · no API key

SQL API for LLMs & agents

שאילתות SQL ישירות על כל עסקאות הנדל״ן בישראל — מסד הנתונים המלא שמאחורי משקוף, לקריאה בלבד ובלי מפתח, עם סכמה מתועדת ו-100 דוגמאות שסוכני AI מבינים.

Quick start /llms-full.txt Try the console

GET /api/sql?format=md&q=…

$ curl "mashkof.pov.sh/api/sql?format=md&q=…"
-- q:
SELECT rank_ppsqm, name_en, median_ppsqm_12m, n_ppsqm_12m
FROM settlements WHERE rank_ppsqm IS NOT NULL
ORDER BY rank_ppsqm LIMIT 5

| rank_ppsqm | name_en | median_ppsqm_12m | n_ppsqm_12m |
|---|---|---|---|
| 1 | Tel Aviv - Yafo | 56882 | 3476 |
| 2 | Herzliyya | 41354 | 845 |
| 3 | Giv'atayim | 39640 | 289 |
| 4 | Jerusalem | 36835 | 3613 |
| 5 | Ramat Gan | 36360 | 1042 |

Live result · median ₪/m² of residential deals in the 12 months to 2026-06-30

deals since 1998: 3.18M
tables & views: 49
documented columns: 701
verified examples: 100

01 · Quick start

Ask the database anything, in SQL#

One HTTP endpoint runs one read-only SQLite statement against the full Mashkof database and returns JSON, CSV or a Markdown table. No key, no sign-up. AI agents should start by reading /llms-full.txt.

בעברית, בקצרה

משקוף פותח את כל מסד הנתונים שמאחורי האתר לשאילתות SQL ישירות: 3.18 מיליון עסקאות נדל״ן מרשות המסים, מחירים חציוניים מחושבים מראש, שכונות, רחובות, תחבורה, בתי ספר, שכר דירה, תשואות ונתוני מאקרו — ב-49 טבלאות. הממשק מיועד בעיקר למודלי שפה ולסוכני AI, ולכן התיעוד כאן באנגלית: סכמה מלאה, כללי שימוש, 100 דוגמאות מאומתות וקונסולה להרצה. הגישה חופשית ולקריאה בלבד. תנו לסוכן שלכם את הכתובת https://mashkof.pov.sh/llms-full.txt והוא יסתדר.

01
Read the rules
Fetch /llms-full.txt (or /api/sql/schema): tables, column meanings, units and the statistics rules in one file.
02
Query
GET /api/sql?q=… or POST {"sql": …}. One read-only statement, 10 s, up to 10,000 rows.
03
Cite carefully
Report medians with their n and period (windows end at the stats anchor). Source: Israel Tax Authority via Mashkof.

Call it from anywhere

curl (GET)

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"

curl (POST)

curl -s -X POST "https://mashkof.pov.sh/api/sql" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT quarter, median_ppsqm, n_ppsqm FROM agg_settlement_quarter WHERE settlement_code = ? AND property_group = ? AND rooms_bucket = ? AND is_incomplete = 0 ORDER BY quarter DESC LIMIT 8",
    "params": [5000, "apartment", "all"],
    "format": "json"
  }'

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)))

JavaScript

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

async function sql(query, params = [], format = "json") {
  const res = await fetch(API, {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({ sql: query, params, format }),
  });
  if (!res.ok) throw new Error(JSON.stringify((await res.json()).error));
  return format === "json" ? res.json() : res.text();
}

const { columns, rows, elapsed_ms } = await sql(
  "SELECT month, median_ppsqm, n_ppsqm FROM agg_national_month WHERE property_group = ? AND is_incomplete = 0 ORDER BY month DESC LIMIT 12",
  ["all_residential"],
);
console.table(rows.map((r) => Object.fromEntries(r.map((v, i) => [columns[i].name, v]))));

Instructions for your AI agent

Paste into a system prompt, a custom GPT, a Claude project or an MCP tool description

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.

02 · Reference

Endpoints#

Everything is plain HTTPS with CORS enabled. GET and POST behave the same; GET responses can be cached by CDNs and browsers.

GETPOST/api/sql

Run one read-only SQL statement.

Parameters
Parameter	Description
`q`requiredstringGET query	The SQL statement (URL-encoded). Alias: sql.
`sql`requiredstringPOST JSON body	The SQL statement. A text/plain POST body is also accepted as the SQL itself.
`params`array \| objectPOST body / GET query (JSON)	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`json \| csv \| mdGET query / POST body	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`integerGET query / POST body	Maximum rows returned (default 1,000, max 10,000). Extra rows are cut and truncated = true. Your own LIMIT still applies.

Response (200, format=json)

application/json

{
  "columns": [
    {
      "name": "name",
      "type": "TEXT"
    },
    {
      "name": "median_ppsqm_12m",
      "type": "INTEGER"
    },
    {
      "name": "n_ppsqm_12m",
      "type": "INTEGER"
    }
  ],
  "rows": [
    [
      "תל אביב-יפו",
      56882,
      3476
    ],
    [
      "הרצלייה",
      41354,
      845
    ],
    [
      "גבעתיים",
      39640,
      289
    ]
  ],
  "row_count": 3,
  "truncated": false,
  "limit": 1000,
  "elapsed_ms": 0.4,
  "schema_version": "1.2",
  "data_as_of": "2026-09-17"
}

format=csv returns text/csv with a header row; format=md returns a Markdown table (text/markdown). Both carry the metadata in X-Row-Count, X-Truncated and X-Elapsed-Ms headers where available.

Response fields
`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.

Errors

Non-200 responses are JSON: {"error": {"code", "message", "hint"?}}. Fix the query and retry; don’t retry 400s unchanged.

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 & fair use

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.

Queries run in isolated, memory-capped worker processes against a read-only copy; a heavy query only ever hurts itself.

GET/api/sql/schema

The full schema as JSON: every table with row count, description, primary key, columns (type, nullability, description, example, value domains), indexes, joins, plus the query conventions. ?format=md returns Markdown.

GET/api/sql/examples

The 100 curated examples below as a JSON array: id, category, question, sql, explanation, tables and verification stats.

03 · Query correctly

Rules that keep answers right#

The database is clean, but real-estate statistics have traps. These conventions are what separates a correct answer from a plausible-looking wrong one.

01Engine & 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.

02Types, 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.

03Dates 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.

04Hebrew 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.

05Identifiers 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.

06The 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.

07ppsqm = 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.

08Time 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').

09Incomplete 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.

10Price 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.

11Partial 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.

12Discount 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.

13Geo 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.

14Full-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.

15Bounding 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.

16Performance

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.

17Pseudo-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.

18Real (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.

19Attribution

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.

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

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

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

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

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

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

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

04 · Schema

49 tables, every column explained#

Schema 1.2 · SQLite 3.53.4 · deals through 2026-09-17 · statistics windows end at 2026-06-30. Deep-link any table as #schema-<table> or column as #schema-<table>-<column>.

Logical relationships (SQLite declares no foreign keys). macro_month joins agg_national_month and substr(deals.deal_date, 1, 7) on month. Click a box to open its schema.

49 tables

Core: deals & places

Transactions and the entities they belong to.

deals

3.18M rows · 37 cols

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.

Primary key(id)Joinssettlement_code → settlements.codeproperty_group → property_groups.keydeal_nature → deal_natures.rawdiscount_project_id → discount_projects.project_idgush → gushim.gushgush, chelka → parcels.(gush, chelka)

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.

Columns of deals
Column	Type	Description	Example
idnot null	INTEGER	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	CBS locality code → settlements.code. NULL for 1,065 deals (unresolved regional-council labels): group by code, display settlement.→ settlements.code	5000
settlement	TEXT	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	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%).	צהל
gushnot null	INTEGER	Cadastral block (גוש). Indexed with chelka (ix_deals_gush_chelka).→ gushim.gush	6768
chelkanot null	INTEGER	Parcel (חלקה) within the gush. (gush, chelka) → parcels.	5
sub_chelkanot null	INTEGER	Sub-parcel (תת-חלקה, roughly the unit/apartment). 0 = none.	108
deal_datenot null	TEXT	Transaction date, TEXT 'YYYY-MM-DD' (1998-01-01 … 2026-09-17). Compare as strings: deal_date >= '2025-07-01'.	2026-05-01
yearnot null	INTEGER	Year of deal_date (INTEGER).	2026
quarternot null	TEXT	Quarter of deal_date, TEXT 'YYYY-Qn' (e.g. '2025-Q4'); sorts correctly as a string.	2026-Q2
deal_amountnot null	INTEGER	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	Declared value (שווי מוצהר) in ₪. Equals deal_amount in ~92% of rows. NULL when the source said 0 (4,464 rows).	7555000
deal_naturenot null	TEXT	Raw Hebrew transaction type (מהות), one of 47 values → deal_natures.raw (mapped to property_group).→ deal_natures.raw	דירה בבית קומות
property_groupnot null	TEXT	Normalised property type key → property_groups.key: apartment, garden_apartment, penthouse, house (residential) or land, commercial, agriculture, parking, other.`apartmentgarden_apartmentpenthousehouselandcommercialagricultureparkingother`→ property_groups.key	apartment
is_residentialnot null	INTEGER	1 for apartment, garden_apartment, penthouse and house; else 0.	1
portion	REAL	Share of the property sold (חלק נמכר), 0.001–1. 1 = whole unit. NULL = unknown (77k rows; the source said 0).	1
is_full_dealnot null	INTEGER	1 when portion = 1 (a whole-unit sale).	1
year_built	INTEGER	Year of construction, 1850 … deal year + 5 (off-plan sales can be in the future). NULL when unknown (~793k rows).	2031
is_new_buildnot null	INTEGER	1 when residential and year_built >= year − 1 (new or off-plan, 'דירה חדשה'); 0 otherwise, including unknown year_built.	1
area	REAL	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	Number of rooms (half rooms allowed), 1–20. NULL when unknown (~943k rows). Ignore on non-residential rows.	5
rooms_bucket	TEXT	'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.`1-23456+`	5
price_per_sqm	INTEGER	₪ 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	Latitude (WGS84) of the parcel point shared by every deal of this (gush, chelka); never NULL. See geo_precision.	32.110498
lon	REAL	Longitude (WGS84) of the parcel point; never NULL.	34.797737
geo_precision	TEXT	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).`parcelgushsettlement`	parcel
is_outliernot null	INTEGER	1 when an outlier rule fired (58,558 rows). Outliers are never in_stats.	0
outlier_reason	TEXT	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_unitnot null	INTEGER	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	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.`same_price_unitspair_total_priceflat_price_batchportfolioprivatization`	same_price_units
multi_unit_n	INTEGER	How many distinct units share (gush, date, amount). 1 = unique; max 181.	1
in_statsnot null	INTEGER	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	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_projectnot null	INTEGER	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	Number of source rows (2–4 split shares of one sale) merged into this full deal; NULL for ordinary rows.	2
discount_source	TEXT	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.`officialheuristic`	official
discount_project_id	TEXT	discount_projects.project_id for 'official' discount rows (partial index ix_deals_discount_project). NULL otherwise.→ discount_projects.project_id	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

1,139 rows · 58 cols

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'.

Primary key(code)Joinsdistrict → districts.namecode → settlements_fts.rowid

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_*).

Columns of settlements
Column	Type	Description	Example
codenot null	INTEGER	Primary key: CBS locality code (סמל יישוב), e.g. 5000 = Tel Aviv-Yafo, 3000 = Jerusalem, 4000 = Haifa. Also the rowid of settlements_fts.→ settlements_fts.rowid	5000
namenot null	TEXT	Canonical Hebrew name (same as deals.settlement), raw registry spelling (ASCII " and ' possible).	תל אביב-יפו
slugnot null	TEXT	URL slug (UNIQUE): spaces/dashes → '-', quotes/geresh/parentheses/dots removed, e.g. 'תל-אביב-יפו'.	תל-אביב-יפו
name_en	TEXT	English name (CBS), e.g. 'Tel Aviv - Yafo'. NULL when unknown.	Tel Aviv - Yafo
district	TEXT	District (מחוז) in CBS form: 'הצפון', 'חיפה', 'המרכז', 'תל אביב', 'ירושלים', 'הדרום', 'יהודה והשומרון' → districts.name. NULL for a few small West Bank localities.`הצפוןהדרוםהמרכזחיפהירושליםיהודה והשומרוןתל אביב`→ districts.name	תל אביב
subdistrict	TEXT	Sub-district (נפה), Hebrew.	תל אביב
lat	REAL	Official settlement centre latitude (WGS84). Never NULL.	32.084449
lon	REAL	Official settlement centre longitude (WGS84). Never NULL.	34.791693
bbox_min_lat	REAL	Map extent (south). Polygon box when has_polygon = 1, else deal-point box; always contains lat/lon.	32.029336
bbox_min_lon	REAL	Map extent (west).	34.739149
bbox_max_lat	REAL	Map extent (north).	32.146967
bbox_max_lon	REAL	Map extent (east).	34.852262
has_polygon	INTEGER	1 when a boundary polygon exists (used by the choropleth).	1
muni_type	TEXT	Municipal type: 'עירייה' (city), 'מועצה מקומית' (local council), 'מועצה אזורית' (regional council), 'מועצה מקומית תעשייתית', 'ללא שיפוט'. NULL for some.`מועצה אזוריתמועצה מקומיתעירייהללא שיפוטמועצה מקומית תעשייתית`	עירייה
settlement_kind	TEXT	Settlement kind: 'יישוב עירוני' (urban), 'יישוב כפרי' (rural), 'מוקד תעסוקה' (employment zone), 'מקום'.`יישוב כפרייישוב עירונימקוםמוקד תעסוקה`	יישוב עירוני
regional_council	TEXT	Regional council name for rural localities; NULL for independent municipalities.	לכיש
municipality	TEXT	Name of the local authority the locality belongs to.	תל אביב - יפו
muni_code	INTEGER	Code of the local authority.	5000
population	INTEGER	Current population (Population Authority, else CBS 2023). NULL for 20.	601640
area_km2	REAL	Polygon area in km². NULL without a polygon.	57.178
year_founded	INTEGER	Year founded. May be NULL.	1909
elevation_m	INTEGER	Elevation of the settlement in metres. May be NULL.	17
deals_totalnot null	INTEGER	All unique deals, all property groups, all time.	244806
deals_residential_totalnot null	INTEGER	Residential deals, all time.	178358
first_deal	TEXT	Date of the first deal, 'YYYY-MM-DD'.	1998-01-01
last_deal	TEXT	Date of the latest deal, 'YYYY-MM-DD' (can be after the stats anchor).	2026-07-26
deals_12mnot null	INTEGER	Deals (all groups) in the 12m window ending at the stats anchor.	7038
deals_residential_12mnot null	INTEGER	Residential deals in the 12m window.	6020
deals_prev12mnot null	INTEGER	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_12mnot null	INTEGER	Residential stat deals (in_stats = 1) in 12m behind median_price_12m.	3504
median_price_12m	INTEGER	Median residential deal price in ₪, 12m, all residential groups. NULL when n_stats_12m = 0.	4350000
n_ppsqm_12mnot null	INTEGER	Residential stat deals with price_per_sqm in 12m.	3476
median_ppsqm_12m	INTEGER	Headline median ₪/m², all residential groups, 12m (pooled new + existing; discount projects excluded). NULL for ~684 small places.	56882
n_ppsqm_prev12mnot null	INTEGER	Residential stat deals with ppsqm in prev12m.	3406
median_ppsqm_prev12m	INTEGER	Median residential ₪/m² in prev12m (pooled).	55800
ppsqm_change_pct	REAL	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	Median residential ₪/m² over the 5y window.	54128
n_apartment_12mnot null	INTEGER	Apartment-group (property_group = 'apartment') stat deals, 12m.	3453
median_price_apartment_12m	INTEGER	Median apartment price in ₪, 12m.	4320194
median_ppsqm_apartment_12m	INTEGER	Median apartment ₪/m², 12m.	56882
n_4rooms_12mnot null	INTEGER	Apartments with 4–4.5 rooms, stat rule, 12m.	936
median_price_4rooms_12m	INTEGER	Median price in ₪ of 4–4.5-room apartments, 12m.	5550643
n_house_12mnot null	INTEGER	House-group (private houses / cottages) stat deals, 12m.	8
median_price_house_12m	INTEGER	Median house price in ₪, 12m.	7250000
n_new_build_12mnot null	INTEGER	In-stats residential new-build deals, 12m.	1789
total_volume_12mnot null	INTEGER	Sum of deal_amount in ₪, 12m, all groups, credible amounts only.	25615885169
avg_area	INTEGER	Mean area (m²) of in-stats residential deals, 12m. NULL when none.	89
rank_ppsqm	INTEGER	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	'existing' when ppsqm_change_pct is set, else NULL.`existing`	existing
n_ppsqm_new_12mnot null	INTEGER	New-build (is_new_build = 1) stat deals with ppsqm, 12m.	1775
median_ppsqm_new_12m	INTEGER	Median ₪/m² of new builds, 12m. NULL when none.	59107
n_ppsqm_existing_12mnot null	INTEGER	Existing-stock stat deals with ppsqm, 12m.	1701
median_ppsqm_existing_12m	INTEGER	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	Median ₪/m² of existing stock, prev12m (base of ppsqm_change_pct).	50862
n_discount_12mnot null	INTEGER	Discount-project deals in 12m — NOT in the medians.	173
deals_after_anchornot null	INTEGER	Deals dated after meta.stats_anchor_date reported so far (incomplete).	36
n_parcels_12mnot null	INTEGER	Distinct parcels behind the 12m ppsqm median (rank gate).	1414
max_parcel_share_12m	REAL	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
unique constraint(slug)unique

districts

7 rows · 12 cols

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

Primary key(name)

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

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

Indexes

primary key(name)unique

gushim

12k rows · 24 cols

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.

Primary key(gush)Joinssettlement_code → settlements.code

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.

Columns of gushim
Column	Type	Description	Example
gushnot null	INTEGER	Primary key: block number.	6212
settlement_code	INTEGER	Dominant settlement (most coded deals) → settlements.code. NULL for 96.→ settlements.code	5000
lat	REAL	Gush centroid latitude (WGS84). NULL for 1.	32.094248
lon	REAL	Gush centroid longitude.	34.787909
geo_source	TEXT	'gush' (polygon centroid) or 'parcels_mean' (mean of its parcel points when no polygon).`gush`	gush
has_polygonnot null	INTEGER	1 when the gush polygon exists.	1
area_m2	INTEGER	Gush polygon area in m².	698386
deals_totalnot null	INTEGER	All deals, all time, all groups.	5832
deals_24mnot null	INTEGER	Deals (all groups) in the 24m window.	493
deals_5ynot null	INTEGER	Deals (all groups) in the 5y window.	1477
n_ppsqm_24mnot null	INTEGER	Residential stat deals with ppsqm, 24m.	303
median_ppsqm_24m	INTEGER	Median residential ₪/m², 24m. NULL when n = 0.	65833
n_ppsqm_5ynot null	INTEGER	Residential stat deals with ppsqm, 5y.	745
median_ppsqm_5y	INTEGER	Median residential ₪/m², 5y.	64964
n_ppsqm_prev24mnot null	INTEGER	Residential stat deals with ppsqm, prev24m.	206
median_ppsqm_prev24m	INTEGER	Median residential ₪/m², prev24m.	66709
change_pct	REAL	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	Median residential price in ₪, 24m.	5419000
first_deal	TEXT	First deal date 'YYYY-MM-DD'.	1998-01-01
last_deal	TEXT	Latest deal date 'YYYY-MM-DD'.	2026-06-22
change_method	TEXT	'existing' when change_pct is set, else NULL.`existing`	existing
n_ppsqm_new_24mnot null	INTEGER	New-build residential stat deals with ppsqm, 24m.	229
n_ppsqm_existing_24mnot null	INTEGER	Existing-stock residential stat deals with ppsqm, 24m.	74
n_discount_24mnot null	INTEGER	Discount-project deals in 24m (not in the medians).	0

Indexes

ix_gushim_settlement(settlement_code)

parcels

381k rows · 23 cols

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.

Primary key(id)Joinssettlement_code → settlements.codegush → gushim.gushlast_deal_id → deals.idid → parcels_rtree.id

(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'.

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

Indexes

ix_parcels_settlement(settlement_code)
unique constraint(gush, chelka)unique

Precomputed aggregates

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

agg_settlement_quarter

439k rows · 12 cols

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.

Primary key(settlement_code, property_group, rooms_bucket, quarter)Joinssettlement_code → settlements.codeproperty_group → property_groups.key

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

Columns of agg_settlement_quarter
Column	Type	Description	Example
settlement_codenot null	INTEGER	CBS locality code (סמל יישוב). Join key → settlements.code.→ settlements.code	5000
quarternot null	TEXT	Quarter 'YYYY-Qn' (1998-Q1 … 2026-Q3).	2025-Q4
property_groupnot null	TEXT	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).`all_residentialapartmenthousealllandgarden_apartmentcommercialpenthouseagricultureparkingother`→ property_groups.key	apartment
rooms_bucketnot null	TEXT	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.`all546+31-2`	all
dealsnot null	INTEGER	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	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	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	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	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	Median area (m²) of the stat deals. NULL when none.	77
total_volumenot null	INTEGER	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_incompletenot null	INTEGER	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

Indexes

primary key(settlement_code, property_group, rooms_bucket, quarter)unique

agg_settlement_year

99k rows · 11 cols

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

Primary key(settlement_code, property_group, year)Joinssettlement_code → settlements.codeproperty_group → property_groups.key

Year 2026 is incomplete (is_incomplete = 1).

Columns of agg_settlement_year
Column	Type	Description	Example
settlement_codenot null	INTEGER	CBS locality code (סמל יישוב). Join key → settlements.code.→ settlements.code	5000
yearnot null	INTEGER	Calendar year (1998 … 2026).	2025
property_groupnot null	TEXT	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).`alllandall_residentialhouseapartmentcommercialagriculturegarden_apartmentotherparkingpenthouse`→ property_groups.key	apartment
dealsnot null	INTEGER	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	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	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	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	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	Median area (m²) of the stat deals. NULL when none.	80
total_volumenot null	INTEGER	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_incompletenot null	INTEGER	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

Indexes

primary key(settlement_code, property_group, year)unique

agg_national_month

3,708 rows · 10 cols

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

Primary key(property_group, month)Joinsmonth → macro_month.monthproperty_group → property_groups.key

Columns of agg_national_month
Column	Type	Description	Example
monthnot null	TEXT	Month 'YYYY-MM' (1998-01 … 2026-09).→ macro_month.month	2025-12
property_groupnot null	TEXT	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).`allall_residentialapartmentlandcommercialhouseparkingagricultureothergarden_apartmentpenthouse`→ property_groups.key	all_residential
dealsnot null	INTEGER	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	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	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	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	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	Median area (m²) of the stat deals. NULL when none.	101
total_volumenot null	INTEGER	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_incompletenot null	INTEGER	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

Indexes

primary key(property_group, month)unique

agg_national_quarter

3,875 rows · 11 cols

National quarterly series per property_group × rooms_bucket.

Primary key(property_group, rooms_bucket, quarter)Joinsproperty_group → property_groups.key

Columns of agg_national_quarter
Column	Type	Description	Example
quarternot null	TEXT	Quarter 'YYYY-Qn'.	2025-Q4
property_groupnot null	TEXT	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).`all_residentialapartmenthousegarden_apartmentpenthouseagricultureallcommerciallandotherparking`→ property_groups.key	apartment
rooms_bucketnot null	TEXT	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.`all546+31-2`	all
dealsnot null	INTEGER	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	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	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	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	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	Median area (m²) of the stat deals. NULL when none.	100
total_volumenot null	INTEGER	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_incompletenot null	INTEGER	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

Indexes

primary key(property_group, rooms_bucket, quarter)unique

agg_national_year

1,007 rows · 11 cols

National yearly series per property_group × rooms_bucket.

Primary key(property_group, rooms_bucket, year)Joinsproperty_group → property_groups.key

Columns of agg_national_year
Column	Type	Description	Example
yearnot null	INTEGER	Calendar year.	2025
property_groupnot null	TEXT	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).`all_residentialapartmenthousegarden_apartmentpenthouseagricultureallcommerciallandotherparking`→ property_groups.key	apartment
rooms_bucketnot null	TEXT	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.`all4356+1-2`	all
dealsnot null	INTEGER	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	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	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	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	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	Median area (m²) of the stat deals. NULL when none.	99
total_volumenot null	INTEGER	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_incompletenot null	INTEGER	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

Indexes

primary key(property_group, rooms_bucket, year)unique

agg_district_quarter

763 rows · 10 cols

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

Primary key(district, quarter)Joinsdistrict → districts.name

Columns of agg_district_quarter
Column	Type	Description	Example
districtnot null	TEXT	District name (CBS form) → districts.name.`הדרוםהמרכזהצפוןחיפהירושליםתל אביביהודה והשומרון`→ districts.name	תל אביב
quarternot null	TEXT	Quarter 'YYYY-Qn'.	2025-Q4
dealsnot null	INTEGER	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	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	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	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	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	Median area (m²) of the stat deals. NULL when none.	89
total_volumenot null	INTEGER	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_incompletenot null	INTEGER	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

Indexes

primary key(district, quarter)unique

agg_district_year

2,009 rows · 11 cols

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

Primary key(district, property_group, year)Joinsdistrict → districts.nameproperty_group → property_groups.key

Columns of agg_district_year
Column	Type	Description	Example
districtnot null	TEXT	District name (CBS form) → districts.name.`ירושליםהמרכזהצפוןהדרוםחיפהתל אביביהודה והשומרון`→ districts.name	תל אביב
yearnot null	INTEGER	Calendar year.	2025
property_groupnot null	TEXT	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).`allapartmentall_residentiallandhousecommercialparkingotheragriculturegarden_apartmentpenthouse`→ property_groups.key	all_residential
dealsnot null	INTEGER	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	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	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	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	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	Median area (m²) of the stat deals. NULL when none.	88
total_volumenot null	INTEGER	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_incompletenot null	INTEGER	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

Indexes

primary key(district, property_group, year)unique

agg_gush_year

191k rows · 8 cols

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

Primary key(gush, year)Joinsgush → gushim.gush

Columns of agg_gush_year
Column	Type	Description	Example
gushnot null	INTEGER	Block number → gushim.gush.→ gushim.gush	6212
yearnot null	INTEGER	Calendar year.	2025
dealsnot null	INTEGER	All deals (all groups) in the gush that year.	258
n_stats	INTEGER	Residential stat deals behind median_price.	151
median_price	INTEGER	Median residential price in ₪.	5534000
n_ppsqm	INTEGER	Residential stat deals with ppsqm.	150
median_ppsqm	INTEGER	Median residential ₪/m².	65617
is_incompletenot null	INTEGER	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

Indexes

primary key(gush, year)unique

agg_neighborhood_year

32k rows · 11 cols

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

Primary key(nbhd_id, year)Joinsnbhd_id → neighborhoods.nbhd_id

Columns of agg_neighborhood_year
Column	Type	Description	Example
nbhd_idnot null	INTEGER	Neighbourhood id → neighborhoods.nbhd_id.→ neighborhoods.nbhd_id	50005953
yearnot null	INTEGER	Calendar year.	2025
dealsnot null	INTEGER	All deals (all groups) on the neighbourhood's parcels (same-settlement deals only).	171
deals_residentialnot null	INTEGER	Residential deals.	123
n_statsnot null	INTEGER	Residential stat deals behind median_price.	86
median_price	INTEGER	Median residential price in ₪. NULL when n_stats = 0.	2812500
n_ppsqmnot null	INTEGER	Residential stat deals with ppsqm.	86
median_ppsqm	INTEGER	Median residential ₪/m² (pooled new + existing).	54284
n_ppsqm_existingnot null	INTEGER	Existing-stock stat deals with ppsqm.	60
median_ppsqm_existing	INTEGER	Median ₪/m² of existing stock.	51688
is_incompletenot null	INTEGER	1 for 2026 (after the stats anchor).	0

Indexes

primary key(nbhd_id, year)unique

Search & spatial indexes

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

settlements_fts

FTS51,139 rows · 2 cols

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.

Primary key(rowid)Joinsrowid → settlements.code

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.

Columns of settlements_fts
Column	Type	Description	Example
name	TEXT	Canonical Hebrew name (indexed column; weight it higher in bm25).	תל אביב-יפו
aliases	TEXT	' \| '-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 \| אביב \| יפו \| ת"א \| ת"א יפו \| ת"א-י…

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

streets_fts

FTS532k rows · 3 cols

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

Primary key(rowid)Joinsrowid → streets.id

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

Columns of streets_fts
Column	Type	Description	Example
street	TEXT	Street name (indexed only; contentless → NULL when selected).	—
settlement	TEXT	Settlement name plus its search aliases (indexed only).	—
aliases	TEXT	Street spelling variants without quotes/geresh, 'רחוב'/'שדרות'/'דרך' prefixes (indexed only).	—

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

neighborhoods_fts

FTS51,277 rows · 3 cols

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

Primary key(rowid)Joinsrowid → neighborhoods.nbhd_id

Columns of neighborhoods_fts
Column	Type	Description	Example
name	TEXT	Neighbourhood name (indexed only; NULL when selected).	—
settlement	TEXT	Settlement name and aliases (indexed only).	—
aliases	TEXT	Spelling variants (indexed only).	—

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

parcels_rtree

R*Tree381k rows · 5 cols

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).

Primary key(id)Joinsid → parcels.id

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.

Columns of parcels_rtree
Column	Type	Description	Example
id	INT	= parcels.id.→ parcels.id	91722
minLat	REAL	Box south edge (latitude, float32).	32.093693
maxLat	REAL	Box north edge (latitude).	32.093697
minLon	REAL	Box west edge (longitude).	34.783112
maxLon	REAL	Box east edge (longitude).	34.783119

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

deals_rtree

view3.18M rows · 5 cols

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.

Joinsid → deals.id

Columns of deals_rtree
Column	Type	Description	Example
id	INTEGER	= deals.id.→ deals.id	3411747
minLat	REAL	Parcel point box south edge.	32.070705
maxLat	REAL	Parcel point box north edge.	32.070709
minLon	REAL	Parcel point box west edge.	34.780403
maxLon	REAL	Parcel point box east edge.	34.780407

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

32k rows · 27 cols

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).

Primary key(id)Joinssettlement_code → settlements.codeid → streets_fts.rowid

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

Columns of streets
Column	Type	Description	Example
idnot null	INTEGER	Primary key, stable: settlement_code × 100000 + crc32(street_key) % 100000. → parcel_address.street_id, street_parcels.street_id, streets_fts.rowid.→ streets_fts.rowid	500040063
settlement_codenot null	INTEGER	Settlement → settlements.code.→ settlements.code	5000
settlement	TEXT	Settlement canonical name.	תל אביב-יפו
streetnot null	TEXT	Street display name (most common OSM spelling), Hebrew.	אבן גבירול
street_keynot null	TEXT	Normalised street key (UNIQUE per settlement).	אבנ גבירול
slugnot null	TEXT	URL slug, UNIQUE per settlement.	אבן-גבירול
n_parcels	INTEGER	Parcels on the street (address or nearby-street match).	216
n_parcels_with_deals	INTEGER	Of which parcels with deals.	180
n_parcels_addressed	INTEGER	Of which parcels with an OSM address on this street.	199
n_addresses	INTEGER	OSM address points on the street.	286
house_min	INTEGER	Lowest house number. NULL when no addresses.	1
house_max	INTEGER	Highest house number. NULL when no addresses.	502
lat	REAL	Median address point latitude (else parcel point).	32.084388
lon	REAL	Median address point longitude.	34.78182
bbox_min_lat	REAL	Parcel-point bbox south.	32.071287
bbox_min_lon	REAL	Parcel-point bbox west.	34.779922
bbox_max_lat	REAL	Parcel-point bbox north.	32.136711
bbox_max_lon	REAL	Parcel-point bbox east.	34.7955
gushim	TEXT	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_totalnot null	INTEGER	Deals on the street's parcels, all time (a corner parcel counts on each of its streets).	2730
deals_5ynot null	INTEGER	Deals in the 5y window.	335
deals_12mnot null	INTEGER	Deals in the 12m window.	39
n_ppsqm_5ynot null	INTEGER	Residential stat deals with ppsqm, 5y.	137
median_ppsqm_5y	INTEGER	Median residential ₪/m², 5y. NULL when n = 0.	60930
n_ppsqm_12mnot null	INTEGER	Residential stat deals with ppsqm, 12m.	18
median_ppsqm_12m	INTEGER	Median residential ₪/m², 12m.	57072
last_deal_date	TEXT	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

259k rows · 4 cols

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).

Primary key(street_id, gush, chelka)Joinsstreet_id → streets.idgush, chelka → parcels.(gush, chelka)

Columns of street_parcels
Column	Type	Description	Example
street_idnot null	INTEGER	Street → streets.id.→ streets.id	500040063
gushnot null	INTEGER	Block number.	6111
chelkanot null	INTEGER	Parcel number. Index (gush, chelka) lets you go parcel → streets.	69
kindnot null	TEXT	'address' or 'near' (a nearby street, not the parcel's address).`nearaddress`	address

Indexes

ix_street_parcels_parcel(gush, chelka)
primary key(street_id, gush, chelka)unique

parcel_address

246k rows · 16 cols

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.

Primary key(gush, chelka)Joinsgush, chelka → parcels.(gush, chelka)street_id → streets.idsettlement_code → settlements.code

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

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

parcel_buildings

227k rows · 5 cols

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

Primary key(gush, chelka)Joinsgush, chelka → parcels.(gush, chelka)

Columns of parcel_buildings
Column	Type	Description	Example
gushnot null	INTEGER	Block number.	6212
chelkanot null	INTEGER	Parcel number. PK (gush, chelka).	418
n_buildingsnot null	INTEGER	OSM buildings in the parcel.	1
max_levels	INTEGER	Max building:levels (sparse). NULL when unknown.	4
max_height_m	REAL	Max building height in metres (sparse). NULL when unknown.	2

Indexes

primary key(gush, chelka)unique

neighborhoods

1,277 rows · 47 cols

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.

Primary key(nbhd_id)Joinssettlement_code → settlements.codenbhd_id → neighborhoods_fts.rowid

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).

Columns of neighborhoods
Column	Type	Description	Example
nbhd_idnot null	INTEGER	Primary key, stable: settlement_code × 10000 + crc32(name) % 10000. → parcel_neighborhood.nbhd_id, agg_neighborhood_year.nbhd_id, neighborhoods_fts.rowid.→ neighborhoods_fts.rowid	50005953
settlement_codenot null	INTEGER	Settlement → settlements.code.→ settlements.code	5000
settlement	TEXT	Settlement name.	תל אביב-יפו
namenot null	TEXT	Neighbourhood name as published (Hebrew; some Arabic, 'שכונה N').	פלורנטין
slugnot null	TEXT	URL slug, UNIQUE per settlement.	פלורנטין
sourcenot null	TEXT	Polygon source: tlv_neighborhoods, jlm_neighborhoods, pt_neighborhoods, cbs_stat2011_nbhd, osm_polygon, osm_point_voronoi.`cbs_stat2011_nbhdjlm_neighborhoodsosm_point_voronoitlv_neighborhoodspt_neighborhoodsosm_polygon`	tlv_neighborhoods
area_km2	REAL	Area in km².	0.4476
lat	REAL	Label point latitude (pole of inaccessibility).	32.057525
lon	REAL	Label point longitude.	34.769864
bbox_min_lat	REAL	Polygon bbox south.	32.054745
bbox_min_lon	REAL	Polygon bbox west.	34.764037
bbox_max_lat	REAL	Polygon bbox north.	32.060676
bbox_max_lon	REAL	Polygon bbox east.	34.773932
n_parcelsnot null	INTEGER	DB parcels mapped to the neighbourhood.	608
deals_totalnot null	INTEGER	All deals (all property groups, all time) on the neighbourhood's parcels whose settlement equals the neighbourhood's settlement.	8001
deals_residential_totalnot null	INTEGER	Residential deals, all time.	5379
first_deal	TEXT	Date of the first deal, 'YYYY-MM-DD'. NULL when the neighbourhood has no deals.	1998-01-07
last_deal	TEXT	Date of the latest deal, 'YYYY-MM-DD' (may be after the stats anchor). NULL when no deals.	2026-07-21
deals_12mnot null	INTEGER	Deals (all groups) in the 12m window (meta.window_12m_start … window_12m_end = stats anchor).	117
deals_residential_12mnot null	INTEGER	Residential deals in the 12m window.	102
deals_prev12mnot null	INTEGER	Deals (all groups) in the prev12m window. Do not headline deals_12m vs deals_prev12m as an activity change (late reports).	204
n_stats_12mnot null	INTEGER	Residential stat deals (in_stats = 1) in 12m behind median_price_12m.	85
median_price_12m	INTEGER	Median residential deal price in ₪, 12m, stat rule. NULL when n_stats_12m = 0.	2900000
n_ppsqm_12mnot null	INTEGER	Residential stat deals with price_per_sqm in 12m behind median_ppsqm_12m.	84
median_ppsqm_12m	INTEGER	Median residential ₪/m², 12m (pooled new + existing stock, discount projects excluded). NULL when n = 0; gate on n_ppsqm_12m ≥ 5.	57143
n_ppsqm_prev12mnot null	INTEGER	Same as n_ppsqm_12m for the prev12m window.	89
median_ppsqm_prev12m	INTEGER	Median residential ₪/m², prev12m window (pooled).	54808
n_ppsqm_existing_12mnot null	INTEGER	Existing-stock (is_new_build = 0) stat deals with ppsqm, 12m.	45
median_ppsqm_existing_12m	INTEGER	Median ₪/m² of existing stock, 12m.	53125
n_ppsqm_existing_prev12mnot null	INTEGER	Existing-stock stat deals with ppsqm, prev12m.	62
median_ppsqm_existing_prev12m	INTEGER	Median ₪/m² of existing stock, prev12m (base of ppsqm_change_pct).	51953
n_ppsqm_new_12mnot null	INTEGER	New-build (is_new_build = 1) stat deals with ppsqm, 12m.	39
median_ppsqm_new_12m	INTEGER	Median ₪/m² of new builds, 12m. NULL when n = 0.	63627
ppsqm_change_pct	REAL	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	'existing' when ppsqm_change_pct is set, else NULL.`existing`	existing
n_ppsqm_5ynot null	INTEGER	Residential stat deals with ppsqm in the 5y window (2021-07-01 … 2026-06-30).	488
median_ppsqm_5y	INTEGER	Median residential ₪/m², 5y window — better coverage for quiet areas.	56392
median_price_5y	INTEGER	Median residential price in ₪, 5y window.	2950000
n_4rooms_12mnot null	INTEGER	Apartments with 4–4.5 rooms, stat rule, 12m.	8
median_price_4rooms_12m	INTEGER	Median price in ₪ of 4–4.5-room apartments, 12m.	5100000
n_discount_12mnot null	INTEGER	Discount-project deals (is_discount_project = 1) in 12m — NOT in the medians.	0
deals_after_anchornot null	INTEGER	Deals dated after meta.stats_anchor_date reported so far (incomplete months).	1
n_parcels_12mnot null	INTEGER	Distinct parcels behind the 12m ₪/m² median.	42
max_parcel_share_12m	REAL	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	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	How many neighbourhoods of the settlement were ranked. NULL when unranked.	29
ppsqm_vs_settlement_pct	REAL	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

289k rows · 5 cols

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

Primary key(gush, chelka)Joinsgush, chelka → parcels.(gush, chelka)nbhd_id → neighborhoods.nbhd_id

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

Columns of parcel_neighborhood
Column	Type	Description	Example
gushnot null	INTEGER	Block number.	6212
chelkanot null	INTEGER	Parcel number. PK (gush, chelka).	418
nbhd_idnot null	INTEGER	→ neighborhoods.nbhd_id (index (nbhd_id, gush, chelka) for neighbourhood → parcels).→ neighborhoods.nbhd_id	50000117
point_sourcenot null	TEXT	'db_parcel' (parcel point) or 'db_gush' (parcel sits at its gush centroid: approximate).`db_parceldb_gush`	db_parcel
same_settlementnot null	INTEGER	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)
primary key(gush, chelka)unique

Location metrics & POIs

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

loc_metrics_parcel

381k rows · 24 cols

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.

Primary key(gush, chelka)Joinsgush, chelka → parcels.(gush, chelka)rail_station_id → poi_stations.idlrt_station_id → poi_stations.idlrt_planned_station_id → poi_stations.id

Columns of loc_metrics_parcel
Column	Type	Description	Example
gushnot null	INTEGER	Block number.	6212
chelkanot null	INTEGER	Parcel number. PK (gush, chelka).	418
geo_precision	TEXT	Precision of the point the metrics were computed at: 'parcel', 'gush' (gush centroid) or 'settlement' (settlement centre) — same as parcels.geo_precision.`parcelgushsettlement`	parcel
dist_rail_m	INTEGER	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	Id of that rail station → poi_stations.id (e.g. 'rail:17108'); name in poi_stations.name.→ poi_stations.id	rail:17038
rail_weekday_trips	INTEGER	Trains stopping at that station on the representative weekday (both directions).	464
dist_lrt_m	INTEGER	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	Id of that LRT station → poi_stations.id (e.g. 'lrt:35962-35962').→ poi_stations.id	lrt:20711-20711
lrt_weekday_trips	INTEGER	LRT trips stopping at that station on the representative weekday.	619
dist_lrt_planned_m	INTEGER	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	Id of that planned station → poi_stations.id (e.g. 'lrt:planned136').→ poi_stations.id	lrt:planned078
bus_trips_500m	INTEGER	Weekday bus trips serving stops within 500 m (per route variant the max over its stops, summed).	2124
bus_lines_500m	INTEGER	Distinct bus lines with a served stop within 500 m.	16
bus_stops_500m	INTEGER	Served bus stops within 500 m.	15
transit_score	INTEGER	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	Schools within 1 km (kindergartens and institutions of unknown type excluded; placeholder-located institutions excluded).	11
elementary_1km	INTEGER	Elementary or combined schools within 1 km.	5
dist_elementary_m	INTEGER	Straight-line distance in metres to the nearest elementary / combined school.	214
kindergartens_500m	INTEGER	Kindergartens within 500 m.	6
dist_sea_m	INTEGER	Straight-line distance in metres to the OSM coastline (Mediterranean or Red Sea; lakes excluded).	993
sea	TEXT	Which coastline is nearest: 'mediterranean' or 'red_sea'.`mediterraneanred_sea`	mediterranean
elevation_m	INTEGER	Surface elevation in metres above the EGM2008 geoid (Copernicus GLO-30; includes roofs/trees in dense blocks).	13
noise_zone	TEXT	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).`נתב"ג: LDN 60-65נתב"ג: LDN 65-70נתב"ג: LDN 70-75נתב"ג: תחום רעש מטוסים (תמ"א 35)חיפה: תחום רעש מטוסים (תמ"א 35)מחניים (ראש פינה): תחום רעש מטוסים (תמ"א 35)קריית שמונה: תחום רעש מטוסים (תמ"א 35)נתב"ג: LDN 75+עין שמר: תחום רעש מטוסים (תמ"א 35)`	נתב"ג: LDN 60-65
noise_ldn_min	INTEGER	Lower bound of the Ben Gurion LDN band in dB: 60, 65, 70 or 75. NULL outside LDN bands.	60

Indexes

primary key(gush, chelka)unique

loc_metrics_gush

12k rows · 23 cols

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

Primary key(gush)Joinsgush → gushim.gushrail_station_id → poi_stations.idlrt_station_id → poi_stations.idlrt_planned_station_id → poi_stations.id

Columns of loc_metrics_gush
Column	Type	Description	Example
gushnot null	INTEGER	Primary key → gushim.gush.→ gushim.gush	6212
geo_source	TEXT	'gush' (metrics computed at the gush centroid).`gush`	gush
dist_rail_m	INTEGER	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	Id of that rail station → poi_stations.id (e.g. 'rail:17108'); name in poi_stations.name.→ poi_stations.id	rail:17038
rail_weekday_trips	INTEGER	Trains stopping at that station on the representative weekday (both directions).	464
dist_lrt_m	INTEGER	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	Id of that LRT station → poi_stations.id (e.g. 'lrt:35962-35962').→ poi_stations.id	lrt:20711-20711
lrt_weekday_trips	INTEGER	LRT trips stopping at that station on the representative weekday.	619
dist_lrt_planned_m	INTEGER	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	Id of that planned station → poi_stations.id (e.g. 'lrt:planned136').→ poi_stations.id	lrt:planned078
bus_trips_500m	INTEGER	Weekday bus trips serving stops within 500 m (per route variant the max over its stops, summed).	2892
bus_lines_500m	INTEGER	Distinct bus lines with a served stop within 500 m.	20
bus_stops_500m	INTEGER	Served bus stops within 500 m.	15
transit_score	INTEGER	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	Schools within 1 km (kindergartens and institutions of unknown type excluded; placeholder-located institutions excluded).	11
elementary_1km	INTEGER	Elementary or combined schools within 1 km.	6
dist_elementary_m	INTEGER	Straight-line distance in metres to the nearest elementary / combined school.	183
kindergartens_500m	INTEGER	Kindergartens within 500 m.	5
dist_sea_m	INTEGER	Straight-line distance in metres to the OSM coastline (Mediterranean or Red Sea; lakes excluded).	1414
sea	TEXT	Which coastline is nearest: 'mediterranean' or 'red_sea'.`mediterraneanred_sea`	mediterranean
elevation_m	INTEGER	Surface elevation in metres above the EGM2008 geoid (Copernicus GLO-30; includes roofs/trees in dense blocks).	10
noise_zone	TEXT	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).`נתב"ג: LDN 60-65נתב"ג: LDN 65-70נתב"ג: LDN 70-75חיפה: תחום רעש מטוסים (תמ"א 35)נתב"ג: LDN 75+נתב"ג: תחום רעש מטוסים (תמ"א 35)קריית שמונה: תחום רעש מטוסים (תמ"א 35)מחניים (ראש פינה): תחום רעש מטוסים (תמ"א 35)עין שמר: תחום רעש מטוסים (תמ"א 35)`	נתב"ג: LDN 60-65
noise_ldn_min	INTEGER	Lower bound of the Ben Gurion LDN band in dB: 60, 65, 70 or 75. NULL outside LDN bands.	60

poi_stations

371 rows · 13 cols

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

Primary key(id)

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

Indexes

ix_poi_stations_kind(kind, status)
primary key(id)unique
unique constraint(feature_id)unique

poi_schools

28k rows · 19 cols

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

Primary key(id)Joinssettlement_code → settlements.code

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

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

Indexes

ix_poi_schools_settlement(settlement_code, kind)

poi_bus_stops

35k rows · 10 cols

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_*.

Primary key(stop_id)

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

Indexes

primary key(stop_id)unique

Socio-economic & crime

CBS socio-economic clusters and Israel Police crime files.

settlement_socio

1,110 rows · 13 cols

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

Primary key(settlement_code)Joinssettlement_code → settlements.code

Columns of settlement_socio
Column	Type	Description	Example
settlement_codenot null	INTEGER	Primary key → settlements.code.→ settlements.code	5000
name	TEXT	Name as published by CBS.	תל אביב-יפו
level	TEXT	'local_authority', 'rc_locality' (locality inside a regional council) or 'regional_council_fallback' (13 localities carrying their council's value).`rc_localitylocal_authorityregional_council_fallback`	local_authority
cluster	INTEGER	Socio-economic cluster 1 (lowest) … 10 (highest).	8
index_value	REAL	Standardised index value (higher = better-off).	1.2925
rank	INTEGER	Rank within rank_of (1 = lowest).	227
rank_of	INTEGER	Size of the ranking the rank belongs to (255 local authorities or 996 rc localities).	255
population	INTEGER	Population used by CBS.	466862
cluster_2019	INTEGER	Cluster in the 2019 release.	8
regional_council	TEXT	Regional council name (rc localities). NULL otherwise.	לכיש
rc_cluster	INTEGER	The regional council's own cluster. NULL otherwise.	7
year	INTEGER	Index year (2021).	2021
source	TEXT	CBS table reference.`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

1,641 rows · 11 cols

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

Primary key(stat_area_id)Joinssettlement_code → settlements.code

Columns of stat_area_socio
Column	Type	Description	Example
stat_area_idnot null	INTEGER	Primary key = settlement_code × 10000 + stat_area (CBS 2011 geography).	50000113
settlement_code	INTEGER	→ settlements.code.→ settlements.code	5000
settlement_name	TEXT	Settlement name.	תל אביב -יפו
stat_area	INTEGER	Statistical area number within the settlement.	113
population	INTEGER	Population of the area.	12519
index_value	REAL	Index value.	1.9222
rank	INTEGER	Rank among all 1,641 areas (1 = lowest).	1566
rank_of	INTEGER	1641.	1641
cluster	INTEGER	Cluster 1 … 10.	9
year	INTEGER	2021.	2021
source	TEXT	CBS table reference.`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

234k rows · 6 cols

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

Primary key(gush, chelka)Joinsgush, chelka → parcels.(gush, chelka)stat_area_id → stat_area_socio.stat_area_id

Columns of parcel_stat_area_socio
Column	Type	Description	Example
gushnot null	INTEGER	Block number.	6212
chelkanot null	INTEGER	Parcel number. PK (gush, chelka).	418
stat_area_idnot null	INTEGER	→ stat_area_socio.stat_area_id.→ stat_area_socio.stat_area_id	50000314
stat_area_settlement_code	INTEGER	Settlement of the statistical area (differs from the parcel's for ~1.7%: boundary points).	5000
cluster	INTEGER	Cluster 1 … 10.	9
index_value	REAL	Index value.	1.984

Indexes

ix_parcel_stat_area_socio_area(stat_area_id)
primary key(gush, chelka)unique

crime_settlement_year

1,235 rows · 25 cols

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

Primary key(settlement_code, year)Joinssettlement_code → settlements.code

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

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

Indexes

ix_crime_settlement_year_year(year, settlement_code)
primary key(settlement_code, year)unique

crime_station_year

448 rows · 7 cols

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

Primary key(police_station_code, year)

Columns of crime_station_year
Column	Type	Description	Example
police_station_codenot null	INTEGER	Police station code (0 = national total row).	31311000
yearnot null	INTEGER	Year.	2023
police_station	TEXT	Police station name (Hebrew).	תחנת באר שבע נגב
cases	INTEGER	Case files.	14431
offenses	INTEGER	Offense records.	19147
cases_without_settlement	INTEGER	Cases not attributed to a settlement.	652
settlement_codes	TEXT	JSON array of settlement codes served by the station.	[9000]

Indexes

primary key(police_station_code, year)unique

Macro, rents & yields

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

macro_month

345 rows · 16 cols

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.

Primary key(month)Joinsmonth → agg_national_month.month

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.

Columns of macro_month
Column	Type	Description	Example
monthnot null	TEXT	Primary key 'YYYY-MM'.→ agg_national_month.month	2026-06
cpi	REAL	Consumer price index, chained, 2024 average = 100.	104.8
real_factor	REAL	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	CBS dwelling price index (1993 = 100).	594.8
new_dwellings_index	REAL	CBS new-dwellings price index (from 2017-10). NULL before.	541.2
rent_index	REAL	CPI rent component (2024 = 100).	106.4
owner_housing_index	REAL	CPI owner-occupied housing component (2024 = 100).	107.8
boi_rate	REAL	Bank of Israel policy rate, % (end of month).	3.75
mortgage_rate_unlinked	REAL	Average rate on new non-indexed fixed-rate housing loans, % (from 2011-07).	4.73
mortgage_rate_linked	REAL	Average rate on new CPI-indexed housing loans, % real (from 2011-07).	3.28
mortgage_rate_linked_fixed	REAL	Average rate on new CPI-indexed fixed-rate housing loans, % real (from 2011-07).	3.27
mortgage_rate_variable_unlinked	REAL	Average rate on new non-indexed variable-rate (prime-track) housing loans, % — 2016-01 … 2024-01 only.	1.57
prime_rate	REAL	Prime rate = boi_rate + 1.5, %.	5.25
avg_wage	REAL	Average monthly wage per employee post, nominal ₪, seasonally adjusted.	14275.4
avg_wage_israeli	REAL	Same for Israeli employees only.	14605.1
cpi_base_note	TEXT	How the CPI series was chained.`chained by CBS's linking rule to the latest base '2024 ממוצע' = 100`	chained by CBS's linking rule to the latest base '2024 ממוצע' = 100

Indexes

primary key(month)unique

macro_district_month

630 rows · 6 cols

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

Primary key(district, month)Joinsdistrict → districts.name

Columns of macro_district_month
Column	Type	Description	Example
districtnot null	TEXT	District (CBS form) → districts.name.`הדרוםהמרכזהצפוןחיפהירושליםתל אביב`→ districts.name	תל אביב
monthnot null	TEXT	'YYYY-MM' (first month of the CBS two-month window).	2026-06
housing_price_index	REAL	District dwelling price index (linked to the national scale).	563.2
pct_change_month	REAL	Change vs previous period, %.	0.7
national_index	REAL	National index for the same month.	594.8
cbs_code	INTEGER	CBS series code.	60400

Indexes

primary key(district, month)unique

rent_city

4,354 rows · 14 cols

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.

Primary key(area_type, area_name, rooms_bucket, period)Joinssettlement_code → settlements.code

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.

Columns of rent_city
Column	Type	Description	Example
area_typenot null	TEXT	'national', 'district' or 'city'.`citydistrictnational`	city
area_namenot null	TEXT	Area name (Hebrew).	תל אביב
rooms_bucketnot null	TEXT	Rent rooms bucket: '1-2', '2.5-3', '3.5-4', '4.5-6', '4.5+' (2026), 'all'.`all3.5-42.5-31-24.5-64.5+`	all
periodnot null	TEXT	'YYYY-Qn' for quarters or 'YYYY' for years.	2026-Q2
period_typenot null	TEXT	'quarter' or 'year'.`quarteryear`	quarter
year	INTEGER	Year of the period.	2026
settlement_code	INTEGER	City code → settlements.code (city rows only).→ settlements.code	5000
district	TEXT	District name (district rows).`תל אביבירושליםחיפההצפוןהמרכזהדרום`	הדרום
district_code	INTEGER	CBS district code (district rows).	6
avg_rent	REAL	Average monthly rent in ₪.	7421.4
n	INTEGER	Sample size (2019–2021 only).	70
sampling_error	REAL	Sampling error, ₪ or %.	59.5
source	TEXT	CBS table reference.`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	Source publication URL.`https://www.cbs.gov.il/he/publications/Madad/DocLib/2026/price08a/a4_9_h.xlsxhttps://www.cbs.gov.il/he/publications/Madad/DocLib/2024/price12a/a4_9_h.xlshttps://www.cbs.gov.il/he/publications/Madad/DocLib/2022/price12a/a4_9_h.xlshttps://www.cbs.gov.il/he/publications/Madad/DocLib/2023/price12a/a4_9_h.xlshttps://www.cbs.gov.il/he/publications/Madad/DocLib/2021/price12a/a4_9_h.xlshttps://www.cbs.gov.il/he/publications/Madad/DocLib/2020/price12a/a4_9_h.xlshttps://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)
primary key(area_type, area_name, rooms_bucket, period)unique

gross_yield

922 rows · 17 cols

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.

Primary key(level, area_name, window, rooms_bucket)Joinssettlement_code → settlements.code

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

Columns of gross_yield
Column	Type	Description	Example
levelnot null	TEXT	'city', 'district' or 'national'.`citydistrictnational`	city
area_namenot null	TEXT	Area name (Hebrew).	תל אביב
windownot null	TEXT	'12m' (to the stats anchor) or a year '2019' … '2025'.`20252024202312m2022202120202019`	12m
rooms_bucketnot null	TEXT	Rent rooms bucket ('1-2', '2.5-3', '3.5-4', '4.5-6', 'all').`all3.5-42.5-34.5-61-2`	all
settlement_code	INTEGER	City code (city rows) → settlements.code.→ settlements.code	5000
district	TEXT	District (district rows).`תל אביבירושליםחיפההצפוןהמרכזהדרום`	הדרום
window_start	TEXT	Window start 'YYYY-MM-DD'.`2025-07-012025-01-012024-01-012023-01-012022-01-012021-01-012020-01-012019-01-01`	2025-07-01
window_end	TEXT	Window end 'YYYY-MM-DD'.`2026-06-302025-12-312024-12-312023-12-312022-12-312021-12-312020-12-312019-12-31`	2026-06-30
n_deals	INTEGER	Existing-stock in-stats apartment deals behind median_price.	1693
median_price	INTEGER	Median price in ₪.	3720000
avg_rent_monthly	REAL	Average monthly rent in ₪ over rent_periods.	7329
rent_periods	TEXT	Comma-separated rent quarters averaged.`2025-Q3,2025-Q4,2026-Q1,2026-Q22025-Q1,2025-Q2,2025-Q3,2025-Q42024-Q1,2024-Q2,2024-Q3,2024-Q42023-Q1,2023-Q2,2023-Q3,2023-Q42022-Q1,2022-Q2,2022-Q3,2022-Q42021-Q1,2021-Q2,2021-Q3,2021-Q42020-Q1,2020-Q2,2020-Q3,2020-Q42019-Q1,2019-Q2,2019-Q3,2019-Q4`	2025-Q3,2025-Q4,2026-Q1,2026-Q2
annual_rent	INTEGER	12 × avg_rent_monthly, ₪.	87943
price_to_rent	REAL	median_price / annual_rent.	42.3
gross_yield_pct	REAL	annual_rent / median_price × 100, percent (e.g. 3.07).	2.36
low_n	INTEGER	1 when n_deals < 20.	0
rent_note	TEXT	Note about rent bucket changes. NULL usually.`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)
primary key(level, area_name, window, rooms_bucket)unique

affordability

237 rows · 11 cols

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

Primary key(level, area, period_kind, period, stock)

Columns of affordability
Column	Type	Description	Example
levelnot null	TEXT	'national' or 'district'.`districtnational`	national
areanot null	TEXT	'ישראל' or the district name.`תל אביבישראלירושליםחיפההצפוןהמרכזהדרוםיהודה והשומרון`	ישראל
period_kindnot null	TEXT	'year' or '12m'.`year12m`	12m
periodnot null	TEXT	The year ('2025') or the 12m range ('2025-07-01..2026-06-30').	2025-07-01..2026-06-30
stocknot null	TEXT	'all' or 'existing' (existing stock only).`allexisting`	all
median_price_apartment	INTEGER	Median apartment price in ₪.	2150000
n_deals	INTEGER	Deals behind the median.	56681
avg_monthly_wage	REAL	National average monthly wage in ₪.	13987
months_of_wage	REAL	median_price_apartment / avg_monthly_wage.	153.7
years_of_wage	REAL	months_of_wage / 12.	12.81
wage_scope	TEXT	'national' (no district wages exist).`national`	national

Indexes

primary key(level, area, period_kind, period, stock)unique

Urban renewal & discount projects

Declared renewal compounds and subsidised lottery projects.

renewal_compounds

978 rows · 30 cols

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

Primary key(compound_id)Joinssettlement_code → settlements.code

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

Indexes

ix_renewal_compounds_settlement(settlement_code, status_rank)

parcel_renewal

26k rows · 6 cols

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

Primary key(gush, chelka)Joinsgush, chelka → parcels.(gush, chelka)compound_id → renewal_compounds.compound_id

Columns of parcel_renewal
Column	Type	Description	Example
gushnot null	INTEGER	Block number.	310
chelkanot null	INTEGER	Parcel number. PK (gush, chelka).	17
compound_idnot null	INTEGER	→ renewal_compounds.compound_id.→ renewal_compounds.compound_id	8002186
overlap_share	REAL	Share of the parcel polygon inside the compound (> 0.5). NULL for point matches.	1
n_compounds	INTEGER	Number of compounds the parcel touches.	1
method	TEXT	'polygon_overlap', 'centroid_shuma' or 'centroid_cancelled'.`polygon_overlapcentroid_shumacentroid_cancelled`	polygon_overlap

Indexes

ix_parcel_renewal_compound(compound_id)
primary key(gush, chelka)unique

gush_renewal

953 rows · 10 cols

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

Primary key(gush)Joinsgush → gushim.gush

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

discount_projects

2,087 rows · 37 cols

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.

Primary key(project_id)Joinssettlement_code → settlements.codegush → gushim.gush

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

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

discount_project_parcels

7,275 rows · 4 cols

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

Joinsproject_id → discount_projects.project_idgush, chelka → parcels.(gush, chelka)

Columns of discount_project_parcels
Column	Type	Description	Example
project_idnot null	TEXT	→ discount_projects.project_id.→ discount_projects.project_id	moch:50845
gushnot null	INTEGER	Block number.	187
chelka	INTEGER	Parcel number; NULL when only the gush is known.	12
source	TEXT	'ila_tender', 'moch_gis_overlap' or both.`ila_tendermoch_gis_overlapila_tender+moch_gis_overlap`	ila_tender

Indexes

ix_discount_project_parcels_parcel(gush, chelka)
ix_discount_project_parcels_project(project_id)

discount_match

8,157 rows · 16 cols

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

Joinsgush → gushim.gushofficial_project_id → discount_projects.project_id

Columns of discount_match
Column	Type	Description	Example
gushnot null	INTEGER	Block number.→ gushim.gush	187
deal_yearnot null	INTEGER	Deal year.	2021
official_project_id	TEXT	→ discount_projects.project_id. NULL for flagged_no_located_project rows.→ discount_projects.project_id	moch:50845
component	TEXT	Group of projects sharing gushim (attribution unit).	2767
n_flagged	INTEGER	Deals flagged is_discount_project.	109
n_unflagged_newbuild	INTEGER	Unflagged new-build deals.	21
n_unflagged_near_official_price	INTEGER	Unflagged deals priced near the official ₪/m².	0
n_other_residential	INTEGER	Other residential deals.	8
n_in_project_parcels	INTEGER	Deals on the project's own parcels.	107
n_unflagged_near_price_in_parcels	INTEGER	Unflagged near-price deals on the project's parcels.	0
median_ppsqm_flagged	REAL	Median ₪/m² of flagged deals.	11774
median_ppsqm_unflagged_new	REAL	Median ₪/m² of unflagged new builds.	15899
official_price_per_sqm	REAL	Official project ₪/m².	8720
match_kind	TEXT	'official_project' or 'flagged_no_located_project'.`official_projectflagged_no_located_project`	official_project
settlement_code	INTEGER	Settlement (flagged_no_located_project rows).	1034
settlement_has_official_project	INTEGER	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

9 rows · 7 cols

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

Primary key(key)

Columns of property_groups
Column	Type	Description	Example
keynot null	TEXT	Primary key: apartment, garden_apartment, penthouse, house, land, commercial, agriculture, parking, other.`agricultureapartmentcommercialgarden_apartmenthouselandotherparkingpenthouse`	apartment
label_henot null	TEXT	Hebrew singular label, e.g. 'דירה'.`קרקענכס מסחרינכס חקלאיחניה / מחסןדירת גןדירת גגדירהבית פרטיאחר`	דירה
label_plural_henot null	TEXT	Hebrew plural label, e.g. 'דירות'.`קרקעות ומגרשיםעסקאות אחרותנכסים מסחרייםחקלאות ונחלותחניות ומחסניםדירות גןדירות גג ופנטהאוזיםדירותבתים פרטיים וקוטג'ים`	דירות
is_residentialnot null	INTEGER	1 for apartment, garden_apartment, penthouse, house.	1
sortnot null	INTEGER	Display order (1 = apartment).	1
iconnot null	TEXT	lucide icon name used by the site (kebab-case).`tractorstoresquare-parkingshapesland-plothouseflower-2crownbuilding-2`	building-2
dealsnot null	INTEGER	Number of deals in the group.	2261290

Indexes

primary key(key)unique

deal_natures

47 rows · 3 cols

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

Primary key(raw)Joinsproperty_group → property_groups.key

Columns of deal_natures
Column	Type	Description	Example
rawnot null	TEXT	Primary key: raw Hebrew deal nature as in deals.deal_nature, e.g. 'דירה בבית קומות'.	דירה בבית קומות
property_groupnot null	TEXT	Property group it maps to → property_groups.key.`commercialotherlandagriculturehouseparkingapartmentpenthousegarden_apartment`→ property_groups.key	apartment
dealsnot null	INTEGER	Number of deals with this raw value.	1946146

Indexes

primary key(raw)unique

Run SQL in your browser#

Same endpoint, same limits. Press Ctrl + Enter to run; the URL updates with ?sql= so you can share a query.

SQL console

Ctrl+Enter

Limit302 / 20,000 chars

Write a query and press Run

Read-only · one statement · 10 s timeout · up to 10,000 rows. Try a preset, or pick an example below and press “Run in console”.

06 · Examples

100 example queries#

Real questions an agent might get, each with SQL that follows the rules and an explanation of why it is written that way. Every query was run against the current database.

100 examples

Basics & metadata8

List every table and view

רשימת כל הטבלאות והתצוגות

“Which tables and views exist in the database?”

example-1.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.

Show the CREATE statement of a table

הצגת הגדרת טבלה (CREATE)

“What is the exact DDL of the deals and settlements tables?”

example-2.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.

List the columns of a table

רשימת העמודות של טבלה

“What columns does the neighborhoods table have, with their types?”

example-3.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.

List the indexes of the deals table

רשימת האינדקסים של טבלת העסקאות

“Which indexes exist on deals, so I can write queries that use them?”

example-4.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.

Stats anchor date and time windows

תאריך העוגן וחלונות הזמן

“What date do the statistics run to, and what are the 12-month, 24-month and 5-year windows?”

example-5.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.

Property groups and their deal counts

סוגי נכסים ומספר העסקאות

“What property types are there, which are residential, and how many deals does each have?”

example-6.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).

Row counts of the main tables

מספר השורות בטבלאות העיקריות

“How many rows does each main table have?”

example-7.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.

Data sources and licences

מקורות המידע והרישיונות

“Where does the enrichment data come from and under what licence?”

example-8.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.

Settlements & cities7

Settlement headline numbers

נתוני מפתח של יישוב

“What are the key price statistics for Haifa over the last 12 months?”

example-9.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.

#10

Most expensive settlements per m²

היישובים היקרים ביותר למ״ר

“Which cities have the highest price per square metre?”

example-10.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.

#11

Biggest price risers and fallers

העליות והירידות הגדולות במחיר

“Which cities saw the largest change in price per m² over the last year?”

example-11.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.

#12

District overview

סקירת מחוזות

“How do the seven districts compare on price and activity?”

example-12.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.

#13

Busiest settlements in the last 12 months

היישובים הפעילים ביותר ב-12 החודשים האחרונים

“Where were the most real-estate deals in the last 12 months?”

example-13.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.

#14

Cities of a district ranked by price

דירוג ערי מחוז לפי מחיר

“Rank the settlements of the Central district by median price per m².”

example-14.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.

#15

New-build premium by city

הפרמיה על דירות חדשות לפי עיר

“How much more do new apartments cost per m² than existing ones, by city?”

example-15.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.

Trends over time8

#16

National apartment prices by year

מחירי דירות ארציים לפי שנה

“How has the national median apartment price changed since 1998?”

example-16.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.

#17

Quarterly price series of a city

סדרת מחירים רבעונית של עיר

“Show the quarterly median price per m² of apartments in Tel Aviv since 2015.”

example-17.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.

#18

Compare cities side by side

השוואת ערים זו לצד זו

“Compare the yearly apartment price per m² of Tel Aviv, Jerusalem and Haifa since 2010.”

example-18.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).

#19

National monthly deal volume

היקף עסקאות ארצי חודשי

“How many deals and how much money changed hands each month over the last three years?”

example-19.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.

#20

Apartment prices by number of rooms

מחירי דירות לפי מספר חדרים

“What did apartments cost nationally in 2025 by number of rooms?”

example-20.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.

#21

District price trend by quarter

מגמת מחירים רבעונית לפי מחוז

“How has the residential price per m² moved in each district quarter by quarter since 2023?”

example-21.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.

#22

Seasonality of deals by calendar month

עונתיות העסקאות לפי חודש

“Which months of the year have the most real-estate deals?”

example-22.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.

#23

Biggest 10-year price risers

העליות הגדולות בעשור

“Which cities had the biggest rise in apartment price per m² from 2015 to 2025?”

example-23.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.

Deal lists9

#24

Latest deals nationwide

העסקאות האחרונות בארץ

“What are the 25 most recent deals in the registry?”

example-24.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.

#25

Latest deals in a city

העסקאות האחרונות בעיר

“Show the 50 newest deals in Tel Aviv.”

example-25.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.

#26

Filtered deal search

חיפוש עסקאות עם מסננים

“Find apartments in Tel Aviv with 3 to 4.5 rooms that sold for 2-4 million ILS in the last 12 months.”

example-26.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.

#27

Keyset pagination of a deal list

דפדוף בעסקאות לפי מפתח (keyset)

“How do I get the next page of Jerusalem deals after the last row I already have?”

example-27.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).

#28

Most expensive homes of the last 12 months

הדירות היקרות ביותר ב-12 החודשים האחרונים

“What were the most expensive home sales in Israel in the 12 months to June 2026?”

example-28.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.

#29

Highest price per m² in a city

המחיר הגבוה ביותר למ״ר בעיר

“Which Jerusalem homes sold at the highest price per m² in 2025?”

example-29.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.

#30

Cheapest 4-room apartments in a city

דירות 4 חדרים הזולות ביותר בעיר

“What were the cheapest 4-room apartments sold in Haifa in the last 12 months?”

example-30.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'.

#31

Deal types in a city

סוגי העסקאות בעיר

“What kinds of properties were sold in Haifa in the last 12 months?”

example-31.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.

#32

New-build sales in a city

מכירות דירות חדשות בעיר

“Which new-build apartments sold in Netanya most recently?”

example-32.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.

Parcels, gushim & geo14

#33

Deal history of a parcel

היסטוריית העסקאות בחלקה

“What deals were recorded at gush 7104, chelka 289 (a big Tel Aviv building)?”

example-33.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.

#34

Parcel summary with address

תקציר חלקה כולל כתובת

“Summarise parcel 7104/289: location, activity, prices and street address.”

example-34.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.

#35

Yearly price series of a gush

סדרת מחירים שנתית של גוש

“How has the price per m² in Jerusalem gush 30435 evolved year by year?”

example-35.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.

#36

Gushim of a city ranked by price

גושי העיר מדורגים לפי מחיר

“Which blocks (gushim) in Haifa are the most expensive, and how did they change?”

example-36.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.

#37

Repeat sales of the same unit

מכירות חוזרות של אותה יחידה

“Which apartments in gush 6212 were sold more than once, and how much did their price grow per year?”

example-37.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.

#38

Last sale of every unit in a building

המכירה האחרונה של כל יחידה בבניין

“For each apartment unit in parcel 7104/289, what was its most recent sale?”

example-38.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.

#39

Parcel vs its gush vs its city

חלקה מול הגוש ומול העיר

“Is parcel 6213/1471 in Tel Aviv expensive compared with its block and the city?”

example-39.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.

#40

Parcels inside a map bounding box

חלקות בתוך מלבן במפה

“Which parcels with deals are inside a small box in central Tel Aviv?”

example-40.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.

#41

Deals inside a bounding box

עסקאות בתוך מלבן במפה

“List the residential deals of the last 12 months inside a box in central Tel Aviv.”

example-41.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.

#42

Nearest parcels to a point

החלקות הקרובות לנקודה

“What are the 20 nearest parcels with deals to Dizengoff Center (32.0753, 34.7748), and how far are they?”

example-42.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.

#43

Median price within a radius

מחיר חציוני ברדיוס מנקודה

“What is the median price per m² of homes sold within 1 km of Dizengoff Center in the last 5 years?”

example-43.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.

#44

Deals near a train station

עסקאות ליד תחנת רכבת

“How much did homes sell for within 700 m of Tel Aviv Savidor station in the last 12 months?”

example-44.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.

#45

Which neighbourhood is a point in?

באיזו שכונה נמצאת נקודה?

“Which settlement and neighbourhood are at coordinates 31.7730, 35.2120?”

example-45.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.

#46

Settlements within a radius

יישובים ברדיוס מנקודה

“Which settlements lie within 15 km of Modiin, and what do homes cost there?”

example-46.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.

Name search (FTS5)6

#47

Search settlements by name prefix

חיפוש יישוב לפי תחילת השם

“Which settlements match "באר" (e.g. Be'er Sheva)?”

example-47.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.

#48

Search settlements by abbreviation

חיפוש יישוב לפי ראשי תיבות

“Which settlement does the abbreviation ת"א refer to?”

example-48.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.

#49

Search settlements in English

חיפוש יישוב באנגלית

“Find the settlement called "haifa" in English.”

example-49.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.

#50

Search streets by name

חיפוש רחוב לפי שם

“Which streets called Herzl (הרצל) exist, and where are they busiest?”

example-50.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 הרצלייה.

#51

Search a street within a city

חיפוש רחוב בתוך עיר

“Find Rothschild street in Tel Aviv (query "רוטשילד ת"א").”

example-51.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 (תא, בש, ...).

#52

Search neighbourhoods by name

חיפוש שכונה לפי שם

“Find the neighbourhood Florentin (פלורנטין) and its prices.”

example-52.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.

Neighbourhoods & streets7

#53

Neighbourhoods of a city by price

שכונות העיר לפי מחיר

“Rank the neighbourhoods of Tel Aviv by median price per m².”

example-53.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.

#54

Yearly series of a neighbourhood

סדרה שנתית של שכונה

“How have prices in Rehavia (Jerusalem) evolved year by year?”

example-54.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.

#55

Neighbourhoods with the biggest price change

השכונות עם השינוי הגדול במחיר

“Which neighbourhoods in Israel had the largest existing-stock price rise over the last year?”

example-55.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.

#56

Latest deals in a neighbourhood

העסקאות האחרונות בשכונה

“Show the 50 latest deals in Florentin, Tel Aviv.”

example-56.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.

#57

Most expensive streets of a city

הרחובות היקרים בעיר

“Which streets in Tel Aviv have the highest median price per m² over 5 years?”

example-57.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.

#58

Deals on a street

עסקאות ברחוב

“What sold on Rothschild street in Tel Aviv recently, with house numbers?”

example-58.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.

#59

Neighbourhood premium vs its city

פרמיית השכונה ביחס לעיר

“Which Jerusalem neighbourhoods are most above or below the city's price per m²?”

example-59.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.

Location metrics8

#60

Price vs distance to a train station

מחיר מול מרחק מתחנת רכבת

“In Netanya, do homes closer to a train station sell for more per m²?”

example-60.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.

#61

Price vs distance to the sea

מחיר מול מרחק מהים

“How much more do apartments near the beach cost in Bat Yam?”

example-61.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.

#62

Price vs elementary schools nearby

מחיר מול מספר בתי ספר יסודיים בקרבת מקום

“In Jerusalem, how does price per m² vary with the number of elementary schools within 1 km?”

example-62.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.

#63

Homes inside airport noise zones

דירות באזורי רעש מטוסים

“Which settlements have home sales inside airport noise zones, and at what prices compared with the rest of the town?”

example-63.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).

#64

Price by socio-economic cluster

מחיר לפי אשכול חברתי-כלכלי

“How does the price per m² vary with the CBS socio-economic cluster of the settlement?”

example-64.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.

#65

Crime rate vs price in big cities

שיעור הפשיעה מול מחירים בערים הגדולות

“For cities over 100k residents, how does the 2025 crime rate compare with home prices?”

example-65.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.

#66

Price vs public-transport score

מחיר מול ציון תחבורה ציבורית

“In Jerusalem, do homes with better public transport sell for more?”

example-66.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.

#67

Parcel quality-of-life panel

לוח איכות חיים לחלקה

“For parcel 6212/418 in Tel Aviv, what is its address, neighbourhood, transit, schools, sea distance and socio-economic level?”

example-67.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.

Market, rents & macro8

#68

National prices in today's shekels

מחירים ארציים בשקלים של היום

“What was the national median apartment price each year in June-2026 shekels?”

example-68.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.

#69

Monthly real vs nominal price

מחיר ריאלי מול נומינלי לפי חודש

“Show the monthly national residential price per m², nominal and inflation-adjusted, since 2016.”

example-69.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.

#70

Mortgage rates vs prices and activity

ריבית משכנתאות מול מחירים ופעילות

“How did mortgage rates relate to apartment prices and deal volume year by year?”

example-70.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.

#71

Rent series of a city

סדרת שכר דירה של עיר

“How has average rent in Tel Aviv changed by quarter and apartment size?”

example-71.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.

#72

Gross rental yield by city

תשואה ברוטו משכירות לפי עיר

“Which big cities have the highest gross rental yield?”

example-72.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.

#73

Affordability: years of wages per apartment

נגישות לדיור: שנות שכר לדירה

“How many years of the average wage does a median apartment cost, over time?”

example-73.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.

#74

CBS price index vs registry medians

מדד מחירי הדירות של הלמ״ס מול החציון במרשם

“Does the CBS dwelling price index move like the registry median? Compare both, rebased to 2015 = 100.”

example-74.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.

#75

A parcel's deal history in today's shekels

היסטוריית עסקאות בחלקה בשקלים של היום

“Show the home sales in parcel 7016/25 (Tel Aviv) since 1998 with their prices converted to June-2026 shekels.”

example-75.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.

Renewal & discount projects6

#76

Urban renewal compounds in a city

מתחמי התחדשות עירונית בעיר

“What declared urban-renewal compounds exist in Bat Yam, and how advanced are they?”

example-76.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.

#77

Largest urban renewal compounds

מתחמי ההתחדשות הגדולים ביותר

“Which urban renewal compounds in Israel will add the most housing units?”

example-77.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.

#78

Prices on renewal parcels vs elsewhere

מחירים בחלקות התחדשות מול שאר העיר

“In Bat Yam, do existing apartments on urban-renewal parcels sell at a premium?”

example-78.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.

#79

Discount lottery projects in a city

פרויקטים של דירה בהנחה בעיר

“Which subsidised lottery projects (מחיר למשתכן / דירה בהנחה) were in Beit Shemesh?”

example-79.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.

#80

Discount projects vs the market price

פרויקטי הנחה מול מחיר השוק

“How far below the market were the biggest discount-lottery projects sold?”

example-80.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.

#81

Deals of one discount project

העסקאות של פרויקט הנחה אחד

“Show the registry deals attributed to discount project moch:52 (מחיר למשתכן in Ramla).”

example-81.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.

Data quality & flags7

#82

Why rows are excluded from statistics

מדוע שורות לא נכנסות לסטטיסטיקה

“Of all Tel Aviv apartment deals in 2025, how many enter the price statistics, and why are the others excluded?”

example-82.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.

#83

Outlier reasons

סיבות לסימון חריגים

“Which outlier rules fired most often in 2025?”

example-83.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.

#84

Location precision of a city's deals

דיוק המיקום של עסקאות העיר

“How precise are the map locations of Haifa's deals?”

example-84.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.

#85

Reporting lag in recent months

פיגור בדיווח בחודשים האחרונים

“Why do the statistics stop at June 2026? Show how complete each recent month is.”

example-85.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.

#86

Discount-project flags in a city

סימוני פרויקטי הנחה בעיר

“How many Beer Sheva deals in the last 12 months were flagged as discount-lottery units, and on what basis?”

example-86.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.

#87

Where the headline median misleads

היכן החציון הכללי מטעה

“In which cities does the headline median differ by more than 10% from the existing-stock median?”

example-87.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.

#88

Partial-share deals

עסקאות של חלק מנכס

“How common are partial-share sales in Tel Aviv in 2025, and at what shares?”

example-88.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.

Custom statistics7

#89

Year-over-year change with LAG()

שינוי שנתי עם LAG()

“What was the year-over-year change of Jerusalem's apartment price per m²?”

example-89.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.

#90

Top 3 cities per district with RANK()

שלוש הערים המובילות בכל מחוז עם RANK()

“What are the three most expensive cities in each district?”

example-90.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.

#91

Moving average of a quarterly series

ממוצע נע של סדרה רבעונית

“Smooth the national quarterly apartment price per m² with a 4-quarter moving average.”

example-91.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.

#92

Price percentiles in a city

אחוזוני מחיר בעיר

“What is the price distribution (10th to 90th percentile) of apartments sold in Rishon LeZion in the last 12 months?”

example-92.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.

#93

Portable median with window functions

חציון נייד באמצעות פונקציות חלון

“How do I compute a median without a median() function, e.g. Tel Aviv apartments in 2025?”

example-93.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().

#94

Histogram of price per m²

היסטוגרמה של מחיר למ״ר

“What is the distribution of price per m² for apartments sold in Haifa in the last 12 months, in 2,500 ILS bins?”

example-94.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.

#95

Busiest single days on record

הימים העמוסים ביותר

“On which single days were the most deals recorded from December 2013 to 2025?”

example-95.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.

Recipes5

#96

Recipe: 4-room apartments in Rehavia in 2025

מתכון: דירות 4 חדרים ברחביה ב-2025

“How much did 4-room apartments cost in Rehavia (Jerusalem) in 2025?”

example-96.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.

#97

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

מתכון: מחירי דירות 3 חדרים בעיר ברבעונים האחרונים

“What does a 3-room apartment cost in Haifa lately, and is it going up?”

example-97.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.

#98

Recipe: compare two neighbouring cities

מתכון: השוואה בין שתי ערים שכנות

“Should I buy in Ramat Gan or Givatayim? Compare prices, change, 4-room prices and rental yield.”

example-98.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.

#99

Recipe: real price change over 5 years

מתכון: שינוי ריאלי במחיר ב-5 שנים

“Are Jerusalem apartments more expensive now than 5 years ago after inflation?”

example-99.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.

#100

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

מתכון: היכן אפשר לקנות דירת 4 חדרים בתקציב?

“In which cities can I buy a typical 4-room apartment for up to 2 million ILS?”

example-100.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.

07 · For agents

Machine-readable docs#

Point your agent, crawler or code generator at these. They are generated from the same schema and examples as this page.

Data: Israel Tax Authority real-estate transactions (public records), enriched with OpenStreetMap (© OpenStreetMap contributors, ODbL), CBS, Bank of Israel and government ministries — see sources and licences. Provided as-is, without warranty; not an appraisal. Please attribute “Mashkof (משקוף), based on Israel Tax Authority data”.

Column	Type	Description	Example
keynot null	TEXT	Primary key: metadata key (see the meta key list in the docs).	stats_anchor_date
value	TEXT	Value as TEXT (numbers, dates, or JSON).	2026-06-30

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.