Getting Started

Minimal Setup

Installation:
```
composer require craue/geo-bundle
```
Enable the bundle in config/bundles.php (Symfony Flex) or AppKernel.php (legacy).
Database Schema: Run the migration to create the geo_postal_code table:
```
php bin/console doctrine:migrations:diff
php bin/console doctrine:migrations:migrate
```
(Note: The package provides a GeoPostalCode entity but does not auto-populate it. You must import postal code data manually.)

First Query: Use GEO_DISTANCE in a DQL query for latitude/longitude-based calculations:

$distance = $entityManager->createQuery(
    'SELECT GEO_DISTANCE(?, ?, lat, lng) AS distance FROM App\Entity\Location'
)->setParameter(1, 48.8566) // Origin lat
  ->setParameter(2, 2.3522)  // Origin lng
  ->getSingleScalarResult();

Implementation Patterns

Core Workflows

Lat/Lon Distance Queries: Use GEO_DISTANCE(origin_lat, origin_lng, dest_lat, dest_lng) in DQL for real-time distance filtering:

$nearbyLocations = $entityManager->createQuery(
    'SELECT l FROM App\Entity\Location l
     WHERE GEO_DISTANCE(?, ?, l.latitude, l.longitude) <= 10'
)->setParameters([$userLat, $userLng]);

Postal Code Integration:
- Pre-populate geo_postal_code with data from APIs (e.g., GeoNames) or CSV.
- Use GEO_DISTANCE_BY_POSTAL_CODE(country, postal_code, country, postal_code):
```
$distance = $entityManager->createQuery(
    'SELECT GEO_DISTANCE_BY_POSTAL_CODE(?, ?, ?, ?) AS distance'
)->setParameters(['DE', '10115', 'DE', '80335']);
```

Repository Abstraction: Create a custom repository method to encapsulate distance logic:

// src/Repository/LocationRepository.php
public function findNearby($latitude, $longitude, $maxDistanceKm)
{
    return $this->createQueryBuilder('l')
        ->where('GEO_DISTANCE(:lat, :lng, l.latitude, l.longitude) <= :maxDist')
        ->setParameters([
            'lat' => $latitude,
            'lng' => $longitude,
            'maxDist' => $maxDistanceKm,
        ])
        ->getQuery()
        ->getResult();
}

Hybrid Queries: Combine with native Doctrine filters for complex scenarios:

$qb = $entityManager->createQueryBuilder();
$qb->select('l')
   ->from('App\Entity\Location', 'l')
   ->where($qb->expr()->lt('GEO_DISTANCE(:lat, :lng, l.latitude, l.longitude)', ':maxDist'))
   ->andWhere('l.category = :category')
   ->setParameters(['lat' => 40.7128, 'lng' => -74.0060, 'maxDist' => 50, 'category' => 'restaurant']);

Integration Tips

Performance: Add a SPATIAL INDEX to latitude/longitude columns if your DB supports it (e.g., PostgreSQL CREATE INDEX idx_location_pos ON location USING GIST(pos)). (Note: The bundle does not auto-create indexes; handle manually.)
Data Sources: For geo_postal_code, use:
- CSV Imports: Write a console command to bulk-insert data.
- APIs: Cache responses from services like OpenStreetMap Nominatim or Google Maps API.

Testing: Mock the GEO_DISTANCE function in unit tests using Doctrine’s Query\Expr\Func:

$qb = $this->createMock(QueryBuilder::class);
$qb->expects($this->any())
   ->method('expr')
   ->willReturn(new Expr\Func('GEO_DISTANCE', [1, 2, 3, 4]));

Gotchas and Tips

Pitfalls

Missing Postal Code Data:

The GEO_DISTANCE_BY_POSTAL_CODE function fails silently if no matching postal code exists in geo_postal_code.

Fix: Add a fallback or validation layer:

$postalCodes = $entityManager->getRepository(GeoPostalCode::class)
    ->findBy(['country' => 'DE', 'postalCode' => '10115']);
if (empty($postalCodes)) {
    throw new \RuntimeException("Postal code data missing for DE-10115");
}

Precision Issues:
- Latitude/longitude values must be high-precision decimals (e.g., 52.5200 not 52.52).
- Tip: Use ST_Distance_Sphere (PostgreSQL) or GEOGRAPHY types if available for better accuracy.

Database Compatibility:

MySQL: Requires the haversine function. Enable it with:

CREATE FUNCTION GEO_DISTANCE(lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT) RETURNS FLOAT
DETERMINISTIC
RETURN 6371 * 2 * ASIN(SQRT(
    POWER(SIN((lat2 - lat1) * pi() / 180 / 2), 2) +
    COS(lat1 * pi() / 180) * COS(lat2 * pi() / 180) *
    POWER(SIN((lon2 - lon1) * pi() / 180 / 2), 2)
));

SQLite: Not supported natively. Use a PHP fallback (e.g., geodistance).

Caching:

Distance calculations are expensive. Cache results for static queries (e.g., "stores within 10km of ZIP code X"):

$cacheKey = "distance_{$country}_{$postalCode}_{$maxKm}";
return $cache->get($cacheKey, function() use ($entityManager, $country, $postalCode, $maxKm) {
    return $entityManager->createQuery(...)->getResult();
});

Debugging

Query Logging: Enable Doctrine debug mode to inspect generated SQL:
```
// config/packages/dev/doctrine.php
doctrine:
    dbal:
        logging: true
        profiling: true
```
Look for raw HAVERSINE or custom GEO_DISTANCE SQL.
Common Errors:
- "Function GEO_DISTANCE not found": The bundle does not register functions automatically. Ensure your DB supports it (see Database Compatibility).
- Floating-point errors: Use ROUND(GEO_DISTANCE(...), 2) to format results.

Extension Points

Custom Distance Functions: Extend the bundle by adding new DQL functions. Example:

// src/Doctrine/GeoDistanceExtension.php
namespace App\Doctrine;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;

class CustomGeoDistanceExtension extends \Doctrine\ORM\Query\AST\Functions\FunctionNode
{
    public function parse(Parser $parser) { /* ... */ }
    public function getSql(SqlWalker $sqlWalker) { /* ... */ }
}

services:
    App\Doctrine\CustomGeoDistanceExtension:
        tags: ['doctrine.query_ast_function']

Postal Code Importer: Create a console command to populate geo_postal_code:

// src/Command/ImportPostalCodesCommand.php
namespace App\Command;

use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;
use App\Entity\GeoPostalCode;

class ImportPostalCodesCommand extends Command
{
    protected function execute(InputInterface $input, OutputInterface $output

Geo Bundle Laravel Package