{"$comment":"Generated by site/scripts/gen-sql-schema.mjs from data/nadlan.db + curated descriptions. Do not edit by hand.","schema_version":"1.2","data_as_of":"2026-09-17","stats_anchor_date":"2026-06-30","min_date":"1998-01-01","db_built_at":"2026-09-28T08:55:24Z","enrich_built_at":"2026-09-28T09:04:23Z","generated_at":"2026-09-28T14:59:55Z","sqlite_version":"3.53.4","windows":{"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"]},"groups":[{"id":"core","title":"Core: deals & places","description":"Transactions and the entities they belong to."},{"id":"aggregates","title":"Precomputed aggregates","description":"Medians, counts and volume by place and period. Use these instead of scanning deals."},{"id":"search","title":"Search & spatial indexes","description":"FTS5 name search and R*Tree bounding-box lookups."},{"id":"places","title":"Streets, addresses & neighbourhoods","description":"OpenStreetMap addresses and neighbourhood polygons joined to parcels."},{"id":"location","title":"Location metrics & POIs","description":"Transit, schools, sea, elevation and noise per parcel and gush."},{"id":"society","title":"Socio-economic & crime","description":"CBS socio-economic clusters and Israel Police crime files."},{"id":"macro","title":"Macro, rents & yields","description":"CPI, real-price factor, rates, dwelling indices, rents, yields and affordability."},{"id":"projects","title":"Urban renewal & discount projects","description":"Declared renewal compounds and subsidised lottery projects."},{"id":"reference","title":"Reference & metadata","description":"Lookup tables and build metadata."}],"relationships":[{"from":"deals.settlement_code","to":"settlements.code","cardinality":"many-to-one"},{"from":"deals.(gush, chelka)","to":"parcels.(gush, chelka)","cardinality":"many-to-one"},{"from":"deals.gush","to":"gushim.gush","cardinality":"many-to-one"},{"from":"deals.property_group","to":"property_groups.key","cardinality":"many-to-one"},{"from":"deals.discount_project_id","to":"discount_projects.project_id","cardinality":"many-to-one"},{"from":"settlements.district","to":"districts.name","cardinality":"many-to-one"},{"from":"gushim.settlement_code","to":"settlements.code","cardinality":"many-to-one"},{"from":"parcels.gush","to":"gushim.gush","cardinality":"many-to-one"},{"from":"parcels.id","to":"parcels_rtree.id","cardinality":"one-to-one"},{"from":"settlements_fts.rowid","to":"settlements.code","cardinality":"one-to-one"},{"from":"agg_settlement_*.settlement_code","to":"settlements.code","cardinality":"many-to-one"},{"from":"agg_gush_year.gush","to":"gushim.gush","cardinality":"many-to-one"},{"from":"agg_district_*.district","to":"districts.name","cardinality":"many-to-one"},{"from":"neighborhoods.settlement_code","to":"settlements.code","cardinality":"many-to-one"},{"from":"parcel_neighborhood.(gush, chelka)","to":"parcels.(gush, chelka)","cardinality":"one-to-one"},{"from":"parcel_neighborhood.nbhd_id","to":"neighborhoods.nbhd_id","cardinality":"many-to-one"},{"from":"agg_neighborhood_year.nbhd_id","to":"neighborhoods.nbhd_id","cardinality":"many-to-one"},{"from":"streets.settlement_code","to":"settlements.code","cardinality":"many-to-one"},{"from":"street_parcels.street_id","to":"streets.id","cardinality":"many-to-one"},{"from":"street_parcels.(gush, chelka)","to":"parcels.(gush, chelka)","cardinality":"many-to-one"},{"from":"parcel_address.(gush, chelka)","to":"parcels.(gush, chelka)","cardinality":"one-to-one"},{"from":"parcel_address.street_id","to":"streets.id","cardinality":"many-to-one"},{"from":"loc_metrics_parcel.(gush, chelka)","to":"parcels.(gush, chelka)","cardinality":"one-to-one"},{"from":"loc_metrics_gush.gush","to":"gushim.gush","cardinality":"one-to-one"},{"from":"loc_metrics_*.rail_station_id","to":"poi_stations.id","cardinality":"many-to-one"},{"from":"settlement_socio.settlement_code","to":"settlements.code","cardinality":"one-to-one"},{"from":"parcel_stat_area_socio.stat_area_id","to":"stat_area_socio.stat_area_id","cardinality":"many-to-one"},{"from":"crime_settlement_year.settlement_code","to":"settlements.code","cardinality":"many-to-one"},{"from":"rent_city.settlement_code","to":"settlements.code","cardinality":"many-to-one"},{"from":"gross_yield.settlement_code","to":"settlements.code","cardinality":"many-to-one"},{"from":"agg_national_month.month","to":"macro_month.month","cardinality":"many-to-one"},{"from":"renewal_compounds.settlement_code","to":"settlements.code","cardinality":"many-to-one"},{"from":"parcel_renewal.compound_id","to":"renewal_compounds.compound_id","cardinality":"many-to-one"},{"from":"gush_renewal.gush","to":"gushim.gush","cardinality":"one-to-one"},{"from":"discount_projects.settlement_code","to":"settlements.code","cardinality":"many-to-one"},{"from":"discount_project_parcels.project_id","to":"discount_projects.project_id","cardinality":"many-to-one"}],"meta_keys":[{"key":"built_at","value":"2026-09-28T08:55:24Z","description":"UTC time the core DB was built."},{"key":"change_method","value":"existing: median ppsqm of existing-stock residential deals (is_new_build = 0), stat rule"},{"key":"completeness_ratio_threshold","value":"0.85"},{"key":"data_complete_through","value":"2026-06-30","description":"Same as stats_anchor_date."},{"key":"discount_heuristic_deals","value":"25247"},{"key":"discount_official_deals","value":"61680"},{"key":"discount_project_deals","value":"86927"},{"key":"districts","value":"[\"המרכז\", \"תל אביב\", \"הדרום\", \"חיפה\", \"הצפון\", \"ירושלים\", \"יהודה והשומרון\"]","json":true,"description":"JSON list of district names, busiest first."},{"key":"duplicates_removed","value":"626608"},{"key":"enrich_built_at","value":"2026-09-28T09:04:23Z","description":"UTC time the enrichment tables were written."},{"key":"enrich_bundles","value":"{\"location\": \"2026-09-28T07:52:19\", \"amenities\": \"2026-09-28T07:49:05\", \"market\": \"2026-09-28T07:46:55\", \"projects\": \"2026-09-28T09:03:51\"}","json":true},{"key":"enrich_counts","value":"{\"parcel_address\": 245790, \"parcel_buildings\": 226621, \"streets\": 31687, \"street_parcels\": 259266, \"neighborhoods\": 1277, \"parcel_neighbo…","json":true},{"key":"enrich_coverage","value":"{\"address_pct_deals\": 31.55, \"street_any_pct_deals\": 73.51, \"neighborhoods\": 1277, \"neighborhood_settlements\": 182, \"neighborhood_pct_dea…","json":true},{"key":"enrich_db_built_at","value":"2026-09-28T08:55:24Z"},{"key":"enrich_schema_version","value":"1.2"},{"key":"enrich_tables","value":"[\"affordability\", \"agg_neighborhood_year\", \"crime_settlement_year\", \"crime_station_year\", \"discount_match\", \"discount_project_parcels\", \"…","json":true},{"key":"geo_fallbacks","value":"{\"far_parcel_to_gush\": {\"parcels\": 5, \"deals\": 12}, \"far_to_settlement\": {\"parcels\": 412, \"deals\": 667}, \"no_gush_to_settlement\": {\"parce…","json":true},{"key":"geo_far_from_own_settlement_deals","value":"272"},{"key":"geo_far_km","value":"25.0"},{"key":"geo_gush_deals","value":"280269"},{"key":"geo_gush_pct","value":"8.82"},{"key":"geo_none_pct","value":"0.0"},{"key":"geo_parcel_approx_deals","value":"228044"},{"key":"geo_parcel_approx_pct","value":"7.18"},{"key":"geo_parcel_deals","value":"2895033"},{"key":"geo_parcel_exact_deals","value":"2666989"},{"key":"geo_parcel_exact_pct","value":"83.97"},{"key":"geo_parcel_pct","value":"91.15"},{"key":"geo_settlement_deals","value":"668"},{"key":"geo_settlement_pct","value":"0.021"},{"key":"geo_source_deals","value":"{\"cancelled\": 215962, \"gush\": 280269, \"parcel\": 2666989, \"settlement\": 668, \"shuma\": 12082}","json":true},{"key":"gushim","value":"12408"},{"key":"in_stats_deals","value":"1790860","description":"Rows with in_stats = 1."},{"key":"in_stats_ppsqm_deals","value":"1669094","description":"in_stats rows with price_per_sqm."},{"key":"incomplete_from_month","value":"2026-07","description":"First incomplete month (is_incomplete = 1 from here on)."},{"key":"last_first_seen","value":"2026-09-19T11:22:31Z"},{"key":"max_date","value":"2026-09-17","description":"Last deal date (NOT the end of the statistics windows)."},{"key":"max_parcel_share_rank","value":"0.5"},{"key":"merged_deals","value":"37403"},{"key":"merged_rows_removed","value":"41622"},{"key":"min_date","value":"1998-01-01","description":"First deal date."},{"key":"min_n_change_gush","value":"20"},{"key":"min_n_change_neighborhood","value":"50"},{"key":"min_n_change_settlement","value":"50"},{"key":"min_n_rank","value":"30"},{"key":"min_n_rank_neighborhood","value":"20"},{"key":"min_parcels_rank","value":"5"},{"key":"months_completeness","value":"[{\"month\": \"2026-09\", \"residential\": 72, \"same_month_prev_year\": 7609, \"ratio\": 0.009}, {\"month\": \"2026-08\", \"residential\": 978, \"same_mo…","json":true,"description":"JSON: monthly residential counts vs the same month a year earlier (anchor rule)."},{"key":"multi_unit_deals","value":"71985"},{"key":"national_deals_12m","value":"107515","description":"All deals nationally in the 12m window."},{"key":"national_deals_after_anchor","value":"4594","description":"Deals reported after the anchor so far."},{"key":"national_median_ppsqm_12m","value":"21844","description":"National median residential ₪/m², 12m."},{"key":"national_median_ppsqm_apartment_12m","value":"21959","description":"National median apartment ₪/m², 12m."},{"key":"national_median_ppsqm_existing_12m","value":"20298","description":"National existing-stock median ₪/m², 12m."},{"key":"national_median_ppsqm_new_12m","value":"26463","description":"National new-build median ₪/m², 12m."},{"key":"national_median_ppsqm_prev12m","value":"22068","description":"National median residential ₪/m², prev12m (pooled)."},{"key":"national_median_price_12m","value":"2200000","description":"National median residential price, 12m (₪)."},{"key":"national_median_price_apartment_12m","value":"2167878","description":"National median apartment price, 12m."},{"key":"national_ppsqm_change_method","value":"existing"},{"key":"national_ppsqm_change_pct","value":"-1.3","description":"National existing-stock ₪/m² change, percent points."},{"key":"national_ppsqm_change_pct_pooled","value":"-1.0"},{"key":"no_settlement_code_deals","value":"1065"},{"key":"nonres_min_amount","value":"{\"parking\": 10000, \"land\": 10000, \"agriculture\": 10000, \"other\": 10000, \"commercial\": 50000}","json":true,"description":"JSON: non-residential median floors per group (₪)."},{"key":"outlier_deals","value":"58558"},{"key":"parcels","value":"381177"},{"key":"partial_year","value":"2026","description":"Year with incomplete data."},{"key":"privatization_deals","value":"718"},{"key":"ranked_settlements","value":"92"},{"key":"real_price_base_month","value":"2026-06","description":"Month that macro_month.real_factor converts to."},{"key":"representative_weekday_gtfs","value":"2026-10-13","description":"GTFS day behind every trip count."},{"key":"rooms_buckets","value":"[\"1-2\", \"3\", \"4\", \"5\", \"6+\"]","json":true,"description":"JSON list of deals.rooms_bucket values."},{"key":"schema_version","value":"1.2","description":"Schema version of this DB (additive changes only within a major)."},{"key":"settlements","value":"1139"},{"key":"source_file","value":"taxes-nadlan-full-f41fb496_append.csv"},{"key":"source_rows","value":"3844200"},{"key":"sources","value":"[{\"bundle\": \"location\", \"name\": \"OpenStreetMap - Geofabrik extract israel-and-palestine-latest.osm.pbf\", \"url\": \"https://download.geofabr…","json":true,"description":"JSON array of every public data source with URL and licence."},{"key":"stats_anchor_date","value":"2026-06-30","description":"End of the last complete month; every statistics window ends here."},{"key":"total_deals","value":"3175970","description":"Rows in deals."},{"key":"total_volume_ils","value":"4116091864997","description":"Sum of credible deal amounts, all time (₪)."},{"key":"window_12m_end","value":"2026-06-30","description":"End of the 12m window (= stats anchor)."},{"key":"window_12m_start","value":"2025-07-01","description":"Start of the 12m window."},{"key":"window_24m_end","value":"2026-06-30","description":"End of the 24m window."},{"key":"window_24m_start","value":"2024-07-01","description":"Start of the 24m window (gushim)."},{"key":"window_5y_end","value":"2026-06-30","description":"End of the 5y window."},{"key":"window_5y_start","value":"2021-07-01","description":"Start of the 5y window."},{"key":"window_prev12m_end","value":"2025-06-30","description":"End of the prev12m window."},{"key":"window_prev12m_start","value":"2024-07-01","description":"Start of the prev12m window."},{"key":"window_prev24m_end","value":"2024-06-30","description":"End of the prev24m window."},{"key":"window_prev24m_start","value":"2022-07-01","description":"Start of the prev24m window."}],"source":"live+docs","tables":[{"name":"deals","kind":"table","group":"core","row_count":3175970,"description":"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"],"columns":[{"name":"id","type":"INTEGER","nullable":false,"description":"Primary key = the source CSV line number (stable across rebuilds, not dense: gaps where duplicates were removed or rows merged). Safe for URLs.","example":3011433},{"name":"settlement_code","type":"INTEGER","nullable":true,"description":"CBS locality code → settlements.code. NULL for 1,065 deals (unresolved regional-council labels): group by code, display settlement.","example":5000,"references":"settlements.code"},{"name":"settlement","type":"TEXT","nullable":true,"description":"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. 'ניר ח\"ן').","example":"תל אביב-יפו"},{"name":"settlement_raw","type":"TEXT","nullable":true,"description":"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%).","example":"צהל"},{"name":"gush","type":"INTEGER","nullable":false,"description":"Cadastral block (גוש). Indexed with chelka (ix_deals_gush_chelka).","example":6768,"references":"gushim.gush"},{"name":"chelka","type":"INTEGER","nullable":false,"description":"Parcel (חלקה) within the gush. (gush, chelka) → parcels.","example":5},{"name":"sub_chelka","type":"INTEGER","nullable":false,"description":"Sub-parcel (תת-חלקה, roughly the unit/apartment). 0 = none.","example":108},{"name":"deal_date","type":"TEXT","nullable":false,"description":"Transaction date, TEXT 'YYYY-MM-DD' (1998-01-01 … 2026-09-17). Compare as strings: deal_date >= '2025-07-01'.","example":"2026-05-01"},{"name":"year","type":"INTEGER","nullable":false,"description":"Year of deal_date (INTEGER).","example":2026},{"name":"quarter","type":"TEXT","nullable":false,"description":"Quarter of deal_date, TEXT 'YYYY-Qn' (e.g. '2025-Q4'); sorts correctly as a string.","example":"2026-Q2"},{"name":"deal_amount","type":"INTEGER","nullable":false,"description":"Deal value (שווי מכירה) in ₪ for the SOLD SHARE (see portion). 1 … 8.9e9. For merged rows the sum of the merged shares.","example":7555000},{"name":"declared_amount","type":"INTEGER","nullable":true,"description":"Declared value (שווי מוצהר) in ₪. Equals deal_amount in ~92% of rows. NULL when the source said 0 (4,464 rows).","example":7555000},{"name":"deal_nature","type":"TEXT","nullable":false,"description":"Raw Hebrew transaction type (מהות), one of 47 values → deal_natures.raw (mapped to property_group).","example":"דירה בבית קומות","references":"deal_natures.raw"},{"name":"property_group","type":"TEXT","nullable":false,"description":"Normalised property type key → property_groups.key: apartment, garden_apartment, penthouse, house (residential) or land, commercial, agriculture, parking, other.","example":"apartment","values":["apartment","garden_apartment","penthouse","house","land","commercial","agriculture","parking","other"],"references":"property_groups.key"},{"name":"is_residential","type":"INTEGER","nullable":false,"description":"1 for apartment, garden_apartment, penthouse and house; else 0.","example":1},{"name":"portion","type":"REAL","nullable":true,"description":"Share of the property sold (חלק נמכר), 0.001–1. 1 = whole unit. NULL = unknown (77k rows; the source said 0).","example":1},{"name":"is_full_deal","type":"INTEGER","nullable":false,"description":"1 when portion = 1 (a whole-unit sale).","example":1},{"name":"year_built","type":"INTEGER","nullable":true,"description":"Year of construction, 1850 … deal year + 5 (off-plan sales can be in the future). NULL when unknown (~793k rows).","example":2031},{"name":"is_new_build","type":"INTEGER","nullable":false,"description":"1 when residential and year_built >= year − 1 (new or off-plan, 'דירה חדשה'); 0 otherwise, including unknown year_built.","example":1},{"name":"area","type":"REAL","nullable":true,"description":"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).","example":139},{"name":"rooms","type":"REAL","nullable":true,"description":"Number of rooms (half rooms allowed), 1–20. NULL when unknown (~943k rows). Ignore on non-residential rows.","example":5},{"name":"rooms_bucket","type":"TEXT","nullable":true,"description":"'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.","example":"5","values":["1-2","3","4","5","6+"]},{"name":"price_per_sqm","type":"INTEGER","nullable":true,"description":"₪ 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).","example":54353},{"name":"lat","type":"REAL","nullable":true,"description":"Latitude (WGS84) of the parcel point shared by every deal of this (gush, chelka); never NULL. See geo_precision.","example":32.110498},{"name":"lon","type":"REAL","nullable":true,"description":"Longitude (WGS84) of the parcel point; never NULL.","example":34.797737},{"name":"geo_precision","type":"TEXT","nullable":true,"description":"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).","example":"parcel","values":["parcel","gush","settlement"]},{"name":"is_outlier","type":"INTEGER","nullable":false,"description":"1 when an outlier rule fired (58,558 rows). Outliers are never in_stats.","example":0},{"name":"outlier_reason","type":"TEXT","nullable":true,"description":"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.","example":"ppsqm_out_of_band"},{"name":"is_multi_unit","type":"INTEGER","nullable":false,"description":"1 for a bulk / non-market group deal (71,985 rows): several units sharing gush, date and amount, portfolios, kibbutz privatisations. Never in_stats.","example":0},{"name":"multi_unit_kind","type":"TEXT","nullable":true,"description":"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.","example":"same_price_units","values":["same_price_units","pair_total_price","flat_price_batch","portfolio","privatization"]},{"name":"multi_unit_n","type":"INTEGER","nullable":true,"description":"How many distinct units share (gush, date, amount). 1 = unique; max 181.","example":1},{"name":"in_stats","type":"INTEGER","nullable":false,"description":"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.","example":1},{"name":"first_seen","type":"TEXT","nullable":true,"description":"ISO-8601 UTC time the scraper first saw the row (all 2026-09-18/19). Useless for 'new deals' — use deal_date.","example":"2026-09-18T10:41:08Z"},{"name":"is_discount_project","type":"INTEGER","nullable":false,"description":"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'.","example":0},{"name":"merged_rows","type":"INTEGER","nullable":true,"description":"Number of source rows (2–4 split shares of one sale) merged into this full deal; NULL for ordinary rows.","example":2},{"name":"discount_source","type":"TEXT","nullable":true,"description":"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.","example":"official","values":["official","heuristic"]},{"name":"discount_project_id","type":"TEXT","nullable":true,"description":"discount_projects.project_id for 'official' discount rows (partial index ix_deals_discount_project). NULL otherwise.","example":"moch:10","references":"discount_projects.project_id"}],"indexes":[{"name":"ix_deals_discount_project","columns":["discount_project_id"],"unique":false,"where":"discount_project_id IS NOT NULL","origin":"index"},{"name":"ix_deals_ppsqm","columns":["price_per_sqm","id","settlement_code","property_group","deal_date","rooms","deal_amount","is_outlier","is_multi_unit"],"unique":false,"where":"price_per_sqm IS NOT NULL","origin":"index"},{"name":"ix_deals_amount","columns":["deal_amount"],"unique":false,"origin":"index"},{"name":"ix_deals_group_amount","columns":["property_group","deal_amount"],"unique":false,"origin":"index"},{"name":"ix_deals_group_date","columns":["property_group","deal_date","id","rooms","deal_amount","price_per_sqm","is_outlier","is_multi_unit"],"unique":false,"origin":"index"},{"name":"ix_deals_date","columns":["deal_date","id","settlement_code","property_group","rooms","deal_amount","price_per_sqm","is_outlier","is_multi_unit"],"unique":false,"origin":"index"},{"name":"ix_deals_gush_chelka","columns":["gush","chelka","deal_date"],"unique":false,"origin":"index"},{"name":"ix_deals_settlement_ppsqm","columns":["settlement_code","price_per_sqm"],"unique":false,"where":"price_per_sqm IS NOT NULL","origin":"index"},{"name":"ix_deals_settlement_amount","columns":["settlement_code","deal_amount"],"unique":false,"origin":"index"},{"name":"ix_deals_settlement_group_date","columns":["settlement_code","property_group","deal_date","id","rooms","deal_amount","price_per_sqm","is_outlier","is_multi_unit"],"unique":false,"origin":"index"},{"name":"ix_deals_settlement_date","columns":["settlement_code","deal_date","id","property_group","rooms","deal_amount","price_per_sqm","is_outlier","is_multi_unit"],"unique":false,"origin":"index"}],"foreign_keys":[{"column":"settlement_code","references":"settlements.code"},{"column":"property_group","references":"property_groups.key"},{"column":"deal_nature","references":"deal_natures.raw"},{"column":"discount_project_id","references":"discount_projects.project_id"},{"column":"gush","references":"gushim.gush"},{"column":"gush, chelka","references":"parcels.(gush, chelka)"}],"notes":["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."]},{"name":"settlements","kind":"table","group":"core","row_count":1139,"description":"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"],"columns":[{"name":"code","type":"INTEGER","nullable":false,"description":"Primary key: CBS locality code (סמל יישוב), e.g. 5000 = Tel Aviv-Yafo, 3000 = Jerusalem, 4000 = Haifa. Also the rowid of settlements_fts.","example":5000,"references":"settlements_fts.rowid"},{"name":"name","type":"TEXT","nullable":false,"description":"Canonical Hebrew name (same as deals.settlement), raw registry spelling (ASCII \" and ' possible).","example":"תל אביב-יפו"},{"name":"slug","type":"TEXT","nullable":false,"description":"URL slug (UNIQUE): spaces/dashes → '-', quotes/geresh/parentheses/dots removed, e.g. 'תל-אביב-יפו'.","example":"תל-אביב-יפו"},{"name":"name_en","type":"TEXT","nullable":true,"description":"English name (CBS), e.g. 'Tel Aviv - Yafo'. NULL when unknown.","example":"Tel Aviv - Yafo"},{"name":"district","type":"TEXT","nullable":true,"description":"District (מחוז) in CBS form: 'הצפון', 'חיפה', 'המרכז', 'תל אביב', 'ירושלים', 'הדרום', 'יהודה והשומרון' → districts.name. NULL for a few small West Bank localities.","example":"תל אביב","values":["הצפון","הדרום","המרכז","חיפה","ירושלים","יהודה והשומרון","תל אביב"],"references":"districts.name"},{"name":"subdistrict","type":"TEXT","nullable":true,"description":"Sub-district (נפה), Hebrew.","example":"תל אביב"},{"name":"lat","type":"REAL","nullable":true,"description":"Official settlement centre latitude (WGS84). Never NULL.","example":32.084449},{"name":"lon","type":"REAL","nullable":true,"description":"Official settlement centre longitude (WGS84). Never NULL.","example":34.791693},{"name":"bbox_min_lat","type":"REAL","nullable":true,"description":"Map extent (south). Polygon box when has_polygon = 1, else deal-point box; always contains lat/lon.","example":32.029336},{"name":"bbox_min_lon","type":"REAL","nullable":true,"description":"Map extent (west).","example":34.739149},{"name":"bbox_max_lat","type":"REAL","nullable":true,"description":"Map extent (north).","example":32.146967},{"name":"bbox_max_lon","type":"REAL","nullable":true,"description":"Map extent (east).","example":34.852262},{"name":"has_polygon","type":"INTEGER","nullable":true,"description":"1 when a boundary polygon exists (used by the choropleth).","example":1},{"name":"muni_type","type":"TEXT","nullable":true,"description":"Municipal type: 'עירייה' (city), 'מועצה מקומית' (local council), 'מועצה אזורית' (regional council), 'מועצה מקומית תעשייתית', 'ללא שיפוט'. NULL for some.","example":"עירייה","values":["מועצה אזורית","מועצה מקומית","עירייה","ללא שיפוט","מועצה מקומית תעשייתית"]},{"name":"settlement_kind","type":"TEXT","nullable":true,"description":"Settlement kind: 'יישוב עירוני' (urban), 'יישוב כפרי' (rural), 'מוקד תעסוקה' (employment zone), 'מקום'.","example":"יישוב עירוני","values":["יישוב כפרי","יישוב עירוני","מקום","מוקד תעסוקה"]},{"name":"regional_council","type":"TEXT","nullable":true,"description":"Regional council name for rural localities; NULL for independent municipalities.","example":"לכיש"},{"name":"municipality","type":"TEXT","nullable":true,"description":"Name of the local authority the locality belongs to.","example":"תל אביב - יפו"},{"name":"muni_code","type":"INTEGER","nullable":true,"description":"Code of the local authority.","example":5000},{"name":"population","type":"INTEGER","nullable":true,"description":"Current population (Population Authority, else CBS 2023). NULL for 20.","example":601640},{"name":"area_km2","type":"REAL","nullable":true,"description":"Polygon area in km². NULL without a polygon.","example":57.178},{"name":"year_founded","type":"INTEGER","nullable":true,"description":"Year founded. May be NULL.","example":1909},{"name":"elevation_m","type":"INTEGER","nullable":true,"description":"Elevation of the settlement in metres. May be NULL.","example":17},{"name":"deals_total","type":"INTEGER","nullable":false,"description":"All unique deals, all property groups, all time.","example":244806},{"name":"deals_residential_total","type":"INTEGER","nullable":false,"description":"Residential deals, all time.","example":178358},{"name":"first_deal","type":"TEXT","nullable":true,"description":"Date of the first deal, 'YYYY-MM-DD'.","example":"1998-01-01"},{"name":"last_deal","type":"TEXT","nullable":true,"description":"Date of the latest deal, 'YYYY-MM-DD' (can be after the stats anchor).","example":"2026-07-26"},{"name":"deals_12m","type":"INTEGER","nullable":false,"description":"Deals (all groups) in the 12m window ending at the stats anchor.","example":7038},{"name":"deals_residential_12m","type":"INTEGER","nullable":false,"description":"Residential deals in the 12m window.","example":6020},{"name":"deals_prev12m","type":"INTEGER","nullable":false,"description":"Deals (all groups) in the prev12m window. Don't present deals_12m / deals_prev12m as an activity change (late reports still arrive).","example":8927},{"name":"n_stats_12m","type":"INTEGER","nullable":false,"description":"Residential stat deals (in_stats = 1) in 12m behind median_price_12m.","example":3504},{"name":"median_price_12m","type":"INTEGER","nullable":true,"description":"Median residential deal price in ₪, 12m, all residential groups. NULL when n_stats_12m = 0.","example":4350000},{"name":"n_ppsqm_12m","type":"INTEGER","nullable":false,"description":"Residential stat deals with price_per_sqm in 12m.","example":3476},{"name":"median_ppsqm_12m","type":"INTEGER","nullable":true,"description":"Headline median ₪/m², all residential groups, 12m (pooled new + existing; discount projects excluded). NULL for ~684 small places.","example":56882},{"name":"n_ppsqm_prev12m","type":"INTEGER","nullable":false,"description":"Residential stat deals with ppsqm in prev12m.","example":3406},{"name":"median_ppsqm_prev12m","type":"INTEGER","nullable":true,"description":"Median residential ₪/m² in prev12m (pooled).","example":55800},{"name":"ppsqm_change_pct","type":"REAL","nullable":true,"description":"Existing-stock ₪/m² change in percent points (12m vs prev12m). Set only when both windows have ≥ 50 existing-stock deals (81 settlements); NULL otherwise.","example":1.2},{"name":"median_ppsqm_5y","type":"INTEGER","nullable":true,"description":"Median residential ₪/m² over the 5y window.","example":54128},{"name":"n_apartment_12m","type":"INTEGER","nullable":false,"description":"Apartment-group (property_group = 'apartment') stat deals, 12m.","example":3453},{"name":"median_price_apartment_12m","type":"INTEGER","nullable":true,"description":"Median apartment price in ₪, 12m.","example":4320194},{"name":"median_ppsqm_apartment_12m","type":"INTEGER","nullable":true,"description":"Median apartment ₪/m², 12m.","example":56882},{"name":"n_4rooms_12m","type":"INTEGER","nullable":false,"description":"Apartments with 4–4.5 rooms, stat rule, 12m.","example":936},{"name":"median_price_4rooms_12m","type":"INTEGER","nullable":true,"description":"Median price in ₪ of 4–4.5-room apartments, 12m.","example":5550643},{"name":"n_house_12m","type":"INTEGER","nullable":false,"description":"House-group (private houses / cottages) stat deals, 12m.","example":8},{"name":"median_price_house_12m","type":"INTEGER","nullable":true,"description":"Median house price in ₪, 12m.","example":7250000},{"name":"n_new_build_12m","type":"INTEGER","nullable":false,"description":"In-stats residential new-build deals, 12m.","example":1789},{"name":"total_volume_12m","type":"INTEGER","nullable":false,"description":"Sum of deal_amount in ₪, 12m, all groups, credible amounts only.","example":25615885169},{"name":"avg_area","type":"INTEGER","nullable":true,"description":"Mean area (m²) of in-stats residential deals, 12m. NULL when none.","example":89},{"name":"rank_ppsqm","type":"INTEGER","nullable":true,"description":"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.","example":1},{"name":"ppsqm_change_method","type":"TEXT","nullable":true,"description":"'existing' when ppsqm_change_pct is set, else NULL.","example":"existing","values":["existing"]},{"name":"n_ppsqm_new_12m","type":"INTEGER","nullable":false,"description":"New-build (is_new_build = 1) stat deals with ppsqm, 12m.","example":1775},{"name":"median_ppsqm_new_12m","type":"INTEGER","nullable":true,"description":"Median ₪/m² of new builds, 12m. NULL when none.","example":59107},{"name":"n_ppsqm_existing_12m","type":"INTEGER","nullable":false,"description":"Existing-stock stat deals with ppsqm, 12m.","example":1701},{"name":"median_ppsqm_existing_12m","type":"INTEGER","nullable":true,"description":"Median ₪/m² of existing stock (is_new_build = 0), 12m. Show next to median_ppsqm_12m when they differ by > ~10%.","example":51471},{"name":"median_ppsqm_existing_prev12m","type":"INTEGER","nullable":true,"description":"Median ₪/m² of existing stock, prev12m (base of ppsqm_change_pct).","example":50862},{"name":"n_discount_12m","type":"INTEGER","nullable":false,"description":"Discount-project deals in 12m — NOT in the medians.","example":173},{"name":"deals_after_anchor","type":"INTEGER","nullable":false,"description":"Deals dated after meta.stats_anchor_date reported so far (incomplete).","example":36},{"name":"n_parcels_12m","type":"INTEGER","nullable":false,"description":"Distinct parcels behind the 12m ppsqm median (rank gate).","example":1414},{"name":"max_parcel_share_12m","type":"REAL","nullable":true,"description":"Largest single parcel's share (0–1) of the 12m ppsqm deals (rank gate: must be ≤ 0.5).","example":0.155}],"indexes":[{"name":"ix_settlements_district","columns":["district"],"unique":false,"origin":"index"},{"name":"ix_settlements_rank","columns":["rank_ppsqm"],"unique":false,"where":"rank_ppsqm IS NOT NULL","origin":"index"},{"name":"sqlite_autoindex_settlements_1","columns":["slug"],"unique":true,"origin":"unique constraint"}],"foreign_keys":[{"column":"district","references":"districts.name"},{"column":"code","references":"settlements_fts.rowid"}],"notes":["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_*)."]},{"name":"districts","kind":"table","group":"core","row_count":7,"description":"The 7 CBS districts (מחוזות) with deal counts and residential medians over the 12m / prev12m windows.","primary_key":["name"],"columns":[{"name":"name","type":"TEXT","nullable":false,"description":"Primary key: district name in CBS form ('הצפון', 'חיפה', 'המרכז', 'תל אביב', 'ירושלים', 'הדרום', 'יהודה והשומרון'). Joins settlements.district, agg_district_*.district.","example":"תל אביב","values":["הדרום","המרכז","הצפון","חיפה","יהודה והשומרון","ירושלים","תל אביב"]},{"name":"n_settlements","type":"INTEGER","nullable":true,"description":"Number of settlements with deals in the district.","example":14},{"name":"deals_total","type":"INTEGER","nullable":true,"description":"All deals, all time.","example":662777},{"name":"deals_12m","type":"INTEGER","nullable":true,"description":"Deals (all groups) in the 12m window.","example":17755},{"name":"median_price_12m","type":"INTEGER","nullable":true,"description":"Median residential price in ₪, 12m, stat rule.","example":3200000},{"name":"n_ppsqm_12m","type":"INTEGER","nullable":true,"description":"Residential stat deals with ppsqm, 12m.","example":9477},{"name":"median_ppsqm_12m","type":"INTEGER","nullable":true,"description":"Median residential ₪/m², 12m.","example":37500},{"name":"median_ppsqm_prev12m","type":"INTEGER","nullable":true,"description":"Median residential ₪/m², prev12m (pooled).","example":36036},{"name":"ppsqm_change_pct","type":"REAL","nullable":true,"description":"Existing-stock ₪/m² change, percent points, 12m vs prev12m; NULL unless both windows have ≥ 50 existing-stock deals.","example":-0.8},{"name":"lat","type":"REAL","nullable":true,"description":"Mean parcel point latitude (label anchor).","example":32.072238},{"name":"lon","type":"REAL","nullable":true,"description":"Mean parcel point longitude (label anchor).","example":34.799767},{"name":"ppsqm_change_method","type":"TEXT","nullable":true,"description":"'existing' when ppsqm_change_pct is set, else NULL.","example":"existing","values":["existing"]}],"indexes":[{"name":"sqlite_autoindex_districts_1","columns":["name"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[],"notes":["'יהודה והשומרון' has only a few hundred deals: the Tax Authority register barely covers the West Bank."]},{"name":"gushim","kind":"table","group":"core","row_count":12408,"description":"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"],"columns":[{"name":"gush","type":"INTEGER","nullable":false,"description":"Primary key: block number.","example":6212},{"name":"settlement_code","type":"INTEGER","nullable":true,"description":"Dominant settlement (most coded deals) → settlements.code. NULL for 96.","example":5000,"references":"settlements.code"},{"name":"lat","type":"REAL","nullable":true,"description":"Gush centroid latitude (WGS84). NULL for 1.","example":32.094248},{"name":"lon","type":"REAL","nullable":true,"description":"Gush centroid longitude.","example":34.787909},{"name":"geo_source","type":"TEXT","nullable":true,"description":"'gush' (polygon centroid) or 'parcels_mean' (mean of its parcel points when no polygon).","example":"gush","values":["gush"]},{"name":"has_polygon","type":"INTEGER","nullable":false,"description":"1 when the gush polygon exists.","example":1},{"name":"area_m2","type":"INTEGER","nullable":true,"description":"Gush polygon area in m².","example":698386},{"name":"deals_total","type":"INTEGER","nullable":false,"description":"All deals, all time, all groups.","example":5832},{"name":"deals_24m","type":"INTEGER","nullable":false,"description":"Deals (all groups) in the 24m window.","example":493},{"name":"deals_5y","type":"INTEGER","nullable":false,"description":"Deals (all groups) in the 5y window.","example":1477},{"name":"n_ppsqm_24m","type":"INTEGER","nullable":false,"description":"Residential stat deals with ppsqm, 24m.","example":303},{"name":"median_ppsqm_24m","type":"INTEGER","nullable":true,"description":"Median residential ₪/m², 24m. NULL when n = 0.","example":65833},{"name":"n_ppsqm_5y","type":"INTEGER","nullable":false,"description":"Residential stat deals with ppsqm, 5y.","example":745},{"name":"median_ppsqm_5y","type":"INTEGER","nullable":true,"description":"Median residential ₪/m², 5y.","example":64964},{"name":"n_ppsqm_prev24m","type":"INTEGER","nullable":false,"description":"Residential stat deals with ppsqm, prev24m.","example":206},{"name":"median_ppsqm_prev24m","type":"INTEGER","nullable":true,"description":"Median residential ₪/m², prev24m.","example":66709},{"name":"change_pct","type":"REAL","nullable":true,"description":"Existing-stock median ₪/m² change, percent points, 24m vs prev24m. Set only when both windows have ≥ 20 existing-stock deals (1,148 gushim).","example":1},{"name":"median_price_24m","type":"INTEGER","nullable":true,"description":"Median residential price in ₪, 24m.","example":5419000},{"name":"first_deal","type":"TEXT","nullable":true,"description":"First deal date 'YYYY-MM-DD'.","example":"1998-01-01"},{"name":"last_deal","type":"TEXT","nullable":true,"description":"Latest deal date 'YYYY-MM-DD'.","example":"2026-06-22"},{"name":"change_method","type":"TEXT","nullable":true,"description":"'existing' when change_pct is set, else NULL.","example":"existing","values":["existing"]},{"name":"n_ppsqm_new_24m","type":"INTEGER","nullable":false,"description":"New-build residential stat deals with ppsqm, 24m.","example":229},{"name":"n_ppsqm_existing_24m","type":"INTEGER","nullable":false,"description":"Existing-stock residential stat deals with ppsqm, 24m.","example":74},{"name":"n_discount_24m","type":"INTEGER","nullable":false,"description":"Discount-project deals in 24m (not in the medians).","example":0}],"indexes":[{"name":"ix_gushim_settlement","columns":["settlement_code"],"unique":false,"origin":"index"}],"foreign_keys":[{"column":"settlement_code","references":"settlements.code"}],"notes":["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."]},{"name":"parcels","kind":"table","group":"core","row_count":381177,"description":"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"],"columns":[{"name":"id","type":"INTEGER","nullable":false,"description":"Rowid (INTEGER PK) = parcels_rtree.id. Build-local: do not store it; use (gush, chelka).","example":91722,"references":"parcels_rtree.id"},{"name":"gush","type":"INTEGER","nullable":false,"description":"Block number.","example":6212,"references":"gushim.gush"},{"name":"chelka","type":"INTEGER","nullable":false,"description":"Parcel number. UNIQUE (gush, chelka).","example":418},{"name":"settlement_code","type":"INTEGER","nullable":true,"description":"Dominant settlement of the parcel's deals → settlements.code. NULL for 346.","example":5000,"references":"settlements.code"},{"name":"lat","type":"REAL","nullable":true,"description":"Parcel point latitude (WGS84) — the point every deal of this parcel carries.","example":32.093693},{"name":"lon","type":"REAL","nullable":true,"description":"Parcel point longitude (WGS84).","example":34.783113},{"name":"geo_precision","type":"TEXT","nullable":true,"description":"'parcel', 'gush' (gush centroid) or 'settlement' (settlement centre, not a location; excluded from parcels_rtree).","example":"parcel","values":["parcel","gush","settlement"]},{"name":"geo_source","type":"TEXT","nullable":true,"description":"'parcel' (current cadastre, exact), 'cancelled' (cancelled parcel placed at its successors, approximate), 'shuma' (unsettled tax parcel, ±70 m), 'gush' or 'settlement'.","example":"parcel","values":["parcel","gush","cancelled","shuma","settlement"]},{"name":"uncertainty_m","type":"INTEGER","nullable":true,"description":"Estimated error in metres of a parcel-level point. NULL for gush / settlement points.","example":12},{"name":"deals_total","type":"INTEGER","nullable":false,"description":"All deals on the parcel, all time.","example":5},{"name":"deals_5y","type":"INTEGER","nullable":false,"description":"Deals in the 5y window.","example":0},{"name":"deals_residential","type":"INTEGER","nullable":false,"description":"Residential deals, all time.","example":5},{"name":"n_units","type":"INTEGER","nullable":false,"description":"Distinct sub_chelka values seen ≈ number of units in the building.","example":3},{"name":"first_deal_date","type":"TEXT","nullable":true,"description":"First deal date 'YYYY-MM-DD'.","example":"2006-09-07"},{"name":"last_deal_date","type":"TEXT","nullable":true,"description":"Latest deal date 'YYYY-MM-DD'.","example":"2015-01-05"},{"name":"last_price","type":"INTEGER","nullable":true,"description":"deal_amount (₪) of the most recent deal (the sold share; see last_portion).","example":1500000},{"name":"last_group","type":"TEXT","nullable":true,"description":"property_group of the most recent deal.","example":"apartment","values":["apartment","land","house","commercial","garden_apartment","agriculture","other","parking","penthouse"]},{"name":"last_portion","type":"REAL","nullable":true,"description":"portion of the most recent deal (show when < 1). NULL = unknown.","example":1},{"name":"last_deal_id","type":"INTEGER","nullable":true,"description":"deals.id of the most recent deal.","example":2580110,"references":"deals.id"},{"name":"n_ppsqm_5y","type":"INTEGER","nullable":false,"description":"Residential stat deals with ppsqm, 5y.","example":0},{"name":"median_ppsqm_5y","type":"INTEGER","nullable":true,"description":"Median residential ₪/m², 5y. NULL for most parcels.","example":18182},{"name":"median_price_5y","type":"INTEGER","nullable":true,"description":"Median residential price in ₪, 5y.","example":2600000},{"name":"dominant_group","type":"TEXT","nullable":true,"description":"property_group with the most deals on the parcel.","example":"apartment","values":["apartment","land","house","commercial","agriculture","other","garden_apartment","parking","penthouse"]}],"indexes":[{"name":"ix_parcels_settlement","columns":["settlement_code"],"unique":false,"origin":"index"},{"name":"sqlite_autoindex_parcels_1","columns":["gush","chelka"],"unique":true,"origin":"unique constraint"}],"foreign_keys":[{"column":"settlement_code","references":"settlements.code"},{"column":"gush","references":"gushim.gush"},{"column":"last_deal_id","references":"deals.id"},{"column":"id","references":"parcels_rtree.id"}],"notes":["(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'."]},{"name":"agg_settlement_quarter","kind":"table","group":"aggregates","row_count":439396,"description":"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"],"columns":[{"name":"settlement_code","type":"INTEGER","nullable":false,"description":"CBS locality code (סמל יישוב). Join key → settlements.code.","example":5000,"references":"settlements.code"},{"name":"quarter","type":"TEXT","nullable":false,"description":"Quarter 'YYYY-Qn' (1998-Q1 … 2026-Q3).","example":"2025-Q4"},{"name":"property_group","type":"TEXT","nullable":false,"description":"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).","example":"apartment","values":["all_residential","apartment","house","all","land","garden_apartment","commercial","penthouse","agriculture","parking","other"],"references":"property_groups.key"},{"name":"rooms_bucket","type":"TEXT","nullable":false,"description":"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.","example":"all","values":["all","5","4","6+","3","1-2"]},{"name":"deals","type":"INTEGER","nullable":false,"description":"Count of ALL unique deals in the cell (partial, outlier, multi-unit and discount-project rows included) = transaction volume. Never NULL.","example":1592},{"name":"n_stats","type":"INTEGER","nullable":true,"description":"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'.","example":854},{"name":"median_price","type":"INTEGER","nullable":true,"description":"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.","example":4133559},{"name":"n_ppsqm","type":"INTEGER","nullable":true,"description":"Number of stat deals that also have price_per_sqm, behind median_ppsqm. 0 for non-residential groups; NULL for 'all'.","example":849},{"name":"median_ppsqm","type":"INTEGER","nullable":true,"description":"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).","example":57447},{"name":"median_area","type":"INTEGER","nullable":true,"description":"Median area (m²) of the stat deals. NULL when none.","example":77},{"name":"total_volume","type":"INTEGER","nullable":false,"description":"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).","example":6061499136},{"name":"is_incomplete","type":"INTEGER","nullable":false,"description":"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.","example":0}],"indexes":[{"name":"sqlite_autoindex_agg_settlement_quarter_1","columns":["settlement_code","property_group","rooms_bucket","quarter"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[{"column":"settlement_code","references":"settlements.code"},{"column":"property_group","references":"property_groups.key"}],"notes":["Always constrain property_group AND rooms_bucket (rooms_bucket = 'all' for totals).","Mark or drop is_incomplete = 1 rows (2026-Q3)."],"without_rowid":true},{"name":"agg_settlement_year","kind":"table","group":"aggregates","row_count":98509,"description":"Precomputed per settlement × property_group × year (no rooms dimension): counts, medians, volume.","primary_key":["settlement_code","property_group","year"],"columns":[{"name":"settlement_code","type":"INTEGER","nullable":false,"description":"CBS locality code (סמל יישוב). Join key → settlements.code.","example":5000,"references":"settlements.code"},{"name":"year","type":"INTEGER","nullable":false,"description":"Calendar year (1998 … 2026).","example":2025},{"name":"property_group","type":"TEXT","nullable":false,"description":"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).","example":"apartment","values":["all","land","all_residential","house","apartment","commercial","agriculture","garden_apartment","other","parking","penthouse"],"references":"property_groups.key"},{"name":"deals","type":"INTEGER","nullable":false,"description":"Count of ALL unique deals in the cell (partial, outlier, multi-unit and discount-project rows included) = transaction volume. Never NULL.","example":6253},{"name":"n_stats","type":"INTEGER","nullable":true,"description":"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'.","example":3269},{"name":"median_price","type":"INTEGER","nullable":true,"description":"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.","example":4208000},{"name":"n_ppsqm","type":"INTEGER","nullable":true,"description":"Number of stat deals that also have price_per_sqm, behind median_ppsqm. 0 for non-residential groups; NULL for 'all'.","example":3225},{"name":"median_ppsqm","type":"INTEGER","nullable":true,"description":"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).","example":56707},{"name":"median_area","type":"INTEGER","nullable":true,"description":"Median area (m²) of the stat deals. NULL when none.","example":80},{"name":"total_volume","type":"INTEGER","nullable":false,"description":"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).","example":23210399012},{"name":"is_incomplete","type":"INTEGER","nullable":false,"description":"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.","example":0}],"indexes":[{"name":"sqlite_autoindex_agg_settlement_year_1","columns":["settlement_code","property_group","year"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[{"column":"settlement_code","references":"settlements.code"},{"column":"property_group","references":"property_groups.key"}],"notes":["Year 2026 is incomplete (is_incomplete = 1)."],"without_rowid":true},{"name":"agg_national_month","kind":"table","group":"aggregates","row_count":3708,"description":"National monthly series per property_group: counts, medians, volume. month joins macro_month.month.","primary_key":["property_group","month"],"columns":[{"name":"month","type":"TEXT","nullable":false,"description":"Month 'YYYY-MM' (1998-01 … 2026-09).","example":"2025-12","references":"macro_month.month"},{"name":"property_group","type":"TEXT","nullable":false,"description":"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).","example":"all_residential","values":["all","all_residential","apartment","land","commercial","house","parking","agriculture","other","garden_apartment","penthouse"],"references":"property_groups.key"},{"name":"deals","type":"INTEGER","nullable":false,"description":"Count of ALL unique deals in the cell (partial, outlier, multi-unit and discount-project rows included) = transaction volume. Never NULL.","example":9136},{"name":"n_stats","type":"INTEGER","nullable":true,"description":"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'.","example":6164},{"name":"median_price","type":"INTEGER","nullable":true,"description":"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.","example":2230000},{"name":"n_ppsqm","type":"INTEGER","nullable":true,"description":"Number of stat deals that also have price_per_sqm, behind median_ppsqm. 0 for non-residential groups; NULL for 'all'.","example":6075},{"name":"median_ppsqm","type":"INTEGER","nullable":true,"description":"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).","example":22477},{"name":"median_area","type":"INTEGER","nullable":true,"description":"Median area (m²) of the stat deals. NULL when none.","example":101},{"name":"total_volume","type":"INTEGER","nullable":false,"description":"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).","example":20450103635},{"name":"is_incomplete","type":"INTEGER","nullable":false,"description":"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.","example":0}],"indexes":[{"name":"sqlite_autoindex_agg_national_month_1","columns":["property_group","month"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[{"column":"month","references":"macro_month.month"},{"column":"property_group","references":"property_groups.key"}],"notes":[],"without_rowid":true},{"name":"agg_national_quarter","kind":"table","group":"aggregates","row_count":3875,"description":"National quarterly series per property_group × rooms_bucket.","primary_key":["property_group","rooms_bucket","quarter"],"columns":[{"name":"quarter","type":"TEXT","nullable":false,"description":"Quarter 'YYYY-Qn'.","example":"2025-Q4"},{"name":"property_group","type":"TEXT","nullable":false,"description":"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).","example":"apartment","values":["all_residential","apartment","house","garden_apartment","penthouse","agriculture","all","commercial","land","other","parking"],"references":"property_groups.key"},{"name":"rooms_bucket","type":"TEXT","nullable":false,"description":"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.","example":"all","values":["all","5","4","6+","3","1-2"]},{"name":"deals","type":"INTEGER","nullable":false,"description":"Count of ALL unique deals in the cell (partial, outlier, multi-unit and discount-project rows included) = transaction volume. Never NULL.","example":20765},{"name":"n_stats","type":"INTEGER","nullable":true,"description":"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'.","example":13776},{"name":"median_price","type":"INTEGER","nullable":true,"description":"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.","example":2150000},{"name":"n_ppsqm","type":"INTEGER","nullable":true,"description":"Number of stat deals that also have price_per_sqm, behind median_ppsqm. 0 for non-residential groups; NULL for 'all'.","example":13518},{"name":"median_ppsqm","type":"INTEGER","nullable":true,"description":"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).","example":22065},{"name":"median_area","type":"INTEGER","nullable":true,"description":"Median area (m²) of the stat deals. NULL when none.","example":100},{"name":"total_volume","type":"INTEGER","nullable":false,"description":"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).","example":44121174310},{"name":"is_incomplete","type":"INTEGER","nullable":false,"description":"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.","example":0}],"indexes":[{"name":"sqlite_autoindex_agg_national_quarter_1","columns":["property_group","rooms_bucket","quarter"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[{"column":"property_group","references":"property_groups.key"}],"notes":[],"without_rowid":true},{"name":"agg_national_year","kind":"table","group":"aggregates","row_count":1007,"description":"National yearly series per property_group × rooms_bucket.","primary_key":["property_group","rooms_bucket","year"],"columns":[{"name":"year","type":"INTEGER","nullable":false,"description":"Calendar year.","example":2025},{"name":"property_group","type":"TEXT","nullable":false,"description":"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).","example":"apartment","values":["all_residential","apartment","house","garden_apartment","penthouse","agriculture","all","commercial","land","other","parking"],"references":"property_groups.key"},{"name":"rooms_bucket","type":"TEXT","nullable":false,"description":"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.","example":"all","values":["all","4","3","5","6+","1-2"]},{"name":"deals","type":"INTEGER","nullable":false,"description":"Count of ALL unique deals in the cell (partial, outlier, multi-unit and discount-project rows included) = transaction volume. Never NULL.","example":88371},{"name":"n_stats","type":"INTEGER","nullable":true,"description":"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'.","example":59310},{"name":"median_price","type":"INTEGER","nullable":true,"description":"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.","example":2120000},{"name":"n_ppsqm","type":"INTEGER","nullable":true,"description":"Number of stat deals that also have price_per_sqm, behind median_ppsqm. 0 for non-residential groups; NULL for 'all'.","example":58512},{"name":"median_ppsqm","type":"INTEGER","nullable":true,"description":"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).","example":22044},{"name":"median_area","type":"INTEGER","nullable":true,"description":"Median area (m²) of the stat deals. NULL when none.","example":99},{"name":"total_volume","type":"INTEGER","nullable":false,"description":"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).","example":182276628050},{"name":"is_incomplete","type":"INTEGER","nullable":false,"description":"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.","example":0}],"indexes":[{"name":"sqlite_autoindex_agg_national_year_1","columns":["property_group","rooms_bucket","year"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[{"column":"property_group","references":"property_groups.key"}],"notes":[],"without_rowid":true},{"name":"agg_district_quarter","kind":"table","group":"aggregates","row_count":763,"description":"Per district × quarter, RESIDENTIAL ONLY (all_residential) — there is no property_group column. Other groups: agg_district_year.","primary_key":["district","quarter"],"columns":[{"name":"district","type":"TEXT","nullable":false,"description":"District name (CBS form) → districts.name.","example":"תל אביב","values":["הדרום","המרכז","הצפון","חיפה","ירושלים","תל אביב","יהודה והשומרון"],"references":"districts.name"},{"name":"quarter","type":"TEXT","nullable":false,"description":"Quarter 'YYYY-Qn'.","example":"2025-Q4"},{"name":"deals","type":"INTEGER","nullable":false,"description":"Count of ALL unique deals in the cell (partial, outlier, multi-unit and discount-project rows included) = transaction volume. Never NULL.","example":4229},{"name":"n_stats","type":"INTEGER","nullable":true,"description":"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'.","example":2620},{"name":"median_price","type":"INTEGER","nullable":true,"description":"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.","example":3256810},{"name":"n_ppsqm","type":"INTEGER","nullable":true,"description":"Number of stat deals that also have price_per_sqm, behind median_ppsqm. 0 for non-residential groups; NULL for 'all'.","example":2535},{"name":"median_ppsqm","type":"INTEGER","nullable":true,"description":"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).","example":38116},{"name":"median_area","type":"INTEGER","nullable":true,"description":"Median area (m²) of the stat deals. NULL when none.","example":89},{"name":"total_volume","type":"INTEGER","nullable":false,"description":"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).","example":13550109042},{"name":"is_incomplete","type":"INTEGER","nullable":false,"description":"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.","example":0}],"indexes":[{"name":"sqlite_autoindex_agg_district_quarter_1","columns":["district","quarter"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[{"column":"district","references":"districts.name"}],"notes":[],"without_rowid":true},{"name":"agg_district_year","kind":"table","group":"aggregates","row_count":2009,"description":"Per district × property_group × year (all groups plus all_residential and all).","primary_key":["district","property_group","year"],"columns":[{"name":"district","type":"TEXT","nullable":false,"description":"District name (CBS form) → districts.name.","example":"תל אביב","values":["ירושלים","המרכז","הצפון","הדרום","חיפה","תל אביב","יהודה והשומרון"],"references":"districts.name"},{"name":"year","type":"INTEGER","nullable":false,"description":"Calendar year.","example":2025},{"name":"property_group","type":"TEXT","nullable":false,"description":"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).","example":"all_residential","values":["all","apartment","all_residential","land","house","commercial","parking","other","agriculture","garden_apartment","penthouse"],"references":"property_groups.key"},{"name":"deals","type":"INTEGER","nullable":false,"description":"Count of ALL unique deals in the cell (partial, outlier, multi-unit and discount-project rows included) = transaction volume. Never NULL.","example":17459},{"name":"n_stats","type":"INTEGER","nullable":true,"description":"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'.","example":10368},{"name":"median_price","type":"INTEGER","nullable":true,"description":"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.","example":3150000},{"name":"n_ppsqm","type":"INTEGER","nullable":true,"description":"Number of stat deals that also have price_per_sqm, behind median_ppsqm. 0 for non-residential groups; NULL for 'all'.","example":10058},{"name":"median_ppsqm","type":"INTEGER","nullable":true,"description":"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).","example":36885},{"name":"median_area","type":"INTEGER","nullable":true,"description":"Median area (m²) of the stat deals. NULL when none.","example":88},{"name":"total_volume","type":"INTEGER","nullable":false,"description":"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).","example":52298333365},{"name":"is_incomplete","type":"INTEGER","nullable":false,"description":"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.","example":0}],"indexes":[{"name":"sqlite_autoindex_agg_district_year_1","columns":["district","property_group","year"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[{"column":"district","references":"districts.name"},{"column":"property_group","references":"property_groups.key"}],"notes":[],"without_rowid":true},{"name":"agg_gush_year","kind":"table","group":"aggregates","row_count":191306,"description":"Per gush × year: deals counts ALL groups; the n/median columns are residential stats. No median_area / total_volume columns.","primary_key":["gush","year"],"columns":[{"name":"gush","type":"INTEGER","nullable":false,"description":"Block number → gushim.gush.","example":6212,"references":"gushim.gush"},{"name":"year","type":"INTEGER","nullable":false,"description":"Calendar year.","example":2025},{"name":"deals","type":"INTEGER","nullable":false,"description":"All deals (all groups) in the gush that year.","example":258},{"name":"n_stats","type":"INTEGER","nullable":true,"description":"Residential stat deals behind median_price.","example":151},{"name":"median_price","type":"INTEGER","nullable":true,"description":"Median residential price in ₪.","example":5534000},{"name":"n_ppsqm","type":"INTEGER","nullable":true,"description":"Residential stat deals with ppsqm.","example":150},{"name":"median_ppsqm","type":"INTEGER","nullable":true,"description":"Median residential ₪/m².","example":65617},{"name":"is_incomplete","type":"INTEGER","nullable":false,"description":"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.","example":0}],"indexes":[{"name":"sqlite_autoindex_agg_gush_year_1","columns":["gush","year"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[{"column":"gush","references":"gushim.gush"}],"notes":[],"without_rowid":true},{"name":"agg_neighborhood_year","kind":"table","group":"aggregates","row_count":32280,"description":"Per neighbourhood × year (1998–2026): deal counts and residential medians, including an existing-stock median.","primary_key":["nbhd_id","year"],"columns":[{"name":"nbhd_id","type":"INTEGER","nullable":false,"description":"Neighbourhood id → neighborhoods.nbhd_id.","example":50005953,"references":"neighborhoods.nbhd_id"},{"name":"year","type":"INTEGER","nullable":false,"description":"Calendar year.","example":2025},{"name":"deals","type":"INTEGER","nullable":false,"description":"All deals (all groups) on the neighbourhood's parcels (same-settlement deals only).","example":171},{"name":"deals_residential","type":"INTEGER","nullable":false,"description":"Residential deals.","example":123},{"name":"n_stats","type":"INTEGER","nullable":false,"description":"Residential stat deals behind median_price.","example":86},{"name":"median_price","type":"INTEGER","nullable":true,"description":"Median residential price in ₪. NULL when n_stats = 0.","example":2812500},{"name":"n_ppsqm","type":"INTEGER","nullable":false,"description":"Residential stat deals with ppsqm.","example":86},{"name":"median_ppsqm","type":"INTEGER","nullable":true,"description":"Median residential ₪/m² (pooled new + existing).","example":54284},{"name":"n_ppsqm_existing","type":"INTEGER","nullable":false,"description":"Existing-stock stat deals with ppsqm.","example":60},{"name":"median_ppsqm_existing","type":"INTEGER","nullable":true,"description":"Median ₪/m² of existing stock.","example":51688},{"name":"is_incomplete","type":"INTEGER","nullable":false,"description":"1 for 2026 (after the stats anchor).","example":0}],"indexes":[{"name":"sqlite_autoindex_agg_neighborhood_year_1","columns":["nbhd_id","year"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[{"column":"nbhd_id","references":"neighborhoods.nbhd_id"}],"notes":[],"without_rowid":true},{"name":"settlements_fts","kind":"fts5","group":"search","row_count":1139,"description":"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":[],"columns":[{"name":"name","type":null,"nullable":true,"description":"Canonical Hebrew name (indexed column; weight it higher in bm25).","example":"תל אביב-יפו"},{"name":"aliases","type":null,"nullable":true,"description":"' | '-joined search aliases: spelling variants, parts of composite names, raw Tax Authority names, CBS names, English names, abbreviations.","example":"Tel Aviv - Yafo | tel aviv | tel aviv - yafo | tlv | אביב | יפו | ת\"א | ת\"א יפו | ת\"א-י…"}],"indexes":[],"foreign_keys":[{"column":"rowid","references":"settlements.code"}],"notes":["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."],"sql":"CREATE VIRTUAL TABLE settlements_fts USING fts5( name, aliases, tokenize = 'unicode61 remove_diacritics 2', prefix = '1 2 3')"},{"name":"streets_fts","kind":"fts5","group":"search","row_count":31687,"description":"Contentless FTS5 index over streets (columns street, settlement, aliases). rowid = streets.id. Columns read back as NULL: join streets to get values.","primary_key":[],"columns":[{"name":"street","type":null,"nullable":true,"description":"Street name (indexed only; contentless → NULL when selected).","example":null},{"name":"settlement","type":null,"nullable":true,"description":"Settlement name plus its search aliases (indexed only).","example":null},{"name":"aliases","type":null,"nullable":true,"description":"Street spelling variants without quotes/geresh, 'רחוב'/'שדרות'/'דרך' prefixes (indexed only).","example":null}],"indexes":[],"foreign_keys":[{"column":"rowid","references":"streets.id"}],"notes":["Column filters: street:\"הרצל\" matches the street name only; settlement:\"תא\"* limits the city. Example: WHERE streets_fts MATCH '{street aliases}:\"רוטשילד\" AND settlement:\"תא\"*'."],"sql":"CREATE VIRTUAL TABLE streets_fts USING fts5(street, settlement, aliases, content='', tokenize='unicode61 remove_diacritics 2', prefix='1 2 3')"},{"name":"neighborhoods_fts","kind":"fts5","group":"search","row_count":1277,"description":"Contentless FTS5 index over neighbourhood names (name, settlement, aliases). rowid = neighborhoods.nbhd_id; join neighborhoods for values.","primary_key":[],"columns":[{"name":"name","type":null,"nullable":true,"description":"Neighbourhood name (indexed only; NULL when selected).","example":null},{"name":"settlement","type":null,"nullable":true,"description":"Settlement name and aliases (indexed only).","example":null},{"name":"aliases","type":null,"nullable":true,"description":"Spelling variants (indexed only).","example":null}],"indexes":[],"foreign_keys":[{"column":"rowid","references":"neighborhoods.nbhd_id"}],"notes":[],"sql":"CREATE VIRTUAL TABLE neighborhoods_fts USING fts5(name, settlement, aliases, content='', tokenize='unicode61 remove_diacritics 2', prefix='1 2 3')"},{"name":"parcels_rtree","kind":"rtree","group":"search","row_count":380764,"description":"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":[],"columns":[{"name":"id","type":"INT","nullable":true,"description":"= parcels.id.","example":91722,"references":"parcels.id"},{"name":"minLat","type":"REAL","nullable":true,"description":"Box south edge (latitude, float32).","example":32.093693},{"name":"maxLat","type":"REAL","nullable":true,"description":"Box north edge (latitude).","example":32.093697},{"name":"minLon","type":"REAL","nullable":true,"description":"Box west edge (longitude).","example":34.783112},{"name":"maxLon","type":"REAL","nullable":true,"description":"Box east edge (longitude).","example":34.783119}],"indexes":[],"foreign_keys":[{"column":"id","references":"parcels.id"}],"notes":["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."],"sql":"CREATE VIRTUAL TABLE parcels_rtree USING rtree(id, minLat, maxLat, minLon, maxLon)"},{"name":"deals_rtree","kind":"view","group":"search","row_count":3175302,"description":"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.","primary_key":[],"columns":[{"name":"id","type":"INTEGER","nullable":true,"description":"= deals.id.","example":3411747,"references":"deals.id"},{"name":"minLat","type":"REAL","nullable":true,"description":"Parcel point box south edge.","example":32.070705},{"name":"maxLat","type":"REAL","nullable":true,"description":"Parcel point box north edge.","example":32.070709},{"name":"minLon","type":"REAL","nullable":true,"description":"Parcel point box west edge.","example":34.780403},{"name":"maxLon","type":"REAL","nullable":true,"description":"Parcel point box east edge.","example":34.780407}],"indexes":[],"foreign_keys":[{"column":"id","references":"deals.id"}],"notes":[],"sql":"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"},{"name":"streets","kind":"table","group":"places","row_count":31687,"description":"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"],"columns":[{"name":"id","type":"INTEGER","nullable":false,"description":"Primary key, stable: settlement_code × 100000 + crc32(street_key) % 100000. → parcel_address.street_id, street_parcels.street_id, streets_fts.rowid.","example":500040063,"references":"streets_fts.rowid"},{"name":"settlement_code","type":"INTEGER","nullable":false,"description":"Settlement → settlements.code.","example":5000,"references":"settlements.code"},{"name":"settlement","type":"TEXT","nullable":true,"description":"Settlement canonical name.","example":"תל אביב-יפו"},{"name":"street","type":"TEXT","nullable":false,"description":"Street display name (most common OSM spelling), Hebrew.","example":"אבן גבירול"},{"name":"street_key","type":"TEXT","nullable":false,"description":"Normalised street key (UNIQUE per settlement).","example":"אבנ גבירול"},{"name":"slug","type":"TEXT","nullable":false,"description":"URL slug, UNIQUE per settlement.","example":"אבן-גבירול"},{"name":"n_parcels","type":"INTEGER","nullable":true,"description":"Parcels on the street (address or nearby-street match).","example":216},{"name":"n_parcels_with_deals","type":"INTEGER","nullable":true,"description":"Of which parcels with deals.","example":180},{"name":"n_parcels_addressed","type":"INTEGER","nullable":true,"description":"Of which parcels with an OSM address on this street.","example":199},{"name":"n_addresses","type":"INTEGER","nullable":true,"description":"OSM address points on the street.","example":286},{"name":"house_min","type":"INTEGER","nullable":true,"description":"Lowest house number. NULL when no addresses.","example":1},{"name":"house_max","type":"INTEGER","nullable":true,"description":"Highest house number. NULL when no addresses.","example":502},{"name":"lat","type":"REAL","nullable":true,"description":"Median address point latitude (else parcel point).","example":32.084388},{"name":"lon","type":"REAL","nullable":true,"description":"Median address point longitude.","example":34.78182},{"name":"bbox_min_lat","type":"REAL","nullable":true,"description":"Parcel-point bbox south.","example":32.071287},{"name":"bbox_min_lon","type":"REAL","nullable":true,"description":"Parcel-point bbox west.","example":34.779922},{"name":"bbox_max_lat","type":"REAL","nullable":true,"description":"Parcel-point bbox north.","example":32.136711},{"name":"bbox_max_lon","type":"REAL","nullable":true,"description":"Parcel-point bbox east.","example":34.7955},{"name":"gushim","type":"TEXT","nullable":true,"description":"Comma-separated gush numbers along the street.","example":"6111,6212,6213,6214,6215,6216,6217,6620,6632,6634,6635,6798,6951,6952,6953,7085,7111"},{"name":"deals_total","type":"INTEGER","nullable":false,"description":"Deals on the street's parcels, all time (a corner parcel counts on each of its streets).","example":2730},{"name":"deals_5y","type":"INTEGER","nullable":false,"description":"Deals in the 5y window.","example":335},{"name":"deals_12m","type":"INTEGER","nullable":false,"description":"Deals in the 12m window.","example":39},{"name":"n_ppsqm_5y","type":"INTEGER","nullable":false,"description":"Residential stat deals with ppsqm, 5y.","example":137},{"name":"median_ppsqm_5y","type":"INTEGER","nullable":true,"description":"Median residential ₪/m², 5y. NULL when n = 0.","example":60930},{"name":"n_ppsqm_12m","type":"INTEGER","nullable":false,"description":"Residential stat deals with ppsqm, 12m.","example":18},{"name":"median_ppsqm_12m","type":"INTEGER","nullable":true,"description":"Median residential ₪/m², 12m.","example":57072},{"name":"last_deal_date","type":"TEXT","nullable":true,"description":"Latest deal date 'YYYY-MM-DD'. NULL when none.","example":"2026-06-03"}],"indexes":[{"name":"ix_streets_settlement_deals","columns":["settlement_code","deals_total"],"unique":false,"origin":"index"},{"name":"ux_streets_slug","columns":["settlement_code","slug"],"unique":true,"origin":"index"},{"name":"ux_streets_key","columns":["settlement_code","street_key"],"unique":true,"origin":"index"}],"foreign_keys":[{"column":"settlement_code","references":"settlements.code"},{"column":"id","references":"streets_fts.rowid"}],"notes":["Street statistics include parcels merely NEAR the street (street_parcels.kind = 'near'). Gate medians on n ≥ 5.","© OpenStreetMap contributors (ODbL)."]},{"name":"street_parcels","kind":"table","group":"places","row_count":259266,"description":"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"],"columns":[{"name":"street_id","type":"INTEGER","nullable":false,"description":"Street → streets.id.","example":500040063,"references":"streets.id"},{"name":"gush","type":"INTEGER","nullable":false,"description":"Block number.","example":6111},{"name":"chelka","type":"INTEGER","nullable":false,"description":"Parcel number. Index (gush, chelka) lets you go parcel → streets.","example":69},{"name":"kind","type":"TEXT","nullable":false,"description":"'address' or 'near' (a nearby street, not the parcel's address).","example":"address","values":["near","address"]}],"indexes":[{"name":"ix_street_parcels_parcel","columns":["gush","chelka"],"unique":false,"origin":"index"},{"name":"sqlite_autoindex_street_parcels_1","columns":["street_id","gush","chelka"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[{"column":"street_id","references":"streets.id"},{"column":"gush, chelka","references":"parcels.(gush, chelka)"}],"notes":[],"without_rowid":true},{"name":"parcel_address","kind":"table","group":"places","row_count":245790,"description":"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"],"columns":[{"name":"gush","type":"INTEGER","nullable":false,"description":"Block number.","example":6212},{"name":"chelka","type":"INTEGER","nullable":false,"description":"Parcel number. PK (gush, chelka).","example":418},{"name":"settlement_code","type":"INTEGER","nullable":true,"description":"Settlement of the address points → settlements.code.","example":5000,"references":"settlements.code"},{"name":"street","type":"TEXT","nullable":true,"description":"Primary street name (most OSM addresses in the parcel), or the nearest named road ≤ 25 m for osm_nearest_street rows.","example":"אבן גבירול"},{"name":"street_key","type":"TEXT","nullable":true,"description":"Normalised street key.","example":"אבנ גבירול"},{"name":"street_id","type":"INTEGER","nullable":true,"description":"→ streets.id. NULL for 20 rows.","example":500040063,"references":"streets.id"},{"name":"house_numbers","type":"TEXT","nullable":true,"description":"Compact sorted house numbers on the primary street, e.g. '12-16, 18'. NULL for nearby-street rows.","example":"183"},{"name":"n_addresses","type":"INTEGER","nullable":true,"description":"Distinct (street, number) OSM addresses in the parcel.","example":1},{"name":"n_streets","type":"INTEGER","nullable":true,"description":"Distinct streets with addresses in the parcel (corner parcels > 1).","example":1},{"name":"other_streets","type":"TEXT","nullable":true,"description":"'street numbers | …' for non-primary streets. NULL when none.","example":"אריאל שרון 50"},{"name":"address_label","type":"TEXT","nullable":true,"description":"Display address, e.g. 'רחוב אבן גבירול 183'. NULL when only a nearby street is known.","example":"רחוב אבן גבירול 183"},{"name":"street_source","type":"TEXT","nullable":false,"description":"'osm_address' (real address), 'osm_addr_place' (addr:place villages) or 'osm_nearest_street' (a NEARBY street, not an address).","example":"osm_address","values":["osm_nearest_street","osm_address","osm_addr_place"]},{"name":"street_dist_m","type":"REAL","nullable":true,"description":"Parcel → road distance in metres for osm_nearest_street rows; NULL otherwise.","example":0},{"name":"n_buildings","type":"INTEGER","nullable":true,"description":"OSM buildings whose representative point is in the parcel.","example":1},{"name":"max_levels","type":"INTEGER","nullable":true,"description":"Max OSM building:levels in the parcel (sparse, lower bound). NULL when unknown.","example":4},{"name":"max_height_m","type":"REAL","nullable":true,"description":"Max OSM building height in metres (sparse). NULL when unknown.","example":5}],"indexes":[{"name":"ix_parcel_address_settlement","columns":["settlement_code","street_key"],"unique":false,"origin":"index"},{"name":"ix_parcel_address_street","columns":["street_id"],"unique":false,"origin":"index"},{"name":"sqlite_autoindex_parcel_address_1","columns":["gush","chelka"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[{"column":"gush, chelka","references":"parcels.(gush, chelka)"},{"column":"street_id","references":"streets.id"},{"column":"settlement_code","references":"settlements.code"}],"notes":["Coverage: ~31.6% of deals have a real address, ~73.5% at least a nearby street. © OpenStreetMap contributors (ODbL)."],"without_rowid":true},{"name":"parcel_buildings","kind":"table","group":"places","row_count":226621,"description":"OSM building summary for every DB parcel with at least one building (226,621 rows), including parcels without an address.","primary_key":["gush","chelka"],"columns":[{"name":"gush","type":"INTEGER","nullable":false,"description":"Block number.","example":6212},{"name":"chelka","type":"INTEGER","nullable":false,"description":"Parcel number. PK (gush, chelka).","example":418},{"name":"n_buildings","type":"INTEGER","nullable":false,"description":"OSM buildings in the parcel.","example":1},{"name":"max_levels","type":"INTEGER","nullable":true,"description":"Max building:levels (sparse). NULL when unknown.","example":4},{"name":"max_height_m","type":"REAL","nullable":true,"description":"Max building height in metres (sparse). NULL when unknown.","example":2}],"indexes":[{"name":"sqlite_autoindex_parcel_buildings_1","columns":["gush","chelka"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[{"column":"gush, chelka","references":"parcels.(gush, chelka)"}],"notes":[],"without_rowid":true},{"name":"neighborhoods","kind":"table","group":"places","row_count":1277,"description":"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"],"columns":[{"name":"nbhd_id","type":"INTEGER","nullable":false,"description":"Primary key, stable: settlement_code × 10000 + crc32(name) % 10000. → parcel_neighborhood.nbhd_id, agg_neighborhood_year.nbhd_id, neighborhoods_fts.rowid.","example":50005953,"references":"neighborhoods_fts.rowid"},{"name":"settlement_code","type":"INTEGER","nullable":false,"description":"Settlement → settlements.code.","example":5000,"references":"settlements.code"},{"name":"settlement","type":"TEXT","nullable":true,"description":"Settlement name.","example":"תל אביב-יפו"},{"name":"name","type":"TEXT","nullable":false,"description":"Neighbourhood name as published (Hebrew; some Arabic, 'שכונה N').","example":"פלורנטין"},{"name":"slug","type":"TEXT","nullable":false,"description":"URL slug, UNIQUE per settlement.","example":"פלורנטין"},{"name":"source","type":"TEXT","nullable":false,"description":"Polygon source: tlv_neighborhoods, jlm_neighborhoods, pt_neighborhoods, cbs_stat2011_nbhd, osm_polygon, osm_point_voronoi.","example":"tlv_neighborhoods","values":["cbs_stat2011_nbhd","jlm_neighborhoods","osm_point_voronoi","tlv_neighborhoods","pt_neighborhoods","osm_polygon"]},{"name":"area_km2","type":"REAL","nullable":true,"description":"Area in km².","example":0.4476},{"name":"lat","type":"REAL","nullable":true,"description":"Label point latitude (pole of inaccessibility).","example":32.057525},{"name":"lon","type":"REAL","nullable":true,"description":"Label point longitude.","example":34.769864},{"name":"bbox_min_lat","type":"REAL","nullable":true,"description":"Polygon bbox south.","example":32.054745},{"name":"bbox_min_lon","type":"REAL","nullable":true,"description":"Polygon bbox west.","example":34.764037},{"name":"bbox_max_lat","type":"REAL","nullable":true,"description":"Polygon bbox north.","example":32.060676},{"name":"bbox_max_lon","type":"REAL","nullable":true,"description":"Polygon bbox east.","example":34.773932},{"name":"n_parcels","type":"INTEGER","nullable":false,"description":"DB parcels mapped to the neighbourhood.","example":608},{"name":"deals_total","type":"INTEGER","nullable":false,"description":"All deals (all property groups, all time) on the neighbourhood's parcels whose settlement equals the neighbourhood's settlement.","example":8001},{"name":"deals_residential_total","type":"INTEGER","nullable":false,"description":"Residential deals, all time.","example":5379},{"name":"first_deal","type":"TEXT","nullable":true,"description":"Date of the first deal, 'YYYY-MM-DD'. NULL when the neighbourhood has no deals.","example":"1998-01-07"},{"name":"last_deal","type":"TEXT","nullable":true,"description":"Date of the latest deal, 'YYYY-MM-DD' (may be after the stats anchor). NULL when no deals.","example":"2026-07-21"},{"name":"deals_12m","type":"INTEGER","nullable":false,"description":"Deals (all groups) in the 12m window (meta.window_12m_start … window_12m_end = stats anchor).","example":117},{"name":"deals_residential_12m","type":"INTEGER","nullable":false,"description":"Residential deals in the 12m window.","example":102},{"name":"deals_prev12m","type":"INTEGER","nullable":false,"description":"Deals (all groups) in the prev12m window. Do not headline deals_12m vs deals_prev12m as an activity change (late reports).","example":204},{"name":"n_stats_12m","type":"INTEGER","nullable":false,"description":"Residential stat deals (in_stats = 1) in 12m behind median_price_12m.","example":85},{"name":"median_price_12m","type":"INTEGER","nullable":true,"description":"Median residential deal price in ₪, 12m, stat rule. NULL when n_stats_12m = 0.","example":2900000},{"name":"n_ppsqm_12m","type":"INTEGER","nullable":false,"description":"Residential stat deals with price_per_sqm in 12m behind median_ppsqm_12m.","example":84},{"name":"median_ppsqm_12m","type":"INTEGER","nullable":true,"description":"Median residential ₪/m², 12m (pooled new + existing stock, discount projects excluded). NULL when n = 0; gate on n_ppsqm_12m ≥ 5.","example":57143},{"name":"n_ppsqm_prev12m","type":"INTEGER","nullable":false,"description":"Same as n_ppsqm_12m for the prev12m window.","example":89},{"name":"median_ppsqm_prev12m","type":"INTEGER","nullable":true,"description":"Median residential ₪/m², prev12m window (pooled).","example":54808},{"name":"n_ppsqm_existing_12m","type":"INTEGER","nullable":false,"description":"Existing-stock (is_new_build = 0) stat deals with ppsqm, 12m.","example":45},{"name":"median_ppsqm_existing_12m","type":"INTEGER","nullable":true,"description":"Median ₪/m² of existing stock, 12m.","example":53125},{"name":"n_ppsqm_existing_prev12m","type":"INTEGER","nullable":false,"description":"Existing-stock stat deals with ppsqm, prev12m.","example":62},{"name":"median_ppsqm_existing_prev12m","type":"INTEGER","nullable":true,"description":"Median ₪/m² of existing stock, prev12m (base of ppsqm_change_pct).","example":51953},{"name":"n_ppsqm_new_12m","type":"INTEGER","nullable":false,"description":"New-build (is_new_build = 1) stat deals with ppsqm, 12m.","example":39},{"name":"median_ppsqm_new_12m","type":"INTEGER","nullable":true,"description":"Median ₪/m² of new builds, 12m. NULL when n = 0.","example":63627},{"name":"ppsqm_change_pct","type":"REAL","nullable":true,"description":"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.","example":-1},{"name":"ppsqm_change_method","type":"TEXT","nullable":true,"description":"'existing' when ppsqm_change_pct is set, else NULL.","example":"existing","values":["existing"]},{"name":"n_ppsqm_5y","type":"INTEGER","nullable":false,"description":"Residential stat deals with ppsqm in the 5y window (2021-07-01 … 2026-06-30).","example":488},{"name":"median_ppsqm_5y","type":"INTEGER","nullable":true,"description":"Median residential ₪/m², 5y window — better coverage for quiet areas.","example":56392},{"name":"median_price_5y","type":"INTEGER","nullable":true,"description":"Median residential price in ₪, 5y window.","example":2950000},{"name":"n_4rooms_12m","type":"INTEGER","nullable":false,"description":"Apartments with 4–4.5 rooms, stat rule, 12m.","example":8},{"name":"median_price_4rooms_12m","type":"INTEGER","nullable":true,"description":"Median price in ₪ of 4–4.5-room apartments, 12m.","example":5100000},{"name":"n_discount_12m","type":"INTEGER","nullable":false,"description":"Discount-project deals (is_discount_project = 1) in 12m — NOT in the medians.","example":0},{"name":"deals_after_anchor","type":"INTEGER","nullable":false,"description":"Deals dated after meta.stats_anchor_date reported so far (incomplete months).","example":1},{"name":"n_parcels_12m","type":"INTEGER","nullable":false,"description":"Distinct parcels behind the 12m ₪/m² median.","example":42},{"name":"max_parcel_share_12m","type":"REAL","nullable":true,"description":"Share (0–1) of the 12m ppsqm deals held by the single largest parcel. High values (> 0.5) mean one project drives the median.","example":0.107},{"name":"rank_ppsqm_in_settlement","type":"INTEGER","nullable":true,"description":"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.","example":10},{"name":"ranked_in_settlement","type":"INTEGER","nullable":true,"description":"How many neighbourhoods of the settlement were ranked. NULL when unranked.","example":29},{"name":"ppsqm_vs_settlement_pct","type":"REAL","nullable":true,"description":"median_ppsqm_12m vs the settlement's, in percent (only when the neighbourhood has ≥ 20 and the settlement ≥ 30 deals).","example":0.5}],"indexes":[{"name":"ux_neighborhoods_slug","columns":["settlement_code","slug"],"unique":true,"origin":"index"},{"name":"ix_neighborhoods_settlement","columns":["settlement_code","deals_12m"],"unique":false,"origin":"index"}],"foreign_keys":[{"column":"settlement_code","references":"settlements.code"},{"column":"nbhd_id","references":"neighborhoods_fts.rowid"}],"notes":["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)."]},{"name":"parcel_neighborhood","kind":"table","group":"places","row_count":288727,"description":"Neighbourhood containing each parcel's DB point (288,727 rows).","primary_key":["gush","chelka"],"columns":[{"name":"gush","type":"INTEGER","nullable":false,"description":"Block number.","example":6212},{"name":"chelka","type":"INTEGER","nullable":false,"description":"Parcel number. PK (gush, chelka).","example":418},{"name":"nbhd_id","type":"INTEGER","nullable":false,"description":"→ neighborhoods.nbhd_id (index (nbhd_id, gush, chelka) for neighbourhood → parcels).","example":50000117,"references":"neighborhoods.nbhd_id"},{"name":"point_source","type":"TEXT","nullable":false,"description":"'db_parcel' (parcel point) or 'db_gush' (parcel sits at its gush centroid: approximate).","example":"db_parcel","values":["db_parcel","db_gush"]},{"name":"same_settlement","type":"INTEGER","nullable":false,"description":"1 when the neighbourhood belongs to the parcel's own settlement; 0 otherwise (6,968 parcels).","example":1}],"indexes":[{"name":"ix_parcel_neighborhood_nbhd","columns":["nbhd_id","gush","chelka"],"unique":false,"origin":"index"},{"name":"sqlite_autoindex_parcel_neighborhood_1","columns":["gush","chelka"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[{"column":"gush, chelka","references":"parcels.(gush, chelka)"},{"column":"nbhd_id","references":"neighborhoods.nbhd_id"}],"notes":["same_settlement = 0 parcels sit in a neighbouring settlement's neighbourhood and are NOT in its statistics."],"without_rowid":true},{"name":"loc_metrics_parcel","kind":"table","group":"location","row_count":381177,"description":"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"],"columns":[{"name":"gush","type":"INTEGER","nullable":false,"description":"Block number.","example":6212},{"name":"chelka","type":"INTEGER","nullable":false,"description":"Parcel number. PK (gush, chelka).","example":418},{"name":"geo_precision","type":"TEXT","nullable":true,"description":"Precision of the point the metrics were computed at: 'parcel', 'gush' (gush centroid) or 'settlement' (settlement centre) — same as parcels.geo_precision.","example":"parcel","values":["parcel","gush","settlement"]},{"name":"dist_rail_m","type":"INTEGER","nullable":true,"description":"Straight-line distance in metres to the nearest heavy-rail station with service on the representative weekday (meta.representative_weekday_gtfs).","example":1807},{"name":"rail_station_id","type":"TEXT","nullable":true,"description":"Id of that rail station → poi_stations.id (e.g. 'rail:17108'); name in poi_stations.name.","example":"rail:17038","references":"poi_stations.id"},{"name":"rail_weekday_trips","type":"INTEGER","nullable":true,"description":"Trains stopping at that station on the representative weekday (both directions).","example":464},{"name":"dist_lrt_m","type":"INTEGER","nullable":true,"description":"Straight-line distance in metres to the nearest OPERATING light-rail station (Tel Aviv Red/Purple, Jerusalem lines per GTFS).","example":1848},{"name":"lrt_station_id","type":"TEXT","nullable":true,"description":"Id of that LRT station → poi_stations.id (e.g. 'lrt:35962-35962').","example":"lrt:20711-20711","references":"poi_stations.id"},{"name":"lrt_weekday_trips","type":"INTEGER","nullable":true,"description":"LRT trips stopping at that station on the representative weekday.","example":619},{"name":"dist_lrt_planned_m","type":"INTEGER","nullable":true,"description":"Straight-line distance in metres to the nearest PLANNED / under-construction LRT station (ministry lrt_stat table; not in GTFS).","example":34},{"name":"lrt_planned_station_id","type":"TEXT","nullable":true,"description":"Id of that planned station → poi_stations.id (e.g. 'lrt:planned136').","example":"lrt:planned078","references":"poi_stations.id"},{"name":"bus_trips_500m","type":"INTEGER","nullable":true,"description":"Weekday bus trips serving stops within 500 m (per route variant the max over its stops, summed).","example":2124},{"name":"bus_lines_500m","type":"INTEGER","nullable":true,"description":"Distinct bus lines with a served stop within 500 m.","example":16},{"name":"bus_stops_500m","type":"INTEGER","nullable":true,"description":"Served bus stops within 500 m.","example":15},{"name":"transit_score","type":"INTEGER","nullable":true,"description":"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.","example":88},{"name":"schools_1km","type":"INTEGER","nullable":true,"description":"Schools within 1 km (kindergartens and institutions of unknown type excluded; placeholder-located institutions excluded).","example":11},{"name":"elementary_1km","type":"INTEGER","nullable":true,"description":"Elementary or combined schools within 1 km.","example":5},{"name":"dist_elementary_m","type":"INTEGER","nullable":true,"description":"Straight-line distance in metres to the nearest elementary / combined school.","example":214},{"name":"kindergartens_500m","type":"INTEGER","nullable":true,"description":"Kindergartens within 500 m.","example":6},{"name":"dist_sea_m","type":"INTEGER","nullable":true,"description":"Straight-line distance in metres to the OSM coastline (Mediterranean or Red Sea; lakes excluded).","example":993},{"name":"sea","type":"TEXT","nullable":true,"description":"Which coastline is nearest: 'mediterranean' or 'red_sea'.","example":"mediterranean","values":["mediterranean","red_sea"]},{"name":"elevation_m","type":"INTEGER","nullable":true,"description":"Surface elevation in metres above the EGM2008 geoid (Copernicus GLO-30; includes roofs/trees in dense blocks).","example":13},{"name":"noise_zone","type":"TEXT","nullable":true,"description":"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).","example":"נתב\"ג: LDN 60-65","values":["נתב\"ג: LDN 60-65","נתב\"ג: LDN 65-70","נתב\"ג: LDN 70-75","נתב\"ג: תחום רעש מטוסים (תמ\"א 35)","חיפה: תחום רעש מטוסים (תמ\"א 35)","מחניים (ראש פינה): תחום רעש מטוסים (תמ\"א 35)","קריית שמונה: תחום רעש מטוסים (תמ\"א 35)","נתב\"ג: LDN 75+","עין שמר: תחום רעש מטוסים (תמ\"א 35)"]},{"name":"noise_ldn_min","type":"INTEGER","nullable":true,"description":"Lower bound of the Ben Gurion LDN band in dB: 60, 65, 70 or 75. NULL outside LDN bands.","example":60}],"indexes":[{"name":"sqlite_autoindex_loc_metrics_parcel_1","columns":["gush","chelka"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[{"column":"gush, chelka","references":"parcels.(gush, chelka)"},{"column":"rail_station_id","references":"poi_stations.id"},{"column":"lrt_station_id","references":"poi_stations.id"},{"column":"lrt_planned_station_id","references":"poi_stations.id"}],"notes":[],"without_rowid":true},{"name":"loc_metrics_gush","kind":"table","group":"location","row_count":12407,"description":"The same location metrics computed at each gush centroid (12,407 rows).","primary_key":["gush"],"columns":[{"name":"gush","type":"INTEGER","nullable":false,"description":"Primary key → gushim.gush.","example":6212,"references":"gushim.gush"},{"name":"geo_source","type":"TEXT","nullable":true,"description":"'gush' (metrics computed at the gush centroid).","example":"gush","values":["gush"]},{"name":"dist_rail_m","type":"INTEGER","nullable":true,"description":"Straight-line distance in metres to the nearest heavy-rail station with service on the representative weekday (meta.representative_weekday_gtfs).","example":1522},{"name":"rail_station_id","type":"TEXT","nullable":true,"description":"Id of that rail station → poi_stations.id (e.g. 'rail:17108'); name in poi_stations.name.","example":"rail:17038","references":"poi_stations.id"},{"name":"rail_weekday_trips","type":"INTEGER","nullable":true,"description":"Trains stopping at that station on the representative weekday (both directions).","example":464},{"name":"dist_lrt_m","type":"INTEGER","nullable":true,"description":"Straight-line distance in metres to the nearest OPERATING light-rail station (Tel Aviv Red/Purple, Jerusalem lines per GTFS).","example":1626},{"name":"lrt_station_id","type":"TEXT","nullable":true,"description":"Id of that LRT station → poi_stations.id (e.g. 'lrt:35962-35962').","example":"lrt:20711-20711","references":"poi_stations.id"},{"name":"lrt_weekday_trips","type":"INTEGER","nullable":true,"description":"LRT trips stopping at that station on the representative weekday.","example":619},{"name":"dist_lrt_planned_m","type":"INTEGER","nullable":true,"description":"Straight-line distance in metres to the nearest PLANNED / under-construction LRT station (ministry lrt_stat table; not in GTFS).","example":439},{"name":"lrt_planned_station_id","type":"TEXT","nullable":true,"description":"Id of that planned station → poi_stations.id (e.g. 'lrt:planned136').","example":"lrt:planned078","references":"poi_stations.id"},{"name":"bus_trips_500m","type":"INTEGER","nullable":true,"description":"Weekday bus trips serving stops within 500 m (per route variant the max over its stops, summed).","example":2892},{"name":"bus_lines_500m","type":"INTEGER","nullable":true,"description":"Distinct bus lines with a served stop within 500 m.","example":20},{"name":"bus_stops_500m","type":"INTEGER","nullable":true,"description":"Served bus stops within 500 m.","example":15},{"name":"transit_score","type":"INTEGER","nullable":true,"description":"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.","example":92},{"name":"schools_1km","type":"INTEGER","nullable":true,"description":"Schools within 1 km (kindergartens and institutions of unknown type excluded; placeholder-located institutions excluded).","example":11},{"name":"elementary_1km","type":"INTEGER","nullable":true,"description":"Elementary or combined schools within 1 km.","example":6},{"name":"dist_elementary_m","type":"INTEGER","nullable":true,"description":"Straight-line distance in metres to the nearest elementary / combined school.","example":183},{"name":"kindergartens_500m","type":"INTEGER","nullable":true,"description":"Kindergartens within 500 m.","example":5},{"name":"dist_sea_m","type":"INTEGER","nullable":true,"description":"Straight-line distance in metres to the OSM coastline (Mediterranean or Red Sea; lakes excluded).","example":1414},{"name":"sea","type":"TEXT","nullable":true,"description":"Which coastline is nearest: 'mediterranean' or 'red_sea'.","example":"mediterranean","values":["mediterranean","red_sea"]},{"name":"elevation_m","type":"INTEGER","nullable":true,"description":"Surface elevation in metres above the EGM2008 geoid (Copernicus GLO-30; includes roofs/trees in dense blocks).","example":10},{"name":"noise_zone","type":"TEXT","nullable":true,"description":"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).","example":"נתב\"ג: LDN 60-65","values":["נתב\"ג: LDN 60-65","נתב\"ג: LDN 65-70","נתב\"ג: LDN 70-75","חיפה: תחום רעש מטוסים (תמ\"א 35)","נתב\"ג: LDN 75+","נתב\"ג: תחום רעש מטוסים (תמ\"א 35)","קריית שמונה: תחום רעש מטוסים (תמ\"א 35)","מחניים (ראש פינה): תחום רעש מטוסים (תמ\"א 35)","עין שמר: תחום רעש מטוסים (תמ\"א 35)"]},{"name":"noise_ldn_min","type":"INTEGER","nullable":true,"description":"Lower bound of the Ben Gurion LDN band in dB: 60, 65, 70 or 75. NULL outside LDN bands.","example":60}],"indexes":[],"foreign_keys":[{"column":"gush","references":"gushim.gush"},{"column":"rail_station_id","references":"poi_stations.id"},{"column":"lrt_station_id","references":"poi_stations.id"},{"column":"lrt_planned_station_id","references":"poi_stations.id"}],"notes":[]},{"name":"poi_stations","kind":"table","group":"location","row_count":371,"description":"371 heavy-rail and light-rail stations (operating, planned, under construction) with weekday service from GTFS.","primary_key":["id"],"columns":[{"name":"id","type":"TEXT","nullable":false,"description":"Primary key: 'rail:<GTFS stop_code>', 'rail:asset<n>', 'lrt:<codes>' or 'lrt:planned<n>'. Referenced by loc_metrics_*.*_station_id.","example":"rail:17038"},{"name":"feature_id","type":"INTEGER","nullable":false,"description":"Integer feature id in the map tiles.","example":19},{"name":"kind","type":"TEXT","nullable":false,"description":"'rail' or 'lrt'.","example":"rail","values":["lrt","rail"]},{"name":"name","type":"TEXT","nullable":true,"description":"Official Ministry of Transport name when matched, e.g. 'תל אביב - סבידור מרכז'.","example":"תל אביב - סבידור מרכז"},{"name":"status","type":"TEXT","nullable":true,"description":"'operating', 'planned', 'under_construction', 'no_service'.","example":"operating","values":["planned","operating","under_construction","no_service"]},{"name":"lat","type":"REAL","nullable":false,"description":"Latitude, WGS84 decimal degrees.","example":32.083715},{"name":"lon","type":"REAL","nullable":false,"description":"Longitude, WGS84 decimal degrees.","example":34.798247},{"name":"weekday_trips","type":"INTEGER","nullable":true,"description":"Trips stopping on the representative weekday. NULL when not in GTFS.","example":464},{"name":"lines","type":"TEXT","nullable":true,"description":"LRT line names serving the station. NULL for rail.","example":"כפיר 1"},{"name":"name_gtfs","type":"TEXT","nullable":true,"description":"Name in the GTFS feed. NULL when not in GTFS.","example":"תל אביב מרכז"},{"name":"source","type":"TEXT","nullable":true,"description":"Where the station comes from (GTFS, rail_stat, lrt_stat per metropolitan area).","example":"GTFS stops.txt + rail_stat","values":["lrt_stat (מטרופולין תל אביב)","lrt_stat (מטרופולין ירושלים)","GTFS (route_type 0)","GTFS stops.txt + rail_stat","lrt_stat (מטרופולין חיפה)"]},{"name":"official_asset_no","type":"INTEGER","nullable":true,"description":"Ministry asset number (rail). NULL otherwise.","example":3700},{"name":"official_status","type":"TEXT","nullable":true,"description":"Ministry status text (Hebrew): 'נוסעים', 'בבניה', 'מתוכננת'.","example":"נוסעים","values":["מתוכננת","נוסעים","בבניה"]}],"indexes":[{"name":"ix_poi_stations_kind","columns":["kind","status"],"unique":false,"origin":"index"},{"name":"sqlite_autoindex_poi_stations_2","columns":["feature_id"],"unique":true,"origin":"unique constraint"},{"name":"sqlite_autoindex_poi_stations_1","columns":["id"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[],"notes":[]},{"name":"poi_schools","kind":"table","group":"location","row_count":28312,"description":"28,312 educational institutions (Ministry of Education) with type, sector, supervision and location. Attributes are 2011–2015.","primary_key":["id"],"columns":[{"name":"id","type":"INTEGER","nullable":false,"description":"Primary key: Ministry of Education institution code (סמל מוסד).","example":110023},{"name":"name","type":"TEXT","nullable":true,"description":"Institution name (Hebrew).","example":"בית יעקב מטרסדורף"},{"name":"kind","type":"TEXT","nullable":true,"description":"kindergarten, elementary, middle, high, secondary, combined, school_unknown, post_secondary, other, unknown.","example":"elementary","values":["kindergarten","unknown","elementary","other","high","secondary","combined","school_unknown","middle","post_secondary"]},{"name":"kind_he","type":"TEXT","nullable":true,"description":"Hebrew label of kind.","example":"יסודי","values":["גן ילדים","לא ידוע","יסודי","מוסד אחר (פנימייה, מתנ\"ס, השלמה...)","תיכון (חטיבה עליונה)","על-יסודי שש-שנתי (ז-יב)","רב-שכבתי (א-ט / א-יב)","בית ספר (שכבות לא ידועות)","חטיבת ביניים","על-תיכוני (יג-יד)"]},{"name":"kind_source","type":"TEXT","nullable":true,"description":"'mosdot' (≤ 2015 attribute file) or 'name' (keyword in the name).","example":"mosdot","values":["mosdot","name"]},{"name":"sector","type":"TEXT","nullable":true,"description":"Sector (Hebrew): יהודי, ערבי, בדואי, דרוזי, צרקסי. NULL when unknown.","example":"יהודי","values":["יהודי","ערבי","בדואי","דרוזי","צרקסי"]},{"name":"supervision","type":"TEXT","nullable":true,"description":"Supervision (Hebrew): ממלכתי (מ\"מ), ממלכתי-דתי (חמ\"ד), חרדי. NULL when unknown.","example":"חרדי","values":["מ\"מ\"","חרדי","\"חמ\"ד"]},{"name":"legal_status","type":"TEXT","nullable":true,"description":"Legal status (Hebrew): רשמי, מוכר, פטור, … NULL when unknown.","example":"מוכר","values":["רשמי","מוכר","ת. י","פטור"]},{"name":"special_ed","type":"INTEGER","nullable":true,"description":"1 = special education. NULL when unknown.","example":0},{"name":"grade_from","type":"INTEGER","nullable":true,"description":"Lowest grade (0 = kindergarten). NULL when unknown.","example":1},{"name":"grade_to","type":"INTEGER","nullable":true,"description":"Highest grade. NULL when unknown.","example":8},{"name":"students","type":"INTEGER","nullable":true,"description":"Number of students (attrs_year). NULL when unknown.","example":1057},{"name":"attrs_year","type":"INTEGER","nullable":true,"description":"Year of the attribute data (2011–2015). NULL when attributes came from the name.","example":2015},{"name":"lat","type":"REAL","nullable":false,"description":"Latitude (converted from the ministry's ITM coordinates).","example":31.79595},{"name":"lon","type":"REAL","nullable":false,"description":"Longitude.","example":35.200045},{"name":"loc_accuracy","type":"TEXT","nullable":true,"description":"Ministry location accuracy (Hebrew): גבוהה מאוד, גבוהה, בינונית, נמוכה.","example":"גבוהה מאוד","values":["גבוהה מאוד","גבוהה","נמוכה","בינונית"]},{"name":"loc_placeholder","type":"INTEGER","nullable":false,"description":"1 = placeholder point (excluded from metrics and tiles).","example":0},{"name":"settlement_code","type":"INTEGER","nullable":true,"description":"→ settlements.code.","example":3000,"references":"settlements.code"},{"name":"settlement_name","type":"TEXT","nullable":true,"description":"Settlement name as given by the ministry.","example":"ירושלים"}],"indexes":[{"name":"ix_poi_schools_settlement","columns":["settlement_code","kind"],"unique":false,"origin":"index"}],"foreign_keys":[{"column":"settlement_code","references":"settlements.code"}],"notes":["loc_placeholder = 1 institutions sit on a placeholder point and are excluded from the loc_metrics_* school counts."]},{"name":"poi_bus_stops","kind":"table","group":"location","row_count":35085,"description":"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":[{"name":"stop_id","type":"TEXT","nullable":false,"description":"Primary key: GTFS stop_id.","example":"13103"},{"name":"stop_code","type":"TEXT","nullable":true,"description":"Public stop code.","example":"21472"},{"name":"name","type":"TEXT","nullable":true,"description":"Stop name (Hebrew).","example":"קניון עזריאלי/דרך מנחם בגין"},{"name":"lat","type":"REAL","nullable":false,"description":"Latitude, WGS84 decimal degrees.","example":32.074584},{"name":"lon","type":"REAL","nullable":false,"description":"Longitude, WGS84 decimal degrees.","example":34.790607},{"name":"weekday_trips","type":"INTEGER","nullable":true,"description":"Trips (all modes) stopping on the representative weekday.","example":3179},{"name":"bus_trips","type":"INTEGER","nullable":true,"description":"Bus trips stopping on the representative weekday.","example":3179},{"name":"n_lines","type":"INTEGER","nullable":true,"description":"Distinct lines serving the stop.","example":98},{"name":"route_types","type":"TEXT","nullable":true,"description":"JSON array of GTFS route_type values (3 = bus, 0 = tram/LRT, 2 = rail, 715 = demand-responsive).","example":"[3]","values":["[3]","[0]","[3,715]","[715]","[2]","[5]","[8]"]},{"name":"city_gtfs","type":"TEXT","nullable":true,"description":"City name in GTFS.","example":"תל אביב יפו"}],"indexes":[{"name":"sqlite_autoindex_poi_bus_stops_1","columns":["stop_id"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[],"notes":[]},{"name":"settlement_socio","kind":"table","group":"society","row_count":1110,"description":"CBS 2021 socio-economic index per settlement (1,110 rows): cluster 1 (lowest) … 10 (highest), index value and rank.","primary_key":["settlement_code"],"columns":[{"name":"settlement_code","type":"INTEGER","nullable":false,"description":"Primary key → settlements.code.","example":5000,"references":"settlements.code"},{"name":"name","type":"TEXT","nullable":true,"description":"Name as published by CBS.","example":"תל אביב-יפו"},{"name":"level","type":"TEXT","nullable":true,"description":"'local_authority', 'rc_locality' (locality inside a regional council) or 'regional_council_fallback' (13 localities carrying their council's value).","example":"local_authority","values":["rc_locality","local_authority","regional_council_fallback"]},{"name":"cluster","type":"INTEGER","nullable":true,"description":"Socio-economic cluster 1 (lowest) … 10 (highest).","example":8},{"name":"index_value","type":"REAL","nullable":true,"description":"Standardised index value (higher = better-off).","example":1.2925},{"name":"rank","type":"INTEGER","nullable":true,"description":"Rank within rank_of (1 = lowest).","example":227},{"name":"rank_of","type":"INTEGER","nullable":true,"description":"Size of the ranking the rank belongs to (255 local authorities or 996 rc localities).","example":255},{"name":"population","type":"INTEGER","nullable":true,"description":"Population used by CBS.","example":466862},{"name":"cluster_2019","type":"INTEGER","nullable":true,"description":"Cluster in the 2019 release.","example":8},{"name":"regional_council","type":"TEXT","nullable":true,"description":"Regional council name (rc localities). NULL otherwise.","example":"לכיש"},{"name":"rc_cluster","type":"INTEGER","nullable":true,"description":"The regional council's own cluster. NULL otherwise.","example":7},{"name":"year","type":"INTEGER","nullable":true,"description":"Index year (2021).","example":2021},{"name":"source","type":"TEXT","nullable":true,"description":"CBS table reference.","example":"CBS socio-economic index 2021, table 2 (https://www.cbs.gov.il/he/publications/DocLib/2…","values":["CBS socio-economic index 2021, table 8 (https://www.cbs.gov.il/he/publications/DocLib/2025/1955/t08.xlsx)","CBS socio-economic index 2021, table 2 (https://www.cbs.gov.il/he/publications/DocLib/2025/1955/t02.xlsx)","CBS socio-economic index 2021, table 2 (https://www.cbs.gov.il/he/publications/DocLib/2025/1955/t02.xlsx) - the locality's regional council (no locality index computed)"]}],"indexes":[],"foreign_keys":[{"column":"settlement_code","references":"settlements.code"}],"notes":[]},{"name":"stat_area_socio","kind":"table","group":"society","row_count":1641,"description":"CBS 2021 socio-economic index per statistical area (1,641 areas in 81 cities / local councils).","primary_key":["stat_area_id"],"columns":[{"name":"stat_area_id","type":"INTEGER","nullable":false,"description":"Primary key = settlement_code × 10000 + stat_area (CBS 2011 geography).","example":50000113},{"name":"settlement_code","type":"INTEGER","nullable":true,"description":"→ settlements.code.","example":5000,"references":"settlements.code"},{"name":"settlement_name","type":"TEXT","nullable":true,"description":"Settlement name.","example":"תל אביב -יפו"},{"name":"stat_area","type":"INTEGER","nullable":true,"description":"Statistical area number within the settlement.","example":113},{"name":"population","type":"INTEGER","nullable":true,"description":"Population of the area.","example":12519},{"name":"index_value","type":"REAL","nullable":true,"description":"Index value.","example":1.9222},{"name":"rank","type":"INTEGER","nullable":true,"description":"Rank among all 1,641 areas (1 = lowest).","example":1566},{"name":"rank_of","type":"INTEGER","nullable":true,"description":"1641.","example":1641},{"name":"cluster","type":"INTEGER","nullable":true,"description":"Cluster 1 … 10.","example":9},{"name":"year","type":"INTEGER","nullable":true,"description":"2021.","example":2021},{"name":"source","type":"TEXT","nullable":true,"description":"CBS table reference.","example":"CBS socio-economic index 2021, table 12 (https://www.cbs.gov.il/he/publications/DocLib/…","values":["CBS socio-economic index 2021, table 12 (https://www.cbs.gov.il/he/publications/DocLib/2025/1955/t12.xlsx)"]}],"indexes":[{"name":"ix_stat_area_socio_settlement","columns":["settlement_code"],"unique":false,"origin":"index"}],"foreign_keys":[{"column":"settlement_code","references":"settlements.code"}],"notes":[]},{"name":"parcel_stat_area_socio","kind":"table","group":"society","row_count":233599,"description":"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"],"columns":[{"name":"gush","type":"INTEGER","nullable":false,"description":"Block number.","example":6212},{"name":"chelka","type":"INTEGER","nullable":false,"description":"Parcel number. PK (gush, chelka).","example":418},{"name":"stat_area_id","type":"INTEGER","nullable":false,"description":"→ stat_area_socio.stat_area_id.","example":50000314,"references":"stat_area_socio.stat_area_id"},{"name":"stat_area_settlement_code","type":"INTEGER","nullable":true,"description":"Settlement of the statistical area (differs from the parcel's for ~1.7%: boundary points).","example":5000},{"name":"cluster","type":"INTEGER","nullable":true,"description":"Cluster 1 … 10.","example":9},{"name":"index_value","type":"REAL","nullable":true,"description":"Index value.","example":1.984}],"indexes":[{"name":"ix_parcel_stat_area_socio_area","columns":["stat_area_id"],"unique":false,"origin":"index"},{"name":"sqlite_autoindex_parcel_stat_area_socio_1","columns":["gush","chelka"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[{"column":"gush, chelka","references":"parcels.(gush, chelka)"},{"column":"stat_area_id","references":"stat_area_socio.stat_area_id"}],"notes":[],"without_rowid":true},{"name":"crime_settlement_year","kind":"table","group":"society","row_count":1235,"description":"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"],"columns":[{"name":"settlement_code","type":"INTEGER","nullable":false,"description":"→ settlements.code.","example":5000,"references":"settlements.code"},{"name":"year","type":"INTEGER","nullable":false,"description":"Year (2021–2025).","example":2025},{"name":"settlement_name","type":"TEXT","nullable":true,"description":"Settlement name.","example":"תל אביב יפו"},{"name":"quarters","type":"INTEGER","nullable":true,"description":"Quarters of data in the year (4 = full year).","example":4},{"name":"cases","type":"INTEGER","nullable":true,"description":"Distinct case files.","example":27484},{"name":"offenses","type":"INTEGER","nullable":true,"description":"Offense records.","example":37064},{"name":"population","type":"INTEGER","nullable":true,"description":"Today's population (used for every year). NULL for some.","example":601640},{"name":"population_source","type":"TEXT","nullable":true,"description":"Where population came from.","example":"nadlan.db settlements.population","values":["nadlan.db settlements.population","geo_settlements (population_authority)"]},{"name":"per_1000","type":"REAL","nullable":true,"description":"Cases per 1,000 residents.","example":45.68},{"name":"per_1000_index","type":"REAL","nullable":true,"description":"per_1000 relative to the national rate of the same year (100 = national). Use this to compare across years.","example":186.4},{"name":"small_population","type":"INTEGER","nullable":true,"description":"1 = population < 2,000 (noisy rates).","example":0},{"name":"top_categories","type":"TEXT","nullable":true,"description":"JSON array [{\"group\", \"cases\"}] of the top 3 categories (Hebrew group names).","example":"[{\"group\":\"עבירות כלפי הרכוש\",\"cases\":17745},{\"group\":\"עבירות סדר ציבורי\",\"cases\":6938}…"},{"name":"police_station","type":"TEXT","nullable":true,"description":"Police station name.","example":"תחנת לב תא ירקון"},{"name":"cases_property","type":"INTEGER","nullable":true,"description":"Property-crime cases.","example":17745},{"name":"cases_public_order","type":"INTEGER","nullable":true,"description":"Public-order cases.","example":6938},{"name":"cases_violence","type":"INTEGER","nullable":true,"description":"Violence cases.","example":3778},{"name":"cases_fraud","type":"INTEGER","nullable":true,"description":"Fraud cases.","example":1270},{"name":"cases_security","type":"INTEGER","nullable":true,"description":"Security-offense cases.","example":224},{"name":"cases_morality_drugs","type":"INTEGER","nullable":true,"description":"Morality / drugs cases.","example":1372},{"name":"cases_sexual","type":"INTEGER","nullable":true,"description":"Sexual-offense cases.","example":396},{"name":"cases_economic","type":"INTEGER","nullable":true,"description":"Economic-offense cases.","example":501},{"name":"cases_traffic","type":"INTEGER","nullable":true,"description":"Traffic-offense cases.","example":268},{"name":"cases_licensing","type":"INTEGER","nullable":true,"description":"Licensing-offense cases.","example":130},{"name":"cases_against_person","type":"INTEGER","nullable":true,"description":"Offenses against a person (other).","example":51},{"name":"cases_administrative","type":"INTEGER","nullable":true,"description":"Administrative-offense cases.","example":5}],"indexes":[{"name":"ix_crime_settlement_year_year","columns":["year","settlement_code"],"unique":false,"origin":"index"},{"name":"sqlite_autoindex_crime_settlement_year_1","columns":["settlement_code","year"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[{"column":"settlement_code","references":"settlements.code"}],"notes":["Compare years with per_1000_index (national = 100 each year): the 2021–2022 files hold about half the cases of 2023–2025."],"without_rowid":true},{"name":"crime_station_year","kind":"table","group":"society","row_count":448,"description":"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":[{"name":"police_station_code","type":"INTEGER","nullable":false,"description":"Police station code (0 = national total row).","example":31311000},{"name":"year","type":"INTEGER","nullable":false,"description":"Year.","example":2023},{"name":"police_station","type":"TEXT","nullable":true,"description":"Police station name (Hebrew).","example":"תחנת באר שבע נגב"},{"name":"cases","type":"INTEGER","nullable":true,"description":"Case files.","example":14431},{"name":"offenses","type":"INTEGER","nullable":true,"description":"Offense records.","example":19147},{"name":"cases_without_settlement","type":"INTEGER","nullable":true,"description":"Cases not attributed to a settlement.","example":652},{"name":"settlement_codes","type":"TEXT","nullable":true,"description":"JSON array of settlement codes served by the station.","example":"[9000]"}],"indexes":[{"name":"sqlite_autoindex_crime_station_year_1","columns":["police_station_code","year"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[],"notes":[],"without_rowid":true},{"name":"macro_month","kind":"table","group":"macro","row_count":345,"description":"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"],"columns":[{"name":"month","type":"TEXT","nullable":false,"description":"Primary key 'YYYY-MM'.","example":"2026-06","references":"agg_national_month.month"},{"name":"cpi","type":"REAL","nullable":true,"description":"Consumer price index, chained, 2024 average = 100.","example":104.8},{"name":"real_factor","type":"REAL","nullable":true,"description":"cpi['2026-06'] / cpi[month]: multiply a nominal ₪ amount of that month to get June-2026 ₪. NULL when no CPI yet.","example":1},{"name":"housing_price_index","type":"REAL","nullable":true,"description":"CBS dwelling price index (1993 = 100).","example":594.8},{"name":"new_dwellings_index","type":"REAL","nullable":true,"description":"CBS new-dwellings price index (from 2017-10). NULL before.","example":541.2},{"name":"rent_index","type":"REAL","nullable":true,"description":"CPI rent component (2024 = 100).","example":106.4},{"name":"owner_housing_index","type":"REAL","nullable":true,"description":"CPI owner-occupied housing component (2024 = 100).","example":107.8},{"name":"boi_rate","type":"REAL","nullable":true,"description":"Bank of Israel policy rate, % (end of month).","example":3.75},{"name":"mortgage_rate_unlinked","type":"REAL","nullable":true,"description":"Average rate on new non-indexed fixed-rate housing loans, % (from 2011-07).","example":4.73},{"name":"mortgage_rate_linked","type":"REAL","nullable":true,"description":"Average rate on new CPI-indexed housing loans, % real (from 2011-07).","example":3.28},{"name":"mortgage_rate_linked_fixed","type":"REAL","nullable":true,"description":"Average rate on new CPI-indexed fixed-rate housing loans, % real (from 2011-07).","example":3.27},{"name":"mortgage_rate_variable_unlinked","type":"REAL","nullable":true,"description":"Average rate on new non-indexed variable-rate (prime-track) housing loans, % — 2016-01 … 2024-01 only.","example":1.57},{"name":"prime_rate","type":"REAL","nullable":true,"description":"Prime rate = boi_rate + 1.5, %.","example":5.25},{"name":"avg_wage","type":"REAL","nullable":true,"description":"Average monthly wage per employee post, nominal ₪, seasonally adjusted.","example":14275.4},{"name":"avg_wage_israeli","type":"REAL","nullable":true,"description":"Same for Israeli employees only.","example":14605.1},{"name":"cpi_base_note","type":"TEXT","nullable":true,"description":"How the CPI series was chained.","example":"chained by CBS's linking rule to the latest base '2024 ממוצע' = 100","values":["chained by CBS's linking rule to the latest base '2024 ממוצע' = 100"]}],"indexes":[{"name":"sqlite_autoindex_macro_month_1","columns":["month"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[{"column":"month","references":"agg_national_month.month"}],"notes":["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."],"without_rowid":true},{"name":"macro_district_month","kind":"table","group":"macro","row_count":630,"description":"CBS dwelling price index per district and month (from 2017-10), linked to the national index.","primary_key":["district","month"],"columns":[{"name":"district","type":"TEXT","nullable":false,"description":"District (CBS form) → districts.name.","example":"תל אביב","values":["הדרום","המרכז","הצפון","חיפה","ירושלים","תל אביב"],"references":"districts.name"},{"name":"month","type":"TEXT","nullable":false,"description":"'YYYY-MM' (first month of the CBS two-month window).","example":"2026-06"},{"name":"housing_price_index","type":"REAL","nullable":true,"description":"District dwelling price index (linked to the national scale).","example":563.2},{"name":"pct_change_month","type":"REAL","nullable":true,"description":"Change vs previous period, %.","example":0.7},{"name":"national_index","type":"REAL","nullable":true,"description":"National index for the same month.","example":594.8},{"name":"cbs_code","type":"INTEGER","nullable":true,"description":"CBS series code.","example":60400}],"indexes":[{"name":"sqlite_autoindex_macro_district_month_1","columns":["district","month"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[{"column":"district","references":"districts.name"}],"notes":[],"without_rowid":true},{"name":"rent_city","kind":"table","group":"macro","row_count":4354,"description":"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"],"columns":[{"name":"area_type","type":"TEXT","nullable":false,"description":"'national', 'district' or 'city'.","example":"city","values":["city","district","national"]},{"name":"area_name","type":"TEXT","nullable":false,"description":"Area name (Hebrew).","example":"תל אביב"},{"name":"rooms_bucket","type":"TEXT","nullable":false,"description":"Rent rooms bucket: '1-2', '2.5-3', '3.5-4', '4.5-6', '4.5+' (2026), 'all'.","example":"all","values":["all","3.5-4","2.5-3","1-2","4.5-6","4.5+"]},{"name":"period","type":"TEXT","nullable":false,"description":"'YYYY-Qn' for quarters or 'YYYY' for years.","example":"2026-Q2"},{"name":"period_type","type":"TEXT","nullable":false,"description":"'quarter' or 'year'.","example":"quarter","values":["quarter","year"]},{"name":"year","type":"INTEGER","nullable":true,"description":"Year of the period.","example":2026},{"name":"settlement_code","type":"INTEGER","nullable":true,"description":"City code → settlements.code (city rows only).","example":5000,"references":"settlements.code"},{"name":"district","type":"TEXT","nullable":true,"description":"District name (district rows).","example":"הדרום","values":["תל אביב","ירושלים","חיפה","הצפון","המרכז","הדרום"]},{"name":"district_code","type":"INTEGER","nullable":true,"description":"CBS district code (district rows).","example":6},{"name":"avg_rent","type":"REAL","nullable":true,"description":"Average monthly rent in ₪.","example":7421.4},{"name":"n","type":"INTEGER","nullable":true,"description":"Sample size (2019–2021 only).","example":70},{"name":"sampling_error","type":"REAL","nullable":true,"description":"Sampling error, ₪ or %.","example":59.5},{"name":"source","type":"TEXT","nullable":true,"description":"CBS table reference.","example":"CBS price statistics bulletin table 4.9 (2026/price08a)","values":["CBS price statistics bulletin table 4.9 (2026/price08a)","CBS price statistics bulletin table 4.9 (2024/price12a)","CBS price statistics bulletin table 4.9 (2022/price12a)","CBS price statistics bulletin table 4.9 (2023/price12a)","CBS price statistics bulletin table 4.9 (2021/price12a)","CBS price statistics bulletin table 4.9 (2020/price12a)","CBS price statistics bulletin table 4.9 (2025/price12a)"]},{"name":"source_file","type":"TEXT","nullable":true,"description":"Source publication URL.","example":"https://www.cbs.gov.il/he/publications/Madad/DocLib/2026/price08a/a4_9_h.xlsx","values":["https://www.cbs.gov.il/he/publications/Madad/DocLib/2026/price08a/a4_9_h.xlsx","https://www.cbs.gov.il/he/publications/Madad/DocLib/2024/price12a/a4_9_h.xls","https://www.cbs.gov.il/he/publications/Madad/DocLib/2022/price12a/a4_9_h.xls","https://www.cbs.gov.il/he/publications/Madad/DocLib/2023/price12a/a4_9_h.xls","https://www.cbs.gov.il/he/publications/Madad/DocLib/2021/price12a/a4_9_h.xls","https://www.cbs.gov.il/he/publications/Madad/DocLib/2020/price12a/a4_9_h.xls","https://www.cbs.gov.il/he/publications/Madad/DocLib/2025/price12a/a4_9_h.xlsx"]}],"indexes":[{"name":"ix_rent_city_settlement","columns":["settlement_code","rooms_bucket","period"],"unique":false,"origin":"index"},{"name":"sqlite_autoindex_rent_city_1","columns":["area_type","area_name","rooms_bucket","period"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[{"column":"settlement_code","references":"settlements.code"}],"notes":["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."],"without_rowid":true},{"name":"gross_yield","kind":"table","group":"macro","row_count":922,"description":"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"],"columns":[{"name":"level","type":"TEXT","nullable":false,"description":"'city', 'district' or 'national'.","example":"city","values":["city","district","national"]},{"name":"area_name","type":"TEXT","nullable":false,"description":"Area name (Hebrew).","example":"תל אביב"},{"name":"window","type":"TEXT","nullable":false,"description":"'12m' (to the stats anchor) or a year '2019' … '2025'.","example":"12m","values":["2025","2024","2023","12m","2022","2021","2020","2019"]},{"name":"rooms_bucket","type":"TEXT","nullable":false,"description":"Rent rooms bucket ('1-2', '2.5-3', '3.5-4', '4.5-6', 'all').","example":"all","values":["all","3.5-4","2.5-3","4.5-6","1-2"]},{"name":"settlement_code","type":"INTEGER","nullable":true,"description":"City code (city rows) → settlements.code.","example":5000,"references":"settlements.code"},{"name":"district","type":"TEXT","nullable":true,"description":"District (district rows).","example":"הדרום","values":["תל אביב","ירושלים","חיפה","הצפון","המרכז","הדרום"]},{"name":"window_start","type":"TEXT","nullable":true,"description":"Window start 'YYYY-MM-DD'.","example":"2025-07-01","values":["2025-07-01","2025-01-01","2024-01-01","2023-01-01","2022-01-01","2021-01-01","2020-01-01","2019-01-01"]},{"name":"window_end","type":"TEXT","nullable":true,"description":"Window end 'YYYY-MM-DD'.","example":"2026-06-30","values":["2026-06-30","2025-12-31","2024-12-31","2023-12-31","2022-12-31","2021-12-31","2020-12-31","2019-12-31"]},{"name":"n_deals","type":"INTEGER","nullable":true,"description":"Existing-stock in-stats apartment deals behind median_price.","example":1693},{"name":"median_price","type":"INTEGER","nullable":true,"description":"Median price in ₪.","example":3720000},{"name":"avg_rent_monthly","type":"REAL","nullable":true,"description":"Average monthly rent in ₪ over rent_periods.","example":7329},{"name":"rent_periods","type":"TEXT","nullable":true,"description":"Comma-separated rent quarters averaged.","example":"2025-Q3,2025-Q4,2026-Q1,2026-Q2","values":["2025-Q3,2025-Q4,2026-Q1,2026-Q2","2025-Q1,2025-Q2,2025-Q3,2025-Q4","2024-Q1,2024-Q2,2024-Q3,2024-Q4","2023-Q1,2023-Q2,2023-Q3,2023-Q4","2022-Q1,2022-Q2,2022-Q3,2022-Q4","2021-Q1,2021-Q2,2021-Q3,2021-Q4","2020-Q1,2020-Q2,2020-Q3,2020-Q4","2019-Q1,2019-Q2,2019-Q3,2019-Q4"]},{"name":"annual_rent","type":"INTEGER","nullable":true,"description":"12 × avg_rent_monthly, ₪.","example":87943},{"name":"price_to_rent","type":"REAL","nullable":true,"description":"median_price / annual_rent.","example":42.3},{"name":"gross_yield_pct","type":"REAL","nullable":true,"description":"annual_rent / median_price × 100, percent (e.g. 3.07).","example":2.36},{"name":"low_n","type":"INTEGER","nullable":true,"description":"1 when n_deals < 20.","example":0},{"name":"rent_note","type":"TEXT","nullable":true,"description":"Note about rent bucket changes. NULL usually.","example":"2026 quarters: CBS rent group '4.5+' (includes > 6 rooms) stands in for 4.5-6","values":["2026 quarters: CBS rent group '4.5+' (includes > 6 rooms) stands in for 4.5-6"]}],"indexes":[{"name":"ix_gross_yield_settlement","columns":["settlement_code","window"],"unique":false,"origin":"index"},{"name":"sqlite_autoindex_gross_yield_1","columns":["level","area_name","window","rooms_bucket"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[{"column":"settlement_code","references":"settlements.code"}],"notes":["Indicative only: a median sale price vs an average rent of other homes."],"without_rowid":true},{"name":"affordability","kind":"table","group":"macro","row_count":237,"description":"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":[{"name":"level","type":"TEXT","nullable":false,"description":"'national' or 'district'.","example":"national","values":["district","national"]},{"name":"area","type":"TEXT","nullable":false,"description":"'ישראל' or the district name.","example":"ישראל","values":["תל אביב","ישראל","ירושלים","חיפה","הצפון","המרכז","הדרום","יהודה והשומרון"]},{"name":"period_kind","type":"TEXT","nullable":false,"description":"'year' or '12m'.","example":"12m","values":["year","12m"]},{"name":"period","type":"TEXT","nullable":false,"description":"The year ('2025') or the 12m range ('2025-07-01..2026-06-30').","example":"2025-07-01..2026-06-30"},{"name":"stock","type":"TEXT","nullable":false,"description":"'all' or 'existing' (existing stock only).","example":"all","values":["all","existing"]},{"name":"median_price_apartment","type":"INTEGER","nullable":true,"description":"Median apartment price in ₪.","example":2150000},{"name":"n_deals","type":"INTEGER","nullable":true,"description":"Deals behind the median.","example":56681},{"name":"avg_monthly_wage","type":"REAL","nullable":true,"description":"National average monthly wage in ₪.","example":13987},{"name":"months_of_wage","type":"REAL","nullable":true,"description":"median_price_apartment / avg_monthly_wage.","example":153.7},{"name":"years_of_wage","type":"REAL","nullable":true,"description":"months_of_wage / 12.","example":12.81},{"name":"wage_scope","type":"TEXT","nullable":true,"description":"'national' (no district wages exist).","example":"national","values":["national"]}],"indexes":[{"name":"sqlite_autoindex_affordability_1","columns":["level","area","period_kind","period","stock"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[],"notes":[],"without_rowid":true},{"name":"renewal_compounds","kind":"table","group":"projects","row_count":978,"description":"978 declared urban-renewal compounds (מתחמי התחדשות עירונית: פינוי-בינוי / עיבוי) with status, plan, units and location. No TAMA 38.","primary_key":["compound_id"],"columns":[{"name":"compound_id","type":"INTEGER","nullable":false,"description":"Primary key (Urban Renewal Authority id). → parcel_renewal.compound_id.","example":8003420},{"name":"name","type":"TEXT","nullable":true,"description":"Compound name (Hebrew).","example":"הדר יוסף"},{"name":"settlement_code","type":"INTEGER","nullable":true,"description":"→ settlements.code.","example":5000,"references":"settlements.code"},{"name":"settlement_name","type":"TEXT","nullable":true,"description":"Settlement name.","example":"תל אביב יפו"},{"name":"track","type":"TEXT","nullable":true,"description":"Normalised track: 'פינוי-בינוי', 'עיבוי', 'משולב פינוי-בינוי ועיבוי', 'משולב', 'טרם הוחלט'. NULL for ~30%.","example":"פינוי-בינוי","values":["פינוי-בינוי","משולב פינוי-בינוי ועיבוי","עיבוי","משולב","טרם הוחלט"]},{"name":"track_raw","type":"TEXT","nullable":true,"description":"Track as published.","example":"פינוי בינוי","values":["פינוי בינוי","משולב פ\"ב-עיבוי","בינוי פינוי","עיבוי","משולב","טרם הוחלט"]},{"name":"declaration_track","type":"TEXT","nullable":true,"description":"'מיסוי' (tax track), 'רשויות' (local-authority track) or 'טרם הוכרז'.","example":"רשויות","values":["מיסוי","רשויות","טרם הוכרז"]},{"name":"status","type":"TEXT","nullable":true,"description":"Planning status (Hebrew), see status_rank.","example":"תכנית מאושרת במימוש","values":["תכנית מאושרת לפני מימוש","תכנון סטטוטורי","תכנית מאושרת - אחרי רישוי","תכנון ראשוני","תכנית מאושרת במימוש"]},{"name":"status_rank","type":"INTEGER","nullable":true,"description":"1 תכנון ראשוני < 2 תכנון סטטוטורי < 3 תכנית מאושרת לפני מימוש < 4 תכנית מאושרת - אחרי רישוי < 5 תכנית מאושרת במימוש.","example":5},{"name":"status_code","type":"INTEGER","nullable":true,"description":"Source status code.","example":4},{"name":"status_date","type":"TEXT","nullable":true,"description":"Date of the status, 'YYYY-MM-DD'.","example":"2003-07-01"},{"name":"in_execution","type":"INTEGER","nullable":true,"description":"1 = in execution.","example":1},{"name":"declared_date","type":"TEXT","nullable":true,"description":"Declaration date.","example":"2006-08-20"},{"name":"plan_valid_date","type":"TEXT","nullable":true,"description":"Plan approval date.","example":"2003-07-01"},{"name":"plan_valid_year","type":"INTEGER","nullable":true,"description":"Plan approval year.","example":2003},{"name":"plan_number","type":"TEXT","nullable":true,"description":"Statutory plan number (e.g. 'גב/490').","example":"תא/מק/2204/א"},{"name":"mavat_url","type":"TEXT","nullable":true,"description":"Link to the plan on mavat.iplan.gov.il.","example":"https://mavat.iplan.gov.il/SV4/1/5051121/310"},{"name":"units_existing","type":"INTEGER","nullable":true,"description":"Existing housing units.","example":666},{"name":"units_added","type":"INTEGER","nullable":true,"description":"Units added by the plan.","example":600},{"name":"units_planned","type":"INTEGER","nullable":true,"description":"Total planned units.","example":1544},{"name":"units_permits","type":"INTEGER","nullable":true,"description":"Units with building permits. NULL when unknown.","example":210},{"name":"area_m2","type":"REAL","nullable":true,"description":"Compound area in m².","example":72382},{"name":"n_parcels","type":"INTEGER","nullable":true,"description":"Parcels in the compound.","example":113},{"name":"gushim","type":"TEXT","nullable":true,"description":"Comma-separated gush numbers.","example":"6636"},{"name":"settlement_code_geo","type":"INTEGER","nullable":true,"description":"Settlement code from the polygon location.","example":5000},{"name":"source_match","type":"TEXT","nullable":true,"description":"'list+polygon', 'list_only' or 'polygon_only'.","example":"list+polygon","values":["list+polygon","list_only","polygon_only"]},{"name":"gis_source","type":"TEXT","nullable":true,"description":"GIS layer the polygon came from.","example":"קליטה ידנית","values":["מנהל התכנון 1","קליטה ידנית","מנהל התכנון 2"]},{"name":"lat","type":"REAL","nullable":true,"description":"Compound centroid latitude.","example":32.108621},{"name":"lon","type":"REAL","nullable":true,"description":"Compound centroid longitude.","example":34.821221},{"name":"has_polygon","type":"INTEGER","nullable":false,"description":"1 when a polygon exists (in the map tiles, not the DB).","example":1}],"indexes":[{"name":"ix_renewal_compounds_settlement","columns":["settlement_code","status_rank"],"unique":false,"origin":"index"}],"foreign_keys":[{"column":"settlement_code","references":"settlements.code"}],"notes":[]},{"name":"parcel_renewal","kind":"table","group":"projects","row_count":26239,"description":"Parcels inside a renewal compound (26,239 rows).","primary_key":["gush","chelka"],"columns":[{"name":"gush","type":"INTEGER","nullable":false,"description":"Block number.","example":310},{"name":"chelka","type":"INTEGER","nullable":false,"description":"Parcel number. PK (gush, chelka).","example":17},{"name":"compound_id","type":"INTEGER","nullable":false,"description":"→ renewal_compounds.compound_id.","example":8002186,"references":"renewal_compounds.compound_id"},{"name":"overlap_share","type":"REAL","nullable":true,"description":"Share of the parcel polygon inside the compound (> 0.5). NULL for point matches.","example":1},{"name":"n_compounds","type":"INTEGER","nullable":true,"description":"Number of compounds the parcel touches.","example":1},{"name":"method","type":"TEXT","nullable":true,"description":"'polygon_overlap', 'centroid_shuma' or 'centroid_cancelled'.","example":"polygon_overlap","values":["polygon_overlap","centroid_shuma","centroid_cancelled"]}],"indexes":[{"name":"ix_parcel_renewal_compound","columns":["compound_id"],"unique":false,"origin":"index"},{"name":"sqlite_autoindex_parcel_renewal_1","columns":["gush","chelka"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[{"column":"gush, chelka","references":"parcels.(gush, chelka)"},{"column":"compound_id","references":"renewal_compounds.compound_id"}],"notes":[],"without_rowid":true},{"name":"gush_renewal","kind":"table","group":"projects","row_count":953,"description":"Urban-renewal summary per gush (953 rows): compounds, approvals and units allocated by area share.","primary_key":["gush"],"columns":[{"name":"gush","type":"INTEGER","nullable":false,"description":"Primary key → gushim.gush.","example":10444,"references":"gushim.gush"},{"name":"n_compounds","type":"INTEGER","nullable":true,"description":"Compounds overlapping the gush.","example":10},{"name":"compound_ids","type":"TEXT","nullable":true,"description":"Comma-separated compound ids.","example":"4353,4383,4425,38941,41540,5000650,8001305,8001307,8001659,8003057"},{"name":"n_approved","type":"INTEGER","nullable":true,"description":"Compounds with status_rank ≥ 3.","example":5},{"name":"n_in_execution","type":"INTEGER","nullable":true,"description":"Compounds in execution.","example":2},{"name":"planned_units","type":"INTEGER","nullable":true,"description":"Planned units allocated to this gush by area share (additive across gushim).","example":10662},{"name":"existing_units","type":"INTEGER","nullable":true,"description":"Existing units allocated by area share.","example":2129},{"name":"added_units","type":"INTEGER","nullable":true,"description":"Added units allocated by area share.","example":8538},{"name":"renewal_area_m2","type":"REAL","nullable":true,"description":"Compound area inside the gush, m².","example":622795},{"name":"n_parcels","type":"INTEGER","nullable":true,"description":"Renewal parcels in the gush.","example":265}],"indexes":[],"foreign_keys":[{"column":"gush","references":"gushim.gush"}],"notes":[]},{"name":"discount_projects","kind":"table","group":"projects","row_count":2087,"description":"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"],"columns":[{"name":"project_id","type":"TEXT","nullable":false,"description":"Primary key: 'moch:<ministry project id>' or 'ila:<tender id>:<plot>'. ← deals.discount_project_id.","example":"moch:52"},{"name":"moch_project_id","type":"INTEGER","nullable":true,"description":"Housing ministry project id. NULL for ILA-only plots.","example":52},{"name":"program","type":"TEXT","nullable":true,"description":"'מחיר למשתכן', 'מחיר מטרה' or 'דיור במחיר מופחת'.","example":"מחיר למשתכן","values":["מחיר למשתכן","מחיר מטרה","דיור במחיר מופחת"]},{"name":"settlement_code","type":"INTEGER","nullable":true,"description":"→ settlements.code.","example":8500,"references":"settlements.code"},{"name":"settlement_name","type":"TEXT","nullable":true,"description":"Settlement name.","example":"רמלה"},{"name":"neighborhood","type":"TEXT","nullable":true,"description":"Neighbourhood name as published. NULL often.","example":"מערב"},{"name":"name","type":"TEXT","nullable":true,"description":"Project name / plot label.","example":"מערב"},{"name":"developer","type":"TEXT","nullable":true,"description":"Developer name (cut at 35 characters).","example":"פרשקובסקי השקעות ובניין בע\"מ"},{"name":"units","type":"INTEGER","nullable":true,"description":"Units in the first lottery. NULL when unknown.","example":572},{"name":"units_local_residents","type":"INTEGER","nullable":true,"description":"Units reserved for local residents.","example":135},{"name":"price_per_sqm","type":"REAL","nullable":true,"description":"Official price per m² in ₪ (for ILA-only plots the winning bid).","example":8768},{"name":"lottery_id","type":"INTEGER","nullable":true,"description":"Lottery id.","example":238},{"name":"lottery_date","type":"TEXT","nullable":true,"description":"Lottery date 'YYYY-MM-DD'.","example":"2017-07-24"},{"name":"signup_end_date","type":"TEXT","nullable":true,"description":"Sign-up end date.","example":"2017-07-09"},{"name":"last_lottery_date","type":"TEXT","nullable":true,"description":"Date of the latest lottery.","example":"2018-11-19"},{"name":"n_lotteries","type":"INTEGER","nullable":true,"description":"Number of lotteries held.","example":3},{"name":"subscribers","type":"INTEGER","nullable":true,"description":"Registered subscribers.","example":1396},{"name":"winners_total","type":"INTEGER","nullable":true,"description":"Total winners across lotteries.","example":572},{"name":"permit_status","type":"TEXT","nullable":true,"description":"Building-permit status (Hebrew).","example":"היתר מלא","values":["היתר מלא","החלטת ועדה (היתר בתנאים)","טרם הוגשה בקשה","הוגשה בקשה","היתר מלא לחלק מהמגרשים","הוגשה בקשה לחלק מהמגרשים","החלטת ועדה (היתר בתנאים) לחלק מהמגרשים"]},{"name":"project_status","type":"TEXT","nullable":true,"description":"Project status (Hebrew).","example":"בחירת דירות","values":["בתהליכי הגרלה","בחירת דירות","בקרת חוזים","בקרה לאחר אכלוס"]},{"name":"marketing_rep","type":"TEXT","nullable":true,"description":"Marketing body: 'משב\"ש' or 'רמ\"י'.","example":"רמ\"י","values":["משב\"ש","רמ\"י"]},{"name":"lottery_round","type":"TEXT","nullable":true,"description":"Lottery round type (Hebrew).","example":"יוני 2017"},{"name":"rmi_tender","type":"TEXT","nullable":true,"description":"Israel Land Authority tender number (e.g. '107/2015').","example":"180/2016"},{"name":"rmi_tender_id","type":"INTEGER","nullable":true,"description":"ILA tender id.","example":20160180},{"name":"rmi_committee_date","type":"TEXT","nullable":true,"description":"ILA committee date.","example":"2016-12-26"},{"name":"winning_bid","type":"REAL","nullable":true,"description":"Winning bid (₪ per m² or per unit as published).","example":8657},{"name":"plans","type":"TEXT","nullable":true,"description":"Plan and plot references.","example":"לה/6/170 מגרש 201"},{"name":"gush","type":"INTEGER","nullable":true,"description":"Main gush. NULL when unlocated.","example":7323,"references":"gushim.gush"},{"name":"chelka","type":"INTEGER","nullable":true,"description":"Main chelka. NULL when unknown.","example":17},{"name":"gushim","type":"TEXT","nullable":true,"description":"Comma-separated gushim.","example":"7323,4351"},{"name":"n_parcels","type":"INTEGER","nullable":true,"description":"Parcels linked.","example":3},{"name":"lat","type":"REAL","nullable":true,"description":"Latitude. NULL for ~289 unlocated projects.","example":31.932631},{"name":"lon","type":"REAL","nullable":true,"description":"Longitude.","example":34.850064},{"name":"location_source","type":"TEXT","nullable":true,"description":"'moch_gis_polygon', 'ila_parcels', 'gush_centroid' or 'none'.","example":"moch_gis_polygon","values":["moch_gis_polygon","ila_parcels","gush_centroid","none"]},{"name":"source","type":"TEXT","nullable":true,"description":"Source datasets joined.","example":"moch_lotteries+moch_gis_2019+ila_tenders","values":["ila_tenders","moch_lotteries+ila_tenders","moch_lotteries+moch_gis_2019+ila_tenders","moch_lotteries+moch_gis_2019","moch_lotteries","moch_gis_2019","moch_gis_2019+ila_tenders"]},{"name":"has_polygon","type":"INTEGER","nullable":false,"description":"1 when a project polygon exists.","example":1},{"name":"n_deals_official","type":"INTEGER","nullable":false,"description":"Deals with deals.discount_project_id = project_id (capped at max(units, winners_total)).","example":572}],"indexes":[{"name":"ix_discount_projects_gush","columns":["gush"],"unique":false,"origin":"index"},{"name":"ix_discount_projects_settlement","columns":["settlement_code","lottery_date"],"unique":false,"origin":"index"},{"name":"sqlite_autoindex_discount_projects_1","columns":["project_id"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[{"column":"settlement_code","references":"settlements.code"},{"column":"gush","references":"gushim.gush"}],"notes":["Attribution is by place, time and price, not by buyer: say 'deals attributed to the project'.","Tracker covers 2016-02 … 2025-01."]},{"name":"discount_project_parcels","kind":"table","group":"projects","row_count":7275,"description":"Parcels (or whole gushim when chelka is NULL) of each discount project (7,275 rows).","primary_key":["rowid"],"columns":[{"name":"project_id","type":"TEXT","nullable":false,"description":"→ discount_projects.project_id.","example":"moch:50845","references":"discount_projects.project_id"},{"name":"gush","type":"INTEGER","nullable":false,"description":"Block number.","example":187},{"name":"chelka","type":"INTEGER","nullable":true,"description":"Parcel number; NULL when only the gush is known.","example":12},{"name":"source","type":"TEXT","nullable":true,"description":"'ila_tender', 'moch_gis_overlap' or both.","example":"ila_tender","values":["ila_tender","moch_gis_overlap","ila_tender+moch_gis_overlap"]}],"indexes":[{"name":"ix_discount_project_parcels_project","columns":["project_id"],"unique":false,"origin":"index"},{"name":"ix_discount_project_parcels_parcel","columns":["gush","chelka"],"unique":false,"origin":"index"}],"foreign_keys":[{"column":"project_id","references":"discount_projects.project_id"},{"column":"gush, chelka","references":"parcels.(gush, chelka)"}],"notes":[]},{"name":"discount_match","kind":"table","group":"projects","row_count":8157,"description":"Validation table of the discount-project flag per gush × deal year × official project: flagged vs unflagged counts and medians.","primary_key":["rowid"],"columns":[{"name":"gush","type":"INTEGER","nullable":false,"description":"Block number.","example":187,"references":"gushim.gush"},{"name":"deal_year","type":"INTEGER","nullable":false,"description":"Deal year.","example":2021},{"name":"official_project_id","type":"TEXT","nullable":true,"description":"→ discount_projects.project_id. NULL for flagged_no_located_project rows.","example":"moch:50845","references":"discount_projects.project_id"},{"name":"component","type":"TEXT","nullable":true,"description":"Group of projects sharing gushim (attribution unit).","example":"2767"},{"name":"n_flagged","type":"INTEGER","nullable":true,"description":"Deals flagged is_discount_project.","example":109},{"name":"n_unflagged_newbuild","type":"INTEGER","nullable":true,"description":"Unflagged new-build deals.","example":21},{"name":"n_unflagged_near_official_price","type":"INTEGER","nullable":true,"description":"Unflagged deals priced near the official ₪/m².","example":0},{"name":"n_other_residential","type":"INTEGER","nullable":true,"description":"Other residential deals.","example":8},{"name":"n_in_project_parcels","type":"INTEGER","nullable":true,"description":"Deals on the project's own parcels.","example":107},{"name":"n_unflagged_near_price_in_parcels","type":"INTEGER","nullable":true,"description":"Unflagged near-price deals on the project's parcels.","example":0},{"name":"median_ppsqm_flagged","type":"REAL","nullable":true,"description":"Median ₪/m² of flagged deals.","example":11774},{"name":"median_ppsqm_unflagged_new","type":"REAL","nullable":true,"description":"Median ₪/m² of unflagged new builds.","example":15899},{"name":"official_price_per_sqm","type":"REAL","nullable":true,"description":"Official project ₪/m².","example":8720},{"name":"match_kind","type":"TEXT","nullable":true,"description":"'official_project' or 'flagged_no_located_project'.","example":"official_project","values":["official_project","flagged_no_located_project"]},{"name":"settlement_code","type":"INTEGER","nullable":true,"description":"Settlement (flagged_no_located_project rows).","example":1034},{"name":"settlement_has_official_project","type":"INTEGER","nullable":true,"description":"1 when the settlement has any official project.","example":0}],"indexes":[{"name":"ix_discount_match_gush","columns":["gush","deal_year"],"unique":false,"origin":"index"}],"foreign_keys":[{"column":"gush","references":"gushim.gush"},{"column":"official_project_id","references":"discount_projects.project_id"}],"notes":[]},{"name":"property_groups","kind":"table","group":"reference","row_count":9,"description":"The 9 normalised property groups (deals.property_group) with Hebrew labels, residential flag, display order, icon name and deal count.","primary_key":["key"],"columns":[{"name":"key","type":"TEXT","nullable":false,"description":"Primary key: apartment, garden_apartment, penthouse, house, land, commercial, agriculture, parking, other.","example":"apartment","values":["agriculture","apartment","commercial","garden_apartment","house","land","other","parking","penthouse"]},{"name":"label_he","type":"TEXT","nullable":false,"description":"Hebrew singular label, e.g. 'דירה'.","example":"דירה","values":["קרקע","נכס מסחרי","נכס חקלאי","חניה / מחסן","דירת גן","דירת גג","דירה","בית פרטי","אחר"]},{"name":"label_plural_he","type":"TEXT","nullable":false,"description":"Hebrew plural label, e.g. 'דירות'.","example":"דירות","values":["קרקעות ומגרשים","עסקאות אחרות","נכסים מסחריים","חקלאות ונחלות","חניות ומחסנים","דירות גן","דירות גג ופנטהאוזים","דירות","בתים פרטיים וקוטג'ים"]},{"name":"is_residential","type":"INTEGER","nullable":false,"description":"1 for apartment, garden_apartment, penthouse, house.","example":1},{"name":"sort","type":"INTEGER","nullable":false,"description":"Display order (1 = apartment).","example":1},{"name":"icon","type":"TEXT","nullable":false,"description":"lucide icon name used by the site (kebab-case).","example":"building-2","values":["tractor","store","square-parking","shapes","land-plot","house","flower-2","crown","building-2"]},{"name":"deals","type":"INTEGER","nullable":false,"description":"Number of deals in the group.","example":2261290}],"indexes":[{"name":"sqlite_autoindex_property_groups_1","columns":["key"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[],"notes":[]},{"name":"deal_natures","kind":"table","group":"reference","row_count":47,"description":"Mapping of the 47 raw Hebrew transaction types (deals.deal_nature, מהות) to a property_group, with deal counts.","primary_key":["raw"],"columns":[{"name":"raw","type":"TEXT","nullable":false,"description":"Primary key: raw Hebrew deal nature as in deals.deal_nature, e.g. 'דירה בבית קומות'.","example":"דירה בבית קומות"},{"name":"property_group","type":"TEXT","nullable":false,"description":"Property group it maps to → property_groups.key.","example":"apartment","values":["commercial","other","land","agriculture","house","parking","apartment","penthouse","garden_apartment"],"references":"property_groups.key"},{"name":"deals","type":"INTEGER","nullable":false,"description":"Number of deals with this raw value.","example":1946146}],"indexes":[{"name":"sqlite_autoindex_deal_natures_1","columns":["raw"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[{"column":"property_group","references":"property_groups.key"}],"notes":[]},{"name":"meta","kind":"table","group":"reference","row_count":89,"description":"Key/value build metadata (all values TEXT; CAST as needed, some are JSON): schema_version, built_at, min_date/max_date, stats_anchor_date, window_*_start/end, national_* headline statistics, thresholds, counts and data sources.","primary_key":["key"],"columns":[{"name":"key","type":"TEXT","nullable":false,"description":"Primary key: metadata key (see the meta key list in the docs).","example":"stats_anchor_date"},{"name":"value","type":"TEXT","nullable":true,"description":"Value as TEXT (numbers, dates, or JSON).","example":"2026-06-30"}],"indexes":[{"name":"sqlite_autoindex_meta_1","columns":["key"],"unique":true,"origin":"primary key","primary_key":true}],"foreign_keys":[],"notes":["Read window bounds from here instead of hard-coding dates: (SELECT value FROM meta WHERE key = 'window_12m_start').","JSON values (sources, enrich_counts, months_completeness, …) can be unpacked with json_each()."]}],"conventions":[{"id":"engine","title":"Engine & dialect","body":"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."},{"id":"types","title":"Types, units and NULLs","body":"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."},{"id":"dates","title":"Dates and periods","body":"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."},{"id":"hebrew","title":"Hebrew text","body":"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."},{"id":"codes","title":"Identifiers and join keys","body":"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."},{"id":"stat-rule","title":"The stat rule (in_stats)","body":"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."},{"id":"ppsqm","title":"ppsqm = price per square metre","body":"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."},{"id":"windows","title":"Time windows end at the stats anchor","body":"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')."},{"id":"incomplete","title":"Incomplete months (is_incomplete)","body":"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."},{"id":"changes","title":"Price changes are existing-stock changes","body":"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."},{"id":"partial","title":"Partial deals, outliers, multi-unit rows","body":"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."},{"id":"discount","title":"Discount lottery projects","body":"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."},{"id":"geo","title":"Geo precision","body":"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."},{"id":"fts","title":"Full-text search (FTS5) with Hebrew","body":"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."},{"id":"rtree","title":"Bounding boxes (R*Tree)","body":"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."},{"id":"performance","title":"Performance","body":"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."},{"id":"pseudo-groups","title":"Pseudo-groups and buckets in agg_* tables","body":"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."},{"id":"real-prices","title":"Real (inflation-adjusted) prices","body":"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."},{"id":"attribution","title":"Attribution","body":"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."}],"functions":["->","->>","abs","acos","acosh","asin","asinh","atan","atan2","atanh","avg","bm25","ceil","ceiling","changes","char","coalesce","concat","concat_ws","cos","cosh","count","cume_dist","current_date","current_time","current_timestamp","date","datetime","degrees","dense_rank","exp","first_value","floor","format","fts3_tokenizer","fts5","fts5_get_locale","fts5_insttoken","fts5_locale","fts5_source_id","geopoly_area","geopoly_bbox","geopoly_blob","geopoly_ccw","geopoly_contains_point","geopoly_debug","geopoly_group_bbox","geopoly_json","geopoly_overlap","geopoly_regular","geopoly_svg","geopoly_within","geopoly_xform","glob","group_concat","hex","highlight","if","ifnull","iif","instr","json","json_array","json_array_insert","json_array_length","json_error_position","json_extract","json_group_array","json_group_object","json_insert","json_object","json_patch","json_pretty","json_quote","json_remove","json_replace","json_set","json_type","json_valid","jsonb","jsonb_array","jsonb_array_insert","jsonb_extract","jsonb_group_array","jsonb_group_object","jsonb_insert","jsonb_object","jsonb_patch","jsonb_remove","jsonb_replace","jsonb_set","julianday","lag","last_insert_rowid","last_value","lead","length","like","likelihood","likely","ln","load_extension","log","log10","log2","lower","ltrim","match","matchinfo","max","median","min","mod","nth_value","ntile","nullif","octet_length","offsets","optimize","percent_rank","percentile","percentile_cont","percentile_disc","pi","pow","power","printf","quote","radians","random","randomblob","rank","replace","round","row_number","rtreecheck","rtreedepth","rtreenode","rtrim","sign","sin","sinh","snippet","soundex","sqlite_compileoption_get","sqlite_compileoption_used","sqlite_log","sqlite_source_id","sqlite_version","sqrt","strftime","string_agg","substr","substring","subtype","sum","tan","tanh","time","timediff","total","total_changes","trim","trunc","typeof","unhex","unicode","unistr","unistr_quote","unixepoch","unlikely","upper","zeroblob"]}