जगहों की अहम जानकारी से जुड़ी क्वेरी लिखना

Places Insights का डेटा ऐक्सेस करने के लिए, BigQuery में एसक्यूएल क्वेरी लिखें. इससे आपको जगहों के बारे में एग्रीगेट की गई अहम जानकारी मिलती है. क्वेरी में दी गई खोज की शर्तों के हिसाब से, डेटासेट से नतीजे दिखाए जाते हैं.

ध्यान दें: एग्रीगेशन की संख्या सिर्फ़ तब दिखाई जाती है, जब यह पांच या इससे ज़्यादा हो. उदाहरण के लिए, अगर खोज के लिए तय की गई शर्तों के हिसाब से 1, 2, 3 या 4 नतीजे मिलते हैं, तो नतीजे में एग्रीगेशन नहीं दिखाया जाता. जगह की गिनती करने वाले फ़ंक्शन कोई भी संख्या दिखा सकते हैं. इसमें 0 भी शामिल है. हालांकि, ये फ़ंक्शन कम से कम 40.0 मीटर x 40.0 मीटर (1600 मीटर²) के खोज क्षेत्र पर लागू होते हैं.

क्वेरी की बुनियादी बातें

नीचे दी गई इमेज में, क्वेरी का सामान्य फ़ॉर्मैट दिखाया गया है:

क्वेरी के हर हिस्से के बारे में ज़्यादा जानकारी यहां दी गई है.

सफलता: BigQuery, भू-स्थानिक डेटा पर क्वेरी चलाने के लिए दस्तावेज़ और उदाहरण भी उपलब्ध कराता है. ज़्यादा जानकारी के लिए, जियोस्पेशल (भौगोलिक) डेटा के विश्लेषण के बारे में जानकारी लेख पढ़ें.

क्वेरी से जुड़ी ज़रूरी शर्तें

क्वेरी में मौजूद SELECT स्टेटमेंट में WITH AGGREGATION_THRESHOLD शामिल होना चाहिए. साथ ही, डेटासेट के बारे में जानकारी दी जानी चाहिए. उदाहरण के लिए:

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `PROJECT_NAME.places_insights___us.places`

इस उदाहरण में, अमेरिका के लिए places_insights___us.places डेटासेट के बारे में बताने के लिए, FROM का इस्तेमाल किया गया है.

प्रोजेक्ट का नाम बताएं (ज़रूरी नहीं)

क्वेरी में अपने प्रोजेक्ट का नाम शामिल करना ज़रूरी नहीं है. अगर आपने प्रोजेक्ट का नाम नहीं डाला है, तो आपकी क्वेरी डिफ़ॉल्ट रूप से चालू प्रोजेक्ट पर सेट हो जाएगी.

अगर आपने अलग-अलग प्रोजेक्ट में एक ही नाम वाले डेटासेट लिंक किए हैं या आपको चालू प्रोजेक्ट के बाहर की किसी टेबल पर क्वेरी करनी है, तो आपको अपने प्रोजेक्ट का नाम शामिल करना पड़ सकता है.

उदाहरण के लिए, [project name].[dataset name].places.

एग्रीगेशन फ़ंक्शन तय करना

नीचे दिए गए उदाहरण में, BigQuery के साथ काम करने वाले एग्रीगेशन फ़ंक्शन दिखाए गए हैं. इस क्वेरी में, न्यूयॉर्क शहर में एंपायर स्टेट बिल्डिंग के 1,000 मीटर के दायरे में मौजूद सभी जगहों की रेटिंग को इकट्ठा किया जाता है, ताकि रेटिंग के आंकड़े तैयार किए जा सकें:

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)

इस उदाहरण में, आपकी क्वेरी में न्यूयॉर्क शहर की एम्पायर स्टेट बिल्डिंग को टारगेट करने की पाबंदी के बारे में बताया गया है. साथ ही, इसके लिए 1,000 मीटर का दायरा तय किया गया है.

ध्यान दें: किसी जगह को नतीजों में सिर्फ़ तब शामिल किया जाता है, जब उस जगह का सेंटर पॉइंट, जगह की पाबंदी के दायरे में आता हो. अगर किसी जगह, जैसे कि पार्क का सेंटर पॉइंट, खोज के नतीजों में शामिल नहीं की गई जगह के बाहर है, तो उसे नतीजों में शामिल नहीं किया जाएगा.

