yumebayashi's note

Calculate approximate distance between two latitude/longitude points by simple query

  • make initial data set
CREATE TABLE tmp_test (
   name character varying(255),
   lat1 numeric(8,5),
   lng1 numeric(8,5),
   lat2 numeric(8,5),
   lng2 numeric(8,5)
)

INSERT INTO tmp_test VALUES
('tokyo-shinjuku',35.680605, 139.767351,35.689862,139.700253);

select * from tmp_test ;
      name      |   lat1   |   lng1    |   lat2   |   lng2
----------------+----------+-----------+----------+-----------
 tokyo-shinjuku | 35 ...

Query Cheet Sheet in RedShift(Postgres) 

CREATE TABLE tmp_test (
   name character varying(255),
   value1 numeric(8,5),
   value2 numeric(8,5),
   created_at timestamp
)

bulk insert & create table

INSERT INTO tmp_test (name,value1,value2,created_at) VALUES
('a',11.111, 111.11, getdate()),
('b',22.222, 222.22, getdate() + interval '1 day'),
('c',33.333, 333.33, getdate ...

RedShift Query Tips

  • Confirm table definition
select * from pg_table_def where tablename = 'target_table';
  • Check running query and its pid
    • terminate query -> cancel {pid}
select pid,starttime, left(query,50) from stv_recents where status='Running';
  • Check whether the table needs to be vacuumed or not
/** 0.not vacuumed table */
select
  '0_not_sorted' as status,
  sum_result.tablename ...