daily pastebin goal
41%
SHARE
TWEET

Untitled

a guest Jan 18th, 2019 53 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. create table places(lat_lng point, place_name varchar(50));
  2.  
  3. insert into places values (POINT(-126.4, 45.32), 'Food Bar');
  4.    
  5. select *
  6. from places
  7. where lat_lng <-> POINT(-125.4, 46.32) < 1
  8. order by lat_lng <-> POINT(-125.4, 46.32)
  9.    
  10. create table places(
  11.     lat_lng geography(Point,4326),
  12.     place_name varchar(50)
  13. );
  14.  
  15. -- Two ways to make a geography point
  16. insert into places values (ST_MakePoint(-126.4, 45.32), 'Food Bar1');
  17. insert into places values ('POINT(-126.4 45.32)', 'Food Bar2');
  18.  
  19. -- Spatial index
  20. create index places_lat_lng_idx on places using gist(lat_lng);
  21.    
  22. select *, ST_Distance(lat_lng, ST_MakePoint(-126.4, 45.32)::geography)
  23. from places
  24. where ST_DWithin(lat_lng, ST_MakePoint(-126.4, 45.32)::geography, 1000)
  25. order by ST_Distance(lat_lng, ST_MakePoint(-126.4, 45.32)::geography);
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top