Skip to content

Instantly share code, notes, and snippets.

@denishpatel
Last active August 24, 2020 15:05
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save denishpatel/b1ead051108c56873590cccd3f7e6a48 to your computer and use it in GitHub Desktop.
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
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment