by 
Brooklyn is having issues with a particular supergang : The rectangles.
They focus on controlling busy points within the city and carve out a particularly shaped area of influence: a rectangle. 1 point of interest, or node, is central to their operation, they control every other node within 750 meters.
They only seem to settle down if a node has at least 3 other nodes within 750 of a central node and they’ve also got a strange fascination with electronic stores. This means that there will be at least 4 nodes in a group.
Data is contained within the OpenStreetMap New York (by Sonra) on the Snowflake Marketplace
Requirements
– connect to OpenStreetMap New York by Sonra
– use the v_osm_ny_shop_electronics view
– If 3 nodes are within 750 meters of another central node, group these together.
– Build a rectangle containing these groups , 1 rectangle per group
– The central node should be within the city of Brooklyn (look in addr_city and ignore the null values)
– Be able to plot all these rectangles on a single map using wkt formatting (search for wkt plotting to find these sites)
END RESULT
And below is what you should end up with if you were to plot the single line on a wkt plotter (pay attention to the red rectangles):
DATA SOURCES:
- https://clydedacruz.github.io/openstreetmap-wkt-playground/
- https://app.snowflake.com/marketplace/listing/GZSVZ3XQXH?search=OpenStreetMap%20New%20York
Remember, if you want to participate:
- Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘
- Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
- Post the URL in the comments of the challenge
4 responses to “Week 17 – Intermediate”
-
My approach:
– Create the share
– Keep points* within the Brooklyn boundary as potential central points.
– Keep points within 750m of the Brooklyn boundary as all candidates.
– Cross join and keep points within 750m of each other
– Keep central points that have > 3 other points nearby
– Group all points within 750 m of the central point and create a bounding box
– Combine everything, convert to wkt and plot…*Points: I used the V_OSM_NY_AMENITY_OTHERS view instead of the NODES as that one has just too many points to play around.
Even with this smaller subset, I get loads of squares more than the proposed solution, so I must be missing something.
I created it in 2 steps
valid points
https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_17_01.sql
wkt string
https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_17_02.sql -
It took a few days for me to get to this as I was away last week. I enjoyed the challenge, though I think my warehouse credits took a larger hit than usual due to the volume of data!
I have included a README.md in the subdirectory for this challenge that includes screenshots from both Tableau and the suggested OpenStreeMap WKT Playground.
The most fun part was looking at the syntax for ST_ENVELOPE and finding out that it is being deprecated by Snowflake, so I built the bounding polygons more manually that I would have liked.
- Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
-
Some of the coordinates are in polygon form so I just took the center as the point using ST_CENTROID. I used joins and qualify to group the nodes within 750m. I then built the boxes based on those nodes. .
- Solution URL – https://github.com/darylkit/Frosty_Friday/blob/main/Week%2017%20-%20Spatial/spatial.sql
-
I didn’t know much about the genre of the challenge, but Daryl-san’s solution was very helpful.
It’s a time-honoured challenge, but it’s a learning one!
I’m very impressed that the visualisation in Snowflake is now complete.
https://github.com/Sakatoku/Frosty-Friday/blob/main/week17_intermediate_spacial/result.png?raw=trueあまり知らないジャンルの問題だったけど、Darylさんの回答がとても参考になりました。
時を経た問題だけどこれは学びが多いぞ！
地理空間情報の可視化がSnowflakeで完結するようになったのは感慨深いですね。- Solution URL – https://github.com/Sakatoku/Frosty-Friday/blob/main/week17_intermediate_spacial/notebook_app.ipynb
Leave a Reply
You must be logged in to post a comment.