gistfile1.txt

Postgres/Postgis
Distance is an alias attribute.
Models: Business, Bookings
     a business has_many bookings
     business.types are 'Brick & mortar', 'food trucks'
	food trucks can move to different locations throughout the day called a booking

EX:  Sams Food Truck (2pm)
	Date: Today 
		morning booking
		afternoon booking (select me) 
		evening booking
	Date Tomorrow
	    morning booking
		afternoon booking
Booking states:
Active
Upcoming
Past

Query:
Find businesses within X mile radius and 
  set distance from given geo coordinates
or 
Find businesses (food trucks)
	That have bookings within X miles radius
		* A business can have multiple bookings in a day
         return closest booking thats closest to time in zone (see method)
	set the distance from the closest booking
So i'm trying to set the distance attr by choosing an associated single booking.

My Start
SELECT *
FROM (SELECT DISTINCT ON (id) biz.* 
       FROM businesses biz
       RIGHT OUTER JOIN bookings bk 
         ON bk.business_id = biz.id
       WHERE bk.open_to_public = TRUE AND bk.active = TRUE
        AND bk.lat IS NOT NULL
        AND bk.long IS NOT NULL
        AND bk.start_time >= CURRENT_DATE
        AND bk.start_time <  date_trunc('DAY', CURRENT_DATE) + INTERVAL '7 DAY'
      ) biz
WHERE biz.live = true

create_table "bookings", id: :serial, force: :cascade do |t|
    t.string "name"
    t.decimal "lat"
    t.decimal "long"
    t.integer "business_id"
    t.integer "user_id"
    t.datetime "start_time", null: false
    t.datetime "end_time", null: false
    t.datetime "deleted_at"
    t.string "address"
    t.string "street"
    t.string "city"
    t.string "state"
    t.string "zip_code"
    t.string "inside"
    t.string "intersection"
    t.text "public_note"
    t.text "owner_note"
    t.boolean "open_to_public", default: true, null: false
    t.boolean "active", default: true, null: false
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["business_id"], name: "index_bookings_on_business_id"
  end
 create_table "businesses", id: :serial, force: :cascade do |t|
    t.string "name", null: false
    t.string "address", null: false
    t.string "address2"
    t.string "website"
    t.string "phone1"
    t.string "phone2"
    t.string "city"
    t.string "state"
    t.string "zipcode"
    t.string "borough"
    t.text "description"
    t.string "image_url"
    t.string "cross_streets"
    t.string "neighborhood"
    t.decimal "lat"
    t.decimal "long"
    t.boolean "is_verfied"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.integer "price_range", default: 2
    t.boolean "delivery", default: false
    t.integer "created_by_id"
    t.string "year_launched"
    t.string "twitter_handle"
    t.string "instagram_handle"
    t.string "facebook_handle"
    t.string "opentable_url"
    t.string "menu_url"
    t.string "business_email"
    t.string "slug"
    t.integer "user_id"
    t.boolean "live", default: false
    t.string "uber_eats_url"
    t.string "grub_hub_url"
    t.string "eat24_url"
    t.string "seamless_url"
    t.string "doordash_url"
    t.string "postmates_url"
    t.text "dining_options", default: [], array: true
    t.string "caviar_url"
    t.string "delivery_url"
    t.datetime "deleted_at"
    t.integer "cached_votes_total", default: 0
    t.integer "cached_votes_up", default: 0
    t.integer "score", default: 0, null: false
    t.float "cached_weighted_average", default: 0.0
    t.string "type"
    t.boolean "mobile"
    t.integer "mobile_type"
    t.string "black_and_mobile_url"
    t.text "bio"
    t.date "opened_at"
    t.date "closed_at"
    t.jsonb "details", default: {}, null: false
    t.string "cached_classification_list"
    t.integer "reviews_count", default: 0
    t.integer "last_updated_by_id"
    t.integer "last_verified_by_id"
    t.geometry "lnglat", limit: {:srid=>4326, :type=>"st_point"}
    t.index ["deleted_at"], name: "index_businesses_on_deleted_at"
    t.index ["details"], name: "index_businesses_on_details", using: :gin
    t.index ["last_updated_by_id"], name: "index_businesses_on_last_updated_by_id"
    t.index ["last_verified_by_id"], name: "index_businesses_on_last_verified_by_id"
  end


