 Session will do some live querying with him so you were told to think of some ideas that we could search for and wiki data and when we get to that point I would ask you to raise your hand and wait till I get to you with the microphone so the people in the stream can also hear what we're talking about so that's the thing I'll give back to Lucas and we still have translations wenn ihr es auf deutsch hören wollt wir haben immer noch Übersätze die euch alles versuchen live noch mal auf deutsch zu erzählen also hört mal rein Have fun! some finding all it's probably going to be a timeout but that would be something like select going with child would be the simplest so item child plus item again so if we put the star like early then it could then every triple would match that but with the plus it means it pairs to be at least one child link or more and let's just add a limit one because I'm not that optimistic that this is even going to find one but I'm pretty sure we cannot find all of them but let's see if we can find one and this might just take a while but I don't think there is a good way to do this otherwise unless you download one of the dumps either the JSON datadumps or the RDF dumps which is the same data format used here and then you can do it locally without any timeout yeah I don't think there's much I can optimize about this query it's pretty short unless like I had an idea that people named John are more likely to have these kinds of cycles than we could filter it down first but even then I'm afraid that is not going to work it looks like yeah timeout and you can see the thing is written in Java the server yeah one thing we can do with this p40 plus is something like a search start with a certain mythical creature such as King Arthur I hope I can find him like this search is being all right WD not WDT there we go that's a legendary British or wealth king and then we are searching for an item who is definitely a real human and who has a date of birth and the we say the date of birth should be greater than let's say 1950 and this is date time value and this let's let's even say 1980 I think that might be more efficient there we go no no no results okay I thought King Arthur had some real descendants no then it was some other mythical creature let's just start with any ancestor who has the item as child and the ancestor is also instance of mythical creature mythical character let's see if we have any mythical characters with children who were born after 1950 oh I still have the limit one here could make at a limit 10 probably or something but I'm optimistic I think there are some people here especially I think even British MPs there's some as already on the list of example curious British MPs with mythical ancestors and there's a lot of them that trace their lineage back to some sixth or fifth century person and you have all the parental links in there and it's kind of tricky to figure out where it starts being wrong that's not working out so well does anyone else have ideas in meantime they're way in the back we all know that stupid game in wikipedia where you try to find the adult Hitler page by only clicking links so can you find the number of pages that are directly connected to the adult Hitler page in wikipedia um you can oh that was a timeout so that would be kind of so one funny story about that for example is there's the main the main for the first example clear we have here is cats and why do we have cats and not dogs because if you search for dogs the second result no it's the fourth result by now but that's the dog of Hitler and we don't really want that normally so we usually use cats as the example instead but let's just search for anything where the item has any connection and we don't care which property it is to adult Hitler like that and we are going to find 920 results okay some of these are site links so we also want the item to have some label which uses this new namespace and we want only the English label so the language of the label should be in English and we then we just select the item and the label and hopefully that's still pretty efficient yeah there we go and it's the AP membership number that's actually a property but I assume it has as the example yep there's a property example he has membership number one world war two has probably cause of death do we have him as an example on cause of death really and we have nitric acid poisoning stroke cholera shot to the head cyanide poisoning hanging that's a very pleasant list do we need to have that many protein samples of cause of death I don't know yeah then we have Nazi Party Clara Hitler I don't know who that is 1936 summer olympics all kinds of things yeah so that's how you can find all the things with a direct connection to Hitler yeah any other examples yes over there in the right or was there already somewhere someone back there that I missed can you can you find the cheapest public infrastructure projects in Germany the cheapest public infrastructure what projects like a bridge building um I don't think we're going to have a full data set about that but you can try let's start with a more expensive one see perhaps move away from the box might help let's start with a very expensive project and see just what the data model looks like so what does infrastructure project look like was was was the cost so the cost is probably going to be in euro and I don't know how to write euro okay it's a property called cost in euro and does it have something like instance of international airport building under construction greenfield airport proposed airport being built so we could check first is Berlin Brandenburg airport is that an instance of some subclass of public infrastructure is that a thing that looks like the wrong item what is this this is nothing okay does anything link to this item no nothing links in this item okay so it could be an international airport is a subclass of airport which is a subclass of an aerodrome which is an architectural structure we can search for architectural structures so the structure would be an instance of subclass of architectural structure and it would have a cost and order by descending cost limit 10 and we're probably going to get things in like Yen or some other currency where this number is just going to be very high because we're not taking any conversions into account right now but let's see if we find something there was it doing okay not sure why this is taking so long let's try a second version in the meantime where we do quantity amount as cost and the base quantity unit should be the euro is still running and yeah let's try this that worked any better or not okay this was a timeout this looks like it's going to be a timeout as well I don't know we can just search for the most expensive things at all remove this part there we go this costs 55 billion euros what is this thing power of Siberia natural gas pipeline that's that's in euro the cost apparently and then this is 15 billion euros and then 8.7 7. something that's the channel oh the channel tunnel is expensive the Brenner tunnel was also expensive and Stuttgart 21 Stuttgart 21 whatever was also alright is projected to be expensive do we have one cost or several okay in 2018 we have a cost of 7 billion yeah so that's sort by the ascending cost instead because that was what we actually wanted and then we get okay now we're going to get a lot of things that aren't really infrastructure projects we have the whole and a hot and energetic universe does that mean it's a no-budget film or what okay so we would need some kind of okay let's say let's do duck typing instead of saying it is an infrastructure project let's say it has I don't know a coordinate location and if it has a coordinate location we're going to call it some kind of infrastructure project or at least it's not going to be a documentary film perhaps that works better yeah so 21.000 euros cost this thing which was in france oh okay right it should also be have country germany there we go that's 400.000 euros for a fountain in Stuttgart does that count i guess and that's an instance of something it doesn't even have a german name an englisch label just germ one wait oh so this is the class of all the fountains with exactly this name which are subclass of well and are all named after this goddess okay cool yeah so there we have some of these cheap projects which is this public square a bridge oh yeah there's a tiny bridge a footbridge has even an image that's what it looks like and it costs what was it 1.6 million euros already wow and then we have another public square yeah so um cheap public infrastructure projects and also public infrastructure in quotes because we're really just saying it has a location in country germany and yeah i can send this query around afterwards and this didn't work this didn't work okay any other ideas that's bad news we could try to continue with some of these was there something oh for a camera i have a question i saw that with wiki data query service we can draw these nice trees and have images in them and one example that came to my mind was all the programming patterns the programming design patterns but group by they are kind like they are structural patterns convenience patterns and so on and like can we draw a graph and maybe put an image in them we can try that so let's see how that's modeled i don't know with the visitor pattern for example um that's a design pattern what kind of statements does it have it's a subclass of behavioral pattern is this a programming thing or already oh yeah it's a sub okay it's a software design pattern so we should say the we're going to have a pattern with its label and a pattern kind with its label and the pattern is going to be a subclass of the pattern kind which is going to be some subclass of um what was it of software design pattern and i'm just going to copy this id so it's the right one label service and say i would like to see this by default in the graph view there we go and that looks not as bad as i thought we have a lot of structural patterns behavioral patterns one architectural pattern a few creation patterns and one fundamental pattern yeah and yeah what we could also do is if we do this then we should should also see connections of all of these yeah now we have the tree rooted at software design patterns we have monad and fundamentals pattern is a kind of software design pattern structural pattern and it's all linked there and this is working very well i this is much better than i expected i expect a huge mess of because it sometimes gets different to determine when should you use instance off and when should you use sub subclass off like if it's software or patterns like this i expected we would have to account for both of these but this looks very good to me i think we don't need to do anything with this query yeah so that is um software design patterns by pattern tree okay any other ideas or i can try to keep optimizing this one which cities have applied to the host city of the revision song contest the most times but were never successful oh that's a very good question i don't know if we have the do you know who applied for this year or or for some year i could check if that state if that's modeled anywhere um i need some example cities so um let's check esc 2018 if it has information on where took place which one won the bit but also who nominate who was nominated or something or who applied we have presenters we have followed by star time and time participants we have the winner do we have a location at all oh yeah there it is okay we have a country and a location but i'm not seeing any other countries here and i assume that information is not going to be on the country item it's possible that we have uh some separate item for your vision 2018 bit or no way that would have to be which city because the country is determined by the winner isn't it so the city uh but i suspect we don't have that information we have a list of host cities but that's just uh we keep me a list article do we have to switch to the other mic oh no that sounds great okay um yeah so we don't have any of the structured information here it's just linking all of these wikipedia articles together and then here is the actual list with the different venues but i don't think we have that information wiki data at the moment we could add it you have to figure out the data model but it would probably be relevant enough i think i wonder if we have that for the olympic games so olympics 2020 do we have the process of who applied to host those we have location we have parts let's check perhaps english wikipedia has a separate article about the selection process for 2020 summer olympics doesn't look like it host city selection no i don't see a main oh no there bits for the 2020 summer olympics that's the wikipedia article does that have any useful information on wiki data bits for olympic games no damn it so you can see when these bits all happened but we don't have the bidding countries and cities apparently on wiki data at least not as far as i can see bits for the 2012 for example uh yeah no sadly we don't have that information yet did this one run by the way no any other questions our translation angels had a question and they wanted out you can give them the countries with the most colorful flags yes that should be okay that should be possible so select country and the count of the colors as count the country has oops has a flag not the flag image a flag and the flag has color color that should be color and not colors and then we group by country so this is a bit like a grouping and aggregate functions so use the other microphone okay then you can't really walk around anymore okay so now um this could be really fun um yeah so we are searching for countries with flags and hope that the flags have colors and then counting them and what i didn't do is what's this do i want to know okay okay it's at least not the straight pride flag i guess um does this have 14 colors no what was it no eight i guess one two three four five six seven eight yeah that's accurate uh yeah i didn't filter for countries here uh the thing is country is really uh stupidly complicated term so what i did was queries i have a pre-prepared query for the un-member states somewhere which i just copy all the time and this is now going to be called the state and then we only get state flags um and there's exactly oh right i need to group by state and state label and copy these up here as well and then it will hopefully work and we will find out that the united kingdom has 12 i suspect that's because it has four flags which all have the same rank or no no it should be five right united kingdom a northern ireland scotland wales and england let's search for flag flag is the flag of the united kingdom no why does it have 12 colors it has blue red white but i see but that still doesn't explain the 12 let's count only the distinct colors distinct there's autocompletion thank god perhaps that helps so i don't know why it would have oh it would have had the state multiple times because it's uh sovereign state multiple times probably let's check yeah the united kingdom is it's a commonwealth realm and an island nation and a sovereign state and that's probably why we got it multiple times and yeah that looks more reasonable south africa ecuador south sudan and what we can also do is add the of the flag the image and call that i because i can't be bothered to type the whole thing and add that here and also add it to the group by because otherwise it's not the right aggregate and i can't be bothered to write sample with one hand and then we can hopefully also see it oh we get two images of the flag of south africa that also looks like one of them should be preferred rank but anyways we can switch to image grid and then we can see all these colorful flags one two three four five six yeah that's six and this is more than six so i guess i would say that should actually be two separate items for this old flag and no this old flag and the new flag but this is six is that only six colors i'll believe it this is six colors six and then we have five colors yeah so here are the let's just add a comment there and i will tweet this out later as well colorful state flags yeah and uh yeah also we can use the image grid as the default view we probably have time for one more question if it's a short one though i won't be able to tie very fast yes um let's hope this works otherwise i can repeat it for the stream if i hear you so does it work yeah seems so i don't know if it's possible but the smallest images that are on Wikipedia so by image size that would not be possible with the query service i think but i think on commons you can search can you search oops i don't have that search shortcut set up here can you search by image size i think that might be possible advanced search file type sorting order no um you could probably sort by file size in an sql query um which is not a thing from the wiki data query service but it's possible with something else and as it happens i am going to have another talk later uh where i talk about and among other things how you can write sql queries against the wikipedia databases and then we might be able to find a solution for that that's i think at six p.m. today over in the estimate or you come over to me after after the talk and then i can try to figure it out there yeah emergency idea that we have to try out you're muted i'm muted do you have another one more idea small idea maybe we could do but other than that i think we also filled the time quite well yeah i think we're done but if you have any other ideas you can always contact me on twitter wiki data facts or on mastodon as well and then i will see what i can do for you yeah thanks thank you very much lucas that was a great introduction to wiki data query