खोज के लिए, पॉलीगॉन का इस्तेमाल किया जा सकता है. पॉलीगॉन का इस्तेमाल करते समय, पॉलीगॉन के पॉइंट एक क्लोज़्ड लूप तय करने चाहिए. साथ ही, पॉलीगॉन का पहला पॉइंट, आखिरी पॉइंट के जैसा होना चाहिए:

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)

इस उदाहरण में, लाइन के चारों ओर खोज का दायरा 100 मीटर पर सेट किया गया है.

जगह के डेटासेट फ़ील्ड के हिसाब से फ़िल्टर करना

डेटासेट स्कीमा में तय किए गए फ़ील्ड के आधार पर, अपनी खोज को बेहतर बनाएं. डेटासेट फ़ील्ड के आधार पर नतीजों को फ़िल्टर करें. जैसे, जगह regular_opening_hours, price_level, और ग्राहक rating.

अपने देश के हिसाब से, डेटासेट के स्कीमा में तय किए गए किसी भी फ़ील्ड का रेफ़रंस दें. हर देश के लिए डेटासेट स्कीमा में दो हिस्से होते हैं:

• कोर स्कीमा, जो सभी देशों के डेटासेट के लिए एक जैसा होता है.
• देश के हिसाब से स्कीमा जो उस देश के हिसाब से स्कीमा कॉम्पोनेंट तय करता है.

उदाहरण के लिए, आपकी क्वेरी में WHERE क्लॉज़ शामिल हो सकता है. यह क्लॉज़, क्वेरी के लिए फ़िल्टर करने की शर्तों को तय करता है. यहां दिए गए उदाहरण में, true पर सेट किए गए tourist_attraction टाइप की जगहों के लिए एग्रीगेशन डेटा दिखाया गया है. इन जगहों का business_status OPERATIONAL है और rating 4.0 से ज़्यादा या इसके बराबर है:allows_dogs

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;

जगह के मुख्य टाइप और जगह के टाइप के हिसाब से फ़िल्टर करना

डेटासेट में मौजूद हर जगह के लिए, ये चीज़ें हो सकती हैं:

• एक प्राइमरी टाइप, जो जगह के टाइप के हिसाब से तय किए गए टाइप में से किसी एक से जुड़ा हो. उदाहरण के लिए, प्राइमरी टाइप 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;

पहले से तय की गई डेटा वैल्यू के आधार पर फ़िल्टर करना

डेटासेट के कई फ़ील्ड के लिए, पहले से तय की गई वैल्यू होती हैं. उदाहरण के लिए

• 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

ध्यान दें: आम तौर पर, business_status फ़िल्टर का इस्तेमाल यह पक्का करने के लिए किया जाता है कि आपको OPERATIONAL कारोबारों की संख्या मिले. ऐसा न करने पर, गिनती में बंद हो चुके कारोबार भी शामिल हो जाएंगे.

इस उदाहरण में, क्वेरी से उन सभी फ़्लोरिस्ट की संख्या मिलती है जिनका business_status, न्यूयॉर्क शहर में एंपायर स्टेट बिल्डिंग के 1,000 मीटर के दायरे में 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)
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);

क्षेत्र (पते के कॉम्पोनेंट) के हिसाब से फ़िल्टर करना

हमारे जगहों के डेटासेट में, पते के कॉम्पोनेंट का एक सेट भी होता है. यह राजनीतिक सीमाओं के आधार पर नतीजों को फ़िल्टर करने के लिए काम आता है. हर पते के कॉम्पोनेंट की पहचान, उसके टेक्स्ट कोड के नाम (न्यूयॉर्क शहर के पिन कोड के लिए 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;

इस उदाहरण में, उन जगहों की संख्या का पता लगाया गया है जहां कम से कम 10 Tesla चार्जर हैं. ये चार्जर, फ़ास्ट चार्जिंग की सुविधा के साथ काम करते हैं:

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 ऑपरेटर का भी इस्तेमाल किया जा सकता है.

ध्यान दें: ग्रेट ब्रिटेन में postal_code_names पर 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

इस क्वेरी के जवाब का उदाहरण यहां दिया गया है:

इस उदाहरण में, जगहों की एक टेबल तय की गई है. इसके बाद, हर जगह के लिए आस-पास मौजूद रेस्टोरेंट की संख्या का हिसाब लगाया जाता है. इसका मतलब है कि 1,000 मीटर के दायरे में मौजूद रेस्टोरेंट की संख्या का हिसाब लगाया जाता है:

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

इस क्वेरी के जवाब का उदाहरण यहां दिया गया है: