Entries API point

[sidneygijzen]

Hello,

I am playing around a bit with the API, for now just trying to re-create some numbers from the example document of the municipality of Weststellingwerf.

When trying to request the entries with the following query string

http://openspending.nl:80/api/v1/entries/?period=5&gov_code=0098&year=2011

I get the following error

TransportError(400, u'SearchPhaseExecutionException[Failed to execute phase [query], all shards failed; shardFailures {[cSQKJ_e6SNiSsdDs9dJ-ZA][spending][0]: SearchParseException[[spending][0]: query[filtered(gov_code:0098)->cache(_type:entry)],from[0],size[-1]: Parse Failure [Failed to parse source [{"sort": [], "from": 0, "facets": {}, "filter": {}, "query": [{"text": {"entry.gov_code": "0098"}}, {"text": {"entry.period": "5"}}, {"text": {"entry.year": "2011"}}], "size": 20}]]]; nested: SearchParseException[[spending][0]: query[filtered(gov_code:0098)->cache(_type:entry)],from[0],size[-1]: Parse Failure [No parser for element [text]]]; }{[ulHUXXieSzSR9kW3DZm8Qg][spending][1]: RemoteTransportException[[Grey King][inet[/172.17.0.4:9300]][search/phase/query]]; nested: SearchParseException[[spending][1]: query[filtered(gov_code:0098)->cache(_type:entry)],from[0],size[-1]: Parse Failure [Failed to parse source [{"sort": [], "from": 0, "facets": {}, "filter": {}, "query": [{"text": {"entry.gov_code": "0098"}}, {"text": {"entry.period": "5"}}, {"text": {"entry.year": "2011"}}], "size": 20}]]]; nested: SearchParseException[[spending][1]: query[filtered(gov_code:0098)->cache(_type:entry)],from[0],size[-1]: Parse Failure [No parser for element [text]]]; }{[ulHUXXieSzSR9kW3DZm8Qg][spending][2]: RemoteTransportException[[Grey King][inet[/172.17.0.4:9300]][search/phase/query]]; nested: SearchParseException[[spending][2]: query[filtered(gov_code:0098)->cache(_type:entry)],from[0],size[-1]: Parse Failure [Failed to parse source [{"sort": [], "from": 0, "facets": {}, "filter": {}, "query": [{"text": {"entry.gov_code": "0098"}}, {"text": {"entry.period": "5"}}, {"text": {"entry.year": "2011"}}], "size": 20}]]]; nested: SearchParseException[[spending][2]: query[filtered(gov_code:0098)->cache(_type:entry)],from[0],size[-1]: Parse Failure [No parser for element [text]]]; }{[ulHUXXieSzSR9kW3DZm8Qg][spending][3]: RemoteTransportException[[Grey King][inet[/172.17.0.4:9300]][search/phase/query]]; nested: SearchParseException[[spending][3]: query[filtered(gov_code:0098)->cache(_type:entry)],from[0],size[-1]: Parse Failure [Failed to parse source [{"sort": [], "from": 0, "facets": {}, "filter": {}, "query": [{"text": {"entry.gov_code": "0098"}}, {"text": {"entry.period": "5"}}, {"text": {"entry.year": "2011"}}], "size": 20}]]]; nested: SearchParseException[[spending][3]: query[filtered(gov_code:0098)->cache(_type:entry)],from[0],size[-1]: Parse Failure [No parser for element [text]]]; }{[cSQKJ_e6SNiSsdDs9dJ-ZA][spending][4]: SearchParseException[[spending][4]: query[filtered(gov_code:0098)->cache(_type:entry)],from[0],size[-1]: Parse Failure [Failed to parse source [{"sort": [], "from": 0, "facets": {}, "filter": {}, "query": [{"text": {"entry.gov_code": "0098"}}, {"text": {"entry.period": "5"}}, {"text": {"entry.year": "2011"}}], "size": 20}]]]; nested: SearchParseException[[spending][4]: query[filtered(gov_code:0098)->cache(_type:entry)],from[0],size[-1]: Parse Failure [No parser for element [text]]]; }]')

Do you have any idea where I went wrong? :-)

(After submitting this issue, I noticed it's probably the wrong repo where I submitted it.... but I can' t find the one for openspending API. Let me know, and I gladly will move the issue over there!)

