0

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

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.