pg.sql

CREATE EXTENSION postgis;

SELECT postgis_full_version();

SELECT st_geomfromtext('POINT(-1 38)', 4329);

SELECT st_asewkt('0101000020E9100000000000000000F0BF0000000000004340');

SELECT st_geomfromtext('LINESTRING(-13 21,0 0,3 2)') AS mycheckmark;

SELECT st_geomfromtext('POLYGON((0 1,1 -1, -1 -1, 0 1))') AS mytriange;

CREATE SCHEMA ch01;
CREATE TABLE ch01.lu_franchises (
  franchise_code CHAR(1) PRIMARY KEY,
  franchise_name VARCHAR(100)
);


DELETE FROM ch01.lu_franchises;

INSERT INTO ch01.lu_franchises (franchise_code, franchise_name)
VALUES ('b', 'Burger King'),
  ('c', 'Carl''s Jr'),
  ('h', 'Hardee''s'),
  ('i', 'In-N-Out'),
  ('j', 'Jack in the Box'),
  ('k', 'Kentucky Fried Chicken'),
  ('m', 'McDonald''s'),
  ('p', 'Pizza Hut'),
  ('t', 'Taco Bell'),
  ('w', 'Wendy''s');


CREATE TABLE ch01.fastfoods
(
  franchise CHAR(1) NOT NULL,
  lat       DOUBLE PRECISION,
  lon       DOUBLE PRECISION
);

-- Download:  https://gist.github.com/hanfeisun/3218217092a442e2b3a3
COPY ch01.fastfoods FROM '/Users/hanfeisun/Downloads/ch01_code_data/data/fastfoods.csv' DELIMITER ',';

SELECT *
FROM ch01.fastfoods;


-- Solution 1: Using geometry type
SELECT addgeometrycolumn('ch01', 'fastfoods', 'geom', 2163, 'point', 2);

UPDATE ch01.fastfoods
SET geom = st_transform(st_geomfromtext('point(' || lon || ' ' || lat || ')', 4326), 2163);

CREATE INDEX idex_fastfoods_geom ON ch01.fastfoods USING GIST (geom);

-- Solution 2: Using geography type
ALTER TABLE ch01.fastfoods ADD COLUMN geog GEOGRAPHY(POINT, 4326);

UPDATE ch01.fastfoods
SET geog = st_geogfromtext('SRID=4326; POINT(' || lon || ' ' || lat || ')');
CREATE INDEX idex_fastfoods_geog ON ch01.fastfoods USING GIST (geog);