PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Find xy points within radius

I have an app written in TS with a PostgreSQL database that I access using Prisma. In the database I have a table called Points with columns X and Y that represents points on the cartesian plane. I need to write a query that given point (x_p, y_p) and radius R will return all points within the radius. I’d rather avoid writing raw query, so I am looking for a solution using Prisma query(edit: I cannot use PostGIS). Is it something doable with Prisma query? Thanks!

Tags:

2

Answers


  1. 0

    You can try something like this:

    import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function getPointsWithinRadius(x_p: number, y_p: number, R: number) {
    const radiusInDegrees = R / 111; // Rough approximation: 1 degree ≈ 111 km
    const minX = x_p - radiusInDegrees;
    const maxX = x_p + radiusInDegrees;
    const minY = y_p - radiusInDegrees;
    const maxY = y_p + radiusInDegrees;
    const points = await prisma.points.findMany({
        where: {
            AND: [
                { X: { gte: minX } },
                { X: { lte: maxX } },
                { Y: { gte: minY } },
                { Y: { lte: maxY } },
            ],
        },
    });
    const pointsWithinRadius = points.filter(point => {
        const distance = Math.sqrt(
            Math.pow(point.X - x_p, 2) + Math.pow(point.Y - y_p, 2)
        );
        return distance <= R;
    });
    return pointsWithinRadius;
}

    getPointsWithinRadius(10, 10, 5).then(points => {
    console.log(points);
}).catch(error => {
    console.error(error);
});
    Login or Signup to reply.
  2. 0

    I would just use the SQL query:

    SELECT x, y FROM points
WHERE (x-x_p)^2 + (y-y_p)^2 <= R^2
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search