Reference — locations (Turkish Geography)

Project: Okul Database · Hub: Okul Database — Reference

Reference: locations Table

Hierarchical Turkish location tree. Self-referential via parent_id.

---

Table Schema

CREATE TABLE locations (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(255),
  slug       VARCHAR(255),
  parent_id  INT UNSIGNED NULLABLE,  -- NULL = top-level province
  status     TINYINT DEFAULT 1,
  -- FK: parent_id → locations.id
)

---

Hierarchy

Level 0: Province (İl)    — parent_id IS NULL
Level 1: District (İlçe)  — parent_id = province.id
Level 2: Neighborhood     — parent_id = district.id (less commonly used)

---

Key Provinces (Level 0 — parent_id IS NULL)

id	name	slug	Notes
1	İstanbul	istanbul	Largest city, most schools
2	Ankara	ankara	Capital
3	İzmir	izmir
4	Antalya	antalya
…	…	…	81 provinces total

Full province list: 81 provinces matching Turkey’s administrative structure. There is NO plate_code column in this table — plate codes (34=İstanbul) are not stored here. Use id for joins.

---

Sample Districts (Level 1)

id	name	slug	parent_id
5	Arnavutköy	arnavutkoy	1 (İstanbul)
6	Ataşehir	atasehir	1 (İstanbul)
14	Akyurt	akyurt	2 (Ankara)
16	Altındağ	altindag	2 (Ankara)

---

How It’s Used

Table	Column	Level
school_filters	city_id	Province (Level 0)
school_filters	county_id	District (Level 1)
schools	city_id	Province
schools	county_id	District
customers	location_level_1	Province
customers	location_level_2	District
campuses	location_id	Any level
high_school_points	location_level_1, location_level_2	Province, District

---

Geographic Queries

-- Province list (for dropdown / mapping)
SELECT id, name, slug
FROM locations
WHERE parent_id IS NULL AND status = 1
ORDER BY name;
 
-- Districts of İstanbul (id=1)
SELECT id, name, slug
FROM locations
WHERE parent_id = 1 AND status = 1
ORDER BY name;
 
-- School count by province
SELECT l.name as province, COUNT(*) as school_count
FROM school_filters sf
JOIN locations l ON l.id = sf.city_id
WHERE sf.deleted_at IS NULL AND sf.is_active = 1
GROUP BY sf.city_id, l.name
ORDER BY school_count DESC;
 
-- NOTE: school_filters already has city_name and county_name
-- so you often don't need to join locations at all
SELECT city_name, county_name, COUNT(*) as schools
FROM school_filters
WHERE deleted_at IS NULL
GROUP BY city_id, city_name, county_id, county_name;

---

Related

• table-school-filters — city_id, county_id, city_name, county_name (denormalized — no join needed)
• table-schools — FK to locations
• domain-config-meta — Config & Meta domain