I did not find existing data of good quality that I can use directly. I thought that Wikidata was the best source to use and that it would be an opportunity to learn how to extract from this data source. Seems not so obvious to extract what I want and to deal with the links between all elements, the different data types.

How to use query.wikidata.org or the REST API to get or construct the table with all cities in the United States with associated counties ?

Example of what I want to get (for all the US cities) :


If more than one county, I want to list them all (with another separator or multiple lines)

Thanks in advance for all the tips / explanations about Wikidata.

  • did you try on query.wikidata.org?
    – Alexan
    Aug 24 '17 at 20:36
  • What did you try so far? Do you have experience with database queries? Is it a problem of finding the needed data tables or of writing the query? Aug 25 '17 at 9:17
  • I tried on query.wikidata.org but I did not get what I was expecting using the interface. As you said, it was a problem of finding the needed data tables and writing the query with all the identifiers. I was looking for a more user friendly way of dealing with data (via REST), using JSON for example.
    – DavidK
    Aug 25 '17 at 11:52
  • 1
    It seems census.gov would be the primary source and geonames.org would be a good 2ndary source?
    – user3856
    Aug 25 '17 at 14:55
  • Do you have a link ? I think we can find files for a specific state, but not a global file containing all data.
    – DavidK
    Aug 25 '17 at 15:15

I do not think that Wikidata is a reliable source in this case.

A basic query is here below. The first revision of this answer contains more advanced attempt.

SELECT DISTINCT ?code ?capitalLabel ?countyLabel WHERE {
        ?county wdt:P31/wdt:P279* wd:Q47168 .
        ?county wdt:P36 ?capital . 
        ?county wdt:P131 ?state .
        ?state wdt:P31 wd:Q35657 . 
        ?state wdt:P883 ?code .
        FILTER (regex(?code, "[A-Z][A-Z]"))
        SERVICE wikibase:label {bd:serviceParam wikibase:language "en" .}
    }  ORDER BY ASC(?code) ASC(?capitalLabel)

Try it!

In order to show Wikidata's unreliability in this particular case, one should compare this list of counties with more official lists, but please do it yourself.

  • Thanks for you answer. Do you know a good resource to learn how to construct this kind of query ? How to find the good identifiers ? I have been looking at the Wikipedia webpages and I wanted that level of data quality. Why do you think it would not be reliable ?
    – DavidK
    Aug 25 '17 at 11:59
  • And is it possible easily to get NY instead of New York for example. (Abbreviations for states)
    – DavidK
    Aug 25 '17 at 12:01
  • I noticed some counties alone without state or city.
    – DavidK
    Aug 25 '17 at 12:25
  • @DavidK, I'll answer later or tommorow. Aug 25 '17 at 12:26
  • 1
    @DavidK, I have edited my answer, now it is not pretending to be a complete one... As for your questions in comments, Wikidata SPARQL tutorial and Wikidata examples are good starting points. Also, Query Helper (in the left part of the Wikidata Query Service UI) explains simple queries well. In the right pane, tooltips are displayed after hovering a mouse over (prefixed) URI. Visiting Wikidata page of a typical object you are interested in is also helpful. Aug 28 '17 at 9:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.