Week 17 – Intermediate

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


– 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)


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):

https://clydedacruz.github.io/openstreetmap-wkt-playground/ was used to plot the above image


Remember, if you want to participate:

  1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER
  2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
  3. Post the URL in the comments of the challenge

2 responses to “Week 17 – Intermediate”

  1. dsmdavid avatar

    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.

    ![map showing too many rectangles](https://raw.githubusercontent.com/dsmdavid/frostyfridays-sf/main/assets/ch_17.png)

    I created it in 2 steps
    valid points
    wkt string

    • ChrisHastie avatar

      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

    Leave a Reply