jsor / doctrine-postgis Goto Github PK

Hello,

I do not know how to setup an event subscriber and I don't understand the Symfony's documentation.

I have this error :

Attempted to load class "ORMSchemaEventSubscriber" from namespace "Jsor\Doctrine\PostGIS\Event".
Did you forget a "use" statement for another namespace?

i don't know what to do to implements correctly postgis to my symfony 4.2 application

Can someone help me ?

Hi ! it's me again :)

i'm stuck on how to use your PostGIS functions with Dql.

this is my code :

public function findTByGeo($lat, $lng, $distance = 30000) {
        $configuration = new \Doctrine\ORM\Configuration();
        $configuration->addCustomStringFunction('ST_Distance', 'Jsor\Doctrine\PostGIS\Functions\ST_Distance');
        $configuration->addCustomStringFunction('ST_GeographyFromText', 'Jsor\Doctrine\PostGIS\Functions\ST_GeographyFromText');

        $em = $this->getEntityManager();
        $dql = "SELECT t, ST_Distance(ST_GeographyFromText(SRID=4326;POINT(:lat :lng)), p.coordinates ) AS distance "
                . "FROM SiteBundle:Trainings t "
                . "JOIN t.place p "
                . "WHERE ST_Distance(ST_GeographyFromText(SRID=4326;POINT(:lat2 :lng2)), p.coordinates ) < :dist ";
        $requete = $em->createQuery($dql)
                ->setParameter(":lat", $lat)
                ->setParameter(":lng", $lng)
                ->setParameter(":lat2", $lat)
                ->setParameter(":lng2", $lng)
                ->setParameter(":dist", $distance);

        return $requete->getResult();
    }

i get this error now :

[Syntax Error] line 0, col 47: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got '='
500 Internal Server Error - QueryException
1 linked Exception: QueryException »

I don't understand why it doesn't work, am i doing something wrong ?

PS :
i got to declare your functions in config.yml like this

        dql:
            string_functions:
                st_distance: Jsor\Doctrine\PostGIS\Functions\ST_Distance
                st_geographyfromtext: Jsor\Doctrine\PostGIS\Functions\ST_GeographyFromText

since this:

$configuration = new \Doctrine\ORM\Configuration();
        $configuration->addCustomStringFunction('ST_Distance', 'Jsor\Doctrine\PostGIS\Functions\ST_Distance');
        $configuration->addCustomStringFunction('ST_GeographyFromText', 'Jsor\Doctrine\PostGIS\Functions\ST_GeographyFromText');

does not seems to work.

Thanks a lot if you can help me again =)

Hi,

I get the following error message:

PHP Fatal error: Call to a member function listSpatialIndexes() on null in /var/www/xxx/vendor/jsor/doctrine-postgis/src/Event/DBALSchemaEventSubscriber.php on line 318

It looks like schemaManager is null. I am using zf2 with doctrine 2. Just enabled your module by registering the event subscriber. Any ideas?

Thanks

In our Symfony instance (5.4) with the 1.8 version of the library, we didn’t define types in config/packages/doctrine.yaml like this :

doctrine:
    dbal:
        types:
            geography:
                class: 'Jsor\Doctrine\PostGIS\Types\GeographyType'
                commented: false
            geometry:
                class: 'Jsor\Doctrine\PostGIS\Types\GeometryType'
                commented: false

In dev and preprod environments, these types were loaded anyway and no error occurred. When we deployed in the production environment, error occurred because these types were not loaded :

Fatal error: Uncaught Doctrine\DBAL\Exception: Unknown column type "geometry" requested. Any Doctrine type that you use has to be registered with
\Doctrine\DBAL\Types\Type::addType(). You can get a list of all the known types with \Doctrine\DBAL\Types\Type::getTypesMap(). If this error occurs
during database introspection then you might have forgotten to register all database types for a Doctrine Type. Use
AbstractPlatform#registerDoctrineTypeMapping() or have your custom types implement Type#getMappedDatabaseTypes(). If the type name is empty
you might have a problem with the cache or forgot some mapping information. in
/var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php on line 282

Today, we have defined the types in the doctrine config file, so everything works. But we wanted to give you feedback because if it wasn’t very critical in our case, it could have been.

I think the behavior should be the same on all environments so there are 2 options here :

• For all environments, the types aren’t defined, error occurs.
• For all environments, the types aren’t defined but are loaded, no error occurs.

By the way, thanks for this great library.

Hey, it's me again !

When i use :

php app/console doctrine:schema:update --dump-sql

i have this :

 [RuntimeException]
  The geometry_type of a spatial column cannot be changed (Requested changing type from "POINT" to "GEOMETRY" for column "coordinates" in table "company")

But i didn't ask to change the type...
My entity is exactly the same as doctrine created with mapping:import

/**
     * @var geography
     *
     * @ORM\Column(name="coordinates", type="geography", nullable=true)
     */
    private $coordinates;

I didn't change anything, so i don't understand why it does not work.

thanks

There is problem after install dama/doctrine-test-bundle.

LogicException : It looks like you have registered the Jsor\Doctrine\PostGIS\Event\ORMSchemaEventSubscriber to more than one connection. Please register one instance per connection.

How can I resolve this problem ?

Hello. I worked on making the testsuite work for PHP 8.0 and I was testing stuff with postgis 3.1.

