Week 6 – Hard

This week we’re going to play with spatial functions. Frosty Lobbying is thinking of supporting some candidates in the next UK General Election. What they need is to understand the geographic spread of candidates by nation/region of the UK.

Your job is to build both the nations/regions and parliamentary seats into polygons, and then work out how many Westminster seats intersect with region polygons. 

Be wary that some seats may sit within two different regions, some may not sit within any (Northern Ireland is not included in the data provided) and some may just be awkward.

Note: Within the data, the ‘part’ column is an integer given to each landmass that makes up that region/nation/constituency – for example, the Isle of Mull could be ‘part 34’ of Scotland, and ‘part 12’ of the Argyll and Bute constituency.

You can find the nations are regions data here and the Westminster constituency data here.

Source: ONS, Open Geography Portal

End Result:

South East 116
North West 95
London 91
East of England 82
West Midlands 80
East Midlands 78
Yorkshire and The Humber 76
South West 70
Scotland 63
Wales 52
North East 33

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‘ (note joining our mailing list does not give you a Frosty Friday account)
  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.

If you have any technical questions you’d like to pose to the community, you can ask here on our dedicated thread.

13 responses to “Week 6 – Hard”

  1. Christopher Marland avatar
    Christopher Marland

    A few people notes that the rivers and islands were causing some issues, the challenge data has been tweaked slightly to fix the issue.

    • ChrisHastie avatar

      Much easier with the “part” field added, I was getting all sorts of strange line formations before since I couldn’t find a way to order the points correctly.

      My solution URL:

        1. ChrisHastie avatar

          Reorganised my submission repo, new master solution URL here.

          • Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
      • sachin.mittal04@gmail.com avatar

        I would like to say thanks to @Christopher Marland for such wonderful assignment. This was a new topic for me and before going this, I went through the snowflake documentation to get the idea about Geospatial functions.
        Wonderful documentation:
        Moreover when I started implementing, Chris Hastie approach has really helped me to move further. Thanks @ChrisHastie for your wonderful solution and approach.

        • Solution URL – Solution URL https://github.com/sachingithubaccnt/Frosty_Friday/blob/main/Week6_Hard.sql
      • dsmdavid avatar

        Probably the first time using spatial in Snowflake for me, how I missed Alteryx for this one!

        My approach
        – Use the previous macro to create two stages:
        – One for exploration and retrieval of the headers (challenge_06_01).
        – Another one to import the raw data.
        – Create raw tables for the different file names (Snowflake was throwing some errors when trying to run everything as views from the stage):
        – nations — **challenge_06_02**
        – constituencies — **challenge_06_03**
        – Create the polygons (include a copy of the initial point at the end `ST_MAKEPOLYGON: A GEOGRAPHY object that represents a LineString in which the last point is the same as the first (i.e. a loop)`.
        – Aggregate the geometries to nation / constituency (disregard the part information) **challenge_06_0Xb**
        – Cross join and intersect, followed by a count — **challenge_06_04**

        dbt-docs: http://dvd-il-bucket-public.s3-website-eu-west-1.amazonaws.com/#!/model/model.frosty.challenge_06_04

        Looking forward to learning from other solutions!

        • Solution URL – https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_06_04.sql
      • zlzlzl2 avatar

        Spatial is always tricky to handle, did a bit different from others how to create polygon. Snowflake need to make the spatial function easier.

        • Solution URL – https://github.com/zlzlzl2-data/FrostyFriday/blob/90ebc62147fb93a13e2499191c4e4897bfac17ec/FF6_LZ.sql
      • Pekka Kanerva avatar
        Pekka Kanerva

        This was great learning as I had previously played with geospatial data only in the Snowflake Essentials Badge 4 workshop. Here I applied the same principles to this data and managed to get the part-level polygons, but using those the counts of intersections were a little bit too high. Then I had to check what others had done and from Chris’s answer I discovered the st_collect-function, thanks for that! After adding that the counts were correct.

        • Solution URL – https://github.com/pekka-kanerva/frosty-friday/blob/main/week6/ff-week6-solution.sql
      • Atzmonky avatar

        That was a great challenge!

        Check out the challenge’s Blog: https://theinformationlab.nl/en/2022/10/03/geo-spatial-objects-in-snowflake/

        • Solution URL – https://github.com/Atzmonky/snowflake/blob/main/ch6_Geospatial.sql
      • Jamie Laird avatar
        Jamie Laird

        Here’s my solution. Massive thank you to @Atzmonky for their excellent write-up which I’ve credited in my solution, as I wouldn’t have got there without it.

        • Solution URL – https://github.com/apd-jlaird/frosty-friday/blob/main/week_6/ff_week_6.sql
      • jameskalfox avatar

        A challenging one, but great to learn more about spatial data

        • Solution URL – https://github.com/jameskalfox/frosty-friday-snowflake-challenges/blob/main/Week_06.sql
      • canonicalized avatar

        Love the geospatial stuff

        • Solution URL – https://github.com/canonicalized/FrostyFriday/blob/main/WEEK6.sql
      • mvdvelden avatar

        I tried to take a string-based approach: first creating the whole MultiPolygon as a string (in a well-known text representation of geometry form) and then converting it to a GEOGRAPHY data type :


        • Solution URL – https://github.com/marioveld/frosty_friday/tree/main/ffw6

      Leave a Reply