সরাসরি Places Insights ডেটা অ্যাক্সেস করতে, আপনি BigQuery-তে SQL কোয়েরি লেখেন যা জায়গা সম্পর্কে এগ্রিগেট করা ইনসাইট প্রদান করে। কোয়েরিতে উল্লেখ করা সার্চের মাপকাঠির জন্য ডেটাসেট থেকে ফলাফল দেখানো হয়।
অ্যাগ্রিগেশন থ্রেশহোল্ড সম্পর্কে বোঝা
র টেবিলের উপর সরাসরি রান করা স্ট্যান্ডার্ড SQL কোয়েরি কঠোর অ্যাগ্রিগেশন থ্রেশহোল্ড প্রয়োগ করে। শুধুমাত্র গণনা করা অ্যাগ্রিগেশন সংখ্যা ৫ বা তার বেশি হলে আউটপুট সারি তৈরি করা হয়।
আপনার কোয়েরি ফিল্টার যদি ০, ১, ২, ৩ বা ৪টি ফলাফল দেখায়, তাহলে ফলাফলের সম্পূর্ণ সারিটি উত্তরে দেখানো হয় না। আপনার অ্যাপ্লিকেশন লজিকে যদি সুনির্দিষ্ট কম সংখ্যা (0 সহ) প্রয়োজন হয় বা স্বতন্ত্র স্থান আইডি পুনরুদ্ধার করতে হয়, তাহলে আপনাকে পরিবর্তে Places Count Functions ব্যবহার করে ডেটাসেট কোয়েরি করতে হবে।
কোয়েরি সংক্রান্ত প্রাথমিক তথ্য
নিচের ছবিতে কোয়েরির প্রাথমিক ফর্ম্যাট দেখানো হয়েছে:
কোয়েরির প্রতিটি অংশ নিচে আরও বিস্তারিতভাবে বর্ণনা করা হয়েছে।
কোয়েরি সংক্রান্ত প্রয়োজনীয়তা
সরাসরি ডেটাসেটে করা SQL কোয়েরিতে অবশ্যই ডেটাসেট উল্লেখ করতে হবে এবং SELECT ক্লজে WITH AGGREGATION_THRESHOLD অন্তর্ভুক্ত করতে হবে। এটি ছাড়া, কোয়েরি
কাজ করবে না।
এই উদাহরণে মার্কিন যুক্তরাষ্ট্রের জন্য ডেটাসেট কোয়েরি করতে places_insights___us.places নির্দিষ্ট করা হয়েছে।
SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places`
প্রোজেক্টের নাম উল্লেখ করুন (ঐচ্ছিক)
আপনি চাইলে কোয়েরিতে আপনার প্রোজেক্টের নাম অন্তর্ভুক্ত করতে পারেন। আপনি প্রোজেক্টের নাম উল্লেখ না করলে, আপনার কোয়েরি ডিফল্ট হিসেবে অ্যাক্টিভ প্রোজেক্টে সেট হয়ে যায়।
আপনি যদি বিভিন্ন প্রোজেক্টে একই নামের ডেটাসেট লিঙ্ক করে থাকেন বা অ্যাক্টিভ প্রোজেক্টের বাইরে কোনও টেবিল কোয়েরি করেন, তাহলে আপনার প্রোজেক্টের নাম অন্তর্ভুক্ত করতে হতে পারে।
যেমন, [project name].[dataset name].places
এগ্রিগেশন ফাংশন উল্লেখ করুন
নিচের উদাহরণে কাজ করে এমন BigQuery এগ্রিগেশন ফাংশন দেখানো হয়েছে। এই কোয়েরি নিউ ইয়র্ক সিটির এম্পায়ার স্টেট বিল্ডিংয়ের ১০০০ মিটার ব্যাসার্ধের মধ্যে অবস্থিত সব জায়গার রেটিং একত্রিত করে রেটিংয়ের পরিসংখ্যান তৈরি করে:
SELECT WITH AGGREGATION_THRESHOLD COUNT(id) AS place_count, APPROX_COUNT_DISTINCT(rating) as distinct_ratings, COUNTIF(rating > 4.0) as good_rating_count, LOGICAL_AND(rating <= 5) as all_ratings_equal_or_below_five, LOGICAL_OR(rating = 5) as any_rating_exactly_five, AVG(rating) as avg_rating, SUM(user_rating_count) as rating_count, COVAR_POP(rating, user_rating_count) as rating_covar_pop, COVAR_SAMP(rating, user_rating_count) as rating_covar_samp, STDDEV_POP(rating) as rating_stddev_pop, STDDEV_SAMP(rating) as rating_stddev_samp, VAR_POP(rating) as rating_var_pop, VAR_SAMP(rating) as rating_var_samp, FROM `PROJECT_NAME.places_insights___us.places` WHERE ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000) AND business_status = "OPERATIONAL"
ভৌগোলিক পলিগন ও লাইন ব্যবহার করে ফলাফল ফিল্টার করুন
আপনি লোকেশন সংক্রান্ত বিধিনিষেধ উল্লেখ না করলে, ডেটা অ্যাগ্রিগেশন সম্পূর্ণ ডেটাসেটে প্রয়োগ করা হয়। সাধারণত, আপনি কোনও নির্দিষ্ট এলাকা সার্চ করার জন্য লোকেশন সংক্রান্ত বিধিনিষেধ উল্লেখ করেন।
১. রেডিয়াল সার্চ বাফার (ST_DWITHIN) ব্যবহার করে ফিল্টার করুন
এই উদাহরণ কোয়েরি নিউ ইয়র্ক সিটির এম্পায়ার স্টেট বিল্ডিংয়ের চারপাশে ১০০০ মিটার ব্যাসার্ধের মধ্যে টার্গেট সংক্রান্ত বিধিনিষেধ নির্দিষ্ট করে।
SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places` WHERE ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
২. পলিগন বাউন্ডারি (ST_CONTAINS) ব্যবহার করে ফিল্টার করুন
সার্চ করার এলাকা নির্দিষ্ট করতে আপনি পলিগন ব্যবহার করতে পারেন। বহুভুজ ব্যবহার করার সময়, বহুভুজের বিন্দুগুলি অবশ্যই একটি বদ্ধ লুপকে সংজ্ঞায়িত করবে যেখানে বহুভুজের প্রথম বিন্দুটি শেষ বিন্দুর মতোই হবে:
SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places` WHERE ST_CONTAINS(ST_GEOGFROMTEXT("""POLYGON((-73.985708 40.75773,-73.993324 40.750298, -73.9857 40.7484,-73.9785 40.7575, -73.985708 40.75773))"""), point)
৩. রুট ট্রাভেল বাফার (লাইনস্ট্রিং) অনুযায়ী ফিল্টার করুন
পরবর্তী উদাহরণে, আপনি কানেক্ট করা পয়েন্টের লাইন ব্যবহার করে সার্চের এলাকা নির্ধারণ করেন এবং লাইনের চারপাশে ১০০ মিটার সার্চের ব্যাসার্ধ সেট করেন। লাইনটি Routes API-এর মাধ্যমে হিসেব করা ভ্রমণ রুটের মতো। রুটটি গাড়ি, সাইকেল বা পথচারীর জন্য হতে পারে:
DECLARE route GEOGRAPHY; SET route = ST_GEOGFROMTEXT("""LINESTRING(-73.98903537033028 40.73655649223003, -73.93580216278471 40.80955538843361)"""); SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places` WHERE ST_DWITHIN(route, point, 100)
জায়গার ডেটাসেট ফিল্ড অনুযায়ী ফিল্টার করুন
ডেটাসেট
স্কিমা দ্বারা সংজ্ঞায়িত ফিল্ডের ভিত্তিতে আপনার সার্চ রিফাইন করুন। জায়গা regular_opening_hours, price_level এবং
গ্রাহক rating-এর মতো ডেটাসেট ফিল্ডের উপর ভিত্তি করে
ফলাফল ফিল্টার করুন।
আপনার আগ্রহের দেশের জন্য ডেটাসেট স্কিমা দ্বারা সংজ্ঞায়িত ডেটাসেটের যেকোনো ফিল্ড রেফারেন্স করুন। প্রতিটি দেশের জন্য ডেটাসেট স্কিমা দুটি অংশ নিয়ে গঠিত:
- কোর স্কিমা যা সব দেশের ডেটাসেটের জন্য সাধারণ।
- একটি দেশ-নির্দিষ্ট স্কিমা যা সেই দেশের জন্য নির্দিষ্ট স্কিমা কম্পোনেন্টকে সংজ্ঞায়িত করে।
যেমন, আপনার কোয়েরিতে WHERE ক্লজ থাকতে পারে যা কোয়েরির জন্য ফিল্টারিংয়ের
মাপকাঠি নির্ধারণ করে।
নিম্নলিখিত উদাহরণে, আপনি tourist_attraction ধরনের জায়গার জন্য এগ্রিগেশন ডেটা রিটার্ন করেন, যেখানে business_status হল OPERATIONAL, rating ৪.০-এর থেকে বেশি বা সমান এবং allows_dogs-কে true হিসেবে সেট করা হয়েছে:
SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places` WHERE ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000) AND 'tourist_attraction' IN UNNEST(types) AND business_status = "OPERATIONAL" AND rating >= 4.0 AND allows_dogs = true
পরবর্তী কোয়েরি এমন জায়গার ফলাফল দেখায় যেখানে অন্তত আটটি EV চার্জিং স্টেশন আছে:
SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places` WHERE ev_charge_options.connector_count > 8;
জায়গার প্রাথমিক ধরন ও জায়গার ধরন অনুযায়ী ফিল্টার করুন
ডেটাসেটের প্রতিটি জায়গায় এগুলি থাকতে পারে:
- জায়গার ধরন-এর মাধ্যমে সংজ্ঞায়িত ধরন থেকে এটির সাথে যুক্ত একটি প্রাথমিক ধরন। যেমন,
প্রাথমিক ধরন
mexican_restaurantবাsteak_houseহতে পারে। কোনও জায়গার প্রাথমিক প্রকারের উপর ভিত্তি করে ফলাফল ফিল্টার করতে, কোয়েরিতেprimary_typeব্যবহার করুন। - জায়গার
ধরন থেকে সংজ্ঞায়িত ধরন থেকে এটির সাথে যুক্ত একাধিক ধরনের ভ্যালু। যেমন, কোনও রেস্তোরাঁর
নিম্নলিখিত ধরন থাকতে পারে:
seafood_restaurant,restaurant,food,point_of_interest,establishment। জায়গার সাথে সম্পর্কিত ধরনের তালিকায় ফলাফল ফিল্টার করতে, কোয়েরিতেtypesব্যবহার করুন।
নিম্নলিখিত কোয়েরি এমন সব জায়গার ফলাফল দেখায় যেগুলির প্রাথমিক ধরন হল
skin_care_clinic এবং যেগুলি spa হিসেবেও কাজ করে:
SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places` WHERE 'spa' IN UNNEST(types) AND 'skin_care_clinic' = primary_type
প্লেস আইডি অনুযায়ী ফিল্টার করুন
নিচের উদাহরণটি ৫টি জায়গার গড় রেটিং গণনা করে। place_id-এর মাধ্যমে
জায়গাগুলি শনাক্ত করা হয়।
DECLARE place_ids ARRAY<STRING>; SET place_ids = ['ChIJPQOh8YVZwokRE2WsbZI4tOk', 'ChIJibtT3ohZwokR7tX0gp0nG8U', 'ChIJdfD8moVZwokRO6vxjXAtoWs', 'ChIJsdNONuFbwokRLM-yuifjb8k', 'ChIJp0gKoClawokR0txqrcaEkFc']; SELECT WITH AGGREGATION_THRESHOLD AVG(rating) as avg_rating, FROM `PROJECT_NAME.places_insights___us.places`, UNNEST(place_ids) place_id WHERE id = place_id;
কোয়েরি ফলাফল থেকে নির্দিষ্ট স্থান আইডি বাদ দিন
এছাড়াও, আপনি কোয়েরি থেকে জায়গার আইডি-এর অ্যারে বাদ দিতে পারবেন।
আপনি যে প্লেস আইডি খুঁজছেন তা প্লেস আইডি ফাইন্ডার ব্যবহার করে অথবা প্রোগ্রাম্যাটিক্যালি টেক্সট সার্চ (নতুন) অনুরোধ করতে Places API ব্যবহার করে খুঁজে পেতে পারেন।
নিম্নলিখিত উদাহরণে, কোয়েরিটি অস্ট্রেলিয়ার সিডনির ২০০০ পোস্টাল কোডে ক্যাফের সংখ্যা খুঁজে বের করে, যা excluded_cafes অ্যারেতে নেই।
এই ধরনের কোয়েরি কোনও ব্যবসার মালিকের জন্য উপযোগী হতে পারে, যিনি নিজের ব্যবসাকে
গণনা থেকে বাদ দিতে চান।
WITH excluded_cafes AS ( -- List the specific place IDs to exclude from the final count SELECT * FROM UNNEST([ 'ChIJLTcYGz-uEmsRmazk9oMnP5w', 'ChIJeWDDDNOvEmsRF8SMPUwPbhw', 'ChIJKdaKHbmvEmsRSdxq_1O05bU' ]) AS place_id ) SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `places_insights___au.places` AS places -- Perform a LEFT JOIN to identify which places are in the exclusion list LEFT JOIN excluded_cafes ON places.id = excluded_cafes.place_id WHERE -- Filter for specific place type and postal code places.primary_type = 'cafe' AND '2000' IN UNNEST(places.postal_code_names) -- Keep only the rows where the join failed (meaning the ID was NOT in the list) AND excluded_cafes.place_id IS NULL;
আগে থেকে নির্ধারিত ডেটা ভ্যালু অনুযায়ী ফিল্টার করুন
অনেক ডেটাসেট ফিল্ডে আগে থেকে নির্ধারিত ভ্যালু থাকে। যেমন:
price_levelফিল্ডে নিম্নলিখিত পূর্বনির্ধারিত ভ্যালু ব্যবহার করা যায়:PRICE_LEVEL_FREEPRICE_LEVEL_INEXPENSIVEPRICE_LEVEL_MODERATEPRICE_LEVEL_EXPENSIVEPRICE_LEVEL_VERY_EXPENSIVE
business_statusফিল্ডে নিম্নলিখিত পূর্বনির্ধারিত ভ্যালু ব্যবহার করা যায়:OPERATIONALCLOSED_TEMPORARILYCLOSED_PERMANENTLYFUTURE_OPENING
এই উদাহরণে, কোয়েরিটি নিউ ইয়র্ক সিটির এম্পায়ার স্টেট বিল্ডিংয়ের ১০০০ মিটার ব্যাসার্ধের মধ্যে
OPERATIONAL-এর business_status সহ সমস্ত ফুল বিক্রেতার সংখ্যা দেখায়:
SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places` WHERE ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000) AND business_status = "OPERATIONAL" AND 'florist' IN UNNEST(types)
কর্মসময় অনুযায়ী ফিল্টার করুন
এই উদাহরণে, শুক্রবার হ্যাপি আওয়ার্স সহ ভৌগোলিক এলাকার সব জায়গার সংখ্যা ফেরত পাঠান:
SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places`, UNNEST(regular_opening_hours_happy_hour.friday) AS friday_hours WHERE '17:00:00' BETWEEN friday_hours.start_time AND friday_hours.end_time AND ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000);
অঞ্চল অনুযায়ী ফিল্টার করুন (ঠিকানার অংশ)
আমাদের জায়গার ডেটাসেটে ঠিকানা সংক্রান্ত কম্পোনেন্টের একটি সেটও থাকে যা রাজনৈতিক সীমানার ভিত্তিতে
ফলাফল ফিল্টার করার জন্য উপযোগী। প্রতিটি ঠিকানার কম্পোনেন্ট
তাদের টেক্সট কোড নাম (NYC-তে পোস্টাল কোডের জন্য 10002) বা সমতুল্য পোস্টাল কোড আইডির জন্য
প্লেস আইডি (ChIJm5NfgIBZwokR6jLqucW0ipg) দ্বারা শনাক্ত করা হয়।
SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places` WHERE '10002' IN UNNEST(postal_code_names) -- 'ChIJm5NfgIBZwokR6jLqucW0ipg' IN UNNEST(postal_code_ids) -- same filter as above using postal code ID
EV চার্জিং কনফিগারেশন অনুযায়ী ফিল্টার করুন
এই উদাহরণে অন্তত ৮টি ইভি চার্জার আছে এমন জায়গার সংখ্যা দেখানো হয়েছে:
SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places` WHERE ev_charge_options.connector_count > 8;
এই উদাহরণে এমন জায়গার সংখ্যা গণনা করা হয়েছে যেখানে অন্তত ১০টি টেসলা চার্জার আছে যেগুলি ফাস্ট চার্জিং সাপোর্ট করে:
SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places`, UNNEST(ev_charge_options.connector_aggregation) as connectors WHERE connectors.type ='EV_CONNECTOR_TYPE_TESLA' AND connectors.max_charge_rate_kw >= 50 AND connectors.count >= 10
ডেটা স্ন্যাপশটের তারিখ অনুযায়ী ফিল্টার করুন
জায়গার ডেটাসেটে জানুয়ারি ২০২৪ পর্যন্ত প্রতি মাসের জায়গার সংখ্যা সংক্রান্ত ডেটা থাকে। কোনও নির্দিষ্ট মাসের জন্য জায়গার ডেটার স্ন্যাপশট কোয়েরি করতে, snapshot_date ফিল্ড ব্যবহার করে ফিল্টার করুন।
যেমন, নিম্নলিখিত কোয়েরি ২০২৪ সালের জানুয়ারি মাসে সার্চ করা এলাকার প্রতিটি জায়গার জন্য প্রাথমিক ধরন অনুযায়ী গ্রুপ করা ফলাফল দেখায়:
SELECT WITH AGGREGATION_THRESHOLD primary_type, COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places_historical` WHERE snapshot_date = '2024-01-01' AND ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000) GROUP BY primary_type;
গ্রুপ করা ফলাফল সারি ফিরিয়ে দিন
এখনও পর্যন্ত দেখানো কোয়েরিগুলি ফলাফলে একটি একক সারি দেখায় যাতে কোয়েরির জন্য
অ্যাগ্রিগেশন গণনা থাকে। এছাড়াও, আপনি GROUP BY অপারেটর ব্যবহার করে
গ্রুপিংয়ের মাপকাঠির ভিত্তিতে উত্তরে একাধিক সারি দেখাতে পারবেন।
যেমন, নিম্নলিখিত কোয়েরি সার্চ এলাকার প্রতিটি জায়গার প্রাথমিক ধরন অনুযায়ী গ্রুপ করা ফলাফল দেখায়:
SELECT WITH AGGREGATION_THRESHOLD primary_type, COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places` WHERE ST_DWITHIN(ST_GEOGPOINT(-73.99992071622756, 40.71818785986936), point, 1000) GROUP BY primary_type
এই কোয়েরির জন্য একটি উদাহরণ আউটপুট এই ছবিতে দেখানো হয়েছে:
এই উদাহরণে আপনি লোকেশনের একটি টেবিল নির্ধারণ করেছেন। প্রতিটি লোকেশনের জন্য, আপনি আশেপাশের রেস্তোরাঁর সংখ্যা গণনা করেন, অর্থাৎ ১০০০ মিটারের মধ্যে থাকা রেস্তোরাঁ:
WITH my_locations AS ( SELECT 'Location 1' AS name, ST_GEOGPOINT(-74.00776440888504, 40.70932825380786) AS location UNION ALL SELECT 'Location 2' AS name, ST_GEOGPOINT(-73.98257192833559, 40.750738934863215) AS location UNION ALL SELECT 'Location 3' AS name, ST_GEOGPOINT(-73.94701794263223, 40.80792954838445) AS location ) SELECT WITH AGGREGATION_THRESHOLD l.name, COUNT(*) as count FROM `PROJECT_NAME.places_insights___us.places` AS p JOIN my_locations l ON ST_DWITHIN(l.location, p.point, 1000) WHERE primary_type = "restaurant" AND business_status = "OPERATIONAL" GROUP BY l.name
এই কোয়েরির জন্য একটি উদাহরণ আউটপুট এই ছবিতে দেখানো হয়েছে:
