craue / geo-bundle
Doctrine functions for calculating geographical distances in your Symfony project.
Installs: 317 525
Dependents: 1
Suggesters: 0
Security: 0
Stars: 125
Watchers: 10
Forks: 17
Open Issues: 8
Type:symfony-bundle
Requires
- php: ^7.3|^8
- doctrine/doctrine-bundle: ^1.6.12|^2
- doctrine/orm: ^2.5.2
- symfony/config: ~4.4|~5.3|^6
- symfony/dependency-injection: ~4.4|~5.3|^6
- symfony/framework-bundle: ~4.4|~5.3|^6
Requires (Dev)
- doctrine/annotations: ^1.6
- doctrine/data-fixtures: ^1.0.1
- doctrine/dbal: ^2.7|^3
- doctrine/doctrine-fixtures-bundle: ^2.4.1|^3.1
- phpunit/phpunit: ^9.5
- symfony/browser-kit: ~4.4|~5.3|^6
- symfony/phpunit-bridge: ^6
- symfony/yaml: ~4.4|~5.3|^6
Suggests
README
CraueGeoBundle provides Doctrine functions for your Symfony project which allow you to calculate geographical distances within database queries. This bundle is independent of any web service, so once you got it running, it will keep running. There are two Doctrine functions, which return a distance in km:
GEO_DISTANCE
takes latitude + longitude for origin and destinationGEO_DISTANCE_BY_POSTAL_CODE
takes country + postal code for origin and destination
Installation
Get the bundle
Let Composer download and install the bundle by running
composer require craue/geo-bundle
in a shell.
Enable the bundle
If you don't use Symfony Flex, register the bundle manually:
// in config/bundles.php return [ // ... Craue\GeoBundle\CraueGeoBundle::class => ['all' => true], ];
Or, for Symfony 3.4:
// in app/AppKernel.php public function registerBundles() { $bundles = [ // ... new Craue\GeoBundle\CraueGeoBundle(), ]; // ... }
Prepare the table with geographical data needed for calculations
The GEO_DISTANCE_BY_POSTAL_CODE
function, if you'd like to use it, relies on some data which has to be added to your
database first.
Create the table
The GeoPostalCode
entity provided contains the structure for the geographical data. You import it by calling either
# in a shell
php bin/console doctrine:migrations:diff
php bin/console doctrine:migrations:migrate
or
# in a shell
php bin/console doctrine:schema:update
or however you like.
Import the geographical data
This is probably the most annoying step: Storing all postal codes with their geographical positions for the countries you need. Fortunately, it's not that hard to get this information and import it into your database.
Go to http://download.geonames.org/export/zip/ and download the archives for the countries you need. Let's just take
DE.zip
. Unzip the included DE.txt
file, e.g. to /tmp/DE.txt
.
Create a fixture class (in a separate folder to be able to load only this one) which extends the provided base class:
// MyCompany/MyBundle/Doctrine/Fixtures/CraueGeo/MyGeonamesPostalCodeData.php namespace MyCompany\MyBundle\Doctrine\Fixtures\CraueGeo; use Craue\GeoBundle\Doctrine\Fixtures\GeonamesPostalCodeData; use Doctrine\Common\Persistence\ObjectManager; class MyGeonamesPostalCodeData extends GeonamesPostalCodeData { public function load(ObjectManager $manager) { $this->clearPostalCodesTable($manager); $this->addEntries($manager, '/tmp/DE.txt'); } }
Now, backup your database! Don't blame anyone else for data loss if something goes wrong.
Then import the fixture and remember to use the --append
parameter.
Choose the following steps depending on the version of DoctrineFixturesBundle you're using.
DoctrineFixturesBundle < 3.0
Load the fixture(s) in the given folder.
# in a shell php bin/console doctrine:fixtures:load --append --fixtures="src/MyCompany/MyBundle/Doctrine/Fixtures/CraueGeo"
DoctrineFixturesBundle >= 3.1
- a) You first need to register the fixture as a service with a group of your choice.
# in app/config/config.yml services: my_geonames_postal_code_data: class: MyCompany\MyBundle\Doctrine\Fixtures\CraueGeo\MyGeonamesPostalCodeData public: false tags: - { name: doctrine.fixture.orm, group: my_geo_data }
- b) It's also possible to register all classes in a specific folder as services.
# in app/config/config.yml services: MyCompany\MyBundle\Doctrine\Fixtures\CraueGeo\: resource: '../../src/MyCompany/MyBundle/Doctrine/Fixtures/CraueGeo/*' public: false tags: - { name: doctrine.fixture.orm, group: my_geo_data }
- Then, load the fixture(s) of that group.
# in a shell
php bin/console doctrine:fixtures:load --append --group=my_geo_data
That's it.
You can also use other data sources you have access to, and write a custom fixture to import it.
If you have out of memory issues when importing a large number of entries try adding the --no-debug
switch to avoid
logging every single Doctrine query.
Usage
Let's say you have an entity Poi
containing countries and postal codes. Now you wish to find all entities within a
specific geographical distance with a radius of $radiusInKm
from a given postal code $postalCode
in country
$country
, and order them by distance.
use MyCompany\MyBundle\Entity\Poi; // example values which could come from a form, remember to validate/sanitize them first $country = 'DE'; $postalCode = '10115'; $radiusInKm = 10; // create a query builder $queryBuilder = $this->getDoctrine()->getEntityManager()->getRepository(Poi::class)->createQueryBuilder('poi'); // build the query $queryBuilder ->select('poi, GEO_DISTANCE_BY_POSTAL_CODE(:country, :postalCode, poi.country, poi.postalCode) AS HIDDEN distance') ->having('distance <= :radius') ->setParameter('country', $country) ->setParameter('postalCode', $postalCode) ->setParameter('radius', $radiusInKm) ->orderBy('distance') ;
Advanced stuff
Using the Doctrine functions for a different database platform
By default, the Doctrine functions are automatically registered for usage with MySQL. But you can tell the bundle that
you want to use them with a different database platform by setting a flavor
:
# in app/config/config.yml craue_geo: flavor: postgresql
Currently, the following flavors are supported:
mysql
: MySQL (default value)postgresql
: PostgreSQLnone
: prevents registration of the Doctrine functions in case you want to do it manually
As PostgreSQL doesn't support aliases in the HAVING clause and further requires poi
to appear in the GROUP BY clause,
you need to adapt the query (from the usage example above):
$queryBuilder ->select('poi, GEO_DISTANCE_BY_POSTAL_CODE(:country, :postalCode, poi.country, poi.postalCode) AS HIDDEN distance') ->having('GEO_DISTANCE_BY_POSTAL_CODE(:country, :postalCode, poi.country, poi.postalCode) <= :radius') ->setParameter('country', $country) ->setParameter('postalCode', $postalCode) ->setParameter('radius', $radiusInKm) ->groupBy('poi') ->orderBy('distance') ;
Avoid creating the postal code table
If you want to avoid registering the GeoPostalCode
entity (and as a result, avoid creating the craue_geo_postalcode
table) at all, add
# in app/config/config.yml craue_geo: enable_postal_code_entity: false
to your configuration.
Use custom names for the Doctrine functions
If you don't like the default names or need to avoid conflicts with other functions, you can set custom names:
# in app/config/config.yml craue_geo: functions: geo_distance: MY_VERY_OWN_GEO_DISTANCE_FUNCTION geo_distance_by_postal_code: MY_VERY_OWN_GEO_DISTANCE_BY_POSTAL_CODE_FUNCTION