独自のインデックスを作成する方法: Googleスプレッドシートを活用した簡単ガイド

本記事では、地域の監視に役立つ独自のインデックスを作成する方法をご紹介します。ビデオによる説明は以下よりご覧いただけます、

メール登録

はじめに、Googleアカウントを用意してください。アカウントを作成またはログインしたら、登録用ウェブサイトにアクセスしサインアップしてください

図1: メール登録フォームを送信

サインアップが完了すると、Googleスプレッドシートへのリンクが提供されます。このスプレッドシートは、インデックスの設定に使用します。

Googleスプレッドシートの編集

スプレッドシートをコピーする

まず、Googleスプレッドシートをコピーします。リンクにアクセスして、「コピーを作成」ボタンをクリックしてください。

図2: 「コピーを作成」ボタンをクリック

シートから地域を抽出する

次に、QUERY関数を使用してシートからデータを抽出します。この関数を使用すると、スプレッドシート内のデータをフィルタリングおよびソートできます。

図3: QUERY関数を使用してインデックス用のデータを抽出(例: 「Australia」と「Queensland」でフィルタリング)

例えば、以下のようなQUERY関数を使用します:

SELECT C, D WHERE B = 'Australia' AND C LIKE '%Queensland%'

この場合、以下の結果が得られます:

図4: QUERY関数の結果

スプレッドシートを公開する

ウェブに公開

スプレッドシートをCSV形式でウェブに公開します。

図5: ウェブに公開。形式はCSV

公開後、以下のようなURLが提供されます:

https://docs.google.com/spreadsheets/d/e/2PACX-xxxxxxxx/pub?gid=16********&single=true&output=csv

ここで:

  • 2PACX-xxxxxxxx はスプレッドシートID
  • 16******** はシートIDです。
図6: 共有可能なURLが提供されます

独自のインデックスURLを作成する

最後に、以下の形式に基づいて独自のインデックスURLを作成します:

https://otani.co/crops/spring-wheat/NDVI/<インデックス名>/?sid=<スプレッドシートID>&gid=<シートID>

例えば、Spring Wheat / NDVIインデックスの名前が**「QLD, AU」**で、スプレッドシートIDが2PACX-xxxxxxxx、シートIDが16********の場合、URLは次のようになります

https://otani.co/crops/spring-wheat/NDVI/QLD,AU/?sid=2PACX-xxxxxxxx&gid=16********  

これにより、同じデータに異なる名前でアクセスできます(例: QLD, AUまたはQueensland, Australia)。

結論

以上で、Googleスプレッドシートを使った独自のインデックス作成方法の説明を終わります。このガイドが役に立てば幸いです。ご不明点があれば、お気軽にお問い合わせください

How to Build Your Own Index (Dashboard): A Step-by-Step Guide Using Google Spreadsheets

In this post, we will guide you through creating your own index to monitor regions of interest effectively. A link to youtube video is provided below.

Email registration

To get started, you need a Google account. Once you’ve created or logged into your account, visit the registration website and sign up for the index.

Figure 1: Submit the email registration form

After signing up, you will receive a link to a Google Spreadsheet. This spreadsheet will serve as your configuration.

Edit a google spreadsheet

Copy the Google Spreadsheet

First, make a copy of the Google Spreadsheet. Visit the provided link and click the “Make a copy” button.

Figure 2 : Click “Make a copy” button

Extract Regions from the Sheet

Next, extract regions from the sheet using the QUERY function. The QUERY function enables you to filter and sort data in the spreadsheet.

Figure 3: Extract data for your index using the QUERY function (e.g., filtering data for “Australia” and “Queensland”)

For example, a QUERY function like this:

SELECT C, D WHERE B = 'Australia' AND C LIKE '%Queensland%'

might return:

Figure 4: Result of the QUERY function

Publishing Your Spreadsheet

Publish to the Web

Publish your spreadsheet to the web in CSV format.

Figure 5: Publish to the web. Format: CSV

Once published, you will receive a URL like this:

https://docs.google.com/spreadsheets/d/e/2PACX-xxxxxxxx/pub?gid=16********&single=true&output=csv

Here:

  • 2PACX-xxxxxxxx is the spreadsheet ID.
  • 16******** is the sheet ID.
Figure 6: Sharable URL provided

Developing Your Own Index URL

Finally, create a URL for your custom index using the following format:

