For Postgres, efficiently get points within X distance of point Y?

Question

I have this table as below in Postgres:

create table locations
(
    id serial not null
        constraint games_pkey
            primary key,
    name varchar,
    location point,
)
;

I want to query based on another location point( x, y ) as latitude and longitude; I only need to return records within, say, 1 kilometre distance of that location. How can I efficiently select records meeting that criteria?

I only need the distance to be accurate on a local scale and so curvature of the earth shouldn't be an issue for my application, but is there a performance benefit of either POINT or GIS in this context?

I found this question on stackoverflow which appears to do something similar, but the accepted answer has nothing but code and no indication of how it works: https://stackoverflow.com/questions/37827468/find-the-nearest-location-by-latitude-and-longitude-in-postgresql

Answer 1

I only need the distance to be accurate on a local scale and so curvature of the earth shouldn't be an issue for my application, but is there a performance benefit of either POINT or GIS in this context?

The native point is for geometry. If you want distance on Earth then those "points" are being projected onto a representation of Earth (sphereoid) and you need GIS. If you're using PostgreSQL that means you need PostGIS. For a PostGIS query that returns all points in a table within a 1 km (1000 m) distance of a specific point, you can use ST_DWithin

SELECT *
FROM tbl
WHERE ST_DWithin( tbl.geog, ST_MakePoint(x,y)::geography, 1000 );

That will also use a GIST index (if any) on tbl.geog.