-1

I have a shape as a string field in BigQuery "lat, long; lat, long; ..." and want to create a ST_GEOMETRY from it in a query.

The point is to do it directly in BQ so no code is needed to transform outside of the database.

As this question here is locked and the answer is wrong in the sense that it is not done in BigQuery but using some external tools (Python) I opened this one. Switch position of latitude and longitude in polygon using BigQuery

Improve this question
0

1 Answer 1

Reset to default
-1

The main problem is that lat / long has to be swapped, so the main point is to use array functions and index math to swap. This works directly in the db and can also be used more or less to solve other question mentioned by transforming the existing geo into a string first.

select locations.name, 
(SELECT SAFE.ST_MAKEPOLYGON(ST_GEOGFROMTEXT('LINESTRING(' || STRING_AGG(lon || ' ' || lat, ',') ||
                                                   ')'))
        FROM (SELECT DIV(pos, 2)                                     AS i,
                     ANY_VALUE(CASE WHEN MOD(pos, 2) = 1 THEN c END) AS lon,
                     ANY_VALUE(CASE WHEN MOD(pos, 2) = 0 THEN c END) AS lat
              FROM UNNEST(REGEXP_EXTRACT_ALL(
                      locations.shape_text,
                      '([0-9]+.[0-9]+)')) c WITH OFFSET pos
              GROUP BY i
              ORDER BY i))                                                  AS geo
from locations
Improve this answer
1
  • 1
    Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented Sep 2 at 15:35

Not the answer you're looking for? Browse other questions tagged or ask your own question.