Invalid geometry format in database: '0101000020E61000006F9BA9108F4418401BD82AC1E2634940'

[Kinqdos]

I have this column in one of my entities:

#[ORM\Column(type: 'geometry', nullable: true, options: ['columnDefinition' => 'geometry(Point, 4326)'])]
public protected(set) ?WktSpatialData $coordinates = null;

A simple find ($this->em->getRepository(Adressdaten::class)->find(173040)) triggers this error.
It seems like the database is returning the geometry in Well-Known Binary Format.

[martin-georgiev]

What's the data that you wrote for that row?

[Kinqdos]

INSERT INTO main.adressdaten (..., coordinates) VALUES (..., ST_SetSRID(ST_MakePoint(10.269208156867, 47.40660855), 4326));

[martin-georgiev]

And what does the definition of your table look like exactly?

[Kinqdos]

create table adressdaten
(
    id            integer generated by default as identity
        constraint domain_adressdaten_pkey
            primary key,
    strasse       varchar(255) default NULL::character varying,
    hausnummer    varchar(255) default NULL::character varying,
    plz           varchar(255) default NULL::character varying,
    ort           varchar(255) default NULL::character varying,
    land          varchar(255) default NULL::character varying,
    name          varchar(255) default NULL::character varying,
    lat           double precision,
    lon           double precision,
    coordinates   geometry(Point, 4326),
    creation_date integer      default 0     not null,
    edit_date     integer      default 0     not null,
    deleted       boolean      default false not null,
    synced_at     integer                    not null
);

[Kinqdos]

@martin-georgiev was the whole gespatial thing tested with a real database beforehand?

[martin-georgiev]

PostgreSQL returns geometry columns in binary format (EWKB) by default when you use Doctrine's find() method or query geometry columns directly. The library was expecting WKT (text) format, which caused the error you saw.

Current workaround
Wrap geometry columns in ST_AsEWKT()

SELECT id, ST_AsEWKT(coordinates) as coordinates FROM adressdaten WHERE id = 173040

So why this happens?
The existing integration tests use ST_AsText() to convert geometry to WKT format when reading from the database (see GeometryTypeTest::getSelectExpression()). However, when using Doctrine ORM's find() method or direct queries, PostgreSQL returns the raw binary format.

[Kinqdos]

Thank you for the response. The problem is that this breaks our existing code. The geometry column is a redundant column we wanted to add for distance calculations. We also store lat and lon separate from that.
So atm it is not possible to add this to an existing entity for such a purpose?

[martin-georgiev]

#462 should address your issue. Test with the dev-main to confirm, it may get released later this week as part of a normal release.