PLACES_COUNT تابع

SELECT `places_insights___us___sample.PLACES_COUNT`(
  JSON_OBJECT(
      'geography', ST_GEOGPOINT(-73.9857, 40.7484), -- Empire State Building
      'geography_radius', 1000 -- Radius in meters
      )
) as count;

SELECT `places_insights___us___sample.PLACES_COUNT`(
  JSON_OBJECT(
      'geography', ST_GEOGPOINT(-73.9857, 40.7484), -- Empire State Building
      'geography_radius', 1000, -- Radius in meters
      'types', ["restaurant"],
      'min_rating', 3,
      'price_level', ['PRICE_LEVEL_INEXPENSIVE', 'PRICE_LEVEL_MODERATE'],
      'business_status', ['OPERATIONAL'],
      'allows_dogs', TRUE
      )
) as count;

SELECT `places_insights___us___sample.PLACES_COUNT`(
  JSON_OBJECT(
      'geography', ST_GEOGPOINT(-73.9857, 40.7484), -- Empire State Building
      'geography_radius', 500, -- Radius in meters
      'types', ["restaurant"],
      'min_rating', 4.0,
      'free_parking_lot', TRUE,
      'good_for_watching_sports', TRUE
      )
) as count;

DECLARE geo GEOGRAPHY;
SET geo = ST_GEOGFROMTEXT('''POLYGON((-73.985708 40.75773,-73.993324 40.750298,
                                      -73.9857 40.7484,-73.9785 40.7575,
                                      -73.985708 40.75773))''');  -- NYC viewport

SELECT `places_insights___us___sample.PLACES_COUNT`(
  JSON_OBJECT(
      'geography',geo, -- viewport 
      'types', ["restaurant"],
      'min_rating', 1.0,
      'max_rating', 4.5,
      'min_user_rating_count', 1,
      'max_user_rating_count', 10000,
      'price_level', ['PRICE_LEVEL_INEXPENSIVE', 'PRICE_LEVEL_MODERATE'],
      'business_status', ['OPERATIONAL'],
      'allows_dogs', TRUE
      )
) as count;

DECLARE geo GEOGRAPHY;
SET geo = ST_GEOGFROMTEXT('LINESTRING(-73.98903537033028 40.73655649223003,-73.93580216278471 40.80955538843361)');  -- NYC line

SELECT `places_insights___us___sample.PLACES_COUNT`(
  JSON_OBJECT(
      'geography',geo, -- line
      'geography_radius', 100, -- Radius around line
      'types', ["restaurant"],
      'min_rating', 1.0,
      'max_rating', 4.5,
      'min_user_rating_count', 1,
      'max_user_rating_count', 10000,
      'price_level', ['PRICE_LEVEL_INEXPENSIVE', 'PRICE_LEVEL_MODERATE'],
      'business_status', ['OPERATIONAL'],
      'allows_dogs', TRUE
      )
) as count;

-- Create a temp table to hold the results.
CREATE TEMP TABLE results (type STRING, count INT64);

-- Create a loop that calls PLACES_COUNT for each price level.
FOR types IN (SELECT type FROM UNNEST(["PRICE_LEVEL_INEXPENSIVE", "PRICE_LEVEL_MODERATE", "PRICE_LEVEL_EXPENSIVE", "PRICE_LEVEL_VERY_EXPENSIVE"]) as type)
DO
  INSERT INTO results VALUES (types.type, `places_insights___us___sample.PLACES_COUNT`(
  JSON_OBJECT(
      'types', ["restaurant"],
      'geography', ST_GEOGPOINT(-73.9857, 40.7484), -- Empire State Building
      'geography_radius', 1000, -- Radius in meters
      'business_status', ['OPERATIONAL'],
      'price_level', [types.type]
  )));
END FOR;

-- Query the table of results.
SELECT * FROM results;

SELECT "PRICE_LEVEL_INEXPENSIVE" as price_level, `places_insights___us___sample.PLACES_COUNT`(
  JSON_OBJECT(
      'types', ["restaurant"],
      'geography', ST_GEOGPOINT(-73.9857, 40.7484), -- Empire State Building
      'geography_radius', 1000, -- Radius in meters
      'business_status', ['OPERATIONAL'],
      'price_level', ['PRICE_LEVEL_INEXPENSIVE']
  )
) as count

UNION ALL

SELECT "PRICE_LEVEL_MODERATE" as price_level, `places_insights___us___sample.PLACES_COUNT`(
  JSON_OBJECT(
      'types', ["restaurant"],
      'geography', ST_GEOGPOINT(-73.9857, 40.7484), -- Empire State Building
      'geography_radius', 1000, -- Radius in meters
      'business_status', ['OPERATIONAL'],
      'price_level', ['PRICE_LEVEL_MODERATE']
  )
) as count

UNION ALL

SELECT "PRICE_LEVEL_EXPENSIVE" as price_level, `places_insights___us___sample.PLACES_COUNT`(
  JSON_OBJECT(
      'types', ["restaurant"],
      'geography', ST_GEOGPOINT(-73.9857, 40.7484), -- Empire State Building
      'geography_radius', 1000, -- Radius in meters
      'business_status', ['OPERATIONAL'],
      'price_level', ['PRICE_LEVEL_EXPENSIVE']
  )
) as count

UNION ALL

SELECT "PRICE_LEVEL_VERY_EXPENSIVE" as price_level, `places_insights___us___sample.PLACES_COUNT`(
  JSON_OBJECT(
      'types', ["restaurant"],
      'geography', ST_GEOGPOINT(-73.9857, 40.7484), -- Empire State Building
      'geography_radius', 1000, -- Radius in meters
      'business_status', ['OPERATIONAL'],
      'price_level', ['PRICE_LEVEL_VERY_EXPENSIVE']
  )
) as count