CODEWITHSUNDEEP
postgresqlpostgispostgresql-9.1
kapso
kapso

Reputation: 11903

Nearest places from a certain point

I have the following table

create table places(lat_lng point, place_name varchar(50));

insert into places values (POINT(-126.4, 45.32), 'Food Bar');

What should be the query to get all places close to particular lat/long?

gis is installed.

Upvotes: 9

Views: 5833

Answers (3)

Irshad Khan
Irshad Khan

Reputation: 6046

Create an Indexing on a location field :

CREATE INDEX ON table_name USING GIST(location);

GiST index is capable of optimizing “nearest-neighbor” search :

SELECT * FROM table_name ORDER BY location <-> point '(-74.013, 40.711)' LIMIT 10;

Note: The point first element is longitude and the second element is latitude.

Upvotes: 0

Mike T
Mike T

Reputation: 43692

If you actually wanted to use PostGIS:

create table places(
    lat_lng geography(Point,4326),
    place_name varchar(50)
);

-- Two ways to make a geography point
insert into places values (ST_MakePoint(-126.4, 45.32), 'Food Bar1');
insert into places values ('POINT(-126.4 45.32)', 'Food Bar2');

-- Spatial index
create index places_lat_lng_idx on places using gist(lat_lng);

Now to find all of the places within 1 km (or 1000 m):

select *, ST_Distance(lat_lng, ST_MakePoint(-126.4, 45.32)::geography)
from places
where ST_DWithin(lat_lng, ST_MakePoint(-126.4, 45.32)::geography, 1000)
order by ST_Distance(lat_lng, ST_MakePoint(-126.4, 45.32)::geography);

Upvotes: 10

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125444

select *
from places
where lat_lng <-> POINT(-125.4, 46.32) < 1
order by lat_lng <-> POINT(-125.4, 46.32)

Upvotes: 10

Related Questions