spatial example

create_point.sql

-- create the table
CREATE TABLE brady (
   location    varchar,
   date        date,
   description text,
   latitude    real,
   longitude  real);

-- read CSV file into the table (actually tab delimited)
COPY brady (location, date, description, latitude, longitude) 
   FROM '/Users/sparafina/projects/gun_violence/major-shootings_geocoded.csv'
   WITH DELIMITER AS "\t"
        CSV HEADER;

-- add geometry column
SELECT AddGeometryColumn('public', 'brady', 'geom', 4326, 'POINT', 2);

-- create points from latitude and longitude fields
UPDATE brady SET geom = ST_SetSRID(ST_MakePoint(longitude,latitude),4326);

-- create a spatial index on the geometry
CREATE INDEX brady_idx ON brady USING GIST ( geom );