Пример #1
0
def update_points_in_rectangle(bottom_left_corner, width, height, step):
    if bottom_left_corner is None or width is None or height is None or step is None\
            or not isinstance(bottom_left_corner, tuple) \
            or not isinstance(width, float) \
            or not isinstance(height, float) \
            or not isinstance(step, float):
        raise InvalidInputException(
            "Please provide bottom left corner point, width and height of rectangle and update step."
        )

    connection = providers.db_connection_provider.get_connection()
    updated = 0
    has_errors = "No"
    start_time = datetime.now()
    try:
        cursor = connection.cursor()
        cursor.execute("""
                UPDATE spatial_core_point
                SET point = point(point[0] + {0}, point[1] + {0})
                WHERE point <@ box(point({1}, {2}), point({3}, {4}))
            """.format(step, bottom_left_corner[0], bottom_left_corner[1],
                       bottom_left_corner[0] + width,
                       bottom_left_corner[1] + height))
        updated = cursor.rowcount
        cursor.close()
        connection.commit()
    except Exception:
        has_errors = "Yes"
        connection.rollback()
    finally:
        end_time = datetime.now()
        connection.close()

    return has_errors, updated, get_milliseconds(start_time, end_time)
Пример #2
0
def update_point_by_coordinates(x, y, step):
    if x is None or y is None or step is None \
            or not isinstance(x, float) \
            or not isinstance(y, float) \
            or not isinstance(step, float):
        raise InvalidInputException(
            "Please provide 'x' and 'y' values for existing point and update step."
        )

    connection = providers.db_connection_provider.get_connection()
    updated = 0
    has_errors = "No"
    start_time = datetime.now()
    try:
        cursor = connection.cursor()
        cursor.execute("""
                UPDATE spatial_core_point
                SET point = point(point[0] + {0}, point[1] + {0})
                WHERE point ~= point({1}, {2})
            """.format(step, x, y))
        updated = cursor.rowcount
        cursor.close()
        connection.commit()
    except Exception:
        has_errors = "Yes"
        connection.rollback()
    finally:
        end_time = datetime.now()
        connection.close()

    return has_errors, updated, get_milliseconds(start_time, end_time)
Пример #3
0
def insert_point(x, y):
    if x is None or y is None or not isinstance(x, float) or not isinstance(
            y, float):
        raise InvalidInputException(
            "Please provide 'x' and 'y' values for point")

    connection = providers.db_connection_provider.get_connection()
    inserted = 0
    has_errors = "No"
    start_time = datetime.now()
    try:
        cursor = connection.cursor()
        cursor.execute("""
                INSERT INTO spatial_core_point (point) VALUES (point({0}, {1}))
            """.format(x, y))
        inserted = cursor.rowcount
        cursor.close()
        connection.commit()
    except Exception:
        has_errors = "Yes"
        connection.rollback()
    finally:
        end_time = datetime.now()
        connection.close()

    return has_errors, inserted, get_milliseconds(start_time, end_time)
Пример #4
0
def find_point_by_coordinates(x, y):
    if x is None or y is None or not isinstance(x, float) or not isinstance(
            y, float):
        raise InvalidInputException(
            "Please provide 'x' and 'y' values for point!")

    connection = providers.db_connection_provider.get_connection()
    found = 0
    has_errors = "No"
    start_time = datetime.now()
    try:
        cursor = connection.cursor()
        cursor.execute("""
                SELECT * FROM spatial_core_point
                WHERE point ~= point({0}, {1})
            """.format(x, y))
        found = cursor.rowcount
        cursor.close()
    except Exception:
        has_errors = "Yes"
    finally:
        end_time = datetime.now()
        connection.close()

    return has_errors, found, get_milliseconds(start_time, end_time)
