Creates geometry and distance columns, geometry, and distance between two points

Origin_Destination_DistanceAnalysis.sql

--create geometry columns
ALTER TABLE vta_wkday_od_database ADD COLUMN origin_geom geometry(Point, 4326);
ALTER TABLE vta_wkday_od_database ADD COLUMN first_board_geom geometry(Point, 4326);

--create geometry from latitude and longitude columns
UPDATE vta_wkday_od_database SET first_board_geom = ST_SetSRID(ST_MakePoint(first_board_lon::double precision, first_board_lat::double precision), 4326);
UPDATE vta_wkday_od_database SET origin_geom = ST_SetSRID(ST_MakePoint(origin_lon::double precision, origin_lat::double precision), 4326);

--change to 2227 so that calculations are in feet not meters.
ALTER TABLE vta_wkday_od_database
  ALTER COLUMN origin_geom
  TYPE Geometry(Point, 2227)
  USING ST_Transform(origin_geom, 2227);

--add distance column
ALTER TABLE vta_wkday_od_database ADD COLUMN origin_firstboard_distanceFT double precision;

--set distance column equal to distance between two geometry columns
UPDATE vta_wkday_od_database SET origin_firstboard_distanceFT = ST_Distance(origin_geom, first_board_geom);