Solved: Gemini Function Calls - BigQuery + GEOSpatial data

alont · 03-11-2025 03:46 AM

Hi,

I have a dataset that includes GEO data (points, polygons, multipolygons and such).
Has anyone tried to create a simple app that will calculate GEO based calculations using the built in bigquery api?
it seems to refuse to create an SQL query that will use GEOFunctions...

What's the best practice to create such a simple text-To-GEOQuery app? (i started with the SQL-Talk demo)

Thank you!

nikacalupas

Hi alont,

Welcome to the Google Cloud Community!

Here are some best practices for creating simple text-To-GEOQuery app :

By understanding BigQuery's Geospatial Functions, you'll discover that it offers a range of tools to assist with GEO-based calculations, including functions for creating, parsing, formatting, transforming, accessing, evaluating, measuring, and clustering geospatial data.
Make sure your geospatial data is properly loaded into BigQuery. Supported formats include Well-Known Text (WKT), Well-Known Binary (WKB), GeoJSON, and GeoParquet. For instance, you can load a CSV file with longitude and latitude values and use the ST_GEOGPOINT function to convert them into GEOGRAPHY data. For more information on loading geospatial data, you may refer to this guide.
After loading your data, you can begin creating geospatial queries using BigQuery's SQL syntax. For instance, you can use functions like ST_DISTANCE to calculate the distance between two points or ST_INTERSECTS to determine if two geometries intersect. You may refer to this documentation for examples of Geospatial queries.
To build a text-to-GEOQuery app, start by adapting a Text-to-SQL framework like Text2SQL to handle BigQuery’s geospatial functions (e.g., ST_DISTANCE, ST_INTERSECTS). Fine-tune the system with labeled examples of geospatial queries, such as "Find locations within 5 miles" or "Get polygons that intersect," to help the model generate the correct SQL functions. You can also leverage pre-built NLP libraries like Google's tools, spaCy, or Transformers to convert natural language into structured SQL queries that include geospatial operations.
To execute the generated SQL query securely, use the BigQuery API and handle potential errors by catching API exceptions and providing informative feedback to the user, such as "Invalid query syntax" or "Data type mismatch." Additionally, ensure security by using prepared statements, which bind user inputs securely to the SQL query, preventing SQL injection and safeguarding the app from malicious attacks.
If you're handling location-based queries, consider using Geohashes. Geohashes convert latitude and longitude pairs into a single string, simplifying the storage and querying of location data.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.

View solution in original post

nikacalupas

Hi alont,

Welcome to the Google Cloud Community!

Here are some best practices for creating simple text-To-GEOQuery app :

By understanding BigQuery's Geospatial Functions, you'll discover that it offers a range of tools to assist with GEO-based calculations, including functions for creating, parsing, formatting, transforming, accessing, evaluating, measuring, and clustering geospatial data.
Make sure your geospatial data is properly loaded into BigQuery. Supported formats include Well-Known Text (WKT), Well-Known Binary (WKB), GeoJSON, and GeoParquet. For instance, you can load a CSV file with longitude and latitude values and use the ST_GEOGPOINT function to convert them into GEOGRAPHY data. For more information on loading geospatial data, you may refer to this guide.
After loading your data, you can begin creating geospatial queries using BigQuery's SQL syntax. For instance, you can use functions like ST_DISTANCE to calculate the distance between two points or ST_INTERSECTS to determine if two geometries intersect. You may refer to this documentation for examples of Geospatial queries.
To build a text-to-GEOQuery app, start by adapting a Text-to-SQL framework like Text2SQL to handle BigQuery’s geospatial functions (e.g., ST_DISTANCE, ST_INTERSECTS). Fine-tune the system with labeled examples of geospatial queries, such as "Find locations within 5 miles" or "Get polygons that intersect," to help the model generate the correct SQL functions. You can also leverage pre-built NLP libraries like Google's tools, spaCy, or Transformers to convert natural language into structured SQL queries that include geospatial operations.
To execute the generated SQL query securely, use the BigQuery API and handle potential errors by catching API exceptions and providing informative feedback to the user, such as "Invalid query syntax" or "Data type mismatch." Additionally, ensure security by using prepared statements, which bind user inputs securely to the SQL query, preventing SQL injection and safeguarding the app from malicious attacks.
If you're handling location-based queries, consider using Geohashes. Geohashes convert latitude and longitude pairs into a single string, simplifying the storage and querying of location data.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.