Пример #5
0
def find_points_in_rotated_rectangle(bottom_left_corner, width, height, angle):
    if bottom_left_corner is None or width is None or height is None or angle is None \
            or not isinstance(bottom_left_corner, tuple) \
            or not isinstance(width, float) \
            or not isinstance(height, float)\
            or not isinstance(angle, float):
        raise InvalidInputException(
            "Please provide bottom left corner point, width, height and angle of rectangle!"
        )

    connection = providers.db_connection_provider.get_connection()
    found = 0
    has_errors = "No"
    start_time = datetime.now()
    try:
        cursor = connection.cursor()
        cursor.execute("""
                SELECT * FROM spatial_core_point
                WHERE rotated_rectangle_contains(point, box(point({0}, {1}), point({2}, {3})), {4})
            """.format(bottom_left_corner[0], bottom_left_corner[1],
                       bottom_left_corner[0] + width,
                       bottom_left_corner[1] + height, angle))
        found = cursor.rowcount
        cursor.close()
    except Exception:
        has_errors = "Yes"
    finally:
        end_time = datetime.now()
        connection.close()

    return has_errors, found, get_milliseconds(start_time, end_time)
Пример #6
0
def delete_points_in_rectangle(bottom_left_corner, width, height):
    if bottom_left_corner is None or width is None or height is None \
            or not isinstance(bottom_left_corner, tuple) \
            or not isinstance(width, float) \
            or not isinstance(height, float):
        raise InvalidInputException(
            "Please provide bottom left corner point, width and height of bounding box!"
        )

    connection = providers.db_connection_provider.get_connection()
    deleted = 0
    has_errors = "No"
    start_time = datetime.now()
    try:
        cursor = connection.cursor()
        cursor.execute("""
                DELETE FROM spatial_core_point
                WHERE point <@ box(point({0}, {1}), point({2}, {3}))
            """.format(bottom_left_corner[0], bottom_left_corner[1],
                       bottom_left_corner[0] + width,
                       bottom_left_corner[1] + height))
        deleted = cursor.rowcount
        cursor.close()
        connection.commit()
    except Exception:
        has_errors = "Yes"
        connection.rollback()
    finally:
        end_time = datetime.now()
        connection.close()

    return has_errors, deleted, get_milliseconds(start_time, end_time)
Пример #7
0
def delete_points_in_circle(center, radius):
    if center is None or radius is None or not isinstance(
            center, tuple) or not isinstance(radius, float):
        raise InvalidInputException("Please provide circle center and radius!")

    connection = providers.db_connection_provider.get_connection()
    deleted = 0
    has_errors = "No"
    start_time = datetime.now()
    try:
        cursor = connection.cursor()
        cursor.execute("""
                DELETE FROM spatial_core_point 
                WHERE circle '(({0}, {1}), {2})' @> point;
            """.format(center[0], center[1], radius))
        deleted = cursor.rowcount
        cursor.close()
        connection.commit()
    except Exception:
        has_errors = "Yes"
        connection.rollback()
    finally:
        end_time = datetime.now()
        connection.close()

    return has_errors, deleted, get_milliseconds(start_time, end_time)
Пример #8
0
def find_points_in_rectangle(bottom_left_corner, width, height):
    if bottom_left_corner is None or width is None or height is None \
            or not isinstance(bottom_left_corner, tuple) \
            or not isinstance(width, float) \
            or not isinstance(height, float):
        raise InvalidInputException(
            "Please provide bottom left corner point, width and height of bounding box!"
        )

    connection = providers.db_connection_provider.get_connection()
    found = 0
    has_errors = "No"
    start_time = datetime.now()
    try:
        cursor = connection.cursor()
        cursor.execute("""
                SELECT * FROM relational_point
                WHERE x >= {0} AND x <= {1} AND y >= {2} AND y <= {3}
            """.format(bottom_left_corner[0], bottom_left_corner[0] + width,
                       bottom_left_corner[1], bottom_left_corner[1] + height))
        found = cursor.rowcount
        cursor.close()
    except Exception:
        has_errors = "Yes"
    finally:
        end_time = datetime.now()
        connection.close()

    return has_errors, found, get_milliseconds(start_time, end_time)
Пример #9
0
def update_points_in_circle(center, radius, step):
    if center is None or radius is None or step is None \
            or not isinstance(center, tuple) \
            or not isinstance(radius, float) \
            or not isinstance(step, float):
        raise InvalidInputException(
            "Please provide circle center and radius and update step.")

    connection = providers.db_connection_provider.get_connection()
    updated = 0
    has_errors = "No"
    start_time = datetime.now()
    try:
        cursor = connection.cursor()
        cursor.execute("""
                UPDATE spatial_core_point
                SET point = point(point[0] + {0}, point[1] + {0})
                WHERE circle '(({1}, {2}), {3})' @> point;
            """.format(step, center[0], center[1], radius))
        updated = cursor.rowcount
        cursor.close()
        connection.commit()
    except Exception:
        has_errors = "Yes"
        connection.rollback()
    finally:
        end_time = datetime.now()
        connection.close()

    return has_errors, updated, get_milliseconds(start_time, end_time)
Пример #10
0
def delete_points_in_rotated_rectangle(bottom_left_corner, width, height,
                                       angle):
    if bottom_left_corner is None or width is None or height is None or angle is None \
            or not isinstance(bottom_left_corner, tuple) \
            or not isinstance(width, float) \
            or not isinstance(height, float)\
            or not isinstance(angle, float):
        raise InvalidInputException(
            "Please provide bottom left corner point, width, height and angle of rectangle!"
        )

    connection = providers.db_connection_provider.get_connection()
    deleted = 0
    has_errors = "No"
    start_time = datetime.now()
    try:
        cursor = connection.cursor()
        cursor.execute("""
                DELETE FROM relational_point
                WHERE rotated_rectangle_contains(x, y, {0}, {1}, {2}, {3}, {4})
            """.format(bottom_left_corner[0], bottom_left_corner[1], width,
                       height, angle))
        deleted = cursor.rowcount
        cursor.close()
        connection.commit()
    except Exception:
        has_errors = "Yes"
        connection.rollback()
    finally:
        end_time = datetime.now()
        connection.close()

    return has_errors, deleted, get_milliseconds(start_time, end_time)
Пример #11
0
def delete_point_by_coordinates(x, y):
    if x is None or y is None or not isinstance(x, float) or not isinstance(
            y, float):
        raise InvalidInputException(
            "Please provide 'x' and 'y' values for point")

    connection = providers.db_connection_provider.get_connection()
    deleted = 0
    has_errors = "No"
    start_time = datetime.now()
    try:
        cursor = connection.cursor()
        cursor.execute("""
                DELETE FROM relational_point 
                WHERE x = {0} AND y = {1}
            """.format(x, y))
        deleted = cursor.rowcount
        cursor.close()
        connection.commit()
    except Exception:
        has_errors = "Yes"
        connection.rollback()
    finally:
        end_time = datetime.now()
        connection.close()

    return has_errors, deleted, get_milliseconds(start_time, end_time)
Пример #12
0
def update_point_by_coordinates(x, y, step):
    if x is None or y is None or step is None \
            or not isinstance(x, float) \
            or not isinstance(y, float) \
            or not isinstance(step, float):
        raise InvalidInputException(
            "Please provide 'x' and 'y' values for existing point and update step."
        )

    connection = providers.db_connection_provider.get_connection()
    updated = 0
    has_errors = "No"
    start_time = datetime.now()
    try:
        cursor = connection.cursor()
        cursor.execute("""
                UPDATE spatial_postgis_point
                SET point = st_setsrid(st_makepoint(st_x(point) + {0}, st_y(point) + {0}), 4326)
                WHERE st_intersects(point, st_geomfromtext('POINT({1} {2})', 4326))
            """.format(step, x, y))
        updated = cursor.rowcount
        cursor.close()
        connection.commit()
    except Exception:
        has_errors = "Yes"
        connection.rollback()
    finally:
        end_time = datetime.now()
        connection.close()

    return has_errors, updated, get_milliseconds(start_time, end_time)
Пример #13
0
def update_points_in_circle(center, radius, step):
    if center is None or radius is None or step is None \
            or not isinstance(center, tuple) \
            or not isinstance(radius, float) \
            or not isinstance(step, float):
        raise InvalidInputException(
            "Please provide circle center and radius and update step!")

    connection = providers.db_connection_provider.get_connection()
    updated = 0
    has_errors = "No"
    start_time = datetime.now()
    try:
        cursor = connection.cursor()
        cursor.execute("""
                UPDATE spatial_postgis_point
                SET point = st_setsrid(st_makepoint(st_x(point) + {0}, st_y(point) + {0}), 4326)
                WHERE st_dwithin(st_geomfromtext('POINT({1} {2})', 4326), point, {3});
            """.format(step, center[0], center[1], radius))
        updated = cursor.rowcount
        cursor.close()
        connection.commit()
    except Exception:
        has_errors = "Yes"
        connection.rollback()
    finally:
        end_time = datetime.now()
        connection.close()

    return has_errors, updated, get_milliseconds(start_time, end_time)
Пример #14
0
def find_points_in_circle(center, radius):
    if center is None or radius is None or not isinstance(
            center, tuple) or not isinstance(radius, float):
        raise InvalidInputException("Please provide circle center and radius!")

    connection = providers.db_connection_provider.get_connection()
    found = 0
    has_errors = "No"
    start_time = datetime.now()
    try:
        cursor = connection.cursor()
        cursor.execute("""
                SELECT * FROM relational_point WHERE circle_contains(x, y, {0}, {1}, {2});
            """.format(center[0], center[1], radius))
        found = cursor.rowcount
        cursor.close()
    except Exception:
        has_errors = "Yes"
    finally:
        end_time = datetime.now()
        connection.close()

    return has_errors, found, get_milliseconds(start_time, end_time)
Пример #15
0
def insert_points(points):
    if points is None or not isinstance(points, list):
        raise InvalidInputException("Please provide points")

    connection = providers.db_connection_provider.get_connection()
    inserted = 0
    has_errors = "No"
    start_time = datetime.now()
    try:
        cursor = connection.cursor()
        cursor.executemany(
            "INSERT INTO relational_point (x, y) VALUES (%s, %s);", points)
        inserted = cursor.rowcount
        cursor.close()
        connection.commit()
    except Exception:
        has_errors = "Yes"
        connection.rollback()
    finally:
        end_time = datetime.now()
        connection.close()

    return has_errors, inserted, get_milliseconds(start_time, end_time)
Пример #16
0
def update_points_in_rotated_rectangle(bottom_left_corner, width, height,
                                       angle, step):
    if bottom_left_corner is None or width is None or height is None or angle is None \
            or not isinstance(bottom_left_corner, tuple) \
            or not isinstance(width, float) \
            or not isinstance(height, float)\
            or not isinstance(angle, float) \
            or not isinstance(step, float):
        raise InvalidInputException(
            "Please provide bottom left corner point, width, height and angle of rectangle and update step!"
        )

    connection = providers.db_connection_provider.get_connection()
    updated = 0
    has_errors = "No"
    start_time = datetime.now()
    try:
        cursor = connection.cursor()
        cursor.execute("""
                UPDATE spatial_postgis_point
                SET point = st_setsrid(st_makepoint(st_x(point) + {0}, st_y(point) + {0}), 4326)
                WHERE st_intersects(point, st_rotate(st_makeenvelope({1}, {2}, {3}, {4}, 4326), radians({5})))
            """.format(step, bottom_left_corner[0], bottom_left_corner[1],
                       bottom_left_corner[0] + width,
                       bottom_left_corner[1] + height, angle))
        updated = cursor.rowcount
        cursor.close()
        connection.commit()
    except Exception:
        has_errors = "Yes"
        connection.rollback()
    finally:
        end_time = datetime.now()
        connection.close()

    return has_errors, updated, get_milliseconds(start_time, end_time)