CODEWITHSUNDEEP
sqldatabasepostgresql
id4alex
id4alex

Reputation: 21

How to create composite index which contains the (latitude,longitude) and other datatype column in PostgreSQL?

DDL:

CREATE TABLE test
(
    longid bigint NOT NULL DEFAULT nextval('test_longid_seq'::regclass),
    longitude double precision,
    latitude double precision,
    create_ts_long bigint,
    CONSTRAINT test_pkey PRIMARY KEY (longid)
)

We can use this to create the gist index :

CREATE INDEX ll_idx
    ON test USING gist
    (ll_to_earth(latitude ::double precision, longitude ::double precision))
    TABLESPACE pg_default;

But there is a scenario that I want to find the nearest records of the specific time(create_ts_long).

So how to create such composite index? Thanks in advance!

Upvotes: 2

Views: 231

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246403

Install the btree_gist contrib module.
Then you have a gist_int8_ops operator class that you can use to create a GiST index on a bigint column.

Upvotes: 1

Related Questions