Get the list of cities, suburbs with relations from Overpass-turbo

Question

I'm struggling to build a mini database set for with the following structure

{countryId, governorateId, cityId}

The idea is: I need to find all admin_level=4 for Egypt, then for each result, get the cities|suburb|town

Example: Cairo: ['Nasr City', 'Fifth Sattelment',...etc.]

Where Cairo = Governorate, 'Nasr City' = Suburb

What i have so far:

[out:csv(::id, 'place', 'name:ar', 'name:en')][timeout:25];
// fetch area “Egypt” to search in
{{geocodeArea:Egypt}}->.searchArea;
// gather results
(
  node[place~"city|town|suburb"](area.searchArea);
);
// print results
out body;
>;
out skel qt;

which gives me the list, but without relations, so i have no clue which suburb is inside which city

Answer 1

When you write that query without filtering the columns it should return it will return a node like such:

<node id="21320911" lat="-33.9783333" lon="25.5874001">
    <tag k="is_in" v="Port Elizabeth,Eastern Cape, South Africa"/>
    <tag k="name" v="Walmer"/>
    <tag k="place" v="suburb"/>
    <tag k="sagns_id" v="62090"/>
    <tag k="source" v="sagns"/>
    <tag k="wikidata" v="Q61356595"/>
  </node>

So, to extract the relations you can add the "is_in" column to the query so that it can look something like so...

[out:csv(::id, 'name', 'is_in', 'place')][timeout:25];
// fetch area “South Africa” to search in
{{geocodeArea:South Africa}}->.searchArea;
// gather results
(
  node[place~"city|town|suburb|street"](area.searchArea);
);
// print results
out body;
>;
out skel qt;

and that should show your suburb and the city including the country. Eg:

@id name    is_in   place
21320910    Newton Park "Port Elizabeth, Eastern Cape, South Africa"    suburb
21320911    Walmer  "Eastern Cape, South Africa"    suburb
21320912    Mill Park   "Eastern Cape, South Africa"    suburb

On my result set, it sometimes returns the city and sometimes doesn't...Hopefully this helps out.