input geometry has unkown(0) geometry (Although st_transform is used)

Question

I have 2 tables:

A table with 3 fields:

id (text)
geom (geometry)

select ST_SRID(geom)
from A
where geom is not null

result: 32636

B table with 2 fields:

name (text)
geom (geometry)

select ST_SRID(geom)
from B
where geom is not null

result: 0

A.geom contains polygons

B.geom contains points

I want to get all the distances between A.id, A.geom and B.geom. I tried with:

select id, st_distance(a.geom, ST_Transform(b.geom, 32636)) as dist
from A as a, B as b
where a.geom is not null
group by id, a.geom, b.geom
order by dist desc

But I'm getting error:

"input geom has unkown(0) SRID"

How can it be if I'm using ST_Transform ?

How can I fix it ?

Answer 1

The error message is talking about the SRID of the argument to ST_Transform, which is 0. The message means that the function has no idea in which coordinate system the point is, so it cannot transform it to another coordinate system.

The documentation says:

ST_Transform is often confused with ST_SetSRID. ST_Transform actually changes the coordinates of a geometry from one spatial reference system to another, while ST_SetSRID() simply changes the SRID identifier of the geometry.

That seems to be the case here.

You should probably use ST_SetSRID to interpret b.geom in SRID 32636.