Menu
I have a column with POLYGON strings, and before processing the geo data I need to convert them with ST_GEOGFROMTEXT. However, I probably have some rows with invalid polygons and I get the following error
Error: ST_GeogFromText failed: Invalid polygon loop: Edge 0 has duplicate vertex with edge 4025
This is my query
SELECT st_geogfromtext(string_field_1)
FROM t
Is there a way to deal with incorrect polygons, or at least to identify which row returns the problem?
468
The initiative aims to provide developers, data analysts, and crypto-enthusiasts with a better understanding of the Polygon blockchain. Google’s BigQuery would ensure that query and in-depth analysis of Polygon’s on-chain data are conducted in a simple and organized manner using the Google Cloud Platform.
A practical motivating example here is being able to fix invalid polygons that don’t conform to OGC spec and cannot be loaded to BigQuery. Unfortunately, such bad data can be found in various datasets one has to work with. You can often fix it using ST_MakeValid function in PostgreSQL, but there is no such facility yet in BigQuery GIS.
This example is invalid because the value of the number falls outside the range of BIGNUMERIC: This example is invalid because the string contains invalid characters: When using CAST, a query can fail if BigQuery is unable to perform the cast.
You can often fix it using ST_MakeValid function in PostgreSQL, but there is no such facility yet in BigQuery GIS. Let’s call this function from BigQuery! We’ll use SanFrancisco zoning dataset. Several zone polygons there have self intersecting loops and are not valid polygons. These “spikes” are probably artifacts of drawing tools. Let’s fix them.
Use prefix SAFE:
SELECT SAFE.st_geogfromtext(x), x
FROM (SELECT "bad" x)
null bad
There is now a way to fix this problem in most cases, by using make_valid parameter:
https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions#st_geogfromtext_signature2
Here is a query that find the invalid polygons, and attempt to fix it:
SELECT
st_geogfromtext(string_field_1, make_valid => TRUE) as geog,
SAFE.st_geogfromtext(string_field_1) IS NULL as geog_needed_fix
FROM t
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
