afcotroneo

from pyomnisci import connect
con = connect(user="[username]", 
              password="[password]", 
              host="[hostname]", 
              dbname="[database]")
con

afcotroneo

createQuery = "CREATE TABLE noaa_data_table_name (
  id TEXT ENCODING DICT(32), 
  dt DATE ENCODING DAYS(16), 
  element_ TEXT ENCODING DICT(8), 
  value_ SMALLINT, 
  m_flag TEXT ENCODING DICT(8), 
  q_flag TEXT ENCODING DICT(8), 
  s_flag TEXT ENCODING DICT(8), 
  obs_time TEXT ENCODING DICT(16));"

afcotroneo

CREATE TABLE noaa_data_geo as (
  SELECT 
   a.id,
   a.dt,
   a.element_,
   a.value_,
   b.latitude,
   b.longitude,
   b.elevation,
   b.NAME

afcotroneo

## Import the garbage collection module for clearing junk
import gc 

## These are our columns in the CSV file that will match the table columns
cols = ["id", "dt", "element_", "value_", "m_flag", "q_flag", "s_flag", "obs_time"]

## This loop will run from year 1900 to the present 2021.
for i in range(1900,2021):

## Create the url string

afcotroneo

ALTER TABLE noaa_data_geo ADD COLUMN degrees SMALLINT;

UPDATE noaa_data_geo
SET degrees = value_ / 10
WHERE value_ is not null

afcotroneo

CREATE TABLE nyc_violent_crimes  AS (
  SELECT * 
  FROM nypd_complaints_slimmed 
  WHERE OFNS_DESC LIKE '%ASSAULT%'
  OR OFNS_DESC LIKE '%MANSLAUGHTER%'
  OR OFNS_DESC LIKE '%ARSON%'
  OR OFNS_DESC LIKE '%RAPE%'
  OR OFNS_DESC LIKE '%KIDNAPPING%'
  OR OFNS_DESC LIKE '%WEAPONS%'
  OR OFNS_DESC LIKE '%GUN%'

afcotroneo

## Find the NYC area IDs using a bounding box:

SELECT
    distinct id
FROM
    noaa_data_geo a
WHERE
    ST_Contains(ST_GeomFromText('POLYGON((-74.369989 40.978649, -73.46076 40.978649, -73.46076 40.427511, -74.369989 40.427511, -74.369989 40.978649))', 4326), ST_SetSRID(ST_Point(a.longitude, a.latitude), 4326))
LIMIT 10

afcotroneo

WITH dailytempavgandcrimecount AS (
    SELECT
        a.dt,
        avg(a.value_) as avg_temp,
        count(b.CMPLNT_FR_DT) as crime_count
    FROM nyc_noaa_post2000 a
    JOIN nyc_violent_crimes b 
    ON a.dt = b.CMPLNT_FR_DT
    GROUP BY
        a.dt

afcotroneo

# Public DNS
scp -i /path/my-key-pair.pem SFFind_Neighborhoods.csv my-instance-user-name@my-instance-public-dns-name:~/.

# IPv6
scp -i /path/my-key-pair.pem SFFind_Neighborhoods.csv my-instance-user-name@my-instance-IPv6-address:~/.

afcotroneo

Warning: Permanently added 'my-instance-public-dns-name' (RSA) to the list of known hosts.

SFFind_Neighborhoods.csv                    100%   480     24.4KB/s   00:00