Linestring table (of MultiLinestrings) ST_Intersection

Question

I'm moving from GeoPandas to PostGIS and GeoAlchemy.

I've a table full of multilinestring geometries (timezones from NaturalEarth.com) and I've a linestring which represents what will be the route of an aircraft. I would like to get the intersected timezones AND the position of intersections.

So far I have the intersected timezones using

class TimeZoneLine(Base):
    __tablename__ = 'TimeZone (LineGridExp)'
    __table_args__ = {'autoload': True}

lineGeom = func.ST_GeomFromText('LINESTRING(-14 44, 6 44)',4326)

query = session.query(TimeZoneLine).filter(func.ST_Intersects(TimeZoneLine.geom, lineGeom))

for row in query:
    print (row.zone)

which prints

<__main__.TimeZoneLine object at 0x107656dd8>
<__main__.TimeZoneLine object at 0x107656e48>
<__main__.TimeZoneLine object at 0x107656f98>
<__main__.TimeZoneLine object at 0x107683128>
<__main__.TimeZoneLine object at 0x107683278>
<__main__.TimeZoneLine object at 0x1076833c8>

which are the timezones intersected. My question is, how to get the Intersection points.

This seems to have some thing similar but the below is closest I've come:

query =  session.query(select([column('zone'),column('id'), column('iso_8601'),column( 'map_color6'), func.ST_intersection(lineGeom, TimeZoneLine.geom)]).select_from(TimeZoneLine).where(func.ST_Intersects(lineGeom, TimeZoneLine.geom)))

but this returns

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) subquery in FROM must have an alias

Answer 1

You can find the intersecting point with ST_Intersection. But that only work on the geometry type. So you will have to project your data. But since you are working over large areas that might be a problem.

But I think transforming to webmercator should work in this case. Try transforming both time lines and aircraft routes (on the fly) to webmercator, use ST_Intersection and transform the result back.