Spgsp Laravel Package

Getting Started

Minimal Setup

1. Installation Add the package to composer.json with the provided repository config:

   composer require bnza/spgsp --dev
   

   (Note: The README references pbald/spgsp—verify the correct repo URL in your project.)

2. Doctrine Configuration Register the SPGSP\Doctrine\DBAL\Types\PostgisType in your config/database.php under the doctrine/dbal connection:

   'types' => [
       'spatial' => SPGSP\Doctrine\DBAL\Types\PostgisType::class,
   ],
   

3. First Use Case: Define a Spatial Field In an Entity, map a PostGIS-compatible column (e.g., POINT, POLYGON):

   use Doctrine\ORM\Mapping as ORM;
   use SPGSP\Doctrine\DBAL\Types\PostgisType;
   
   #[ORM\Entity]
   class Location
   {
       #[ORM\Column(type: 'spatial')]
       private $coordinates;
   }
   

4. Database Schema Ensure your PostgreSQL database has PostGIS enabled:

   CREATE EXTENSION postgis;
   

Implementation Patterns

Common Workflows

1. Querying Spatial Data

Use DQL with PostGIS functions (e.g., ST_Distance, ST_Intersects):

$locations = $entityManager->createQuery(
    'SELECT l FROM App\Entity\Location l
     WHERE ST_DWithin(l.coordinates, :point, 1000)'
)->setParameter('point', $searchPoint)->getResult();

2. Geometric Operations

Leverage PostGIS functions directly in queries:

// Buffer a point
$buffered = $entityManager->createQuery(
    'SELECT ST_AsText(ST_Buffer(l.coordinates, 500)) FROM App\Entity\Location l'
)->getSingleScalarResult();

3. Type Conversion

Convert between WKT (Well-Known Text) and objects:

// WKT to object
$point = PostgisType::convertToPHP('POINT(1 2)', $platform);

// Object to WKT
$wkt = PostgisType::convertToDatabaseValue($point, $platform);

4. Repository Methods

Add custom spatial queries to repositories:

public function findNearby($latitude, $longitude, $radius)
{
    $point = "SRID=4326;POINT($longitude $latitude)";
    return $this->createQueryBuilder('l')
        ->where("ST_DWithin(l.coordinates, :point, :radius)")
        ->setParameter('point', $point)
        ->setParameter('radius', $radius)
        ->getQuery()
        ->getResult();
}

Integration Tips

Laravel-Specific Adjustments

• Service Provider Binding Bind the PostgisType in a service provider for global use:

  $this->app->bind('spatial_type', function () {
      return new PostgisType();
  });
  

• Query Builder Extensions Extend Laravel’s query builder for spatial methods:

  use Illuminate\Database\Query\Builder;
  
  Builder::macro('near', function ($field, $latitude, $longitude, $radius) {
      $point = "SRID=4326;POINT($longitude $latitude)";
      return $this->whereRaw("ST_DWithin($field, ST_GeomFromText(:point), :radius)", [
          'point' => $point,
          'radius' => $radius,
      ]);
  });
  

Performance

• Indexing: Add GIST/GIN indexes for spatial columns:

  CREATE INDEX idx_location_coordinates ON locations USING GIST(coordinates);
  

• SRID Consistency: Always specify SRID (e.g., SRID=4326 for WGS84) to avoid implicit conversions.

Gotchas and Tips

Pitfalls

1. Deprecated Package

• Issue: Last release in 2016; may not work with modern Doctrine/Laravel.
  • Fix: Fork the repo and update dependencies (e.g., doctrine/dbal).
  • Alternative: Consider beberlei/doctrineextensions (active PostGIS support).

2. Type Mapping Conflicts

• Issue: spatial type may clash with Laravel’s default json/array types.
  • Fix: Explicitly namespace the type in entities:

    #[ORM\Column(type: 'spatial', options: ['spatial_type' => true])]
    

3. WKT Parsing Errors

• Issue: Invalid WKT strings (e.g., POINT(1) vs. POINT(1 2)) cause exceptions.
  • Fix: Validate input with:

    if (!PostgisType::checkType($wktString)) {
        throw new \InvalidArgumentException("Invalid WKT format");
    }
    

4. PostGIS Extension Missing

• Issue: Queries fail if PostGIS isn’t enabled.
  • Fix: Verify with:

    SELECT PostGIS_version();
    

Debugging Tips

1. Enable SQL Logging

Add to config/logging.php:

'channels' => [
    'doctrine' => [
        'driver' => 'single',
        'path' => storage_path('logs/doctrine.log'),
        'level' => 'debug',
    ],
],

Then log queries in a service provider:

$eventManager->addEventListener(
    ORM\Events::onFlush,
    function (ORM\Event\OnFlushEventArgs $args) {
        $em = $args->getEntityManager();
        $em->getConnection()->getConfiguration()->setSQLLogger(new \Doctrine\DBAL\Logging\EchoSQLLogger());
    }
);

2. Test with Simple Queries

Start with basic ST_AsText() to verify connectivity:

$wkt = $entityManager->createQuery(
    'SELECT ST_AsText(l.coordinates) FROM App\Entity\Location l'
)->getSingleScalarResult();

Extension Points

1. Custom PostGIS Functions

Extend the type to support additional functions:

class CustomPostgisType extends PostgisType
{
    public function getSQLDeclaration(array $column, AbstractPlatform $platform)
    {
        return 'GEOMETRY'; // Override for custom types
    }

    public function convertToPHPValueSQL($sqlExpr, AbstractPlatform $platform)
    {
        return "ST_AsText($sqlExpr)"; // Custom conversion
    }
}

2. Laravel Eloquent Integration

Create a trait for Eloquent models:

trait SpatialTrait
{
    public function scopeNear($query, $latitude, $longitude, $radius)
    {
        $point = "SRID=4326;POINT($longitude $latitude)";
        return $query->whereRaw("ST_DWithin({$this->getTable()}.coordinates, ST_GeomFromText(:point), :radius)", [
            'point' => $point,
            'radius' => $radius,
        ]);
    }
}

3. Geocoding Integration

Combine with a geocoding service (e.g., Google Maps API) to convert addresses to WKT:

public function geocodeAndStore($address)
{
    $coordinates = $this->geocodeService->getCoordinates($address);
    $this->coordinates = "SRID=4326;POINT($coordinates['lon'] $coordinates['lat'])";
    $this->save();
}