25519

Registered Charities

Open Data web service user guide

Best Intended users of the Charities Commission Open Data web service

This service is intended to be used by software developers who wish to write applications making use of the data from the Charities Register. The service should not be used by users simply looking to access information about charities. A search interface allowing anyone access to the same data is available at http://register.charities.govt.nz/CharitiesRegister/AdvancedSearch.aspx.

About the Charities Commission Open Data web service

The Charities Commission Open Data web service contains a large amount of data about charities, their officers and the annual returns they file. It includes all data which is published to the Charities Register and excludes any data which has been withheld from publication. The service is free to access and does not require registration.

By default, the results of any query have been restricted to 1000 records. This is to prevent an open query returning a large volume of results and placing an unnecessary load on both the systems of both the Commission and the origin of the query. This default can be overridden by using the following in the query string:

$returnall=true

For example:

http://www.odata.charities.govt.nz/Organisations?$filter=RegistrationStatus eq 'Deregistered'&returnall=true

The Commission's Register is housed in a Microsoft stack, therefore the OData Protocol is the means to access it.

Further information about OData can be found at http://www.odata.org.

Accessing the Charities OData service

The Charities Commission OData end point can be reached at http://www.odata.charities.govt.nz. There is no authentication required to access this end point and all access is read only.

The end point can be accessed, navigated and queried through your web browser although it is best accessed through code using a client library or another purpose-built OData consumer application. Some of these are available from http://www.odata.org/consumers. Client libraries exist for Javascript, PHP, Java, Windows Phone 7, iOS/Objective-C and .NET.

Data can be retrieved in any of three formats – ATOM, JSON and CSV (e.g., by specifying the $format keyword in the query URI; $format=atom, $format=json, $format=csv). Unless another format is specified, ATOM data will be returned by default.

Access example in Objective-C

