CODEWITHSUNDEEP
sqlgoogle-bigquery
PeterFonteneau
PeterFonteneau

Reputation: 55

Find a POINT( , ) within a Polygon BigQuery SQL

I have two tables, one is a neighborhood table that lists the name of the neighborhood as well as neighborhood polygon with the format GEOGRAPHY.

In my second table I have the location data of a number of trees, including lattitude and longitude as FLOAT64 data types - I can convert these to a geography point using ST_GEOGPOINT.

What I am looking to do, is use the Lat and long from the trees table, and write a query that will return the name of the neighborhood of the cooresponding polygon that the point falls within

Any help is very appreciated

Upvotes: 0

Views: 2109

Answers (1)

PeterFonteneau
PeterFonteneau

Reputation: 55

I found the solution via a join!

SELECT 
    t.tree_id,
    t.species,
    t.address,
    t.plant_type,
    st_geogpoint(longitude, latitude) as point,
    nh.neighborhood
FROM `bigquery-public-data.san_francisco_trees.street_trees` AS t
JOIN `bigquery-public-data.san_francisco_neighborhoods.boundaries` AS nh
ON ST_WITHIN(st_geogpoint(t.longitude, t.latitude), nh.neighborhood_geom)
where longitude IS NOT NULL and latitude IS NOT NULL

Upvotes: 1

Related Questions