https://otani.co/crops/spring-wheat/NDVI/<your own index name>/?sid=<spreadsheet id>&gid=<sheet id>

For example, if your Spring Wheat / NDVI index is named “QLD, AU“, with the spreadsheet ID 2PACX-xxxxxxxx and sheet ID 16********, the URL would be:

https://otani.co/crops/spring-wheat/NDVI/QLD, AU/?sid=2PACX-xxxxxxxx&gid=16********

This allows you to access the same data with different names, such as QLD, AU and Queensland, Australia.

Conclusion

That’s it! You’ve successfully built your own index using a Google Spreadsheet. We hope this guide has been helpful. For any questions, please feel free to contact us.

Discover the Enchantment of Christchurch: New Zealand’s Garden City

Discover the Enchantments of Christchurch

Today we are introducing, Christchurch, the Garden City of New Zealand, where lush greenery meets urban charm. This vibrant city, with its natural beauty and rich history, is ready to enchant you at every turn. From iconic landmarks to tranquil parks, Christchurch beckons you to discover its unique allure.

A Tapestry of History and Modernity

Christchurch is a city steeped in history and brimming with character. Founded in the early 19th century by English settlers, it quickly became a hub of commerce and culture on New Zealand’s South Island. Today, remnants of its colonial past mingle seamlessly with contemporary architecture, creating a unique tapestry of old and new. From the iconic Christchurch Cathedral to the bustling streets of Cathedral Square, the city invites you to step back in time and explore its storied past. Wander through heritage buildings, where each brick and stone tells a story of resilience and rebirth.

Nature’s Embrace: Gardens and Parks in Christchurch

Christchurch is renowned for its expansive parks and gardens, earning its moniker as the Garden City. The Botanic Gardens, established in 1863, is a verdant sanctuary in the heart of the city. Here, you can meander through themed gardens, marvel at exotic plant species, and relax by serene water features. The Avon River, winding gracefully through the city, offers picturesque settings for a leisurely punt ride or a peaceful riverside walk. Hagley Park, another gem, is perfect for picnics, sports, and enjoying the seasonal blooms that transform the landscape year-round.

Urban Vibrancy and Innovation

In the wake of the 2011 earthquake, Christchurch has risen with renewed vigor, showcasing innovation and creativity. The Re:START Mall, initially a symbol of resilience, has evolved into a bustling hub where repurposed shipping containers house trendy shops and cafes. The Christchurch Arts Centre, a neo-Gothic masterpiece, now serves as a thriving cultural venue, hosting exhibitions, performances, and markets that celebrate local talent and creativity. The Riverside Market, a food lover’s paradise, offers a culinary journey through fresh, local produce and gourmet delights.

A Cultural Kaleidoscope

Experience the vibrant tapestry of Māori culture that weaves through the fabric of Christchurch. From the rhythmic beats of the haka to the intricate carvings adorning local marae, the city is alive with the spirit of the indigenous people of New Zealand. Explore the depths of Māori heritage at cultural centers and museums, where traditional performances and exhibitions offer a glimpse into ancient customs and traditions. Embrace the warmth of manaakitanga (hospitality) as you immerse yourself in the traditions and stories passed down through generations. In Christchurch, Māori culture is not just a part of history—it’s a living, breathing force that continues to shape and enrich the city’s identity.

The Flavors of Canterbury

Raise a glass to the exquisite wines of Canterbury, where sun-drenched vineyards and cool ocean breezes combine to produce some of New Zealand’s most acclaimed varietals. Explore the picturesque landscapes of Waipara Valley, home to world-class Pinot Noir, Riesling, and Chardonnay. Wander through the rows of grapevines at family-owned wineries, where passionate winemakers craft each bottle with care and precision. Indulge in cellar door tastings, where every sip tells a story of terroir and tradition. Whether you’re a seasoned oenophile or simply seeking to expand your palate, Canterbury’s wineries invite you on a journey of discovery, one glass at a time.

Farewell, But Not Goodbye

As our journey through Christchurch comes to a close, we hope you’ve been inspired by the beauty, culture, and spirit of this remarkable city. From its lush gardens to its rich history, from the warmth of Māori hospitality to the flavors of its world-class wines, Christchurch offers something truly special for every traveler. As you bid farewell to this enchanting destination, remember that the door is always open for your return. Until we meet again, may your memories of Christchurch be as vibrant and unforgettable as the experiences you’ve shared.

