‘Flight Dirty’ by Will GoodFellow, Tom Noakes And Greg Sharp
septiembre 1, 2021
La Serrana – Branding – WIP
marzo 23, 2022
Plataforma gestora de datos, indicadores y mapas de visualización sobre la calidad del agua.
Contexto
El proyecto de Monitoreo Ciudadano es un proyecto de investigación de la Universidad de los Andes, liderado por investigadores de la Facultad de ingeniería y la Facultad de Economía. Es un proyecto de inversión mixta que busca recoger los datos de fuentes hídricas con el acompañamiento de las diferentes comunidades a las que pertenecen.
Este proyecto requiere gestionar los datos recolectados por las sondas y presentarlos para su respectivo análisis en base a las fechas, ubicaciones e indicadores unitarios.
Retos
- Flexibilidad en la edición de contenidos de las locaciones y de las sondas
- Almacenar los datos de forma segura y presentarlos en mapas y gráficos dinámicos.
- Integrarlos en otros portales web.
- Gestión de datos restringida.
Rol
Desarrollador Backend
Desarrollador Frontend
Implementación
El desarrollo se enfoco en crear una plataforma que gestionara los datos en Laravel, y que a su vez generara un modulo visualizador de datos que se pudiera integrar y usar en cualquier portal web sin usar iframes.
El Servicio API de la plataforma permite recibir los datos de sondas.
Arquitectura
El principal reto de la gestión de los datos es presentar la información correspondiente a la ubicación geográfica de OpenStreet Map. A continuación el análisis ejecutado y su código para poder encontrar los registros dentro del perímetro visualizado.
El calculo se hace en unidades del sistema internacional.
public function dataInPerimeter( float $latitude = 0, float $longitude = 0, $zoom)
{
$latName = "latitud";
$lonName = "longitud";
/*Como se calcularon los valores*/
// $sql = "((ACOS(SIN(? * PI() / 180) * SIN(" . $latName . " * PI() / 180) + COS(? * PI() / 180) * COS(" .
// $latName . " * PI() / 180) * COS((? - " . $lonName . ") * PI() / 180)) * 180 / PI()) * 60 * ?) as distance";
$km = (40000/pow(2, $zoom))* 2;
$sql = "6371 *acos(
cos( radians(?))*
cos(radians(".$latName."))*
cos(radians(".$lonName.") - radians(?))
+ sin (radians(?))*
sin(radians(".$latName."))
) AS distance";
$query = Registro::whereHas('transferencia', function($query){
$query->where('estado',1);
})
->select('id', 'hg', 'region_id', 'longitud', 'latitud', 'conduct', 'ph','temperatura','od', 'created_at')->selectRaw($sql, [$latitude, $longitude, $latitude])
->havingRaw('distance BETWEEN 0 AND '.$km)
->orderBy('distance', 'ASC')
->get();
return $query;
}
{
"registros": [
{
"id": 62,
"hg": null,
"region_id": 1,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 10.11,
"ph": 5.24,
"temperatura": 16,
"od": 7.77,
"created_at": "2019-01-25T23:38:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 61,
"hg": null,
"region_id": 1,
"longitud": -74.072422,
"latitud": 4.600916,
"conduct": 13,
"ph": 8.89,
"temperatura": 17.56,
"od": 8.01,
"created_at": "2019-01-25T23:38:00.000000Z",
"distance": 0.40360244414584256
},
{
"id": 60,
"hg": null,
"region_id": 1,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 14,
"ph": 4.25,
"temperatura": 14,
"od": 11,
"created_at": "2019-01-25T23:30:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 59,
"hg": null,
"region_id": 1,
"longitud": -74.072422,
"latitud": 4.600916,
"conduct": 12.96,
"ph": 6.87,
"temperatura": 16.16,
"od": 9.17,
"created_at": "2019-01-25T23:24:00.000000Z",
"distance": 0.40360244414584256
},
{
"id": 58,
"hg": null,
"region_id": 1,
"longitud": -74.067635,
"latitud": 4.601806,
"conduct": 17.72,
"ph": 6.23,
"temperatura": 17.72,
"od": 13,
"created_at": "2019-01-25T23:18:00.000000Z",
"distance": 0.13989965206078228
},
{
"id": 56,
"hg": null,
"region_id": 1,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 16.25,
"ph": 3.82,
"temperatura": 17.55,
"od": 8.31,
"created_at": "2019-01-25T23:12:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 57,
"hg": null,
"region_id": 1,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 9.19,
"ph": 4.84,
"temperatura": 17.45,
"od": 9.34,
"created_at": "2019-01-25T23:12:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 55,
"hg": null,
"region_id": 1,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 8.25,
"ph": 6.17,
"temperatura": 16.9,
"od": 10.65,
"created_at": "2019-01-25T23:09:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 54,
"hg": null,
"region_id": 1,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 16.34,
"ph": 3.67,
"temperatura": 16.34,
"od": 10.34,
"created_at": "2019-01-25T22:41:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 53,
"hg": null,
"region_id": 1,
"longitud": -74.072422,
"latitud": 4.600916,
"conduct": 14.2,
"ph": 7.4,
"temperatura": 22.14,
"od": 7.77,
"created_at": "2018-12-05T18:51:00.000000Z",
"distance": 0.40360244414584256
},
{
"id": 51,
"hg": null,
"region_id": 1,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 12,
"ph": 7.25,
"temperatura": 17.56,
"od": 11,
"created_at": "2018-12-05T18:45:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 52,
"hg": null,
"region_id": 1,
"longitud": -74.070932,
"latitud": 4.60065,
"conduct": 10,
"ph": 7.12,
"temperatura": 19.14,
"od": 6.9,
"created_at": "2018-12-05T18:45:00.000000Z",
"distance": 0.2477183569756539
},
{
"id": 50,
"hg": null,
"region_id": 1,
"longitud": -74.072422,
"latitud": 4.600916,
"conduct": 12.14,
"ph": 9.89,
"temperatura": 19.05,
"od": 8.23,
"created_at": "2018-12-05T18:38:00.000000Z",
"distance": 0.40360244414584256
},
{
"id": 49,
"hg": null,
"region_id": 1,
"longitud": -74.072422,
"latitud": 4.600916,
"conduct": 10.24,
"ph": 7.14,
"temperatura": 18.25,
"od": 8.62,
"created_at": "2018-12-05T18:38:00.000000Z",
"distance": 0.40360244414584256
},
{
"id": 48,
"hg": null,
"region_id": 1,
"longitud": -74.067635,
"latitud": 4.601806,
"conduct": 8.245,
"ph": 7.01,
"temperatura": 18.24,
"od": 12.06,
"created_at": "2018-12-05T18:36:00.000000Z",
"distance": 0.13989965206078228
},
{
"id": 47,
"hg": null,
"region_id": 1,
"longitud": -74.067635,
"latitud": 4.601806,
"conduct": 8.69,
"ph": 6.09,
"temperatura": 17.24,
"od": 6.45,
"created_at": "2018-12-05T18:34:00.000000Z",
"distance": 0.13989965206078228
},
{
"id": 46,
"hg": null,
"region_id": 1,
"longitud": -74.072422,
"latitud": 4.600916,
"conduct": 6.6,
"ph": 7.86,
"temperatura": 17,
"od": 16,
"created_at": "2018-12-04T17:40:00.000000Z",
"distance": 0.40360244414584256
},
{
"id": 45,
"hg": null,
"region_id": 1,
"longitud": -74.072422,
"latitud": 4.600916,
"conduct": 20,
"ph": 9.1,
"temperatura": 18.55,
"od": 8,
"created_at": "2018-12-04T17:39:00.000000Z",
"distance": 0.40360244414584256
},
{
"id": 44,
"hg": null,
"region_id": 1,
"longitud": -74.072422,
"latitud": 4.600916,
"conduct": 6.45,
"ph": 8.07,
"temperatura": 17.56,
"od": 6.14,
"created_at": "2018-12-04T17:35:00.000000Z",
"distance": 0.40360244414584256
},
{
"id": 43,
"hg": null,
"region_id": 1,
"longitud": -74.072422,
"latitud": 4.600916,
"conduct": 7,
"ph": 9.38,
"temperatura": 17.45,
"od": 10.14,
"created_at": "2018-12-04T17:34:00.000000Z",
"distance": 0.40360244414584256
},
{
"id": 42,
"hg": null,
"region_id": 1,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 12.6,
"ph": 7.204,
"temperatura": 21.1,
"od": 14,
"created_at": "2018-10-17T16:23:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 41,
"hg": null,
"region_id": 1,
"longitud": -74.070932,
"latitud": 4.60065,
"conduct": 14,
"ph": 9.11,
"temperatura": 21.06,
"od": 9.03,
"created_at": "2018-08-31T20:16:00.000000Z",
"distance": 0.2477183569756539
},
{
"id": 40,
"hg": null,
"region_id": 1,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 21.21,
"ph": 5.981,
"temperatura": 17.27,
"od": 10.36,
"created_at": "2018-08-31T19:18:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 39,
"hg": null,
"region_id": 1,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 10.11,
"ph": 4.711,
"temperatura": 18.11,
"od": 9.48,
"created_at": "2018-08-31T17:08:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 38,
"hg": null,
"region_id": 1,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 9.88,
"ph": 6.78,
"temperatura": 18.37,
"od": 10.78,
"created_at": "2018-08-31T17:06:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 37,
"hg": null,
"region_id": 1,
"longitud": -74.070932,
"latitud": 4.60065,
"conduct": 21.14,
"ph": 7.89,
"temperatura": 17.45,
"od": 8.15,
"created_at": "2018-08-31T17:02:00.000000Z",
"distance": 0.2477183569756539
},
{
"id": 35,
"hg": null,
"region_id": 1,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 17.88,
"ph": 6.47,
"temperatura": 17.89,
"od": 8.45,
"created_at": "2018-08-31T17:00:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 36,
"hg": null,
"region_id": 1,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 22.04,
"ph": 4.12,
"temperatura": 16.24,
"od": 9.31,
"created_at": "2018-08-31T17:00:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 34,
"hg": null,
"region_id": 1,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 20.75,
"ph": 6.45,
"temperatura": 16.87,
"od": 8.98,
"created_at": "2018-08-31T16:56:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 33,
"hg": null,
"region_id": 1,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 19.78,
"ph": 6.48,
"temperatura": 18.15,
"od": 10,
"created_at": "2018-08-31T16:55:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 32,
"hg": null,
"region_id": 1,
"longitud": -74.072422,
"latitud": 4.600916,
"conduct": 17.12,
"ph": 9.44,
"temperatura": 22,
"od": 10.91,
"created_at": "2018-08-30T16:04:00.000000Z",
"distance": 0.40360244414584256
},
{
"id": 31,
"hg": null,
"region_id": 1,
"longitud": -74.072422,
"latitud": 4.600916,
"conduct": 14.22,
"ph": 8.18,
"temperatura": 23.01,
"od": 12.56,
"created_at": "2018-08-30T15:57:00.000000Z",
"distance": 0.40360244414584256
},
{
"id": 30,
"hg": null,
"region_id": 1,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 10.12,
"ph": 7.204,
"temperatura": 18,
"od": 6.14,
"created_at": "2018-08-30T15:53:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 28,
"hg": null,
"region_id": 1,
"longitud": -74.072422,
"latitud": 4.600916,
"conduct": 44.2,
"ph": 4.49,
"temperatura": 22.21,
"od": 7.55,
"created_at": "2018-07-19T20:48:00.000000Z",
"distance": 0.40360244414584256
},
{
"id": 29,
"hg": null,
"region_id": 1,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 8.61,
"ph": 6.45,
"temperatura": 17.2,
"od": 4.56,
"created_at": "2018-07-19T20:48:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 26,
"hg": null,
"region_id": 1,
"longitud": -74.072422,
"latitud": 4.600916,
"conduct": 27.89,
"ph": 7.88,
"temperatura": 18.99,
"od": 8.12,
"created_at": "2018-07-19T20:47:00.000000Z",
"distance": 0.40360244414584256
},
{
"id": 27,
"hg": null,
"region_id": 1,
"longitud": -74.072422,
"latitud": 4.600916,
"conduct": 24.55,
"ph": 9.44,
"temperatura": 22.45,
"od": 10.14,
"created_at": "2018-07-19T20:47:00.000000Z",
"distance": 0.40360244414584256
},
{
"id": 25,
"hg": null,
"region_id": 1,
"longitud": -74.070932,
"latitud": 4.60065,
"conduct": 7.23,
"ph": 7.12,
"temperatura": 20.17,
"od": 7.55,
"created_at": "2018-07-19T20:39:00.000000Z",
"distance": 0.2477183569756539
},
{
"id": 24,
"hg": null,
"region_id": 1,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 11.29,
"ph": 5.78,
"temperatura": 14.88,
"od": 6.867,
"created_at": "2018-07-19T20:27:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 23,
"hg": null,
"region_id": 1,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 10.56,
"ph": 7.12,
"temperatura": 19.44,
"od": 6.99,
"created_at": "2018-07-19T20:25:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 22,
"hg": null,
"region_id": 1,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 57.45,
"ph": 4.89,
"temperatura": 18.44,
"od": 7.12,
"created_at": "2018-07-19T20:25:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 21,
"hg": null,
"region_id": 2,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 7.44,
"ph": 4.24,
"temperatura": 15.25,
"od": 5.14,
"created_at": "2018-07-19T20:21:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 17,
"hg": null,
"region_id": 2,
"longitud": -74.072422,
"latitud": 4.600916,
"conduct": 16.55,
"ph": 6.782,
"temperatura": 22.31,
"od": 13.88,
"created_at": "2018-06-29T19:53:00.000000Z",
"distance": 0.40360244414584256
},
{
"id": 18,
"hg": null,
"region_id": 2,
"longitud": -74.072422,
"latitud": 4.600916,
"conduct": 16.52,
"ph": 8.021,
"temperatura": 22.87,
"od": 14,
"created_at": "2018-06-29T19:53:00.000000Z",
"distance": 0.40360244414584256
},
{
"id": 16,
"hg": null,
"region_id": 2,
"longitud": -74.064707,
"latitud": 4.602244,
"conduct": 6.28,
"ph": 5.824,
"temperatura": 21.81,
"od": 10.37,
"created_at": "2018-06-29T19:13:00.000000Z",
"distance": 0.46533053205593494
},
{
"id": 15,
"hg": null,
"region_id": 2,
"longitud": -74.072422,
"latitud": 4.600916,
"conduct": 16.22,
"ph": 9.804,
"temperatura": 21.4,
"od": 18.95,
"created_at": "2018-06-29T18:34:00.000000Z",
"distance": 0.40360244414584256
},
{
"id": 14,
"hg": null,
"region_id": 2,
"longitud": -74.067635,
"latitud": 4.601806,
"conduct": 18.21,
"ph": 7.152,
"temperatura": 22.1,
"od": 8.85,
"created_at": "2018-06-29T18:32:00.000000Z",
"distance": 0.13989965206078228
},
{
"id": 13,
"hg": null,
"region_id": 2,
"longitud": -74.072422,
"latitud": 4.600916,
"conduct": 9.93,
"ph": 9.812,
"temperatura": 22.1,
"od": 17.42,
"created_at": "2018-06-29T18:30:00.000000Z",
"distance": 0.40360244414584256
},
{
"id": 11,
"hg": null,
"region_id": 2,
"longitud": -74.072422,
"latitud": 4.600916,
"conduct": 11.01,
"ph": 5.12,
"temperatura": 21.87,
"od": 7.4,
"created_at": "2018-06-29T16:44:00.000000Z",
"distance": 0.40360244414584256
},
{
"id": 12,
"hg": null,
"region_id": 2,
"longitud": -74.072422,
"latitud": 4.600916,
"conduct": 12.46,
"ph": 6.87,
"temperatura": 21.24,
"od": 6.17,
"created_at": "2018-06-29T16:44:00.000000Z",
"distance": 0.40360244414584256
},
{
"id": 10,
"hg": null,
"region_id": 2,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 10.12,
"ph": 7.204,
"temperatura": 18,
"od": 6.14,
"created_at": "2018-06-29T16:41:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 9,
"hg": null,
"region_id": 2,
"longitud": -74.064707,
"latitud": 4.602244,
"conduct": 9.12,
"ph": 6.987,
"temperatura": 22.44,
"od": 12,
"created_at": "2018-06-29T16:32:00.000000Z",
"distance": 0.46533053205593494
},
{
"id": 8,
"hg": null,
"region_id": 2,
"longitud": -74.066432,
"latitud": 4.601489,
"conduct": 9.65,
"ph": 6.854,
"temperatura": 22.12,
"od": 10.01,
"created_at": "2018-06-29T16:13:00.000000Z",
"distance": 0.26355375848360413
},
{
"id": 7,
"hg": null,
"region_id": 2,
"longitud": -74.0656,
"latitud": 4.603385,
"conduct": 8.95,
"ph": 6.204,
"temperatura": 13,
"od": 10.01,
"created_at": "2018-06-29T15:59:00.000000Z",
"distance": 0.42215731979874643
},
{
"id": 6,
"hg": null,
"region_id": 2,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 11.31,
"ph": 7.38,
"temperatura": 18.6,
"od": 5.56,
"created_at": "2018-06-27T16:41:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 5,
"hg": null,
"region_id": 2,
"longitud": -74.065282,
"latitud": 4.603333,
"conduct": 10.11,
"ph": 6.58,
"temperatura": 19.56,
"od": 7,
"created_at": "2018-06-27T16:38:00.000000Z",
"distance": 0.4493249442694154
},
{
"id": 4,
"hg": null,
"region_id": 2,
"longitud": -74.070932,
"latitud": 4.60065,
"conduct": 12.46,
"ph": 6.99,
"temperatura": 21,
"od": 9.38,
"created_at": "2018-06-27T16:23:00.000000Z",
"distance": 0.2477183569756539
}
],
"message": "Retrieved successfully"
}
