2

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, and 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) :

NY; ROCHESTER; MONROE
NH; MANCHESTER; HILLSBOROUGH

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

Improve this question
5
  • did you try on query.wikidata.org?
    – Alexan
    Commented Aug 24, 2017 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?
    – daniel.heydebreck
    Commented Aug 25, 2017 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
    Commented Aug 25, 2017 at 11:52
  • 1
    It seems census.gov would be the primary source and geonames.org would be a good 2ndary source?
    – user3856
    Commented Aug 25, 2017 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
    Commented Aug 25, 2017 at 15:15

1 Answer 1

Reset to default
1

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.

Improve this answer
6
  • 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
    Commented Aug 25, 2017 at 11:59
  • And is it possible easily to get NY instead of New York for example. (Abbreviations for states)
    – DavidK
    Commented Aug 25, 2017 at 12:01
  • I noticed some counties alone without state or city.
    – DavidK
    Commented Aug 25, 2017 at 12:25
  • @DavidK, I'll answer later or tommorow.
    – Stanislav Kralin
    Commented Aug 25, 2017 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.
    – Stanislav Kralin
    Commented Aug 28, 2017 at 9:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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