Database query on Wikipedia

Good day dbpedia community,

We urgently need help with a database search because we are currently running a school project that has to do with the following data.

Our problem is:

We need from Wikipedia all data entries of the information boxes of all cities and villages throughout Germany

These records are needed:

  • State
  • Government district
  • District
  • Inhabitants
  • Community key
  • Post Code
  • Address
  • Website
  • Coat of Arms (as a .svg file)

I hope you can get this data as we need it for our school project.

Yours sincerely

Thomas Heidrich

1 Like

Hello, DBpedia itself provides extracted and structured data for more then 140 Wikipedia languages stored as RDF. You can query this data on the public endpoint http://dbpedia.org/sparql, setting up your own RDF store or by processing the raw data dumps which can be found e.g https://databus.dbpedia.org/dbpedia/mappings. If you are new to linked data and rdf i can help you to get started with your tasks to accquire the right data for your purpose. Please pm me at Slack: https://dbpedia-slack.herokuapp.com/ User: marvinh

Hello marvinh, first of all thanks for the quick answer but I am still very new in this area. Although I know how to retrieve SQL databases and uses but have never done anything with SparSQL special with RDF.

Okay, SPARQL is quite easy if you know SQL, best way is to lean it on example queries.
Most difficult part is to know which vocabular/ontology is used to query the data reasonably.
DBpedia has its own ontology https://wiki.dbpedia.org/services-resources/ontology

Example query:

PREFIX dbo: <http://dbpedia.org/ontology/>
PREFIX rdfs:   <http://www.w3.org/2000/01/rdf-schema#>
SELECT *
{
   ?City a dbo:City ;
   rdfs:label ?Label ;
   dbo:postalCode ?PostalCode .
   Filter ( lang(?Label) = "en" )
} Limit 100

https://dbpedia.org/sparql?default-graph-uri=http%3A%2F%2Fdbpedia.org&query=PREFIX+dbo%3A+<http%3A%2F%2Fdbpedia.org%2Fontology%2F> PREFIX+rdfs%3A+++<http%3A%2F%2Fwww.w3.org%2F2000%2F01%2Frdf-schema%23> SELECT+* { +%3FCity+a+dbo%3ACity+%3B +++++++rdfs%3Alabel+%3FLabel+%3B +++++++dbo%3ApostalCode+%3FPostalCode+. +Filter+(+lang(%3FLabel)+%3D+"en"+) }+Limit+100&format=text%2Fhtml&CXML_redir_for_subjs=121&CXML_redir_for_hrefs=&timeout=30000&debug=on&run=+Run+Query+

“dbo” is used as a substitution for the in the PREFIX section defined URI http://dbpedia.org/ontology/

Thanks for your quick response the only problem I still have is how I limit this to Germany
:innocent::grinning:

@hopver I’ve now tried to make it like this on the tutorial website and came out:

http://dbpedia.org/sparql?default-graph-uri=http%3A%2F%2Fdbpedia.org&query=PREFIX+dbo%3A+<http%3A%2F%2Fdbpedia.org%2Fontology%2F> PREFIX+rdfs%3A+<http%3A%2F%2Fwww.w3.org%2F2000%2F01%2Frdf-schema%23> SELECT+* { +++%3FCity+a+dbo%3Acity+%3B +++rdfs%3Alabel+%3FLabel%3B +++rdf%3AProperty+%3Fpopulation+%3B +++rdfs%3AsubClassOf+%3Fdistrict+%3B +++rdfs%3Alabel+%3Faddress+%3B +++rdfs%3Alabel+%3Fwebsite+%3B +++dbo%3ApostalCode+%3FPostalCode+. +++Filter+(+lang(%3FLabel)+%3D+"en"+) }+LIMIT+100&format=text%2Fhtml&CXML_redir_for_subjs=121&CXML_redir_for_hrefs=&timeout=30000&debug=on&run=+Run+Query+

Better example for https://dbpedia.org/sparql

PREFIX dbr: http://dbpedia.org/resource/
PREFIX dbo: http://dbpedia.org/ontology/
PREFIX rdfs: http://www.w3.org/2000/01/rdf-schema#

SELECT * {

?City a dbo:City ;
rdfs:label ?Label .

Filter( lang(?Label) = ‘de’ )

Optional { ?City dbo:country dbr:Germany . }
Optional { ?City dbo:populationTotal ?Population . }
Optional { ?City dbo:postalCode ?postalCode . }
Optional { ?City dbo:thumbnail ?coatOfArms . }

} Limit 100

Still the Problem that it looks like that EN-DBpedia is missing some Information for your purpose. Maybe they are contained in the DE version. You can have a look at e.g http://global.dbpedia.org/?s=http%3A%2F%2Fdbpedia.org%2Fresource%2FOberschneiding and search for missing properties, in the source column it will show where the data comes from.