CODEWITHSUNDEEP
google-bigquerygisgeo
ronencozen
ronencozen

Reputation: 2201

Using BigQuery Geo Viz to visualize a polygon and its centroid

Using BigQuery Geo Viz,

I am trying to visualize a polygon and its centroid point, simultaneously on the same map.

I tried the ST_UNION function but could not really combine the two GEOGRAPHYs.

Any idea how to visualize both GEOGRAPHYs.

Polygon:
POLYGON((-95.7082555 29.9212101, -95.665885 29.907145, -95.7742806214083 29.82947355, -95.7303605 29.8538605, -95.659484 29.901497, -95.662932 29.894958, -95.8441482 29.7265376, -95.646749 29.905534, -95.810012 29.719363, -95.664174 29.883618, -95.639718 29.910045, -95.652796 29.89204, -95.649915 29.886317, -95.650089 29.881912, -95.641443 29.897741, -95.632912 29.911674, -95.653458 29.864561, -95.635056 29.864431, -95.636533 29.757219, -95.623339 29.903466, -95.597235 29.75367, -95.3636989932886 29.8063167449664, -95.575123 29.920295, -95.3944858832763 29.94248964622, -95.147033 30.013214, -95.586588 29.947706, -95.456723 31.3287239, -95.69717 29.96911, -95.674433 29.943844, -95.678203 29.935184, -95.7082555 29.9212101))

Centroid point:
POINT(-95.5606651932764 30.2307053050834)

Upvotes: 1

Views: 956

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

In case of the simple scenario you presented in the question of having just one polygon and its centroid below simple solution works 

#standardSQL
WITH objects AS (
  SELECT 'POLYGON((-95.7082555 29.9212101, -95.665885 29.907145, -95.7742806214083 29.82947355, -95.7303605 29.8538605, -95.659484 29.901497, -95.662932 29.894958, -95.8441482 29.7265376, -95.646749 29.905534, -95.810012 29.719363, -95.664174 29.883618, -95.639718 29.910045, -95.652796 29.89204, -95.649915 29.886317, -95.650089 29.881912, -95.641443 29.897741, -95.632912 29.911674, -95.653458 29.864561, -95.635056 29.864431, -95.636533 29.757219, -95.623339 29.903466, -95.597235 29.75367, -95.3636989932886 29.8063167449664, -95.575123 29.920295, -95.3944858832763 29.94248964622, -95.147033 30.013214, -95.586588 29.947706, -95.456723 31.3287239, -95.69717 29.96911, -95.674433 29.943844, -95.678203 29.935184, -95.7082555 29.9212101))' wkt_string UNION ALL
  SELECT 'POINT(-95.5606651932764 30.2307053050834)'
)
SELECT ST_GEOGFROMTEXT(wkt_string) geo
FROM objects

and this can be visualized with different tools - like in below example

enter image description here

For more realistic scenario, when you have many polygons and need to visulaize them along with their centroids - you can use below approach (based on example with us states) 

#standardSQL
SELECT state_geom state, ST_CENTROID(state_geom) centroid
FROM `bigquery-public-data.utility_us.us_states_area`

with result as below

enter image description here

which can be visualized as in below examples (just showing few states to get an idea)

enter image description here

enter image description here

enter image description here

And finally, you can combine all such polygons (states in this example) with their centroids in nice visualization as below

enter image description here

Another (of many endless options) thing you can do is to add some metrics and more attributes to the query - for example state_name and area_land_meters and make your visualization data driven and with dynamic tooltips like in below example

enter image description here

Upvotes: 2

rmesteves
rmesteves

Reputation: 4075

Try selecting the two structures separately and using UNION ALL to gather them in the same visualization:

SELECT ST_GeogFromText('POLYGON((-95.7082555 29.9212101, -95.665885 29.907145, -95.7742806214083 29.82947355, -95.7303605 29.8538605, -95.659484 29.901497, -95.662932 29.894958, -95.8441482 29.7265376, -95.646749 29.905534, -95.810012 29.719363, -95.664174 29.883618, -95.639718 29.910045, -95.652796 29.89204, -95.649915 29.886317, -95.650089 29.881912, -95.641443 29.897741, -95.632912 29.911674, -95.653458 29.864561, -95.635056 29.864431, -95.636533 29.757219, -95.623339 29.903466, -95.597235 29.75367, -95.3636989932886 29.8063167449664, -95.575123 29.920295, -95.3944858832763 29.94248964622, -95.147033 30.013214, -95.586588 29.947706, -95.456723 31.3287239, -95.69717 29.96911, -95.674433 29.943844, -95.678203 29.935184, -95.7082555 29.9212101))') t UNION ALL SELECT ST_GeogFromText('POINT(-95.5606651932764 30.2307053050834)') t

If your intention is to show the geometry and the point in the same visualization it will work as you can see in the image below:

enter image description here

Please let me know if this is what you are looking for

Upvotes: 3

Related Questions