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.
Desarrollador Backend
Desarrollador Frontend
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.
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"
}
