Postgres how to use point type in query

Question

Hello I have an Postgres SQL table, one column of its is type point

CREATE TABLE public.geometry_cylinder
(
    id          serial not null primary key,
    distance    float not null,
    height      float not null,
    coordinates point not null

);

how can I use one of x or y coordinates in an SQL query?

select * from public.geometry_cylinder where coordinates.x > 14.24

user330315 · Accepted Answer · 2020-02-10 13:50:48Z

5

point isn't a record type so you can't access x and y using the dot notation (though I admit, this would be logical). The explanation how to do it, is a bit hidden in the manual:

It is possible to access the two component numbers of a point as though the point were an array with indexes 0 and 1. For example, if t.p is a point column then SELECT p[0] FROM t retrieves the X coordinate

So you can use:

select * 
from public.geometry_cylinder 
where coordinates[0] > 14.24;

answered Feb 10, 2020 at 13:50

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres how to use point type in query

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