Exciting New Features for Shared Maps!

We’re thrilled to announce significant updates to our shared map functionality! These enhancements provide greater control over data selection, improved visualization of location information, and convenient download capabilities.

Enhanced Data Selection:

Previously, viewing your entire dataset at once on the map wasn’t possible. This update empowers you to select specific data points for display. You can choose to display a single data point, multiple points, or your entire dataset. Simply click the eye icon next to the desired data points to make your selection.

Clearer Location Data Visualization:

Previously, even if latitude and longitude data existed within your dataset, it wasn’t readily viewable as a grid. We’ve addressed this issue! For CSV map data formats that include latitude and longitude columns, these coordinates will now be displayed directly within the grid. For GeoJSON data formats, a dedicated column named “_geojson” will be added to the grid. This column stores GeoJSON features as strings within each row.

Streamlined Data Download:

The most significant update is the introduction of a dedicated data download button. Clicking this button initiates the process of downloading all data utilized within the map. The downloaded file format will be CSV with UTF-8 encoding.

***

To help you explore these new features, we’ve created a helpful video tutorial available on YouTube.

Additionally, below is an example of a shared map showcasing wineries in Waiheke Island, New Zealand (courtesy of Open Street Map) alongside daily land surface temperature data (courtesy of JAXA) for March (Please note that the unit for the temperature is not Celsius but Kelvin).

We believe these updates will significantly enhance your shared map experience!

共有マップの機能が大幅に強化されました!

共有マップ機能の大変革を皆様にお知らせできることを大変うれしく思います! これらの強化により、データ選択の自由度が向上し、位置情報の視覚化が改善され、便利なダウンロード機能が搭載されました。

データ選択機能の強化:

従来の共有マップでは、データセット全体を一度にマップ上に表示することはできませんでした。しかし今回のアップデートにより、特定のデータポイントを選択して表示することが可能になりました。1つのデータポイント、複数のデータポイント、またはデータセット全体を選択することができます。選択したいデータポイントの横にある目アイコンをクリックするだけで簡単に選択できます。

位置情報の表示機能の強化:

従来は、データセットに緯度と経度のデータが含まれていても、グリッド形式で表示することはできませんでした。しかし今回のアップデートにより、この問題が解決されました。CSV形式のマップデータで、緯度と経度の列が含まれている場合、これらの座標はグリッド内に直接表示されます。GeoJSON形式のデータの場合、グリッドに”_geojson”という名前の列が追加されます。この列には、各行にGeoJSONフィーチャーが文字列として格納されます。

データダウンロード機能の追加:

今回のアップデートで最も重要なのは、データダウンロードボタンの追加です。このボタンをクリックすると、マップで使用されているすべてのデータをダウンロードするプロセスが開始されます。ダウンロードされたファイル形式は、UTF-8エンコードされたCSV形式になります。

これらの新機能をより詳しく理解するために、YouTubeにチュートリアル動画を公開しました。また、以下に共有マップの例を示します。この例では、ニュージーランドの有名なブドウ園と、JAXA提供の3月の地表温度データを表示しています。

これらのアップデートにより、共有マップの使い勝手が大幅に向上すると確信しています。ぜひお試しください!

O2 Launches to Simplify Geospatial Data Visualization and Sharing

For the Analysis of Time-Spatial Data & Satellite Data

Yokohama, Japan – April 2, 2024 – O2, a new service that simplifies the visualization and sharing of geospatial data, has officially launched. It is designed to meet the needs of a wide range of users, from GIS professionals to business users and students.

Key Features

  • Easy-to-use interface – O2’s intuitive interface makes it easy for users to upload, visualize, and share geospatial data, even without prior GIS experience.
  • Wide range of data support – O2 supports a wide range of geospatial data formats, GeoJSON, CSV and GeoTiff (raster and vector data).
  • Seamless sharing – O2 makes it easy to share geospatial data with others via links or embeds.
  • Satellite data – O2 offers access to JAXA Earth API and NASA Earth Observations.

O2 Images

O2 Logo
O2 Top
Satellite data (JAXA Daytime Daily Land Surface Temperature 22/March/2024 around Yokohama)
Japan Land Price Transition from 1983 to 2023

Shared map: https://otn.pt/s/ER8XsV5D

Blog : https://otani.co/o2/kanto-land-price-transition-1983-2023/

