TGeogPointSeq and intersection?

[wendellwt]

Does the TGeogPointSeq have an ST_Intersection function in mobilitydb-sqlalchemy?

It works when I reference it from within PostGIS/SQL or from Python using mobilitydb. However, with mobilitydb-sqlalchemy it results in: AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Sched.the_traj has an attribute 'ST_Intersection'

The column is declared as a Column(TGeogPoint) and other functions (valueAtTimestamp, atPeriod) work fine. Are there some examples that show how to do a spatial intersection/ST_Intersection on a TGeogPointSeq?

[chaitan94]

Could you perhaps share the full query (or even better, a minimal one which repoduces the issue)? Either SQL or the sqlalchemy code for the query should be good, both are even better.

As such, any postgres function from any extension should work fine as SQLAlchemy's func is generic and doesn't do anything fancy really.

[wendellwt]

I figured it out. (I'm trying to teach myself sqlalchemy.) The way I did it works, but it isn't pretty :-(

First, get the EWKT of a circle around an airport:

apt_clt_circle_wkt = session.query( func.st_asewkt(
    ST_Buffer(Airports.position,1.0))).filter(Airports.ident == 'CLT').one()

then, query to find the geometry of the intersection of the_traj (a TGeogPointSeq) and that circle:

session.query(
    func.st_asgeojson(
        func.st_intersection(func.trajectory(Sched.the_traj),
                             func.st_geomfromewkt(apt_clt_circle_wkt)
                             ))).filter(
        func.st_intersects(func.trajectory(Sched.the_traj),
                             func.st_geomfromewkt(apt_clt_circle_wkt)
                             ) ).first()

Is there a way to include the first query in the second, without having to cast to and from EWKT?

Also, do I need both the .filter( intersects(...) ) part, which apparently forms the WHERE clause, and the st_intersection()? I want the the spatial intersection as the result.

Also, in the cast to func.trajectory(), it drops the temporal component of the TGeogPointSeq. Is there a way to do the st_intersection() and still preserve the temporal part?

Thank you for your time. Wendell

[chaitan94]

What you doing does make sense.

do I need both

I think you do.

Is there a way to do the st_intersection() and still preserve the temporal part?

No, at least not exactly. ST_Intesection is a PostGIS function which mostly deals with geometries without the temporal part. However, PostGIS does have a LinestringM data type, maybe you could make use of it somehow to embed the timestamp information (it might complicate your query a bit futher), if it is really needed? Refer: https://postgis.net/docs/ST_IsValidTrajectory.html