Can I use the Postgres functions to find points in

Question

I'm using Postgres 9.5 and I've just installed PostGIS for some extended functions. I have a table with (x,y) points and I want to find the rectangle that fits the maximum number of points. The constraint is that the rectangle side lenghts are fixed. So far I'm counting how many points are in the box without rotation. My points are centered around the origin, (0,0).

SELECT Sum(CASE
             WHEN x > -5
                  AND x < 5
                  AND y > -10
                  AND y < 10 THEN 1
             ELSE 0
           END) AS inside_points,
       Count(1) AS total_points
FROM   track_t;  

This query gives me the count of points inside a rectangle with origin (0,0) and lenghts x = 10 and y = 20.

From here I would create a helper table of rotated rectangle corner points (angle, x1, y1, x2, y2), then cross join to my data, and count over the points per angle, while GROUP BY angle. Then I can select which angle gives me the most points inside the rectangle.

But this seems a little old fashioned, and perhaps non-performant. Additionally, counting points inside a rotated rectangle is not a trivial calculation.

Are there more efficient and elegant ways, perhaps using Postgres Geometric Datatypes or PostGIS Box2D, to rotate a rectangle with fixed side lenghts, and then to count the number of points inside? The geometric functions look good, but they seem to provide minimum bounding boxes and not the other way around.

In addition to Postgresql, I'm using a Python framework that could be used in case SQL can't make this work.

---

Update: One thing I tried is to use Geometric Types, specifically BOX

  SELECT deg, Box(Point(-5, -10), Point(5, 10)) * Point(1, Radians(deg)) 
        FROM   Generate_series(0, 360, 90) AS deg

Unforunately, the Rotate function by a Point doesn't work for Polygons.

Answer 1

I ended up by generating rectangle vertices, rotating those vertices, and then comparing the area of the rectangle (constant) with the area of the 4 triangles that are made by including the test point.

This technique is based on the parsimonious answer:

Make triangle. Suppose, abcd is the rectangle and x is the point then if area(abx)+area(bcx)+area(cdx)+area(dax) equals area(abcd) then the point is inside it.

The rectangles are defined by

• A bottom left (-x/2,-y/2)

• B top left (-x/2,+y/2)

• C top right (+x/2,+y/2)

• D bottom right (+x/2,-y/2)

This code then checks if point (qx,qy) is inside a rectangle of width x=10 and height y=20, which is rotated around the origin (0,0) by an angle with range of 0 to 180, by 10 degrees.

Here's the code. It's taking 9 minutes to check 750k points, so there is definite room for improvement. Additionally, It can be parallelized once I upgrade to 9.6

with t as (select 10*0.5 as x, 20*0.5 as y, 17.0 as qx, -3.0 as qy)

select 
    z.angle
    -- ABC area
    --,abs(0.5*(z.ax*(z.by-z.cy)+z.bx*(z.cy-z.ay)+z.cx*(z.ay-z.by)))

    -- CDA area
    --,abs(0.5*(z.cx*(z.dy-z.ay)+z.dx*(z.ay-z.cy)+z.ax*(z.cy-z.dy)))

    -- ABCD area
    ,abs(0.5*(z.ax*(z.by-z.cy)+z.bx*(z.cy-z.ay)+z.cx*(z.ay-z.by))) + abs(0.5*(z.cx*(z.dy-z.ay)+z.dx*(z.ay-z.cy)+z.ax*(z.cy-z.dy))) as abcd_area

    -- ABQ area
    --,abs(0.5*(z.ax*(z.by-z.qx)+z.bx*(z.qy-z.ay)+z.qx*(z.ay-z.by)))

    -- BCQ area
    --,abs(0.5*(z.bx*(z.cy-z.qx)+z.cx*(z.qy-z.by)+z.qx*(z.by-z.cy)))

    -- CDQ area
    --,abs(0.5*(z.cx*(z.dy-z.qx)+z.dx*(z.qy-z.cy)+z.qx*(z.cy-z.dy)))

    -- DAQ area
    --,abs(0.5*(z.dx*(z.ay-z.qx)+z.ax*(z.qy-z.dy)+z.qx*(z.dy-z.ay)))

    -- total area of triangles with question point (ABQ + BCQ + CDQ + DAQ)
    ,abs(0.5*(z.ax*(z.by-z.qx)+z.bx*(z.qy-z.ay)+z.qx*(z.ay-z.by)))
        + abs(0.5*(z.bx*(z.cy-z.qx)+z.cx*(z.qy-z.by)+z.qx*(z.by-z.cy)))
        + abs(0.5*(z.cx*(z.dy-z.qx)+z.dx*(z.qy-z.cy)+z.qx*(z.cy-z.dy)))
        + abs(0.5*(z.dx*(z.ay-z.qx)+z.ax*(z.qy-z.dy)+z.qx*(z.dy-z.ay))) as point_area

from
(
SELECT 
    a.id as angle
    -- bottom left (A)
    ,(-t.x) * cos(radians(a.id)) - (-t.y) * sin(radians(a.id)) as ax
    ,(-t.x) * sin(radians(a.id)) + (-t.y) * cos(radians(a.id)) as ay
    --top left (B)
    ,(-t.x) * cos(radians(a.id)) - (t.y) * sin(radians(a.id)) as bx
    ,(-t.x) * sin(radians(a.id)) + (t.y) * cos(radians(a.id)) as by
    --top right (C)
    ,(t.x) * cos(radians(a.id)) - (t.y) * sin(radians(a.id)) as cx
    ,(t.x) * sin(radians(a.id)) + (t.y) * cos(radians(a.id)) as cy
    --bottom right (D)
    ,(t.x) * cos(radians(a.id)) - (-t.y) * sin(radians(a.id)) as dx
    ,(t.x) * sin(radians(a.id)) + (-t.y) * cos(radians(a.id)) as dy

    -- point to check (Q)
    ,t.qx as qx
    ,t.qy as qy
FROM generate_series(0,180,10) AS a(id), t
) z
;

the results then are

angle;abcd_area;point_area
0;200;340
10;200;360.6646055963
20;200;373.409049054212
30;200;377.846096908265
40;200;373.84093170467
50;200;361.515248361426
60;200;341.243556529821
70;200;313.641801308188
80;200;279.548648061772
90;200;240
*100;200;200*
*110;200;200*
*120;200;200*
*130;200;200*
*140;200;200*
150;200;237.846096908265
160;200;277.643408923024
170;200;312.04311584956
180;200;340

Where the rotations of angles 100, 110, 120, 130 and 140 degrees then includes the test-point (indicated with *)