প্লেসেস ইনসাইটস ডেটা সরাসরি অ্যাক্সেস করতে, আপনাকে BigQuery-তে SQL কোয়েরি লিখতে হবে যা স্থান সম্পর্কিত সমষ্টিগত তথ্য প্রদান করে। কোয়েরিতে নির্দিষ্ট করা সার্চ ক্রাইটেরিয়ার জন্য ডেটাসেট থেকে ফলাফল ফেরত দেওয়া হয়।
আপনার যদি ৫-এর কম সংখ্যা জানার প্রয়োজন হয়, তাহলে এর পরিবর্তে প্লেসেস কাউন্ট ফাংশনগুলো ব্যবহার করার কথা বিবেচনা করুন। এই ফাংশনগুলো ০ সহ যেকোনো সংখ্যা ফেরত দিতে পারে, তবে এগুলো সর্বনিম্ন ৪০.০ মিটার বাই ৪০.০ মিটার (১৬০০ মি² ) অনুসন্ধান এলাকা নির্ধারণ করে। কখন সরাসরি কোয়েরি করতে হবে এবং কখন ফাংশন ব্যবহার করতে হবে সে সম্পর্কে আরও জানুন ।
কোয়েরির মৌলিক বিষয়
নিচের চিত্রটিতে একটি কোয়েরির মৌলিক বিন্যাস দেখানো হয়েছে:
কোয়েরিটির প্রতিটি অংশ নিচে আরও বিস্তারিতভাবে বর্ণনা করা হয়েছে।
প্রয়োজনীয়তা জিজ্ঞাসা করুন
সরাসরি ডেটাসেটের উপর করা 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"
অবস্থানগত সীমাবদ্ধতা নির্দিষ্ট করুন
আপনি যদি কোনো অবস্থানগত সীমাবদ্ধতা উল্লেখ না করেন, তাহলে ডেটা অ্যাগ্রিগেশন সম্পূর্ণ ডেটাসেটের উপর প্রয়োগ করা হয়। সাধারণত একটি নির্দিষ্ট এলাকা অনুসন্ধান করার জন্য অবস্থানগত সীমাবদ্ধতা উল্লেখ করা হয়। এই উদাহরণ কোয়েরিটিতে নিউ ইয়র্ক সিটির এম্পায়ার স্টেট বিল্ডিংকে কেন্দ্র করে ১০০০ মিটার ব্যাসার্ধের একটি টার্গেট সীমাবদ্ধতা নির্দিষ্ট করা হয়েছে।
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)
আপনি অনুসন্ধান এলাকা নির্দিষ্ট করতে একটি বহুভুজ ব্যবহার করতে পারেন। বহুভুজ ব্যবহার করার সময়, বহুভুজের বিন্দুগুলোকে অবশ্যই একটি বদ্ধ লুপ তৈরি করতে হবে, যেখানে বহুভুজের প্রথম বিন্দুটি শেষ বিন্দুর সমান হবে:
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 4.0 বা তার বেশি এবং 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
পরবর্তী কোয়েরিটি এমন স্থানগুলির ফলাফল ফেরত দেয় যেখানে কমপক্ষে আটটি ইভি চার্জিং স্টেশন রয়েছে:
SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count FROM `PROJECT_NAME.places_insights___us.places` WHERE ev_charge_options.connector_count > 8;
স্থানের প্রাথমিক প্রকার এবং স্থানের প্রকারের উপর ফিল্টার করুন
ডেটাসেটের প্রতিটি স্থানে থাকতে পারে:
Place types দ্বারা সংজ্ঞায়িত প্রকারগুলো থেকে এর সাথে যুক্ত একটিমাত্র প্রাথমিক প্রকার । উদাহরণস্বরূপ, প্রাথমিক প্রকারটি হতে পারে
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) ব্যবহার করে একটি টেক্সট সার্চ (নতুন) অনুরোধ (Text Search (New) request) সম্পাদন করে আপনার কাঙ্ক্ষিত প্লেস আইডিগুলো খুঁজে পেতে পারেন।
নীচের উদাহরণে, কোয়েরিটি অস্ট্রেলিয়ার সিডনির ২০০০ পোস্টাল কোডে অবস্থিত সেইসব ক্যাফের সংখ্যা খুঁজে বের করে, যেগুলো 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_FREE -
PRICE_LEVEL_INEXPENSIVE -
PRICE_LEVEL_MODERATE -
PRICE_LEVEL_EXPENSIVE -
PRICE_LEVEL_VERY_EXPENSIVE
-
business_statusফিল্ডটি নিম্নলিখিত পূর্বনির্ধারিত মানগুলি সমর্থন করে:-
OPERATIONAL -
CLOSED_TEMPORARILY -
CLOSED_PERMANENTLY -
FUTURE_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
ইভি চার্জিং অনুসারে ফিল্টার করুন
এই উদাহরণটি কমপক্ষে ৮টি ইভি চার্জার রয়েছে এমন স্থানগুলির সংখ্যা গণনা করে দেখায়:
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
ফলাফল গ্রুপ ফেরত দিন
এখন পর্যন্ত দেখানো কোয়েরিগুলো ফলাফলে একটিমাত্র সারি ফেরত দেয়, যেটিতে কোয়েরিটির অ্যাগ্রিগেশন সংখ্যা থাকে। এছাড়াও, গ্রুপিং শর্তের উপর ভিত্তি করে রেসপন্সে একাধিক সারি ফেরত পেতে আপনি 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` JOIN my_locations l ON ST_DWITHIN(l.location, p.point, 1000) WHERE primary_type = "restaurant" AND business_status = "OPERATIONAL" GROUP BY l.name
এই ছবিতে এই কোয়েরিটির একটি উদাহরণ আউটপুট দেখানো হয়েছে:
