I've created irregular geometry table which contains point array as geometry type column and I'm trying to retrieve points as per measurement point id.
But I'm having an error :
ProgrammingError: (psycopg2.errors.CannotCoerce) cannot cast type geometry[] to geometry LINE 1: SELECT ST_X(CAST(db.irregular.axises AS geometry(GEOMETR...
This is how it is in database:
column_name | data_type | numeric_scale || udt_schema | udt_name |
----------------------+-----------+---------------+-------------+------------+
id | integer | 0 | | pg_catalog | int4 |
measurement_point_id | integer | 0 | | pg_catalog | int4 |
axises | ARRAY | | | public | _geometry |
This is my irregular table class:
#%% Irregular Class
class Irregular (object):
measurement_point_id = relationship("measurement_points", back_populates="id")
def __init__(self,measurement_point_id,axises=None,id= None):
self.id = id
self.measurement_point_id = measurement_point_id
self.axises = axises
#self.is_xy = xy
#Irregular Object
__tablename__ = 'irregular'
irregular = Table(
__tablename__,meta,
Column ('id', Integer, primary_key = True),
Column ( 'measurement_point_id',Integer,ForeignKey('measurement_points.id')),
Column ( 'axises', ARRAY(Geometry('POINT'))),
#Column ( 'is_xy', Boolean),
)
mapper(Irregular, irregular)
This is how I am trying to get data :
session.query(fns.ST_X(cast(tb.Irregular.axises, geoalchemy2.types.Geometry)),\
fns.ST_Y(cast(tb.Irregular.axises, geoalchemy2.types.Geometry)).filter(tb.measurement_point_id == id).all()
I removed casting : ProgrammingError: (psycopg2.errors.UndefinedFunction) function st_x(geometry[]) does not exist LINE 1: SELECT ST_X(db.irregular.axises) AS "ST_X_1", ST_Y(db...
I think I need to retrieve as tuple array but I couldn't find how to cast from python side and which function should I use.
I've solved this with
session.query(fns.ST_AsGeoJSON(fns.ST_Collect(tb.Irregular.axises)))
.filter(tb.Irregular.measurement_point_id == mpoint_id)
.first()[0]