[PHP] Area search, distance calculation and address conversion in TYPO3

Last update of the post 2023-05-22

Radius search via MySQL

Here is an excerpt from a possible repository. In this example the table tx_any_domain_model_any has at least the columns uid, latitude and longitude:

<?php
declare(strict_types=1);
namespace Vendor\Any\Domain\Repository;


use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;

class AnyRepository
{
    const TABLE_NAME = 'tx_any_domain_model_any';

    public function findByRadius(float $latitude, float $longitude, int $distance): array
    {
        $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable(self::TABLE_NAME);
        return (array)$connection->executeQuery($this->getSqlForRadialSearch($latitude, $longitude, $distance));
    }

    protected function getSqlForRadialSearch(float $latitude, float $longitude, int $distance): string
    {
        $sql = 'SELECT
            uid,
            latitude,
            longitude,
            (
                6371 * acos(
                    cos(
                        radians(' . $latitude . ')
                    ) * cos(
                        radians( latitude )
                    ) * cos(
                        radians( longitude ) - radians(' . $longitude . ')
                    ) + sin(
                        radians(' . $latitude . ')
                    ) * sin(
                        radians( latitude )
                    )
                )
            ) AS distance

        FROM
            ' . self::TABLE_NAME . '

        HAVING
            distance <= ' . $distance . '

        ORDER BY
            distance ASC;';

        return $sql;
    }
}

Distance calculation between 2 points via PHP

Mit PHP kann man auch ganz einfach die Entfernung in KM missen, wenn man zwei Geo-Koordinaten (also 2 Breiten- und 2 Längengrade) zur Verfügung hat:

/**
 * Calculate distance between 2 geo coordinates (2x lat and lon) in KM
 *
 * @param float $latitude1
 * @param float $longitude1
 * @param float $latitude2
 * @param float $longitude2
 * @return float
 */
public function calculateDistance(float $latitude1, float $longitude1, float $latitude2, float $longitude2): float
{
    $earthRadius = 6371; // in KM
    $lat1InRadians = deg2rad($latitude1);
    $lon1InRadians = deg2rad($longitude1);
    $lat2InRadians = deg2rad($latitude2);
    $lon2InRadians = deg2rad($longitude2);

    $deltaLat = $lat2InRadians - $lat1InRadians;
    $deltaLon = $lon2InRadians - $lon1InRadians;

    $angle = sin($deltaLat / 2) * sin($deltaLat / 2) + cos($lat1InRadians) * cos($lat2InRadians)
        * sin($deltaLon / 2) * sin($deltaLon / 2);
    $centralAngle = 2 * atan2(sqrt($angle), sqrt(1 - $angle));
    $distanceInKm = $earthRadius * $centralAngle;

    return round($distanceInKm, 2);
}

Address conversion to geolocation

Conversion of an address into its geo-coordinates (latitude and longitude) using OpenStreetMap:

/**
 * Use openstreetmap to convert an address to geo coordinates
 *
 * @param string $address "Kunstmühlstr. 12a, 83026 Rosenheim, Deutschland"
 * @return array ['latitude' => 0.0, 'longitude' => 0.0]
 * @throws RequestException
 */
public function getCoordinatesFromAddress(string $address): array
{
    $coordinates = [
        'latitude' => 0.0,
        'longitude' => 0.0,
    ];
    $requestFactory = \TYPO3\CMS\Core\Utility\GeneralUtility::makeInstance(\TYPO3\CMS\Core\Http\RequestFactory::class);
    $request = $requestFactory->request(
        'https://nominatim.openstreetmap.org/search?format=json&polygon=1&q=' . urlencode($address)
    );
    if ($request->getStatusCode() !== 200) {
        throw new \RuntimeException('Could not connect to nominatim.openstreetmap.org', 1683405955);
    }
    $result = $request->getBody()->getContents();
    $resultArray = json_decode($result, true);
    if (isset($resultArray[0]['lat']) && isset($resultArray[0]['lon'])) {
        $coordinates['latitude'] = (float)$resultArray[0]['lat'];
        $coordinates['longitude'] = (float)$resultArray[0]['lon'];
    }
    return $coordinates;
}