filter by polygon includes points

Question

I have a fastapi route, that receive 2 coordinate points of rectangle (left-bottom and right-top). I need to select all my rows from table, that has column "coodinates", that contains geometry(Point,4326) type (I use Geoalchemy2 for it). So, how can I effectively do it? I know that I need to make Polygon and filter rows by contains Point in this Polygon (something like this, I guess. Or maybe I'll find another solution.), but I have no idea, how can I calculate 2 other points (left-top and right-bottom) of Polygon, to create it. Can you, please, help me for it?

Answer 1

Okey, I solved my problem by adding new conditions: 1) original rectangle has no rotation (it can be rotated only to North), 2) there is no angle of view to my rectangle. So, with such conditions, the solution is following:

I just use existing edge latitude and longitude and replace them by formula:

left_top.latitude = right_top.latitude
left_top.longitude = left_bottom.longitude

right_bottom.latitude = left_bottom.latitude
right_bottom.longitude = right_top.longitude 

Result (simplified):

from fastapi import APIRouter, Depends
from sqlalchemy import func
from geoalchemy2 import WKTElement

from .models import models
from .schemas import base as base_schemas
from .schemas.types.pydantic import Point

router = APIRouter(prefix='/clinics', tags=['Клиники'])


def calculate_postgis_polygon_edges_from_two_edges(
    left_bottom: Point,
    right_top: Point,
) -> tuple[Point, Point, Point, Point, Point]:
    left_bottom_lat, left_bottom_lon = left_bottom
    right_top_lat, right_top_lon = right_top

    left_top_lat = right_top_lat
    left_top_lon = left_bottom_lon
    left_top = (left_top_lat, left_top_lon)

    right_bottom_lat = left_bottom_lat
    right_bottom_lon = right_top_lon
    right_bottom = (right_bottom_lat, right_bottom_lon)

    return (left_top, right_top, right_bottom, left_bottom, left_top)


def get_postgis_polygon(polygon_edges: tuple[Point, ...]) -> str:
    unpacked_string_edges = [f'{lat} {lon}' for lat, lon in polygon_edges]
    polygon_values = ','.join(unpacked_string_edges)
    return f'POLYGON(({polygon_values}))'


@router.get('')
def some_endpoint(edges: base_schemas.RectangleEdgesParameters = Depends()) -> Any:
    polygon_edges = polygons.calculate_postgis_polygon_edges_from_two_edges(
        left_bottom=edges.left_bottom,
        right_top=edges.right_top,
    )
    polygon = polygons.get_postgis_polygon(polygon_edges)
    polygon_filter = func.ST_Contains(WKTElement(polygon, srid=4326), models.Clinic.coordinates)
    # And then use it in queries

So, This is not a good solution, but it works for me. If there is angle of view for rectangle from maps, there will be more comprehensive solution.