0

I'm trying to query a PostGIS table using GeoAlchemy to return the row where the point lies inside. I've a point and I'm using the timezones shapefile from natural earth. I've tried the below but it just returns the query and not the row item.

class TimeZonePoly(Base):
    __tablename__ = 'time_zone_poly_grid_exp'
    __table_args__ = {'autoload': True}

def PointInside(PostGIS_Table, Lat, Lon):
    point = func.ST_GeographyFromText('POINT({} {})'.format(Lon, Lat))
    return session.query(PostGIS_Table).filter(func.ST_Contains(PostGIS_Table.geom, point))
     

row = PointInside(TimeZonePoly, 47, -2)
print(row)

which returns the follow:

SELECT time_zone_poly_grid_exp.id AS time_zone_poly_grid_exp_id, ST_AsEWKB(time_zone_poly_grid_exp.geom) AS time_zone_poly_grid_exp_geom, time_zone_poly_grid_exp.objectid AS time_zone_poly_grid_exp_objectid, time_zone_poly_grid_exp.scalerank AS time_zone_poly_grid_exp_scalerank, time_zone_poly_grid_exp.featurecla AS time_zone_poly_grid_exp_featurecla, time_zone_poly_grid_exp.name AS time_zone_poly_grid_exp_name, time_zone_poly_grid_exp.map_color6 AS time_zone_poly_grid_exp_map_color6, time_zone_poly_grid_exp.map_color8 AS time_zone_poly_grid_exp_map_color8, time_zone_poly_grid_exp.note AS time_zone_poly_grid_exp_note, time_zone_poly_grid_exp.zone AS time_zone_poly_grid_exp_zone, time_zone_poly_grid_exp.utc_format AS time_zone_poly_grid_exp_utc_format, time_zone_poly_grid_exp.time_zone AS time_zone_poly_grid_exp_time_zone, time_zone_poly_grid_exp.iso_8601 AS time_zone_poly_grid_exp_iso_8601, time_zone_poly_grid_exp.places AS time_zone_poly_grid_exp_places, time_zone_poly_grid_exp.dst_places AS time_zone_poly_grid_exp_dst_places, time_zone_poly_grid_exp.tz_name1st AS time_zone_poly_grid_exp_tz_name1st, time_zone_poly_grid_exp.tz_namesum AS time_zone_poly_grid_exp_tz_namesum 
FROM time_zone_poly_grid_exp 
WHERE ST_Contains(time_zone_poly_grid_exp.geom, ST_GeographyFromText(%(ST_GeographyFromText_1)s))

It looks like it's the correct statement, I just can't get the results!

Tom Shelley
  • 111
  • 2
  • 11
  • make sure the expected time zone polygon is indeed stored in the table – JGH Jun 23 '20 at 12:45
  • Maybe there are no points inside the polygon? This looks correct. Try extending the polygon geometry, could be because of incorrect data/coordinate system. – frozenOne Jun 28 '20 at 20:09
  • 1
    `qs.filter(func.ST_Contains(POLYGON, POINT))` seems correct. – frozenOne Jun 28 '20 at 20:09
  • Are you saying you have tried to execute the query and it doesn't return what you expect or are you saying that the above code doesn't execute the query? If its the latter, you need to add .all() to the end of your query. – Micah Johnson Jul 12 '20 at 13:31

0 Answers0