food_truck_business =  {"id"=>55, "name"=>"Aura Bar & Lounge", "address"=>"4410 Avenue H", "address2"=>"", "website"=>"aurabarloungebk.com", "phone1"=>"(929) 210-8434", "phone2"=>"", "city"=>"Brooklyn", "state"=>"NY", "zipcode"=>"11234", "borough"=>"Brooklyn", "description"=>nil, "image_url"=>nil, "cross_streets"=>"", "neighborhood"=>"Flatlands, East Flatbush", "lat"=>0.40632545e2, "long"=>-0.73933671e2, "is_verfied"=>nil, "created_at"=>Sun, 28 Jun 2020 13:16:08 UTC +00:00, "updated_at"=>Tue, 18 Aug 2020 19:23:39 UTC +00:00, "price_range"=>2, "delivery"=>false, "created_by_id"=>nil, "year_launched"=>nil, "twitter_handle"=>"", "instagram_handle"=>"", "facebook_handle"=>"", "opentable_url"=>nil, "menu_url"=>"", "business_email"=>nil, "slug"=>"aura-bar-lounge", "user_id"=>2, "live"=>true, "uber_eats_url"=>nil, "grub_hub_url"=>nil, "eat24_url"=>nil, "seamless_url"=>nil, "doordash_url"=>nil, "postmates_url"=>nil, "dining_options"=>["Take out only"], "caviar_url"=>nil, "delivery_url"=>nil, "deleted_at"=>nil, "cached_votes_total"=>0, "cached_votes_up"=>0, "score"=>2, "cached_weighted_average"=>0.0, "type"=>"Mobile", "mobile"=>true, "mobile_type"=>"food_truck", "black_and_mobile_url"=>nil, "bio"=>"", "opened_at"=>nil, "closed_at"=>nil, "details"=>{"note"=>"", "located_inside"=>"", "temporary_closed"=>false, "in_house_delivery"=>false, "last_verified_date"=>nil, "reported_at_closed"=>false, "temporarily_closed"=>false}, "cached_classification_list"=>"American (Traditional)", "reviews_count"=>0, "last_updated_by_id"=>1, "last_verified_by_id"=>nil, "lnglat"=>nil, "subscription_tier"=>0, "classification_list"=>nil}

(Brick and mortar businesses 2x)
businesses = [{"id"=>17, "name"=>"Abu’s Homestyle Bakery", "address"=>"1184 Fulton St", "address2"=>"", "website"=>"abusbakery.com", "phone1"=>"(718) 230-1115", "phone2"=>"", "city"=>"Brooklyn", "state"=>"NY", "zipcode"=>"11216", "borough"=>"Brooklyn", "description"=>nil, "image_url"=>nil, "cross_streets"=>"", "neighborhood"=>"Bedford Stuyvesant", "lat"=>0.40680706e2, "long"=>-0.73953734e2, "is_verfied"=>nil, "created_at"=>Sun, 28 Jun 2020 13:16:08 UTC +00:00, "updated_at"=>Wed, 29 Jul 2020 21:21:55 UTC +00:00, "price_range"=>2, "delivery"=>false, "created_by_id"=>nil, "year_launched"=>nil, "twitter_handle"=>"", "instagram_handle"=>"", "facebook_handle"=>"", "opentable_url"=>nil, "menu_url"=>"", "business_email"=>nil, "slug"=>"abu-s-homestyle-bakery", "user_id"=>nil, "live"=>true, "uber_eats_url"=>nil, "grub_hub_url"=>nil, "eat24_url"=>nil, "seamless_url"=>nil, "doordash_url"=>nil, "postmates_url"=>nil, "dining_options"=>[], "caviar_url"=>nil, "delivery_url"=>nil, "deleted_at"=>nil, "cached_votes_total"=>15, "cached_votes_up"=>15, "score"=>56, "cached_weighted_average"=>3.4, "type"=>"Business", "mobile"=>false, "mobile_type"=>nil, "black_and_mobile_url"=>nil, "bio"=>"", "opened_at"=>nil, "closed_at"=>nil, "details"=>{"note"=>"", "located_inside"=>"", "temporary_closed"=>false, "in_house_delivery"=>false, "last_verified_date"=>"2020-06-02", "reported_at_closed"=>false, "temporarily_closed"=>true}, "cached_classification_list"=>"", "reviews_count"=>0, "last_updated_by_id"=>1, "last_verified_by_id"=>1, "lnglat"=>nil, "subscription_tier"=>0, "classification_list"=>nil}]

food_truck_business has 6 bookings

bookings-  [{"id"=>4, "name"=>"620 Atlantic Ave", "lat"=>0.406826571e2, "long"=>-0.739752822e2, "business_id"=>55, "user_id"=>1, "start_time"=>Wed, 19 Aug 2020 13:00:00 UTC +00:00, "end_time"=>Wed, 19 Aug 2020 18:00:00 UTC +00:00, "deleted_at"=>nil, "address"=>"620 Atlantic Ave", "street"=>nil, "city"=>"Brooklyn", "state"=>"NY", "zip_code"=>"11217", "inside"=>"", "intersection"=>"", "public_note"=>"", "owner_note"=>"", "open_to_public"=>true, "active"=>true, "created_at"=>Wed, 19 Aug 2020 22:35:10 UTC +00:00, "updated_at"=>Wed, 19 Aug 2020 22:35:11 UTC +00:00, "lnglat"=>nil}, {"id"=>5, "name"=>"Brooklyn Running Club", "lat"=>0.406826571e2, "long"=>-0.739752822e2, "business_id"=>55, "user_id"=>1, "start_time"=>Wed, 19 Aug 2020 20:00:00 UTC +00:00, "end_time"=>Thu, 20 Aug 2020 03:00:00 UTC +00:00, "deleted_at"=>nil, "address"=>"222 Grand St", "street"=>nil, "city"=>"Brooklyn", "state"=>"NY", "zip_code"=>"11211", "inside"=>"", "intersection"=>"", "public_note"=>"", "owner_note"=>"", "open_to_public"=>true, "active"=>true, "created_at"=>Wed, 19 Aug 2020 22:37:25 UTC +00:00, "updated_at"=>Wed, 19 Aug 2020 22:37:25 UTC +00:00, "lnglat"=>nil}, {"id"=>6, "name"=>"620 Atlantic Ave", "lat"=>0.406826571e2, "long"=>-0.739752822e2, "business_id"=>55, "user_id"=>1, "start_time"=>Thu, 20 Aug 2020 13:00:00 UTC +00:00, "end_time"=>Thu, 20 Aug 2020 19:00:00 UTC +00:00, "deleted_at"=>nil, "address"=>"620 Atlantic Ave", "street"=>nil, "city"=>"Brooklyn", "state"=>"NY", "zip_code"=>"11217", "inside"=>"", "intersection"=>"", "public_note"=>"", "owner_note"=>"", "open_to_public"=>true, "active"=>true, "created_at"=>Wed, 19 Aug 2020 22:38:56 UTC +00:00, "updated_at"=>Wed, 19 Aug 2020 22:38:56 UTC +00:00, "lnglat"=>nil}, {"id"=>7, "name"=>"620 Atlantic Ave", "lat"=>0.406826571e2, "long"=>-0.739752822e2, "business_id"=>55, "user_id"=>1, "start_time"=>Fri, 21 Aug 2020 10:00:00 UTC +00:00, "end_time"=>Fri, 21 Aug 2020 13:00:00 UTC +00:00, "deleted_at"=>nil, "address"=>"620 Atlantic Ave", "street"=>nil, "city"=>"Brooklyn", "state"=>"NY", "zip_code"=>"11217", "inside"=>"", "intersection"=>"", "public_note"=>"", "owner_note"=>"", "open_to_public"=>true, "active"=>true, "created_at"=>Wed, 19 Aug 2020 22:39:45 UTC +00:00, "updated_at"=>Wed, 19 Aug 2020 22:39:45 UTC +00:00, "lnglat"=>nil}, {"id"=>8, "name"=>"Brooklyn Running Club", "lat"=>0.406826571e2, "long"=>-0.739752822e2, "business_id"=>55, "user_id"=>1, "start_time"=>Fri, 21 Aug 2020 14:00:00 UTC +00:00, "end_time"=>Fri, 21 Aug 2020 20:00:00 UTC +00:00, "deleted_at"=>nil, "address"=>"222 Grand St", "street"=>nil, "city"=>"Brooklyn", "state"=>"NY", "zip_code"=>"11211", "inside"=>"", "intersection"=>"", "public_note"=>"", "owner_note"=>"", "open_to_public"=>true, "active"=>true, "created_at"=>Wed, 19 Aug 2020 22:40:16 UTC +00:00, "updated_at"=>Wed, 19 Aug 2020 22:40:16 UTC +00:00, "lnglat"=>nil}, {"id"=>9, "name"=>"Kosciuszko Pool", "lat"=>0.406912992e2, "long"=>-0.739487166e2, "business_id"=>55, "user_id"=>1, "start_time"=>Fri, 21 Aug 2020 21:00:00 UTC +00:00, "end_time"=>Sat, 22 Aug 2020 03:00:00 UTC +00:00, "deleted_at"=>nil, "address"=>"670 Marcy Ave", "street"=>nil, "city"=>"Brooklyn", "state"=>"NY", "zip_code"=>"11216", "inside"=>"", "intersection"=>"", "public_note"=>"", "owner_note"=>"", "open_to_public"=>true, "active"=>true, "created_at"=>Wed, 19 Aug 2020 22:42:06 UTC +00:00, "updated_at"=>Wed, 19 Aug 2020 22:42:07 UTC +00:00, "lnglat"=>nil}]


