|
The food hygiene rating data published at www.food.gov.uk/ratings is available via an application programming interface (API) in XML and JSON formats. This is a free service and there is no need to register to use it. Terms and conditions apply.
The data provides the food hygiene rating or inspection result given to a business and reflects the standard of food hygiene found on the date of inspection. Businesses include restaurants, pubs, cafés, takeaways, hotels and other food outlets, as well as supermarkets and other food retailers.
Make sure your server can see the FSA URL (might need whitelisting) |
Create a new Data Share XML Feed Datasource using the relevant FSA URL e.g. http://ratings.food.gov.uk/OpenDataFiles/FHRS309en-GB.xml as the Location and the atFoodAgency.xsl as the XSL and give it a Name - this defines the table name in the Public schema in your Data Share database e.g. food_ratings.
As the Name entry is used as the table name, you must make sure that it doesn't contain spaces or invalid characters for table names. |
Create a Spatial Data Transformation Workflow Task to create a new table with the geometry.
Select the DataShare Connection as your Source Data, leave the Source Table name blank as this is specified in the SQL below. For the Output, pick your Data Share or SDW connection and enter a name for the Table you wish to be created.
Now tick Show expert mode and enter the following in the Additional parameters box replacing food_ratings with whatever you entered as the Name in Step 2:
-sql "SELECT *, ST_GeomFromText('POINT(' || \"Longitude\" || ' ' || \"Latitude\" || ')',4326) as wkb_geometry FROM food_ratings WHERE length(\"Longitude\") > 0" -t_srs "EPSG:27700" |