Complete administrative hierarchy with consistent Arabic and English names at every level.
| File | Records | Description |
|---|---|---|
sa_regions.parquet | 13 | Regions (المناطق) |
sa_governorates.parquet | 152 | Governorates (المحافظات) |
sa_municipalities.parquet | 285 | Municipalities (البلديات) |
sa_districts.parquet | 5,484 | Districts / Neighborhoods (الأحياء) |
sa_settlements.parquet | 21,450 | All cities and villages (المدن والقرى) |
sa_major_cities.parquet | 220 | Major cities with full hierarchy cross-references |
| System | Chain |
|---|---|
| Regional | Region → Governorate → Center → City/Village |
| Municipal | Region → Governorate → Municipality → District |
Both share Region and Governorate. Settlements bridge both via center_id + municipality_id.
| Column | Type | Description |
|---|---|---|
{level}_id | VARCHAR | Unique ID |
{level}_name_ar | VARCHAR | Arabic name |
{level}_name_en | VARCHAR | English name |
Parent *_id, *_name_ar, *_name_en | VARCHAR | Denormalized parent hierarchy |
centroid | GEOMETRY | Point centroid |
geometry | GEOMETRY | Polygon boundary |
| Column | Type | Description |
|---|---|---|
city_id | VARCHAR | Settlement ID |
city_name_ar / city_name_en | VARCHAR | Settlement name |
city_type | VARCHAR | مدينة (city) or قرية (village) |
center_id / center_name_* | VARCHAR | Administrative center |
municipality_id / municipality_name_* | VARCHAR | Municipality |
governorate_id / governorate_name_* | VARCHAR | Governorate |
region_id / region_name_* | VARCHAR | Region |
is_major_city | BOOLEAN | Flag for 220 major cities |
longitude / |
220 major cities with full hierarchy and alternate names.
| Column | Type | Description |
|---|---|---|
id | INTEGER | Row ID |
city_id | VARCHAR | Settlement ID (links to sa_settlements) |
city_name_ar / city_name_en | VARCHAR | Primary name |
alt_name_ar / alt_name_en | VARCHAR | Alternate name variant |
city_class | VARCHAR | Classification |
city_category | VARCHAR | Category |
city_type | VARCHAR | مدينة (city) |
city_grade | INTEGER | City grade |
admin_level | VARCHAR | Admin level |
center_id / |
217/220 cities have full hierarchy IDs. 3 cities (الجرارية, الشقيق, عرعر) have governorate/region from spatial containment only.
All queries below require these extensions and settings:
All cities in Riyadh Region:
| city_name_en | city_type | governorate_name_en | municipality_name_en |
|---|---|---|---|
| Riyadh | مدينة | Riyadh Region Principality | Riyadh |
| Arqa | مدينة | Riyadh Region Principality | Riyadh |
| Al Haier | مدينة | Riyadh Region Principality | Riyadh |
| Diriyah | مدينة | Ad Diriyah | Diriyah |
| Al Uyaynah | مدينة | Ad Diriyah | Al Uyaynah |
| ... | 77 rows total |
District count per municipality:
| municipality_name_en | districts |
|---|---|
| AlAhsa | 284 |
| Jeddah | 208 |
| Riyadh | 189 |
| Buraidah | 159 |
| Taif | 158 |
| ... | 285 municipalities total |
Major cities with full hierarchy:
| city_name_en | governorate_name_en | region_name_en | municipality_name_en |
|---|---|---|---|
| Al Baha | Al Baha Region Principality | Al Bahah Region | Al Baha |
| Baljurashi | Biljurashi | Al Bahah Region | Baljurashi |
| Dawmat Al Jandal | Dawmat Al Jandal | Al Jawf Region | Dawmat Al Jandal |
| Sakaka | Al Jawf Region Principality | Al Jawf Region | Sakaka |
| Ar'ar | Northern Borders Region Principality | Northern Borders Region | Arar |
| ... | 220 rows total |
Region → Governorate join: count governorates per region
| region_name_en | region_name_ar | governorates |
|---|---|---|
| Riyadh Region | منطقة الرياض | 23 |
| Asir Region | منطقة عسير | 18 |
| Makkah Al Mukarramah Region | منطقة مكة المكرمة | 17 |
| Jazan Region | منطقة جازان | 17 |
| Al Qassim Region | منطقة القصيم | 13 |
| ... | 13 rows total |
Full 4-level join: Region → Governorate → Municipality → District
| region_name_en | governorates | municipalities | districts |
|---|---|---|---|
| Riyadh Region | 23 | 48 | 1205 |
| Eastern Region | 10 | 19 | 823 |
| Makkah Al Mukarramah Region | 15 | 21 | 750 |
| Asir Region | 14 | 21 | 553 |
| Al Qassim Region | 11 | 24 | 540 |
| ... | 13 rows total |
3-level join: top governorates by district count
| governorate_name_en | region_name_en | municipalities | districts |
|---|---|---|---|
| Al Ahsa | Eastern Region | 1 | 284 |
| Al Qassim Region Principality | Al Qassim Region | 11 | 254 |
| Jiddah | Makkah Al Mukarramah Region | 1 | 208 |
| Riyadh Region Principality | Riyadh Region | 1 | 189 |
| Al Taif | Makkah Al Mukarramah Region | 1 | 158 |
Settlement breakdown: cities vs villages per region
| region_name_en | cities | villages | total |
|---|---|---|---|
| Asir Region | 43 | 5981 | 6025 |
| Makkah Al Mukarramah Region | 51 | 4564 | 4615 |
| Jazan Region | 39 | 3818 | 3857 |
| Riyadh Region | 77 | 1584 | 1661 |
| Al Madinah Al Munawwarah Region | 23 | 1496 | 1521 |
| ... | 13 rows total |
Major cities cross-referenced with settlements (Arabic alternate names)
| city_name_en | city_name_ar | alt_name_ar | governorate_name_en | center_name_en | offset_m |
|---|---|---|---|---|---|
| Al Ahmar | الأحمر | الاحمر | Al Aflaj | NULL | 922 |
| Al Dahena Badia | الداهنة | باديه الداهنه | Shaqra | Al Dahena Badia | 345 |
| Al Fao | قرية | الفاو | Wadi Ad Dawasir | Al Fao | 2076 |
| Al Holwah | الحلوة | الحلوه | Hawtat Bani Tamim | NULL | 620 |
| Al Ithnayn | السوق | الاثنين | Asir Region Principality | NULL | 648 |
Reverse geocode a point through all admin levels (24.7136, 46.6753 = Riyadh)
| level | name_en | name_ar |
|---|---|---|
| Region | Riyadh Region | منطقة الرياض |
| Governorate | Riyadh Region Principality | امارة منطقة الرياض |
| Municipality | Riyadh | الرياض |
| District | Al Wurud | الورود |
Spatial join: which governorate does each major city fall in?
| city_name_en | city_name_ar | spatial_governorate | declared_governorate | status |
|---|---|---|---|---|
| Alzahran | الظهران | Al Khubar | Eastern Region Principality | mismatch |
| As Safaniyah | السفانية | Al Khafji | Al Nuayriyah | mismatch |
| Bani Hasan | الربيان | Al Baha Region Principality | Bani Hasan | mismatch |
| Abha | أبها | Asir Region Principality | Asir Region Principality | match |
| Abu Arish | أبو عريش | Abu Arish | Abu Arish | match |
| ... | 220 rows total |
Major cities spatially joined with districts (point-in-polygon)
| city_name_en | city_name_ar | governorate_name_en | containing_district | containing_municipality |
|---|---|---|---|---|
| Afif | عفيف | Afif | Al Fakhriyah | Afif |
| Al Aflaj | ليلى | Al Aflaj | As Salhiyah | Aflaj |
| Al Ahmar | الأحمر | Al Aflaj | Al Hazm | Al Ahmar |
| Al Dilam | الدلم | Ad Dilam | Ad Dirah | Dilam |
| Al Ghat | الغاط | Al Ghat | Al Muruj | Al Ghat |
Nearest major city to each region centroid
| region_name_en | nearest_major_city | distance_m |
|---|---|---|
| Al Bahah Region | Bani Hasan | 5130 |
| Al Jawf Region | Dawmat Al Jandal | 53757 |
| Al Madinah Al Munawwarah Region | Madinah | 29877 |
| Al Qassim Region | Alfwylq | 14469 |
| Asir Region | Al Ithnayn | 86120 |
| ... | 13 rows total |
Region area (km²) and settlement density
| region_name_en | area_km2 | settlements | density_per_km2 |
|---|---|---|---|
| Eastern Region | 371888 | 324 | 0.00 |
| Riyadh Region | 281170 | 1661 | 0.01 |
| Tabuk Region | 128275 | 323 | 0.00 |
| Al Madinah Al Munawwarah Region | 122053 | 1521 | 0.01 |
| Makkah Al Mukarramah Region | 114975 | 4615 | 0.04 |
| ... | 13 rows total |
Municipality settlement density
| municipality_name_en | governorate_name_en | settlements | area_km2 |
|---|---|---|---|
| Addayer | Ad Dair | 835 | 612.9 |
| Ragal Almaa | Rijal Al Ma | 809 | 883.8 |
| Al Aridah | Al Aridah | 625 | 510.8 |
| Muhayil | Muhayil | 556 | 2208.3 |
| Bariq | Bariq | 533 | 1338.0 |
H3 queries require an additional extension:
Settlement density per H3 hex (resolution 5, ~253 km²)
| h3_index | settlements | region |
|---|---|---|
| 85521c13fffffff | 358 | Jazan Region |
| 85521c1bfffffff | 347 | Jazan Region |
| 85521cc7fffffff | 286 | Jazan Region |
| 85521523fffffff | 252 | Asir Region |
| 85521527fffffff | 248 | Asir Region |
H3 parent cells (res 3) covering the most settlements
| h3_parent | settlements | regions_covered |
|---|---|---|
| 83521cfffffffff | 3401 | 3 |
| 835215fffffffff | 2602 | 3 |
| 835230fffffffff | 2301 | 2 |
| 835233fffffffff | 2018 | 3 |
| 835202fffffffff | 1168 | 2 |
K-ring: settlements within 1-ring of Riyadh (24.7136, 46.6753)
| h3_cell | settlements | cities | villages |
|---|---|---|---|
| 855354a7fffffff | 2 | 2 | 0 |
| 855355d3fffffff | 1 | 1 | 0 |
H3 grid distance between two cities (Riyadh <> Jeddah)
| from_city | to_city | from_h3 | to_h3 | h3_grid_distance |
|---|---|---|---|---|
| Riyadh | Jeddah | 855355d3fffffff | 8553a957fffffff | 56 |
Multi-resolution compaction: compact settlement H3 cells
| cells_before_compact | cells_after_compact |
|---|---|
| 2375 | 1762 |
H3 hex boundary as WKT polygon (densest hexes)
| h3_index | settlements |
|---|---|
| 85521c13fffffff | 358 |
| 85521c1bfffffff | 347 |
| 85521cc7fffffff | 286 |
hex_boundary_wktcolumn contains full WKT POLYGON geometries (omitted for brevity).
Multi-resolution H3 cells for a single point (Riyadh)
| res | h3_cell |
|---|---|
| 3 | 835373fffffffff |
| 4 | 8453737ffffffff |
| 5 | 8553736bfffffff |
| 6 | 86537369fffffff |
| 7 | 87537369affffff |
latitude| DOUBLE |
| Coordinates |
geometry | GEOMETRY | Point |
center_name_*| VARCHAR |
| Administrative center |
municipality_id / municipality_name_* | VARCHAR | Municipality |
governorate_id / governorate_name_* | VARCHAR | Governorate |
region_id / region_name_* | VARCHAR | Region |
amana_id / amana_name_* | VARCHAR | Municipal authority (أمانة) |
longitude / latitude | DOUBLE | Coordinates |
geometry | GEOMETRY | Point |
1graph TD
2 R["🏛️ المنطقة / Region<br/>13"] --> G["🏢 المحافظة / Governorate<br/>152"]
3 G --> M["🏘️ البلدية / Municipality<br/>285"]
4 M --> D["🏠 الحي / District<br/>5,484"]
5 G --> C["📍 المركز / Center<br/>~1,300"]
6 C --> S["🏙️ المدينة / City: 424<br/>🏡 القرية / Village: 21,023"]
7 S -.->|spatial| D
8
9 style R fill:#1a5276,color:#fff
10 style G fill:#2874a6,color:#fff
11 style M fill:#3498db,color:#fff
12 style D fill:#85c1e9,color:#000
13 style C fill:#2e86c1,color:#fff
14 style S fill:#aed6f1,color:#000
1graph TD
2 R["🏛️ المنطقة / Region<br/>13"] --> G["🏢 المحافظة / Governorate<br/>152"]
3 G --> M["🏘️ البلدية / Municipality<br/>285"]
4 M --> D["🏠 الحي / District<br/>5,484"]
5 G --> C["📍 المركز / Center<br/>~1,300"]
6 C --> S["🏙️ المدينة / City: 424<br/>🏡 القرية / Village: 21,023"]
7 S -.->|spatial| D
8
9 style R fill:#1a5276,color:#fff
10 style G fill:#2874a6,color:#fff
11 style M fill:#3498db,color:#fff
12 style D fill:#85c1e9,color:#000
13 style C fill:#2e86c1,color:#fff
14 style S fill:#aed6f1,color:#000
1INSTALL spatial; LOAD spatial;
2INSTALL httpfs; LOAD httpfs;
3SET s3_region='us-west-2';
4SET s3_url_style='path';
1INSTALL spatial; LOAD spatial;
2INSTALL httpfs; LOAD httpfs;
3SET s3_region='us-west-2';
4SET s3_url_style='path';
1SELECT city_name_en, city_type, governorate_name_en, municipality_name_en
2FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_settlements.parquet')
3WHERE region_name_en = 'Riyadh Region' AND city_type = 'مدينة';
1SELECT city_name_en, city_type, governorate_name_en, municipality_name_en
2FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_settlements.parquet')
3WHERE region_name_en = 'Riyadh Region' AND city_type = 'مدينة';
1SELECT municipality_name_en, count(*) AS districts
2FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_districts.parquet')
3GROUP BY 1 ORDER BY 2 DESC LIMIT 10;
1SELECT municipality_name_en, count(*) AS districts
2FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_districts.parquet')
3GROUP BY 1 ORDER BY 2 DESC LIMIT 10;
1SELECT city_name_en, governorate_name_en, region_name_en, municipality_name_en
2FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_major_cities.parquet')
3ORDER BY region_name_en;
1SELECT city_name_en, governorate_name_en, region_name_en, municipality_name_en
2FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_major_cities.parquet')
3ORDER BY region_name_en;
1SELECT r.region_name_en, r.region_name_ar, count(g.governorate_id) AS governorates
2FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_regions.parquet') r
3JOIN read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_governorates.parquet') g
4 ON r.region_id = g.region_id
5GROUP BY r.region_name_en, r.region_name_ar
6ORDER BY governorates DESC;
1SELECT r.region_name_en, r.region_name_ar, count(g.governorate_id) AS governorates
2FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_regions.parquet') r
3JOIN read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_governorates.parquet') g
4 ON r.region_id = g.region_id
5GROUP BY r.region_name_en, r.region_name_ar
6ORDER BY governorates DESC;
1SELECT r.region_name_en,
2 count(DISTINCT g.governorate_id) AS governorates,
3 count(DISTINCT m.municipality_id) AS municipalities,
4 count(DISTINCT d.district_id) AS districts
5FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_regions.parquet') r
6JOIN read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_governorates.parquet') g ON r.region_id = g.region_id
7JOIN read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_municipalities.parquet') m ON g.governorate_id = m.governorate_id
8JOIN read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_districts.parquet') d ON m.municipality_id = d.municipality_id
9GROUP BY r.region_name_en
10ORDER BY districts DESC;
1SELECT r.region_name_en,
2 count(DISTINCT g.governorate_id) AS governorates,
3 count(DISTINCT m.municipality_id) AS municipalities,
4 count(DISTINCT d.district_id) AS districts
5FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_regions.parquet') r
6JOIN read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_governorates.parquet') g ON r.region_id = g.region_id
7JOIN read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_municipalities.parquet') m ON g.governorate_id = m.governorate_id
8JOIN read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_districts.parquet') d ON m.municipality_id = d.municipality_id
9GROUP BY r.region_name_en
10ORDER BY districts DESC;
1SELECT g.governorate_name_en, g.region_name_en,
2 count(DISTINCT m.municipality_id) AS municipalities,
3 count(d.district_id) AS districts
4FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_governorates.parquet') g
5JOIN read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_municipalities.parquet') m ON g.governorate_id = m.governorate_id
6JOIN read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_districts.parquet') d ON m.municipality_id = d.municipality_id
7GROUP BY g.governorate_name_en, g.region_name_en
8ORDER BY districts DESC
9LIMIT 10;
1SELECT g.governorate_name_en, g.region_name_en,
2 count(DISTINCT m.municipality_id) AS municipalities,
3 count(d.district_id) AS districts
4FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_governorates.parquet') g
5JOIN read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_municipalities.parquet') m ON g.governorate_id = m.governorate_id
6JOIN read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_districts.parquet') d ON m.municipality_id = d.municipality_id
7GROUP BY g.governorate_name_en, g.region_name_en
8ORDER BY districts DESC
9LIMIT 10;
1SELECT region_name_en,
2 count(*) FILTER (WHERE city_type = 'مدينة') AS cities,
3 count(*) FILTER (WHERE city_type = 'قرية') AS villages,
4 count(*) AS total
5FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_settlements.parquet')
6GROUP BY region_name_en
7ORDER BY total DESC;
1SELECT region_name_en,
2 count(*) FILTER (WHERE city_type = 'مدينة') AS cities,
3 count(*) FILTER (WHERE city_type = 'قرية') AS villages,
4 count(*) AS total
5FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_settlements.parquet')
6GROUP BY region_name_en
7ORDER BY total DESC;
1SELECT mc.city_name_en, mc.city_name_ar, mc.alt_name_ar,
2 mc.governorate_name_en, s.center_name_en,
3 round(ST_Distance_Spheroid(mc.geometry, s.geometry)) AS offset_m
4FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_major_cities.parquet') mc
5JOIN read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_settlements.parquet') s
6 ON mc.city_id = s.city_id
7WHERE mc.alt_name_ar != mc.city_name_ar
8ORDER BY mc.city_grade, mc.city_name_en
9LIMIT 10;
1SELECT mc.city_name_en, mc.city_name_ar, mc.alt_name_ar,
2 mc.governorate_name_en, s.center_name_en,
3 round(ST_Distance_Spheroid(mc.geometry, s.geometry)) AS offset_m
4FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_major_cities.parquet') mc
5JOIN read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_settlements.parquet') s
6 ON mc.city_id = s.city_id
7WHERE mc.alt_name_ar != mc.city_name_ar
8ORDER BY mc.city_grade, mc.city_name_en
9LIMIT 10;
1WITH point AS (SELECT ST_Point(46.6753, 24.7136) AS geom)
2SELECT 'Region' AS level, r.region_name_en AS name_en, r.region_name_ar AS name_ar
3FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_regions.parquet') r, point p
4WHERE ST_Contains(r.geometry, p.geom)
5UNION ALL
6SELECT 'Governorate', g.governorate_name_en, g.governorate_name_ar
7FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_governorates.parquet') g, point p
8WHERE ST_Contains(g.geometry, p.geom)
9UNION ALL
10SELECT 'Municipality', m.municipality_name_en, m.municipality_name_ar
11FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_municipalities.parquet') m, point p
12WHERE ST_Contains(m.geometry, p.geom)
13UNION ALL
14SELECT 'District', d.district_name_en, d.district_name_ar
15FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_districts.parquet') d, point p
16WHERE ST_Contains(d.geometry, p.geom);
1WITH point AS (SELECT ST_Point(46.6753, 24.7136) AS geom)
2SELECT 'Region' AS level, r.region_name_en AS name_en, r.region_name_ar AS name_ar
3FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_regions.parquet') r, point p
4WHERE ST_Contains(r.geometry, p.geom)
5UNION ALL
6SELECT 'Governorate', g.governorate_name_en, g.governorate_name_ar
7FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_governorates.parquet') g, point p
8WHERE ST_Contains(g.geometry, p.geom)
9UNION ALL
10SELECT 'Municipality', m.municipality_name_en, m.municipality_name_ar
11FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_municipalities.parquet') m, point p
12WHERE ST_Contains(m.geometry, p.geom)
13UNION ALL
14SELECT 'District', d.district_name_en, d.district_name_ar
15FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_districts.parquet') d, point p
16WHERE ST_Contains(d.geometry, p.geom);
1SELECT mc.city_name_en, mc.city_name_ar,
2 g.governorate_name_en AS spatial_governorate,
3 mc.governorate_name_en AS declared_governorate,
4 CASE WHEN g.governorate_name_en = mc.governorate_name_en THEN 'match' ELSE 'mismatch' END AS status
5FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_major_cities.parquet') mc
6JOIN read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_governorates.parquet') g
7 ON ST_Contains(g.geometry, mc.geometry)
8ORDER BY status DESC, mc.city_name_en
9LIMIT 15;
1SELECT mc.city_name_en, mc.city_name_ar,
2 g.governorate_name_en AS spatial_governorate,
3 mc.governorate_name_en AS declared_governorate,
4 CASE WHEN g.governorate_name_en = mc.governorate_name_en THEN 'match' ELSE 'mismatch' END AS status
5FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_major_cities.parquet') mc
6JOIN read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_governorates.parquet') g
7 ON ST_Contains(g.geometry, mc.geometry)
8ORDER BY status DESC, mc.city_name_en
9LIMIT 15;
1SELECT mc.city_name_en, mc.city_name_ar,
2 mc.governorate_name_en,
3 d.district_name_en AS containing_district,
4 d.municipality_name_en AS containing_municipality
5FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_major_cities.parquet') mc
6JOIN read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_districts.parquet') d
7 ON ST_Contains(d.geometry, mc.geometry)
8WHERE mc.region_name_en = 'Riyadh Region'
9ORDER BY mc.city_name_en
10LIMIT 10;
1SELECT mc.city_name_en, mc.city_name_ar,
2 mc.governorate_name_en,
3 d.district_name_en AS containing_district,
4 d.municipality_name_en AS containing_municipality
5FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_major_cities.parquet') mc
6JOIN read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_districts.parquet') d
7 ON ST_Contains(d.geometry, mc.geometry)
8WHERE mc.region_name_en = 'Riyadh Region'
9ORDER BY mc.city_name_en
10LIMIT 10;
1SELECT r.region_name_en,
2 mc.city_name_en AS nearest_major_city,
3 round(ST_Distance_Spheroid(r.centroid, mc.geometry)) AS distance_m
4FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_regions.parquet') r
5CROSS JOIN LATERAL (
6 SELECT mc2.city_name_en, mc2.geometry
7 FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_major_cities.parquet') mc2
8 ORDER BY ST_Distance(r.centroid, mc2.geometry)
9 LIMIT 1
10) mc
11ORDER BY r.region_name_en;
1SELECT r.region_name_en,
2 mc.city_name_en AS nearest_major_city,
3 round(ST_Distance_Spheroid(r.centroid, mc.geometry)) AS distance_m
4FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_regions.parquet') r
5CROSS JOIN LATERAL (
6 SELECT mc2.city_name_en, mc2.geometry
7 FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_major_cities.parquet') mc2
8 ORDER BY ST_Distance(r.centroid, mc2.geometry)
9 LIMIT 1
10) mc
11ORDER BY r.region_name_en;
1SELECT r.region_name_en,
2 round(ST_Area_Spheroid(r.geometry) / 1e6, 0) AS area_km2,
3 count(s.city_id) AS settlements,
4 round(count(s.city_id) / (ST_Area_Spheroid(r.geometry) / 1e6), 2) AS density_per_km2
5FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_regions.parquet') r
6LEFT JOIN read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_settlements.parquet') s
7 ON r.region_id = s.region_id
8GROUP BY r.region_name_en, r.geometry
9ORDER BY area_km2 DESC;
1SELECT r.region_name_en,
2 round(ST_Area_Spheroid(r.geometry) / 1e6, 0) AS area_km2,
3 count(s.city_id) AS settlements,
4 round(count(s.city_id) / (ST_Area_Spheroid(r.geometry) / 1e6), 2) AS density_per_km2
5FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_regions.parquet') r
6LEFT JOIN read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_settlements.parquet') s
7 ON r.region_id = s.region_id
8GROUP BY r.region_name_en, r.geometry
9ORDER BY area_km2 DESC;
1SELECT m.municipality_name_en, m.governorate_name_en,
2 count(s.city_id) AS settlements,
3 round(ST_Area_Spheroid(m.geometry) / 1e6, 1) AS area_km2
4FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_municipalities.parquet') m
5LEFT JOIN read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_settlements.parquet') s
6 ON m.municipality_id = s.municipality_id
7GROUP BY m.municipality_name_en, m.governorate_name_en, m.geometry
8ORDER BY settlements DESC
9LIMIT 10;
1SELECT m.municipality_name_en, m.governorate_name_en,
2 count(s.city_id) AS settlements,
3 round(ST_Area_Spheroid(m.geometry) / 1e6, 1) AS area_km2
4FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_municipalities.parquet') m
5LEFT JOIN read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_settlements.parquet') s
6 ON m.municipality_id = s.municipality_id
7GROUP BY m.municipality_name_en, m.governorate_name_en, m.geometry
8ORDER BY settlements DESC
9LIMIT 10;
1INSTALL h3 FROM community; LOAD h3;
1INSTALL h3 FROM community; LOAD h3;
1SELECT h3_h3_to_string(h3_latlng_to_cell(latitude, longitude, 5)) AS h3_index,
2 count(*) AS settlements,
3 min(region_name_en) AS region
4FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_settlements.parquet')
5GROUP BY 1
6ORDER BY settlements DESC
7LIMIT 10;
1SELECT h3_h3_to_string(h3_latlng_to_cell(latitude, longitude, 5)) AS h3_index,
2 count(*) AS settlements,
3 min(region_name_en) AS region
4FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_settlements.parquet')
5GROUP BY 1
6ORDER BY settlements DESC
7LIMIT 10;
1SELECT h3_h3_to_string(h3_cell_to_parent(h3_latlng_to_cell(latitude, longitude, 5), 3)) AS h3_parent,
2 count(*) AS settlements,
3 count(DISTINCT region_name_en) AS regions_covered
4FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_settlements.parquet')
5GROUP BY 1
6ORDER BY settlements DESC
7LIMIT 10;
1SELECT h3_h3_to_string(h3_cell_to_parent(h3_latlng_to_cell(latitude, longitude, 5), 3)) AS h3_parent,
2 count(*) AS settlements,
3 count(DISTINCT region_name_en) AS regions_covered
4FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_settlements.parquet')
5GROUP BY 1
6ORDER BY settlements DESC
7LIMIT 10;
1WITH riyadh_cell AS (
2 SELECT h3_latlng_to_cell(24.7136, 46.6753, 5) AS center_cell
3),
4neighbors AS (
5 SELECT UNNEST(h3_grid_disk((SELECT center_cell FROM riyadh_cell), 1)) AS cell
6)
7SELECT h3_h3_to_string(h3_latlng_to_cell(s.latitude, s.longitude, 5)) AS h3_cell,
8 count(*) AS settlements,
9 count(*) FILTER (WHERE s.city_type = 'مدينة') AS cities,
10 count(*) FILTER (WHERE s.city_type = 'قرية') AS villages
11FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_settlements.parquet') s
12WHERE h3_latlng_to_cell(s.latitude, s.longitude, 5) IN (SELECT cell FROM neighbors)
13GROUP BY 1
14ORDER BY settlements DESC;
1WITH riyadh_cell AS (
2 SELECT h3_latlng_to_cell(24.7136, 46.6753, 5) AS center_cell
3),
4neighbors AS (
5 SELECT UNNEST(h3_grid_disk((SELECT center_cell FROM riyadh_cell), 1)) AS cell
6)
7SELECT h3_h3_to_string(h3_latlng_to_cell(s.latitude, s.longitude, 5)) AS h3_cell,
8 count(*) AS settlements,
9 count(*) FILTER (WHERE s.city_type = 'مدينة') AS cities,
10 count(*) FILTER (WHERE s.city_type = 'قرية') AS villages
11FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_settlements.parquet') s
12WHERE h3_latlng_to_cell(s.latitude, s.longitude, 5) IN (SELECT cell FROM neighbors)
13GROUP BY 1
14ORDER BY settlements DESC;
1WITH city_cells AS (
2 SELECT city_name_en,
3 h3_latlng_to_cell(latitude, longitude, 5) AS cell
4 FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_major_cities.parquet')
5 WHERE (city_name_en = 'Riyadh' AND city_name_ar = 'الرياض')
6 OR (city_name_en = 'Jeddah' AND city_name_ar = 'جدة')
7)
8SELECT a.city_name_en AS from_city, b.city_name_en AS to_city,
9 h3_h3_to_string(a.cell) AS from_h3,
10 h3_h3_to_string(b.cell) AS to_h3,
11 h3_grid_distance(a.cell, b.cell) AS h3_grid_distance
12FROM city_cells a, city_cells b
13WHERE a.city_name_en = 'Riyadh' AND b.city_name_en = 'Jeddah';
1WITH city_cells AS (
2 SELECT city_name_en,
3 h3_latlng_to_cell(latitude, longitude, 5) AS cell
4 FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_major_cities.parquet')
5 WHERE (city_name_en = 'Riyadh' AND city_name_ar = 'الرياض')
6 OR (city_name_en = 'Jeddah' AND city_name_ar = 'جدة')
7)
8SELECT a.city_name_en AS from_city, b.city_name_en AS to_city,
9 h3_h3_to_string(a.cell) AS from_h3,
10 h3_h3_to_string(b.cell) AS to_h3,
11 h3_grid_distance(a.cell, b.cell) AS h3_grid_distance
12FROM city_cells a, city_cells b
13WHERE a.city_name_en = 'Riyadh' AND b.city_name_en = 'Jeddah';
1WITH cells AS (
2 SELECT DISTINCT h3_latlng_to_cell(latitude, longitude, 5) AS cell
3 FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_settlements.parquet')
4)
5SELECT count(*) AS cells_before_compact,
6 len(h3_compact_cells(list(cell))) AS cells_after_compact
7FROM cells;
1WITH cells AS (
2 SELECT DISTINCT h3_latlng_to_cell(latitude, longitude, 5) AS cell
3 FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_settlements.parquet')
4)
5SELECT count(*) AS cells_before_compact,
6 len(h3_compact_cells(list(cell))) AS cells_after_compact
7FROM cells;
1SELECT h3_h3_to_string(h3_latlng_to_cell(latitude, longitude, 5)) AS h3_index,
2 count(*) AS settlements,
3 h3_cell_to_boundary_wkt(h3_latlng_to_cell(latitude, longitude, 5)) AS hex_boundary_wkt
4FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_settlements.parquet')
5GROUP BY h3_latlng_to_cell(latitude, longitude, 5)
6ORDER BY settlements DESC
7LIMIT 3;
1SELECT h3_h3_to_string(h3_latlng_to_cell(latitude, longitude, 5)) AS h3_index,
2 count(*) AS settlements,
3 h3_cell_to_boundary_wkt(h3_latlng_to_cell(latitude, longitude, 5)) AS hex_boundary_wkt
4FROM read_parquet('s3://us-west-2.opendata.source.coop/tabaqat/geocoding-cng/v0.4.1/sa_settlements.parquet')
5GROUP BY h3_latlng_to_cell(latitude, longitude, 5)
6ORDER BY settlements DESC
7LIMIT 3;
1SELECT res,
2 h3_h3_to_string(h3_latlng_to_cell(24.7136::DOUBLE, 46.6753::DOUBLE, res::INTEGER)) AS h3_cell
3FROM generate_series(3, 7) AS t(res);
1SELECT res,
2 h3_h3_to_string(h3_latlng_to_cell(24.7136::DOUBLE, 46.6753::DOUBLE, res::INTEGER)) AS h3_cell
3FROM generate_series(3, 7) AS t(res);