How does scaling operator (*) work in PostgreSQL?

Question

I just started learning PostgreSQL and I cannot figure out how does scaling operator work on geometric types.

For example select '((1, 1), (0, 0))'::box * '(2, 0)'::point; returns ((2,2),(0,0))

and select '((1, 1), (0, 0))'::box * '(0, 2)'::point; returns ((0,2),(-2,0))

so in both cases box gets scaled by the factor of 2 (for both axes), but the way box is moved makes no sense to me.

Official documentation only shows one example of usage of this operator and nothing about how it works.

If someone knows a better resource for learning PostgreSQL please share it.

Thanks in advance.

How is this Matrix x Vector multiplication? If box is 2x2 Matrix and point is vector of size 2 (2x1 Matrix) multiplying these two should give 2x1 Matrix (which cannot be box) — displayName
– displayName, Commented Aug 28, 2018 at 21:29
Documentation says it is "scaling/rotation" so maybe (magnitude,radians) — Ben
– Ben, Commented Aug 28, 2018 at 21:34
As to "a better resource" maybe try a few examples and it may become obvious. — Ben
– Ben, Commented Aug 28, 2018 at 21:37

Abelisto · Accepted Answer · 2018-08-29 06:02:05Z

Here is implementation of the function box_mul that stays behind the *(box, point) operator (from https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/geo_ops.c):

static inline void
point_mul_point(Point *result, Point *pt1, Point *pt2)
{
    point_construct(result,
                    float8_mi(float8_mul(pt1->x, pt2->x),
                              float8_mul(pt1->y, pt2->y)),
                    float8_pl(float8_mul(pt1->x, pt2->y),
                              float8_mul(pt1->y, pt2->x)));
}

Datum
box_mul(PG_FUNCTION_ARGS)
{
    BOX        *box = PG_GETARG_BOX_P(0);
    Point      *p = PG_GETARG_POINT_P(1);
    BOX        *result;
    Point       high,
                low;

    result = (BOX *) palloc(sizeof(BOX));

    point_mul_point(&high, &box->high, p);
    point_mul_point(&low, &box->low, p);

    box_construct(result, &high, &low);

    PG_RETURN_BOX_P(result);
}

or translated to the more "human" language:

((x₁, y₁), (x₂, y₂)) * (x, y) :- ((x₁*x - y₁*y, x₁*y + y₁*x), (x₂*x - y₂*y, x₂*y + y₂*x))

For your example

((1,1),(0,0)) * (0,2) = ((1*0 - 1*2, 1*2 + 1*0), (0*0 - 0*2, 0*2 + 0 * 0)) = ((-2,2),(0,0))

and finally box_construct() transforms it to (0,2),(-2,0) (just check select '((-2,2),(0,0))'::box;)

If you know/remember the geometric meaning of those transformations - post your final answer here.

Collectives™ on Stack Overflow

How does scaling operator (*) work in PostgreSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related