How to get all the "recorded at" information directly from the database?

MusicBrainz Development
1

I would like to get the information for the “recorded at” details like on the webpage for this example release:
image

Finding the “Studio Fredman” is easy in this case:
select * from place where gid=“82766cb0-3195-4a4e-9943-eadfa75eaed5”

This also returns the area id 9562.

Then I get the area “Gothenburg” with:
select * from area where id=9526

But how can I find now the required area/9d5d954d-5d88-42e3-8a55-f5b6a76a2f02 “Västra Götaland (Västra Götaland county)” in this example?

And how do I find the country “Sweden” (area/23d10872-f5ae-3f0c-bf55-332788a16ecb)?

I know how to click on the links on the webpage and found this area entity map.
I specifically ask how to find the relation between the various areas (city, region, subdivisions, states, provinces) and eventually the relation to the country directly from the database.

Would anyone who is already using an SQL command to retrieve this information be willing to share it?

2

Area part of rels are in l_area_area but rather than doing all the recursive work, you could build the materialized table area_containment if you haven’t already, and then look for stuff with the area you want as descendant.

3 Likes
3

That helps. Thank you for the hint about materialized-tables.

It’s still pretty cumbersome to get all the parts of:
Monmouth → Monmouthshire → Wales → United Kingdom
for “Rockfield Studios”

I still hope that someday MBS-6741 will be introduced. :innocent: