3

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

0

1 Answer 1

5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.