1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
|
public function getCountryAction(){
$em = $this->getDoctrine()->getEntityManager();
$connection = $em->getConnection();
$sql = "SELECT gid, cntry_name, ST_AsGeoJSON(world_borders.geom) as geom, count(tut_user.location) AS density,
(SELECT count(*) FROM tut_user) as total
FROM world_borders LEFT JOIN tut_user
ON st_contains(world_borders.geom, tut_user.location)
GROUP BY world_borders.gid";
$statement = $connection->prepare($sql);
$statement->execute();
$fc = new FeatureCollection();
while ($row = $statement->fetch() ) {
$id = $row['gid'];
$geom = json_decode($row['geom']);
$cntry_name = $row['cntry_name'];
$density = $row['density'];
$total = $row['total'];
$feature = new Feature($id,$geom, array('country' => $cntry_name, 'density' => $density, 'total' => $total) );
$fc->addFeature($feature);
}
return $response = json_encode($fc); |
Partager