config jsor library to make symfony 2/doctrine 2 work with postgis extension

Question

Working with Symfony 2.8.24 and PostGIS

I need to THE TITLE. I found this in github but the problem is the configuration steps there didn't help me a lot. I don't know if it is because I can't use composer online (proxy) to install it as the first step states.

IRDK where to start, for example, I don't see the path 'Jsor\Doctrine\PostGIS\Types\GeographyType' anywhere in the files I am using so I don't know where to copy them to use them, or maybe it has nothing to do with it.

I know it's asking a lot but could anyone walk me through it or point me somewhere on how 2 include this library in symfony2.8 from the beginning, I have been using Symfony for a while now (not postgis) and still have a really hard time including this kind of third party libs, so we're talking baby steps here. thanx

Answer 1

Ensure you have the postgis extension installed on your postgresql server.

Install the package via composer.

composer require jsor/doctrine-postgis

Add the types to doctrine-bundle's configuration.

Example:

# app/config/config.yml
doctrine:
  orm:
    # [..]
    entity_managers:
      default:
        dql:
          string_functions:
            Geometry:             Jsor\Doctrine\PostGIS\Functions\Geometry
            Geography:            Jsor\Doctrine\PostGIS\Functions\Geography
            ST_Distance:          Jsor\Doctrine\PostGIS\Functions\ST_Distance
            ST_Distance_Sphere:   Jsor\Doctrine\PostGIS\Functions\ST_Distance_Sphere
            ST_DWithin:           Jsor\Doctrine\PostGIS\Functions\ST_DWithin
            ST_GeomFromText:      Jsor\Doctrine\PostGIS\Functions\ST_GeomFromText
            ST_GeographyFromText: Jsor\Doctrine\PostGIS\Functions\ST_GeographyFromText
            ST_Transform:         Jsor\Doctrine\PostGIS\Functions\ST_Transform
            ST_Point:             Jsor\Doctrine\PostGIS\Functions\ST_Point
            ST_SetSRID:           Jsor\Doctrine\PostGIS\Functions\ST_SetSRID
            ST_AsEWKT:            Jsor\Doctrine\PostGIS\Functions\ST_AsEWKT

Create an entity:

namespace My\Entity;

class GeoLocation
{
    protected $latitude;

    protected $longitude;

    protected $point;

    public function __construct($latitude, $longitude)
    {
        $this->longitude = $longitude;
        $this->latitude = $latitude;
        $this->setPoint();
    }

    protected function setPoint()
    {
        $this->point = sprintf(
          'POINT(%f %f)', 
          (string)$this->longitude,
          (string)$this->latitude
        );
    }

    public function getPoint()
    {
        return $this->point;
    }

    public function getLatitude()
    {
        return (float) $this->latitude;
    }

    public function getLongitude()
    {
        return (float) $this->longitude;
    }
}

Add a mapping for your entity:

My\Entity\GeoLocation:
  type: 'entity'
  indexes:
    idx_point:
      columns:
        - 'point'
      flags:
        - 'spatial'
  id:
    id:
      type: integer
      generator:
        strategy: AUTO
  fields:
    longitude:
      nullable: true
      type: 'decimal'
      precision: 9
      scale: 6
    latitude:
      nullable: true
      type: 'decimal'
      precision: 8
      scale: 6
    point:
      nullable: true
      type: 'geography'
      options:
        geometry_type: 'POINT'
        srid: 4326

Update your database schema:

app/console doctrine:schema:update [--force]

Now save some points ...

Finally use the postgis types in your doctrine repository:

public function findByDistanceFrom($latitude, $longitude, $distanceInMeters)
{
  $qb = $this->createQueryBuilder('geolocation');
  $pointFrom = 'Geography(ST_SetSRID(ST_Point(geolocation.longitude, geolocation.latitude), 4326))';
  $pointTo = 'Geography(ST_SetSRID(ST_Point(:longitude, :latitude), 4326))';
  $qb
    ->where( $qb->expr()->eq("ST_DWithin($pointFrom, $pointTo, :distance_in_meters)", $qb->expr()->literal(true) ) )
    ->setParameter('latitude', $latitude)
    ->setParameter('longitude', $longitude)
    ->setParameter('distance_in_meters', $distanceInMeters)
  ;

  return $qb
    ->getQuery()
    ->getResult()
  ;
}