Database query for getting the closest destinations in Drupal 8

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
27
28
29
30
31
32
33
<?php
// Database service. It's better to use dependency injection in your class.
$database = \Drupal::service('database');
$earth_radius = 3959;
// Coordinates of the place from which you would like to get nearest places.
$lat = '52.486081';
$lon = '13.404616';
// How many nearest places we would like to show?
$limit = 10;
// Radius to search.
$radius = 100;
$query = $database->select('my_table_name_from_db', 'destination');
$query->fields('destination');
$query->orderBy('distance');
$query->range(0, $limit);
// field_latitude and field_longitude - columns names from 'my_table_name_from_db'
// where coordinates stored.
$expr = "(
$earth_radius * acos(
cos(radians(:lat))
* cos(radians(field_latitude))
* cos(radians(field_longitude) - radians(:lon))
+ sin(radians(:lat))
* sin(radians(field_latitude))
)
)";
$query->addExpression($expr, 'distance', array(':lat' => $lat, ':lon' => $lon));
$query->having('distance <= :radius', array(':radius' => $radius));
$destinations = $query->execute()->fetchAll();
Share Comments