YouTube:  https://www.youtube.com/watch?v=MLMgP81zI3A

For more information about O2, please visit: 

O2

About Otani & Co., Inc.

Otani & Co., Inc. is a geospatial data science company that provides a variety of services, including data visualization, analysis, and consulting. The company was founded in 2010 and is headquartered in Yokohama, Japan. The previously developed software “GEEO” that estimates property and land values with statistical algorithms and open data was awarded both The Encouragement Award for Open Data Application by the Minister for Internal Affairs and Communications and The Good Design Award, one of the prestigious awards for design in Japan.  

A digital CV of Yuichiro Otani, a founder of Otani & Co., Inc., can be found  in the link below:

Brisbane Cloud Cover in Dec 2023

This map visualizes cloud fraction around Brisbane throughout December 2023. White areas represent high cloud cover (1.00), while blue areas indicate thinner clouds (closer to 0.00). Refer to the legend for the exact color scale.

While some thinner cloud cover appeared intermittently, the month generally experienced significant cloudiness. Data was unavailable on the 2nd, 7th, 12th, 19th, 24th, and 31st. Only 7 days had completely clear skies: December 6th, 8th, 15th, 16th, 23rd, 28th, and 29th.


Focusing on the area closest to Brisbane City (north of the Story Bridge), we see the maximum cloud cover (1.00) occurred on December 1st, 14th, 15th, 17th, 21st, 22nd, 25th, and 30th. The lowest cloud cover (0.01) was recorded on December 11th.


The data used for this map comes from NASA Earth Observations. It was created with O2, a platform where you can load various satellite data.


You can create an account directly on O2 to access the data and visualizations.

Satellite Data is Ready To Use

As mentioned on the previous post, the new data is available now.

You will access to the data from JAXA Earth API and NASA Earth Obseervation, and the details are written here. On O2, you will retrieve the data and create your own map. If you are interested in using the API (HTTP) from your codes, please feel free to contact us. For all the pandas users, two file formats, geojson and csv, are available.

The map below illustrates the Cloud Fraction around Brisbane Australia on December 2023. The data source is NASA Earth Observations.

You can create an account directly on O2 to access the data and visualizations.

About Publishing New API

The new API that is available inside O2 is going to be released soon!

At the time, you will retrieve the data from the two data sources, JAXA Earth API and NASA NEO. You will be notified via email and the blog article will be posted when it becomes public. The new blog post will contain the details of the API and a link to help. Plus, the two formats, geojson and csv, will be available when you request the data to the API. Using Python (pandas and geopandas), it will be easy to directly read the data from the urls.

In O2, you can use the data in two ways, Point (geojson has two properties, latitude and longitude ) and Polygon as you can click both of them on this map (the land surface temperature on January around Japan).

The data comes from JAXA Earth API, and the map was created using O2 to visualize this data.

You can create an account directly on O2 to access the data and visualizations.

Sea Surface Temperature around New Zealand (2023)

This map purposes to illustrate the sea surface temperature around New Zealand in 2023.

MonthnMin25%50%Mean75%MaxSD
Jan4811913.5817.4719.8019.1620.6622.211.89
Feb4811914.5918.2420.3219.6421.0522.181.71
Mar4811913.9117.2019.4818.9720.6721.991.95
Apr4811911.7215.3918.0717.5119.5821.092.38
May4811910.9714.8617.1516.4518.3620.052.33
Jun481199.5813.7715.9715.1816.9618.992.28
Jul481198.3512.7214.7313.9915.6817.642.25
Aug481197.8612.0513.9013.2114.8716.692.23
Sep481198.3012.3113.9513.3615.0416.742.24
Oct481198.4512.3914.1613.6315.4417.032.24
Nov4811910.0613.7015.4514.9216.4618.422.04
Dec4811910.9314.9316.8316.4518.2820.202.25
SST around New Zealand

Although its fluctuation decreases, the mean of the sea surface temperature around New Zealand seems to gradually increase as the summer arrives in the southern hemisphere. This trend can be seen in other statistics as well.

The data used comes from SST and SST Anomaly, NOAA Global Coral Bleaching Monitoring, 5km, V.3.1, Monthly, 1985-Present. This map is created with O2 where you can load the various satellite data. This sea surface temperature data will be available soon on O2.

You can create an account directly on O2 to access the data and visualizations.