Using the OData Client for Objective-C (http://odataobjc.codeplex.com/) an iOS developer could access the service as follows:

A proxy class should first be generated using the command:

macuser-imac:Desktop  macuser$ odatagen /uri=http://www.odata.charities.govt.nz /out=/Users/macuser/Desktop/odatagenapp

The proxy class can then be used as follows:

Charities_ODataEntities  *proxy = [[Charities_ODataEntities alloc] 	initWithUri"@"http://www.odata.charities.govt.nz"  credential:nil]; DataServiceQuery* query = proxy  grporglatestreturns]; [query  filter:@"Donationskoha gt 1000000"]; QueryOperationResponse  *response = [query execute];

Access example in PHP

Using the OData SDK for PHP (http://odataphp.codeplex.com/), the OData service can be accessed as follows:

$svc = new  Charities_ODataEntities(http://www.odata.charities.govt.nz); $query =  $svc->GrpOrgLatestReturn() 	->filter("Donationskoha gt  000000") $response =  $query->Execute();

Access example in C#

A Microsoft .NET C# application could access the OData service using either of the following methods:

Adding the service as a service reference, then using LINQ to query the entities

Charities_ODATAEntities  context = new Charities_ODATAEntities( 	new  Uri("http://www.odata.charities.govt.nz")); 	var donationsOverOneMillion  = 		from  charity in context.GrpOrgLatestReturns 		where  charity.Donationskoha > 1000000 		select  charity;

Querying the service directly using the URI

WebRequest  donationsOverOneMillion Request = WebRequest.Create( 	"http://www.odata.charities.govt.nz/GrpOrgLatestReturns?$filter=Donationskoha 	gt 1000000&$format=csv"); WebResponse  donationsOverOneMillion Response = request.GetResponse();

Using the OData URI querying

An OData service is RESTful, with resources identified using Uniform Resource identifiers (URIs) and accessible using simple http messages. Lists of entities can be accessed by name e.g. http://www.odata.charities.govt.nz/Organisations. Individual entities can be accessed using their id e.g. http://www.odata.charities.govt.nz/Organisations(27891) (which will bring up the Organisation data for 'Bayfair Community Garden'). It is also possible to access lists of entities which are connected to another e.g. http://www.odata.charities.govt.nz/Organisations(27891)/Activities would return a list of all activities associated with that organisation. It is often useful to do this in reverse as well e.g. http://www.odata.charities.govt.nz/Activities(4)/Organisations would return a list of all organisations which are associated with activity 4 (Provides advice / information / advocacy).

The OData URI can include a number of specified keywords which allows a variety of more detailed queries to be defined. All keywords are prefixed with a $ sign and should be joined with a '&' sign. The available keywords are orderby, top, skip, expand, select, inlinecount, format and filter. Here are some examples of how these keywords may be used:

The $filter keyword

The filter keyword allows queries comparing field values and some basic calculations or string operations to be performed. Query terms can be linked using 'and' or 'or' and can also be grouped with brackets to define precedence. Details of the most common query operators and examples of the use of the filter keyword follow. This is not an exhaustive list, full details of the OData query syntax can be found at http://www.odata.org/developers/protocols/uri-conventions.

Basic Operators

Operator Description Example
Eq Equal All organisations with a postcode of 5010
http://www.odata.charities.govt.nz/Organisations?$filter=PostalAddress_postcode eq '5010'
Ne Not equal All organisation annual returns which have received some bequests
http://www.odata.charities.govt.nz/
GrpOrgLatestReturns?$filter=Bequests ne 0
Gt Greater than Organisations who have received Donations/koha greater than 1000000 in their latest annual return
http://www.odata.charities.govt.nz/
GrpOrgLatestReturns?$filter=Donationskoha gt 1000000
Ge Greater than or equal All organisations who spend greater than or equal to 10 percent overseas
http://www.odata.charities.govt.nz/Organisations?$filter=percentage_spent_overseas ge 10
Lt Less than Organisation who have received Donations/koha less than 100 in their latest annual return
http://www.odata.charities.govt.nz/
GrpOrgLatestReturns?$filter=Donationskoha lt 100
Le Less than or equal All organisation annual returns where the Average Volunteer Hours Per Week is less than or equal to 100
http://www.odata.charities.govt.nz/GrpOrgAllReturns?$filter=avgallvolunteerhoursperweek le 100

Arithmetic operators

Operator Description Example
Add Addition Organisations with more than 500 employees (full and part time) in their latest annual return
http://www.odata.charities.govt.nz/
GrpOrgLatestReturns?$filter=numberoffulltimeemployees add numberofparttimeemployees gt 500
Sub Subtraction All organisations which posted a loss in their last return (income – expenditure < 0)
http://www.odata.charities.govt.nz/
GrpOrgLatestReturns?$filter=Totalgrossincome sub Totalexpenditure lt 0
Mul Multiplication All annual returns for organisations, in which the staff average more than 2000 paid hours
http://www.odata.charities.govt.nz/
GrpOrgAllReturns?$filter=avgallpaidhoursperweek mul 52 gt 2000
Div Division All annual returns for organisations, in which their volunteers average more than 10 hours per week each. Note that when using division, you may need to add an additional check that the divisor (in this case avgnovolunteersperweek) is not zero, or you will receive an error message.
http://www.odata.charities.govt.nz/
GrpOrgAllReturns?$filter=avgnovolunteersperweek ne 0 and avgallvolunteerhoursperweek div avgnovolunteersperweek gt 10
Mod Modulo All organisations which have an even number of full-time employees in their latest annual return
http://www.odata.charities.govt.nz/
GrpOrgLatestReturns?$filter=numberoffulltimeemployees mod 2 eq 0

String functions

Function Example
Substringof All organisations whose names contain the word 'Association'
http://www.odata.charities.govt.nz/Organisations?$filter=substringof('Association', Name) eq true
Endswith All organisations whose names end with 'Association'
http://www.odata.charities.govt.nz/Organisations?$filter=endswith(Name, 'Association') eq true
Startswith All organisations whose names start with either A or B
http://www.odata.charities.govt.nz/Organisations?$filter=startswith(Name, 'A') eq true or startswith(Name, 'B') eq true
Length All organisations whose names are more than 100 characters long
http://www.odata.charities.govt.nz/Organisations?$filter=length(Name) gt 100
Tolower All organisations whose names contain the word 'Association' (ignoring case)
http://www.odata.charities.govt.nz/Organisations?$filter=substringof('association', tolower(Name)) eq true
Toupper All organisations whose names contain the word 'Association' (ignoring case)
http://www.odata.charities.govt.nz/Organisations?$filter=substringof('ASSOCIATION', toupper(Name)) eq true

Date functions

Function Example
Month All organisations registered in January of any year
http://www.odata.charities.govt.nz/Organisations?$filter=month(DateRegistered) eq 1
Year All organisations registered in 2010
http://www.odata.charities.govt.nz/Organisations?$filter=year(DateRegistered) eq 2010

More query examples

In this section are a few more examples of querying through the OData URI to help get you started.

Example