There's a lot of work to test postgis 1.5, 2.0, 2.1, and 2.2. But it's even harder now because many many many outputs from postgis have changed. Especially precision in calculations (leading to barely all numbers wrong in expected results).

To support PHP 7.1 as well as 8.0 in the test suite, I currently have no other solution than using the PHPUnit Bridge of Symfony, but it seems working nicely.

I post this because I can push my code as a PR but I want to know if you're ok to drop testsuite for old versions of postgis. If so, I'll send a pull request. (the work I did so far is already huge, so I'm not going to make old versions of postgis work with it)

I've got an entity mapping declaration like this:

/**
* @ORM\Column(name="location", type="geometry", options={"geometry_type"="POINT"})
*
* @Expose
*/
private $location;

When I run php app/console doctrine:schema:update in Symfony, it fails:

Unknown database type point requested, Doctrine\DBAL\Platforms\PostgreSQL92Platform may not support it.

PostGIS, is, of course, installed and activated as an extension.

Is this a bug, or do I have to configure this elsewhere?

I'm using version 1.1.0 of doctrine-postgis. Thanks!

bin/console doctrine:schema:validate gives:

[Mapping] FAIL - The entity-class '...' mapping is invalid:

• The field '...' uses a non-existant type 'geometry'.

This is solved by adding the custom DBAL types to app/config/config.yml:

doctrine:
    dbal:
        types:
            geography: 'Jsor\Doctrine\PostGIS\Types\GeographyType'
            geometry: 'Jsor\Doctrine\PostGIS\Types\GeometryType'
            raster: 'Jsor\Doctrine\PostGIS\Types\RasterType'

An error occurred when I performed some database entity updates or created entities in the console:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corres
ponds to your MySQL server version for the right syntax to use near 'current_schemas(false)) )
ORDER BY i.relname' at line 8

I checked the code and found the location of the error code.
location:E:\xampps\htdocs\myProj\vendor\jsor\doctrine-postgis\src\Schema\SchemaManager.php,52 line.
like this
`` public function listSpatialIndexes($table)
{
if (false !== strpos($table, '.')) {
list(, $table) = explode('.', $table);
}
#$sql ,This is the code where the error occurred.
$sql = "SELECT distinct i.relname, d.indkey, pg_get_indexdef(d.indexrelid) AS inddef, t.oid
FROM pg_class t
INNER JOIN pg_index d ON t.oid = d.indrelid
INNER JOIN pg_class i ON d.indexrelid = i.oid
WHERE i.relkind = 'i'
AND d.indisprimary = 'f'
AND t.relname = ?
AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = ANY (current_schemas(false)) )
ORDER BY i.relname";

    $tableIndexes = $this->connection->fetchAll(
        $sql,
        array(
            $this->trimQuotes($table)
        )
    );

    $indexes = array();
    foreach ($tableIndexes as $row) {
        if (!preg_match('/using\s+gist/i', $row['inddef'])) {
            continue;
        }

        $sql = "SELECT a.attname, t.typname
                FROM pg_attribute a, pg_type t
                WHERE a.attrelid = {$row['oid']}
                AND a.attnum IN (" . implode(',', explode(' ', $row['indkey'])) . ')
                AND a.atttypid = t.oid';

        $stmt = $this->connection->executeQuery($sql);
        $indexColumns = $stmt->fetchAll();

        foreach ($indexColumns as $indexRow) {
            if ('geometry' !== $indexRow['typname'] &&
                'geography' !== $indexRow['typname']) {
                continue;
            }

            if (!isset($indexes[$row['relname']])) {
                $indexes[$row['relname']] = array();
            }

            $indexes[$row['relname']][] = trim($indexRow['attname']);
        }
    }

    return $indexes;
}

Hi! I have custom type for doctrine, which extends from Jsor\Doctrine\PostGIS\Types\GeometryType:

<?php

namespace App\Entity\PropertyType;

use Doctrine\DBAL\Platforms\AbstractPlatform;
use Jsor\Doctrine\PostGIS\Types\GeometryType;

class PointType extends GeometryType
{
    public const POINT = 'point_type';

    public function convertToPHPValue($value, AbstractPlatform $platform): ?Point
    {
        if ($value === null) {
            return null;
        }
        $pattern = '/POINT\(([-]?[0-9]+[.]?[0-9]*) ([-]?[0-9]+[.]?[0-9]*)\)/';

        if (preg_match($pattern, $value, $matches)) {
            $latitude = (float) $matches[1];
            $longitude = (float) $matches[2];

            return new Point($latitude, $longitude);
        } else {
            throw new \DomainException('Incorrect string');
        }
    }

    public function convertToDatabaseValue($value, AbstractPlatform $platform): ?string
    {
        /** @var Point $value */
        if ($value === null) {
            return null;
        }
        return sprintf('POINT(%f %f)', $value->getLatitude(), $value->getLongitude());
    }

    public function requiresSQLCommentHint(AbstractPlatform $platform): bool
    {
        return true;
    }
}

My Entity field:

    #[ORM\Column(type: PointType::POINT, nullable: true)]
    protected ?Point $centerLocation = null;

When I create the first migration - it's ok, but if I try to work with my DB (update, new migrations, diff) then I get error:
The type of a spatial column cannot be changed (Requested changing type from "geometry" to "geometry" for column "center_location" in table "parking")

Has anyone encountered this? Any idea how to fix it? I would be grateful for your answers

Running doctrine-postgis 1.4.1 I am seeing deprecation warnings from PostGIS in our logs:

SQL function "st_distance_sphere" during startup
WARNING:  ST_Distance_Sphere signature was deprecated in 2.2.0. Please use ST_DistanceSphere
CONTEXT:  PL/pgSQL function _postgis_deprecate(text,text,text) line 14 at RAISE

We are using Symfony and the DQL function is mapped in the config like this:

ST_Distance_Sphere:     Jsor\Doctrine\PostGIS\Functions\ST_Distance_Sphere

The DQL producing the warning looks something like this

->addSelect("ST_Distance_Sphere(pl.geom, ST_Geomfromtext('POINT($long $lat)', 4326)) AS distance")

I've used this library to do a distance search (like a nearest-neighbour) and I had it with the query builder like:

        $res = $this->createQueryBuilder('l')
            ->addSelect('ST_Distance(l.point, :point) AS distance')
            ->orderBy('distance', 'asc')
            ->setMaxResults(1)
            ->setParameter('point', $point)
            ->getQuery()
            ->getOneOrNullResult();

But this was very slow, even though I had a spatial index on the point field. I came across an alternative way of doing it that is lightning fast:

$connection->executeQuery(
            'SELECT l.id
             FROM locations l 
             ORDER BY l.point <-> :point
             LIMIT 1',
            ['point' => $point]

But I can't get this to work in doctrine / DQL:

$this->createQueryBuilder('l')
            ->orderBy('l.point <-> :point')
            ->setMaxResults(1)
            ->setParameter('point', $point)
            ->getQuery()
            ->getOneOrNullResult();

As it complains about the operator ("<->")

[Doctrine\ORM\Query\QueryException]                                    
  [Syntax Error] line 0, col 65: Error: Expected end of string, got '<'  

Am I doing something wrong or is this just not supported?

Many thanks for the excellent package!

http://postgis.net/docs/reference.html#Operators

Initially posted on the wrong repo symfony/symfony#47912 my issue is related to this package. It causes duplication of messenger_messages table creation on doctrine schema (or migration) diff

Lines duplicated:

CREATE TABLE messenger_messages (id BIGSERIAL NOT NULL, body TEXT NOT NULL, headers TEXT NOT NULL, queue_name VARCHAR(190) NOT NULL, created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, available_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, delivered_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, PRIMARY KEY(id));
CREATE INDEX IDX_75EA56E0FB7336F0 ON messenger_messages (queue_name);
CREATE INDEX IDX_75EA56E0E3BD61CE ON messenger_messages (available_at);
CREATE INDEX IDX_75EA56E016BA31DB ON messenger_messages (delivered_at);

To reproduce add these packages to a symfony project composer file:

"jsor/doctrine-postgis": "^2.1",
"doctrine/doctrine-bundle": "^2.1",
"doctrine/doctrine-migrations-bundle": "^3.0",
"doctrine/orm": "^2.9",
"symfony/doctrine-messenger": "6.1.*",

In messenger.yaml config file set your transport with auto_setup: false
(doc: https://symfony.com/doc/current/messenger.html#transport-configuration)

run php bin/console doctrine:migra:diff --from-empty-schema or php bin/console doctrine:sch:up --dump-sql

Check the content generated (duplication)

Hi,

I need to change my db only for test to sqlite.
After reading your code, you never check if it's PostgreSQLPlatform or not.
Do you know if i can disabled your extension and annotation just for test environnement ?

Regards

&&, ~ etc.

These can only be implemented as DQL functions, is that right?

Hi @jsor and thank you for this repository.

My project is in Symfony 6.2.7 with Doctrine ORM 2.14.1.

I have done setup as you described https://github.com/jsor/doctrine-postgis/blob/main/docs/symfony.md

I have added 2 functions

This one is my dql query in User repository

public function findByLocationWithinDistance(
    string $locationPoint,
    int    $per_page = 10,
    int    $page = 1,
): ApiPlatformPaginator
{
    $count = $per_page <= 50 ? $per_page : 10;
    $qb = $this->createQueryBuilder('u')
        ->where('ST_DWithin(u.locationPoint::geography, ST_PointFromText(:locationPoint)::geography, :distance)')
        ->setParameter('locationPoint', $locationPoint)
        ->setParameter('distance', 500000)
        ->setFirstResult($count * ($page - 1))
        ->setMaxResults($count)
        ->getQuery();
    return new ApiPlatformPaginator(new Paginator($qb));
}

And this one is standard query, it is doing same thing and it is working on direct execution in pgAdmin

SELECT * FROM "user" as u WHERE ST_DWithin( u.location_point::geography, ST_PointFromText('SRID=4326;POINT(56.321057723568 -105.15506956512)')::geography, 500000)

The error is

{"message":"Uncaught Error: assert($token !== null)","context":{"exception":{"class":"AssertionError","message":"assert($token !== null)","code":1,"file":"/var/www/html/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php:2603"}},"level":500,"level_name":"CRITICAL","channel":"php","datetime":"2023-04-25T11:17:02.599771+00:00","extra":{}}

Is it know issue or new version of symfony or doctrine is not supported?

Hi there,
Wondered if anyone has come up against this:
CREATE EXTENSION
In AbstractPlatform.php line 434:
Unknown database type _text requested, Doctrine\DBAL\Platforms\PostgreSQL100Platform may not support it.

I'm running Symfony 4 as part of an API-Platform installation on Docker. But I wanted to add PostGIS so am trying the https://hub.docker.com/r/mdillon/postgis image instead of the vanilla postgres included with API-Platform.

I followed the installation and setup instructions included in your documentation and it seems to load ok until it comes across _text database type as shown in the log above.

I realise it's probably a bit too convoluted to try and reproduce, but any guesses as to what might be happening and how to work around it would be great.

OK - Sorry - fixed it already by mapping the type under the Doctrine - dbal - declaration in doctrine.yaml:
mapping_types:
_text: string

I have this entity with a spatial index (polygons)

/**
 * @ORM\Entity()
 * @ORM\Table(name = "mytable",
 *      options={"spatial_indexes"={"polygons_idx"}},
 *      indexes={
 *          @ORM\Index(name="polygons_idx", columns={"polygons"})
 *      }
 * )
 */
class MyTable
{
    /**
     * @var int
     *
     * @ORM\Column(type = "integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy = "AUTO")
     */
    protected $id;

    /**
     * @var int
     *
     * @ORM\Column(name="polygons", type = "geometry")
     */
    protected $polygons;

    /* ... */

}

What Doctrine tries to create upon schema update :

CREATE TABLE mytable (id INT AUTO_INCREMENT NOT NULL, polygons GEOMETRY NOT NULL COMMENT '(DC2Type:geometry)', INDEX polygons_idx (polygons), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;

I have this error

SQLSTATE[42000]: Syntax error or access violation: 1170 BLOB/TEXT column 'polygons' used in key specification without a key length

I think the problem is that keyword SPATIAL is missing in the index creation.
INDEX polygons_idx (polygons) should be INDEX SPATIAL polygons_idx (polygons) i think no ?

it seems that it's not possible (or at least I haven't found a way) to activate this extension while having the

create extension postgis

being done in one of the late migration

i.e you can't have a project that in 1.0 does not use postgis and have already some doctrine migration existing, and that in 2.0 you start using it , and you want to introduce this library + having a doctrine migration that will do create extension postgis, otherwise when you will try to re-run the migration from scratch (for example when creating a test environment)

as when initializing doctrine, it will trigger this error

In AbstractPostgreSQLDriver.php line 79:
                                                                                                            
  An exception occurred while executing 'SELECT PostGIS_Lib_Version()':                                     
                                                                                                            
  SQLSTATE[42883]: Undefined function: 7 ERROR:  function postgis_lib_version() does not exist              
  LINE 1: SELECT PostGIS_Lib_Version()                                                                      
                 ^                                                                                          
  HINT:  No function matches the given name and argument types. You might need to add explicit type casts.  
                                                                                                           

which is done here:

./vendor/jsor/doctrine-postgis/src/Schema/SchemaManager.php:    public function isPostGis2()

would you accept a patch that catch this error , throw a warning instead and return false ?

The following DQL:

SELECT b
FROM AppBundle\Entity\EntityB b
INNER JOIN AppBundle\Entity\EntityA a
    WITH ...
    AND ST_Contains(a.geom, b.geom)
WHERE
...

gives

Error: Expected =, <, <=, <>, >, >=, !=, got 'WHERE'

The workaround is to change it to:

ST_Contains(a.geom, b.geom) = true

Is this a DQL quirk, or something that we can fix?

I'm a complete newbie in GIS, but I'm wondering...

What is the point of using ST_AsEWKT for converting to PHP value, when ST_GeomFromText is used when converting to SQL value?

Also, since the SRID is already configured as an option on the field, doesn't that mean it should not change in the first place?

can you add ST_Extent?

Hi ! i'm trying to use your doctrine-postgis extension but i have some trouble.

the "installation" part is ok, but then you said :

**All you have to do is, to register an event subscriber.**
use Jsor\Doctrine\PostGIS\Event\ORMSchemaEventSubscriber;
$entityManager->getEventManager()->addEventSubscriber(new ORMSchemaEventSubscriber());

I don't understand where to put this or what i need to do with this.

Thanks
https://github.com/jsor/doctrine-postgis/issues/new#fullscreen

Hey,

I installed the bundle for the Geometry datatype and the functions. This does work well in dev/prod but when I'm trying to run PHPUnit tests this happens:

docker compose exec php bin/console doctrine:schema:create --env=test  

 !                                                                                                                      
 ! [CAUTION] This operation should not be executed in a production environment!                                         
 !                                                                                                                      

 Creating database schema...

[critical] Error thrown while running command "doctrine:schema:create --env=test". Message: "Schema-Tool failed with Error 'An exception occurred while executing a query: SQLSTATE[42704]: Undefined object: 7 ERROR:  type "geometry" does not exist
LINE 1: ...ATE TABLE test_entity (id UUID NOT NULL, geometry geometry(G...
                                                             ^' while executing DDL: CREATE TABLE test_entity (id UUID NOT NULL, geometry geometry(GEOMETRY, 0) DEFAULT NULL, PRIMARY KEY(id))"

In ToolsException.php line 19:
                                                                                                                                                                                                  
  Schema-Tool failed with Error 'An exception occurred while executing a query: SQLSTATE[42704]: Undefined object: 7 ERROR:  type "geometry" does not exist                                       
  LINE 1: ...ATE TABLE test_entity (id UUID NOT NULL, geometry geometry(G...                                                                                                                      
                                                               ^' while executing DDL: CREATE TABLE test_entity (id UUID NOT NULL, geometry geometry(GEOMETRY, 0) DEFAULT NULL, PRIMARY KEY(id))  
                                                                                                                                                                                                  

In ExceptionConverter.php line 87:
                                                                                                                              
  An exception occurred while executing a query: SQLSTATE[42704]: Undefined object: 7 ERROR:  type "geometry" does not exist  
  LINE 1: ...ATE TABLE test_entity (id UUID NOT NULL, geometry geometry(G...                                                  
                                                               ^                                                              
                                                                                                                              

In Exception.php line 28:
                                                                               
  SQLSTATE[42704]: Undefined object: 7 ERROR:  type "geometry" does not exist  
  LINE 1: ...ATE TABLE test_entity (id UUID NOT NULL, geometry geometry(G...   
                                                               ^               
                                                                               

In Connection.php line 32:
                                                                               
  SQLSTATE[42704]: Undefined object: 7 ERROR:  type "geometry" does not exist  
  LINE 1: ...ATE TABLE test_entity (id UUID NOT NULL, geometry geometry(G...   
                                                               ^               
                                                                               

I got the types and the service registered like described here: https://github.com/jsor/doctrine-postgis/blob/main/docs/symfony.md

I also made a little reproducer: https://github.com/KDederichs/postgis_geo_reproducer
Just run
docker compose up -d followed by docker compose exec php bin/console doctrine:database:create --env=test and docker compose exec php bin/console doctrine:schema:create --env=test to get to that issue.

Hey,

I am not sure if it's the correct place to ask for this, so correct me if I am wrong.

When using bin/console doctrine:schema:update to validate the schema, doctrine always returns [Database] FAIL - The database schema is not in sync with the current mapping file .

If I run bin/console doctrine:schema:update --dump-sql to see what's wrong, I always get the following COMMENT ON COLUMN address.geo IS '(DC2Type:geography)'; .

Not sure if it's an issue with doctrine or doctrine-postgis - could you help me on this?

We are using the doctrine postgis extension with Symfony 3.3. If we are running bin/console doctrine:schema:update --dump-sql|--force there is always one query that requires execution:

SELECT UpdateGeometrySRID('m_geo', 'geom', 3857);

The schema definition for this field is:

<field name="geom" type="geometry" >
    <options>
        <option name="srid">3857</option>
    </options>
</field>

This happens as soon you define the srid option. I guess this happens because the schema compare tool reads the option value from database as integer and the srid from the XML definition as string.

I have a problem with the column geometry is created.

In a table postigs type geometry, normally you can load both POINT both POLYGON, 2D and 3D.

In the column geometry created by the bundle you can only load or POINT or POLYGON. How may?

hello,

I apologize for creating an issue if it's a configuration problem on my part, but when I try to use the bundle I have this message:

In ExceptionConverter.php line 74:

  An exception occurred while executing a query: SQLSTATE[42P07]: Duplicate table: 7 ERROR:  relation "messenger_messages" already exists  


In Exception.php line 28:

  SQLSTATE[42P07]: Duplicate table: 7 ERROR:  relation "messenger_messages" already exists  


In Connection.php line 69:

  SQLSTATE[42P07]: Duplicate table: 7 ERROR:  relation "messenger_messages" already exists  

I tried with postgres 13 and 14..
I created my database then I activated postgis and added the field geography of my table.

     $this->addSql('CREATE TABLE messenger_messages (id BIGSERIAL NOT NULL, body TEXT NOT NULL, headers TEXT NOT NULL, queue_name VARCHAR(190) NOT NULL, created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, available_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, delivered_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, PRIMARY KEY(id))');
        $this->addSql('CREATE INDEX IDX_75EA56E0FB7336F0 ON messenger_messages (queue_name)');
        $this->addSql('CREATE INDEX IDX_75EA56E0E3BD61CE ON messenger_messages (available_at)');
        $this->addSql('CREATE INDEX IDX_75EA56E016BA31DB ON messenger_messages (delivered_at)');
        $this->addSql('CREATE TABLE messenger_messages (id BIGSERIAL NOT NULL, body TEXT NOT NULL, headers TEXT NOT NULL, queue_name VARCHAR(190) NOT NULL, created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, available_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, delivered_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, PRIMARY KEY(id))');
        $this->addSql('CREATE INDEX IDX_75EA56E0FB7336F0 ON messenger_messages (queue_name)');
        $this->addSql('CREATE INDEX IDX_75EA56E0E3BD61CE ON messenger_messages (available_at)');
        $this->addSql('CREATE INDEX IDX_75EA56E016BA31DB ON messenger_messages (delivered_at)');
        $this->addSql('CREATE OR REPLACE FUNCTION notify_messenger_messages() RETURNS TRIGGER AS $$

I deleted everything and I started again, I noticed when I add the postgis bundle, doctrine create me 2x the create table messenger... So I don't know if it's the postgis bundle that does this or symfony.

I deleted a creation and obviously it is compliant.

thank you so much

Why do we need this for https://github.com/jsor/doctrine-postgis/blob/master/src/Event/DBALSchemaEventSubscriber.php#L62 ?

There is an issue with MasterSlaveConnection because you have two connections, master and slave.

Hello,

I struggled when extending GeographyType, and then trying to execute migrations or schema diff.

I created a GeoJSONType to be able to manipulate GeoJSON arrays directly.
It extends GeographyType, and calls some PostGIS functions to make the conversion.

With bin/console doctrine:migrations:diff or bin/console doctrine:schema:update I was always getting the following error

The type of a spatial column cannot be changed 
(Requested changing type from "geography" to "geojson" for column "polygon" in table "zone")

There were some problems in my first implementation, but even after activating comment hints & properly mapping my new type I as still getting the error.

After a few hours debugging I found the problem.

DBALSchemaEventSubscriber does not care about the comments, it only reads the raw table definition.

Actually, my custom type creates a geography column in database, and $tableColumn['type'] contains the actual type which is stored in database, not the custom one.

To fix it I extended DBALSchemaEventSubscriber.

I think this is definitely a bug.

Like geometry and geography there should be support for type raster (introduced with PostGIS 2.x)

I've got the following error message:

[Doctrine\DBAL\DBALException]
Unknown database type raster requested, Doctrine\DBAL\Platforms\PostgreSQL92Platform may not support it.

I use SF2.7 , and when try to make
app/console doctrine:migrations:diff and got such exception
i debug these code and have

class Doctrine\DBAL\Schema\ColumnDiff#1308 (4) {
public $oldColumnName =>
string(14) "location_point"
public $column =>
class Doctrine\DBAL\Schema\Column#1223 (16) {
protected $_type =>
class Jsor\Doctrine\PostGIS\Types\GeographyType#529 (0) {
}
protected $_length =>
NULL
protected $_precision =>
int(0)
protected $_scale =>
int(0)
protected $_unsigned =>
bool(false)
protected $_fixed =>
bool(false)
protected $_notnull =>
bool(true)
protected $_default =>
NULL
protected $_autoincrement =>
bool(false)
protected $_platformOptions =>
array(1) {
'version' =>
bool(false)
}
protected $_columnDefinition =>
NULL
protected $_comment =>
NULL
protected $_customSchemaOptions =>
array(4) {
'spatial_type' =>
string(5) "POINT"
'spatial_srid' =>
int(4326)
'geometry_type' =>
string(8) "GEOMETRY"
'srid' =>
int(4326)
}
protected $_name =>
string(14) "location_point"
protected $_namespace =>
NULL
protected $_quoted =>
bool(false)
}
public $changedProperties =>
array(3) {
[0] =>
string(13) "geometry_type"
[1] =>
string(12) "spatial_type"
[2] =>
string(12) "spatial_srid"
}
public $fromColumn =>
class Doctrine\DBAL\Schema\Column#792 (16) {
protected $_type =>
class Jsor\Doctrine\PostGIS\Types\GeographyType#529 (0) {
}
protected $_length =>
NULL
protected $_precision =>
int(10)
protected $_scale =>
int(0)
protected $_unsigned =>
bool(false)
protected $_fixed =>
bool(false)
protected $_notnull =>
bool(true)
protected $_default =>
NULL
protected $_autoincrement =>
bool(false)
protected $_platformOptions =>
array(0) {
}
protected $_columnDefinition =>
NULL
protected $_comment =>
NULL
protected $_customSchemaOptions =>
array(2) {
'geometry_type' =>
string(5) "POINT"
'srid' =>
int(4326)
}
protected $_name =>
string(14) "location_point"
protected $_namespace =>
NULL
protected $_quoted =>
bool(false)
}
}

and my entity

/**

• @Orm\Embeddable
  /
  class Coordinates
  {
  /*
  • @var string
  • @Orm\Column(type="geography", options={"spatial_type"="POINT", "spatial_srid"=4326})
    */
    private $point;

Why i receive such exception and how to fix it?

Hello!

Are you planning to implement php 8 support?

[Sun May 31 10:15:10.106812 2015] [:error] [pid 21830] [client 127.0.0.1:36340] PHP Fatal error: Uncaught exception 'Doctrine\DBAL\DBALException' with message 'Unknown column type "geometry" requested. Any Doctrine type that you use has to be registered with \Doctrine\DBAL\Types\Type::addType(). You can get a list of all the known types with \Doctrine\DBAL\Types\Type::getTypesMap(). If this error occurs during database introspection then you might have forgot to register all database types for a Doctrine Type. Use AbstractPlatform#registerDoctrineTypeMapping() or have your custom types implement Type#getMappedDatabaseTypes(). If the type name is empty you might have a problem with the cache or forgot some mapping information.' in /home/altamiro/Developments/Projects/bitbucket/siema/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:228\nStack trace:\n#0 /home/altamiro/Developments/Projects/bitbucket/siema/vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Type.php(172): Doctrine\DBAL\DBALException::unknownColumnType('geometry')\n#1 /home/altamiro/Developments/Projects/bitbucket/siema/vendor/doctrine/orm/lib in /home/altamiro/Developments/Projects/bitbucket/siema/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php on line 228, referer: http://localhost:86/

Hi,

I am using Symfony v3.3.0 and doctrine v2.5.5
As described here https://github.com/jsor/doctrine-postgis#spatial-indexes my doctrine-config is the following:

<doctrine-mapping xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping"
                                     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                                     xsi:schemaLocation="http://doctrine-project.org/schemas/orm/doctrine-mapping
                   https://raw.github.com/doctrine/doctrine2/master/doctrine-mapping.xsd">

<entity name="Dummy\Module\GeoBundle\Entity\Geo" table="geo"
        repository-class="Dummy\Module\GeoBundle\Entity\Repository\GeoRepository">
    <field name="title" type="string" nullable="true"/>
    <field name="geometry" type="geometry" nullable="false"/>
    <indexes>
        <index name="geometry_IDX" columns="geometry" flags="spatial"/>
    </indexes>
</entity>
</doctrine-mapping>

So, I have set the flag with flags="spatial", but with the console I only get a "normal" index:

php bin\console doctrine:schema:update --dump-sql

gives:
CREATE INDEX geometry_IDX ON geo (geometry);

I was going back and forth between jsor/doctrine-postgis and creof/doctrine2-spatial

I like the way that creof/doctrine2-spatial works. I work with a Point() type and it returns me a Point() object. Can also use the same when entering data. Which is awesome... But it does not support spatial indexes AFAIK.

Anyway, about jsor/doctrine-postgis, entering data as WKT is not a big problem. But I receive response in text and not in EWKT ("0101000020E610000000000000000000000000000000000000") instead of POINT(X,Y)

What am I doing wrong?

Hi
I hit problem. Not sure if this could be treated as issue but maybe you can help me out.
http://stackoverflow.com/questions/41237585/symfony-postgis-db-reverse-engineering

Cheers

Sorry to open an issue, im new to postgis so was hoping to find a wrapper that is easier to use than remembering all the SQL behind the postgis querys. Is it possible to use this with laravel by simply using composer to require the package, I noticed you mention symphony but nothing regarding laravel + other frameworks.

Hey,

just wondering if this is also compatible with version 3.x of PostGis?

Best regards

Hi guys,

I'm trying to execute the following code:

public function nearestStations($geom = null, $srid = null)
{
    $builder = $this->createQueryBuilder('s');
    $builder->select('s')
        ->where(
            $builder->expr()->andX('ST_Intersects(ST_GeomFromText(:geom, :srid), s.geom)'),
            $builder->expr()->isNotNull('s.origin')
        )
        ->setParameter('geom', $geom)
        ->setParameter('srid', $srid);
        
    $query = $builder->getQuery();
    // var_dump($query->getDQL()); die;
    return $query->execute();
}

This is the result when I dump the DQL:

SELECT s FROM Funceme\PCD\Station\Station s WHERE ST_Intersects(ST_GeomFromText(:geom, :srid), s.geom) AND s.origin IS NOT NULL

But I'm getting this error:

[Syntax Error] line 0, col 103: Error: Expected =, <, <=, <>, >, >=, !=, got 'AND'

When I execute the SQL in pgAdmin, it works. Someone knows how to resolve this?
Thx!

Hello

i'm currently trying to run the PHPUnit Tests locally and followed the instructions.

First thing i figured, the docs do not state, that you need to install composer dependencies and how its done.
Not an issue for me, but i would suggest, that his will be added to the HowTo Section on how to run the Tests ( in case you just start )

Second issue, the Scripts (run-xx-xx.sh) inject the volume via -v "$(PWD)":/app i would suggest, that this should be changed to -v "${PWD}":/app
While $(PWD) might work in some Environments ${PWD} should work in more Environments

Now to the third and biggest issue: The Unit Tests do not run correctly on my machine.

Docker Compose for the Databases is running and i tried all the different versions. All result in the same errors

PDOException: SQLSTATE[22023]: Invalid parameter value: 7 ERROR: Geometry has Z dimension but column does not

this happens in pretty much every UnitTest but here is an Example:

I get the following Error:

/app/vendor/doctrine/dbal/src/Driver/PDO/Exception.php:28
/app/vendor/doctrine/dbal/src/Driver/PDO/Statement.php:123
/app/vendor/doctrine/dbal/src/Statement.php:190
/app/vendor/doctrine/dbal/src/Statement.php:249
/app/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php:280
/app/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php:1179
/app/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php:443
/app/vendor/doctrine/orm/lib/Doctrine/ORM/EntityManager.php:403
/app/tests/Functions/GeographyTest.php:47

Caused by
PDOException: SQLSTATE[22023]: Invalid parameter value: 7 ERROR:  Geometry has Z dimension but column does not

Does anyone have a clue what i'm doing wrong and how to fix it?

Environment:
Windows 10 + WSL 2 + Docker Desktop with WSL 2 Integration. Setup for Linux Containers

I have an existing entity with a property using you customType and I have to add some fields in it.

The entity uses the following type and options type: PostGISType::GEOGRAPHY, options: ['geometry_type' => 'POINT'] though I don't see in your examples using both PostGISType::GEOGRAPHY and geometry_type option together so I wonder if my colleague made an error on that and it mess with Doctrine Migration now.

Wanting to generate migration I have the following error message : The geometry_type of a spatial column cannot be changed (Requested changing type from "POINT" to "GEOMETRY" for column "XX" in table "YY")

Is that normal that a change is detected even though I didn't change anything about the geography property in my entity ?

Hi,

seems like this bundle has various doctrine deprecations:

1x: Accessing Doctrine\Common\Lexer\Token properties via ArrayAccess is deprecated, use the value, type or position property instead (Token.php:68 called by ST_DWithin.php:37, https://github.com/doctrine/lexer/pull/79, package doctrine/lexer)


1x: Accessing Doctrine\Common\Lexer\Token properties via ArrayAccess is deprecated, use the value, type or position property instead (Token.php:104 called by ST_DWithin.php:37, https://github.com/doctrine/lexer/pull/79, package doctrine/lexer)

 1x: Subscribing to onSchemaCreateTable events is deprecated. (AbstractPlatform.php:2137 called by AbstractPlatform.php:2040, https://github.com/doctrine/dbal/issues/5784, package doctrine/dbal)

Since symfony/doctrine-bridge 6.3: Registering "Jsor\Doctrine\PostGIS\Event\ORMSchemaEventSubscriber" as a Doctrine subscriber is deprecated. Register it as a listener instead, using e.g. the #[AsDoctrineListener] attribute.

Hello!

I saw issue #17 and I have a question: is the reason to have it the same? I am getting this when I try to switch a connection. I have two connections to the databases (almost like mater-slave, but second connection is manually done to statistics database.

Hello,
I'm using doctrine-postgis 1.1.2 with Symfony 2.8 and I have a strange behaviour.
When I clean cache and run for the first time my script, everything is fine.

echo $ouvrage->getGeometry() . "\n";

returns something like this

SRID=4326;POINT(2.3344311729 49.0224338684)

When I run for the second time the same code, here is what I get

0101000020E610000087F23240EAAC0240E68CED1CDF824840

It's like

Here is my services.yml entries

`services:

app.jsor_doctrine_postgis.subscriber.doctrine.orm_schema:
    class: Jsor\Doctrine\PostGIS\Event\ORMSchemaEventSubscriber
    public: false
    tags:
        - { name: doctrine.event_subscriber, connection: default, priority: 100000  }`

I have also tested with priority 0 and no priority, no differences.

Any ideas for resolving it are welcome!

Matthieu

Hi there, I started getting this error when I run bin/console doctrine:migrations:diff even though I haven't made any changes to the entity and table that are causing this issue.

In DBALSchemaEventSubscriber.php line 157:
                                                                                                                                                       
  The geometry_type of a spatial column cannot be changed (Requested changing type from "POINT" to "GEOMETRY" for column "coordinates" in table "main.locations")                                                                                                                                                                                                                                                                                               

What I did do is frequently update composer packages so I'm suspecting it might be happening only in the newer versions, these are the versions from my composer.lock:

"doctrine/orm": "2.16.1",
"jsor/doctrine-postgis": "^2.2"

I'm using this inside of a Symfony app if that adds more context.

This is my doctrine mapping which hasn't changed since adding this amazing package.

#[ORM\Column(type: PostGISType::GEOGRAPHY, options: ['geometry_type' => 'POINT', 'srid' => 4326])]
private string $coordinates;

This is what I get when I dump the new column in DBALSchemaEventSubscriber.php line 157:

^ Doctrine\DBAL\Schema\Column^ {#2159
  #_name: "coordinates"
  #_namespace: null
  #_quoted: false
  #_type: Jsor\Doctrine\PostGIS\Types\GeographyType^ {#279}
  #_length: null
  #_precision: 10
  #_scale: 0
  #_unsigned: false
  #_fixed: false
  #_notnull: true
  #_default: null
  #_autoincrement: false
  #_platformOptions: array:2 [
    "geometry_type" => "POINT"
    "srid" => 4326
  ]
  #_columnDefinition: null
  #_comment: null
  #_customSchemaOptions: array:2 [
    "geometry_type" => "GEOMETRY"
    "srid" => 4326
  ]
}

And this is what I get for the old column:

^ Doctrine\DBAL\Schema\Column^ {#923
  #_name: "coordinates"
  #_namespace: null
  #_quoted: false
  #_type: Jsor\Doctrine\PostGIS\Types\GeographyType^ {#279}
  #_length: null
  #_precision: 10
  #_scale: 0
  #_unsigned: false
  #_fixed: false
  #_notnull: true
  #_default: null
  #_autoincrement: false
  #_platformOptions: []
  #_columnDefinition: null
  #_comment: null
  #_customSchemaOptions: array:2 [
    "geometry_type" => "POINT"
    "srid" => 4326
  ]
}

As you can see the _customSchemaOptions don't match for some reason. When I go to the \Doctrine\DBAL\Schema\Column class I see that the _customSchemaOptions has been deprecated, so I don't know if that could be potentially related to the issue.

    /**
     * @deprecated Use {@link $_platformOptions} instead
     *
     * @var mixed[]
     */
    protected $_customSchemaOptions = [];

Another way to verify the issue is when you drop the database and try to recreate the diff, in that case, this error won't happen but the newly generated migration won't match it will look like this coordinates geography(GEOMETRY, 4326)

The schema update fails if the table-name is a reserved word, because the table-name will be quoted by Doctrine and the select-query which returns the column-information fails. So Doctrine thinks he has to change the "geometry_type" (from a empty string to the configured value), which is not supported (see #5).

The table-name was in my case user, which will be converted to "user", which results in the following query:

SELECT coord_dimension, srid, type FROM geometry_columns WHERE f_table_name = '"user"' AND f_geometry_column = 'coordinates';

I'm new to Doctrine and I have no idea how to safely unquote the table-name, Is trim($table, '"'); sufficient, or is there a better way?

I'm using PostgreSql 9.5 and the most current Doctrine-version.

I guess this is not the same problem,

now i have :

[Doctrine\DBAL\DBALException]
Unknown database type geometry requested, Doctrine\DBAL\Platforms\PostgreSqlPlatform may not support it.

looks like "geography" type works well but "geometry" does not ...