یک عبارت Places Insights بنویسید

برای دسترسی به داده‌های Places Insights، شما کوئری‌های SQL را در BigQuery می‌نویسید که بینش‌های تجمیعی در مورد مکان‌ها را برمی‌گردانند. نتایج از مجموعه داده‌ها برای معیارهای جستجوی مشخص شده در کوئری بازگردانده می‌شوند.

توجه: تعداد تجمع فقط در صورتی برگردانده می‌شود که تعداد ۵ یا بیشتر باشد. برای مثال، اگر معیار جستجوی شما منجر به تعداد ۱، ۲، ۳ یا ۴ شود، در این صورت تجمع نتیجه از نتیجه حذف می‌شود. توابع شمارش مکان می‌توانند هر تعداد، از جمله ۰ را برگردانند، اما حداقل مساحت جستجو را ۴۰.۰ متر در ۴۰.۰ متر (۱۶۰۰ متر ^مربع ) اعمال می‌کنند.

اصول اولیه پرس و جو

تصویر زیر قالب اولیه یک پرس و جو را نشان می‌دهد:

هر بخش از پرس و جو با جزئیات بیشتر در زیر توضیح داده شده است.

موفقیت: BigQuery همچنین مستندات و مثال‌هایی برای اجرای پرس‌وجوها روی داده‌های مکانی ارائه می‌دهد. برای اطلاعات بیشتر، به مقدمه‌ای بر تحلیل‌های مکانی مراجعه کنید.

الزامات پرس و جو

دستور SELECT در کوئری باید شامل WITH AGGREGATION_THRESHOLD باشد و مجموعه داده را مشخص کند. برای مثال:

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

در این مثال، شما FROM برای مشخص کردن مجموعه داده 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)

در این مثال، شعاع جستجو را روی ۱۰۰ متر در اطراف خط تنظیم می‌کنید.

فیلتر بر اساس فیلدهای مجموعه داده مکان

جستجوی خود را بر اساس فیلدهای تعریف شده توسط طرحواره مجموعه داده اصلاح کنید. نتایج را بر اساس فیلدهای مجموعه داده مانند place regular_opening_hours ، price_level و customer 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

کوئری بعدی نتایج مربوط به مکان‌هایی را برمی‌گرداند که حداقل هشت ایستگاه شارژ خودروهای برقی دارند:

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 در یک پرس و جو برای فیلتر کردن نتایج بر اساس نوع اصلی یک مکان استفاده کنید.

• چندین مقدار نوع از انواع تعریف شده توسط انواع مکان (Place types) به آن مرتبط می‌شوند. برای مثال، یک رستوران ممکن است انواع زیر را داشته باشد: 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;

شناسه‌های مکان خاص را فیلتر کنید

همچنین می‌توانید آرایه‌ای از شناسه‌های مکان (Place IDs) را از یک پرس‌وجو (query) حذف کنید.

شما می‌توانید با استفاده از یابنده‌ی Place ID یا با استفاده از برنامه‌نویسی Places API برای انجام یک درخواست جستجوی متن (جدید) ، Place ID های مورد نظرتان را پیدا کنید.

در مثال زیر، کوئری تعداد کافه‌هایی را در کد پستی ۲۰۰۰ سیدنی، استرالیا، که در آرایه 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

نکته: معمولاً فیلتر business_status را اضافه می‌کنید تا مطمئن شوید که تعداد کسب‌وکارهای OPERATIONAL را دریافت می‌کنید. در غیر این صورت، تعداد کسب‌وکارهای تعطیل‌شده نیز محاسبه می‌شود.

در این مثال، کوئری تعداد تمام گل‌فروشی‌هایی را که 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)
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;

این مثال تعداد مکان‌هایی را که حداقل ۱۰ شارژر تسلا با پشتیبانی از شارژ سریع دارند، شمارش می‌کند:

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 برای بازگرداندن چندین ردیف در پاسخ بر اساس معیارهای گروه‌بندی استفاده کنید.

توجه: انجام عملیات GROUP BY روی postal_code_names در GB مجاز نیست.

برای مثال، کوئری زیر نتایجی را که بر اساس نوع اصلی هر مکان در ناحیه جستجو گروه‌بندی شده‌اند، برمی‌گرداند:

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

تصویر زیر نمونه‌ای از خروجی این کوئری را نشان می‌دهد: