Find users in downtown Boston using Geospatial query for AWS Athena (Trino)

SQL statement for Trino:

            

              Copy
            
            SELECT
  *
FROM
  users
WHERE
  ST_CONTAINS (
    ST_POLYGON_FROM_TEXT (
      'POLYGON((-71.0679 42.3601, -71.0679 42.3631, -71.0629 42.3631, -71.0629 42.3601, -71.0679 42.3601))'
    ),
    ST_POINT (users.lng, users.lat)
  )
          

Explanation:

• The SQL statement selects all columns from the "users" table.
• The WHERE clause filters the results based on the condition that the point defined by the latitude (lat) and longitude (lng) of each user is contained within the polygon representing downtown Boston.
• The polygon is defined using the ST_POLYGON_FROM_TEXT function, which takes a text representation of the polygon's coordinates.
• The coordinates of the polygon are provided in the form of a rectangle that encompasses downtown Boston.