Assistanze in using 3D Geometries

[FelixEickeler]

Describe the problem

I am trying to use geoalchemy2 for handling a 3D records. Everything went smooth but I have a hard time handling the 3D aspect. As in #157 mentioned I tired adding '*Z'. This results in a working PointZ but the database is empty if used with e.g. LinestringZ. The echo of the engine shows the WKT.

Would someone be so kind to clarify the use of 3D geometries ?

Show what you tried to do.

from os import environ

from geoalchemy2.shape import from_shape
from shapely import LineString
from sqlalchemy.event import listen
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, create_engine
from geoalchemy2 import Geometry, load_spatialite_gpkg
from sqlalchemy.orm import sessionmaker
environ['SPATIALITE_LIBRARY_PATH'] = r"mod_spatialite"

engine = create_engine('gpkg:///C:/dev/projekte/Railograph/src/models/avani/test_database.gpkg', echo=True)
listen(engine, "connect", load_spatialite_gpkg)
SessionBlueprint = sessionmaker(bind=engine)

Base = declarative_base()

class Lake(Base):
    __tablename__ = 'lake'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    geom = Column(Geometry('LINESTRINGZ', srid=4326))

Lake.__table__.create(engine)

points = [(0, 0, 0), (1, 1, 1)] # ERROR
# points = [(0, 0), (1, 1)] # WORKS
linestring_3d = LineString(points)
lake = Lake(name='Lake', geom=from_shape(linestring_3d))

session = SessionBlueprint()
session.add(lake)
session.commit()

Describe what you expected.

There should be an existing 3D WKB in the geopackage.

Error

No Error

Additional context

No response

GeoAlchemy 2 Version in Use

0.14.7

[adrien-berchet]

Hi @FelixEickeler ! Thanks for this report. I made a few tests and it seems that the constructed query is incorrect in this case: surprisingly the query in this case should be INSERT INTO lake (name, geom) VALUES (Lake, GeomFromEWKT('SRID=-1;LINESTRING(0 0 0, 1 1 1)')); (no Z at the end of LINESTRING). I don't know why SpatiaLite works like this while other dialects keep the Z. I will add some tests in GeoAlchemy2 to confirm this behavior and fix it if. Sorry for the inconvenience, unfortunately I can't see any temporary workaround for this so you will have to wait for the official fix :-/

[adrien-berchet]

@sdp5 I think the way to fix this issue should be to update the WKT given to this function: https://github.com/geoalchemy/geoalchemy2/blob/master/geoalchemy2%2Ftypes%2Fdialects%2Fsqlite.py#L9-L22

[sdp5]

I can reproduce the issue. thanks @FelixEickeler !

@adrien-berchet Yea, get to that point. Not sure, should we get srid from spatial_type? doing some more testing ..

[adrien-berchet]

Hi @FelixEickeler The new version GeoAlchemy2 == 0.15.0 has just been released and should fix your issue. Can you please check and confirm the result here?