Create PHP/MySQL code to extract and store data from a website which provides tide data.
The website contains a range of locations, and allows user to submit queries to retrieve the tide times for each location for specified dates.
I require PHP code which can be run to automatically extract and store the data into MySQL tables, for all available locations, for a whole specified year.
## Deliverables
Tide Data Extraction Specification
Summary
Create PHP/MySQL code to extract and store data from a website which provides tide data.
The website contains a range of locations, and allows user to submit queries to retrieve the tide times for each location for specified dates.
I require PHP code which can be run to automatically extract and store the data into MySQL tables, for all available locations, for a whole specified year.
Data Source
Bureau of Meteorology, Australia
Main Page: [login to view URL]
This enables user to select locations in a number of different states / regions, for any given date to query the times of low and high tides.
Languages Required
All code to be PHP v5.
All data to be stored into MySQL database.
Location Data to Obtain
Within each state/region there are standard ports and secondary ports
Firstly I need a list of all such ports. For each port I need:
1. Name (eg. "Newcastle")
2. Station id (eg. "nsw_60310") [may not be available for secondary ports?]
3. Location (latitude and longitude in decimal degrees) [I am not sure of this is available directly from the site. It might be obtainable via geocoding eg. "Newcastle, NSW, Australia", or else from coordinates on map image, which appear to be designed as lat/long grids]
For each secondary port I also need:
4. Name or stationid of primary port it is linked to
5. The time difference from that primary port (in minutes, +ve if after, -ve if before primary port) [Some give hrs/mins -> convert to minutes]
Tide Data for Extraction
NB - Each website query for a given date provides data for 1 week, starting on the given date
NB - Extracted date/time are all local times BUT always in non-daylight-savings time
1. Date/Time of tide (Year/Month/Day hour/minute) [NB - date/times should simply be recorded as given ie. local standard time]
2. Tide Type Flag - either Low Tide or High Tide
3. Tide height in metres above Prediction Datum, to 2 decimal places (or alternatively as integer cms) [Unsure if any number might ever be -ve, best to assume it might be]
Data Output Format
I would like all extracted data to be entered into a MySQL database.
I think that this would likely consist of two tables:
1. Locations table - one entry per location
2. Tide data - one entry per tide time/date per location
Routines to Create
1. Routine to create empty MySQL tables
2. Routine to populate MySQL locations table by scraping website
3. Routine to populate MySQL tide data table for a given year number (eg. 2010, 2011), by scraping website