[BramMusters]

Hi Sidney,

I struggled a lot with the same problem. If I remember correctly its your gov_code parameter that results in the error. I fixed it by pulling all governments and handling it in my own code.

[sidneygijzen]

Hello Bram,

Thanks for reaching out! Also for your mail with the link to your Public Spending Visualization.

I took a look at your application, and I see that it is querying the aggregations/entries API access point instead of the entries access point (?). I tried querying aggregations/entries access point with the same parameters, and that works! I will have a look if that yields the data I'm looking for.

btw, I also tried querying the entries access point without a gov_code (and other combinations of parameters), but that unfortunately results in similar errors. I have no clue where I went wrong. Perhaps @breyten can shed a light?

[breyten]

Many apologies for the late response. I would recommend not to use that API call if what you want to do is to recreate what you're seeing on the website ;).

http://www.openspending.nl/weststellingwerf/realisatie/2011-5/lasten/hoofdfuncties/ has the following corresponding API call:

http://openspending.nl:80/api/v1/aggregations/main/?period=5&gov_code=0098&year=2011

http://www.openspending.nl/weststellingwerf/realisatie/2011-5/lasten/hoofdfuncties/algemeen-bestuur/functies/ has this:

http://openspending.nl:80/api/v1/aggregations/sub/?period=5&gov_code=0098&year=2011

And finally http://www.openspending.nl/weststellingwerf/realisatie/2011-5/lasten/categorieen/ is this:

http://openspending.nl:80/api/v1/aggregations/cat/?period=5&gov_code=0098&year=2011

(There is a slight problem with using multiple query operators in the entries call, and I haven't figured out how to correctly solve it yet.)

Hope this helps!

[sidneygijzen]

Thanks for your elaborate reply @breyten. I will look into it and get back to you!

[M-Danes]

@breyten we spoke some weeks ago. Back then you indicated to have some moments during the following weeks to see whether you could fix this issue. Could you provide a small update about you progress, so we can decide whether the api is applicable for our application.

Thanks you very much. Cheers!

[sidneygijzen]

Hello @breyten ,

Thanks for the explanation and the detailed examples. I tried to replay the API calls you mentioned in your post, however the results differ (slightly) from what I see on the website.

For example this page differs slightly in results from the accompanying API call :

• Bestuursorganen (term 001)
  • website: €1.454.000,00
  • API: €1.474.000,00
• Bestuursondersteuning college van burgemeester en wethouders (term 002)
  • website: €2.724.000,00
  • API: €2.724.000,00
• Burgerzaken (term 003)
  • website: €986.000,00
  • API: €986.000,00
• Baten secretarieleges burgerzaken (term 004)
  • website: €95.000,00
  • API: €470.000,00
• Bestuurlijke samenwerking (term 005)
  • website: €377.000,00
  • API: €391.000,00
• Bestuursondersteuning raad en rekenkamer(functie) (term 006)
  • website: €262.000,00
  • API: €262.000,00

The API call returns the count, max, mean, min, term, total and total_count fields per term/subfunction. I assumed I need the numbers from the total field (?). As for the different results; does this have to do with different databases, and thus different versions of the data perhaps, for your website and the API?

Do I understand you correctly when I conclude from your reply that it is currently only possible to retrieve (sub)totals for (sub)functions and categories, but not the actual entries of which they consist of?

[breyten]

Thanks for looking into this. The difference is that I forgot to add a parameter (my bad, I don't actually use the API enough).

http://openspending.nl/api/v1/aggregations/sub/?period=5&gov_code=0098&year=2011&direction=out

Gives you now the same results (So you should apply the direction on the other calls as well). Direction can be out (lasten) or in (baten).

You can get the netries (bar the problems described above), but that leads to a pretty big matrix which is sparsely filled anyway. This is because IV3 is essentially a matrix with main functions and sub functions on one side, and (economic) categories on the other. But I guess (hope?) that the entries is not what you actually need most of the time.

[sidneygijzen]

Aha, cool. That makes sense indeed! Thanks for the explanation.

I will discuss with @M-Danes about the entries question, but I think that the subtotals will suffice for what we have in mind.

[breyten]

OK! Don't hesitate to ask If you can clarify what you want to build, then I can help too :)