mylatlng = 40.6798104,-73.9506075 
select all businesses /food trucks within 20 miles of mylatlng

Goal set attr dist_meters based on the selected booking

Scenarios: 1
August 19, 2020  @ 6am (Upcoming)
- select booking id: 4

August 19, 2020  @ 10am (Active)
- select booking id: 4

August 19, 2020  @ 3pm (upcoming - time is between shifts)
- select booking id: 5

August 19, 2020  @ 11:10pm (Past)
- select booking id: 5


Scenarios: 2  If no bookings on a day
then select the next booking or past booking


This query should return 
businesses + food_truck_business with dist_meters set



MY Start 
distance_in_meters = (params[:distance] || 20).to_f / 0.00062137

businesses = Business.
select("id, lnglat").
available.
where("ST_DWithin(lnglat::geography, ST_GeomFromText('POINT(#{@lng} #{@lat})', 4326)::geography, #{distance_in_meters}) ")

*This needs to join with bookings*



== schema ==


create table "bookings"
 (  id serial,
    "name" text,
    "lat" float,
    "long" float,
    "business_id" int,
    "user_id" int,
    "start_time" timestamp,
    "end_time" timestamp,
    "deleted_at" timestamp,
    "address" text,
    "street" text,
    "city" text,
    "state" text,
    "zip_code" text,
    "inside" text,
    "intersection" text,
    "public_note" text,
    "owner_note" text,
    "open_to_public" boolean default true,
    "active" boolean default true,
    "created_at" timestamp,
    "updated_at" timestamp
);


create table "businesses"
  ( id serial,
    "name" text,
    "address" text,
    "address2" text,
    "website" text,
    "phone1" text,
    "phone2" text,
    "city" text,
    "state" text,
    "zipcode" text,
    "borough" text,
    "description" text,
    "image_url" text,
    "cross_streets" text,
    "neighborhood" text,
    "lat" float,
    "long" float,
    "is_verfied" boolean,
    "created_at" timestamp,
    "updated_at" timestamp,
    "price_range" integer,
    "delivery" boolean default false,
    "created_by_id" integer,
    "year_launched" text,
    "twitter_handle" text,
    "instagram_handle" text,
    "facebook_handle" text,
    "opentable_url" text,
    "menu_url" text,
    "business_email" text,
    "slug" text,
    "user_id" int,
    "live" boolean default  false,
    "uber_eats_url" text,
    "grub_hub_url" text,
    "eat24_url" text,
    "seamless_url" text,
    "doordash_url" text,
    "postmates_url" text,
    "dining_options" text,
    "caviar_url" text,
    "delivery_url" text,
    "deleted_at" timestamp,
    "cached_votes_total" int,
    "cached_votes_up" int,
    "score" int,
    "cached_weighted_average" float,
    "type" text ,
    "mobile" boolean,
    "mobile_type" integer,
    "black_and_mobile_url" text,
    "bio" text,
    "opened_at" date,
    "closed_at" date,
    "details" jsonb,
    "cached_classification_list" text,
    "reviews_count" int,
    "last_updated_by_id" int,
    "last_verified_by_id" int,
    "lnglat" geometry 
);