by 
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:
| NATION_OR_REGION | INTERSECTING_CONSTITUENCIES |
|---|---|
| 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:
- 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)
- 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.
If you have any technical questions you’d like to pose to the community, you can ask here on our dedicated thread.
14 responses to “Week 6 – Hard”
-
A few people notes that the rivers and islands were causing some issues, the challenge data has been tweaked slightly to fix the issue.
-
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:
https://github.com/ChrisHastieIW/Frosty-Friday/blob/main/Week%206%20-%20Hard%20-%20Geospatial/Week%206%20-%20Hard%20-%20Geospatial.sql-
Reorganised my submission repo, new master solution URL here.
- Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
-
-
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:
https://quickstarts.snowflake.com/guide/getting_started_with_geospatial_geography/index.html?#0
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
-
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
-
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
-
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
-
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
-
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
-
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
-
Love the geospatial stuff
- Solution URL – https://github.com/canonicalized/FrostyFriday/blob/main/WEEK6.sql
-
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 :
https://github.com/marioveld/frosty_friday/tree/main/ffw6
- Solution URL – https://github.com/marioveld/frosty_friday/tree/main/ffw6
-
🙂
- Solution URL – https://github.com/darylkit/Frosty_Friday/blob/main/Week%206%20-%20Geospatial/geospatial.sql
Leave a Reply
You must be logged in to post a comment.