Steal this SQL: RAMM basics volume two
This is the second post in our series of articles called ‘Steal this SQL: RAMM Basics’. You can find the first post here.
The focus of this series is to show some basic SQL concepts and code, for use in Thinkproject’s RAMM software.
It is intended to provide resources at a starter or introductory level only. So it is really geared towards those people that are looking to get up and running with RAMM SQL for the first time, or for someone wanting a little bit of a RAMM SQL refresher.
The series does intertwine different concepts and ideas, within the context of the RAMM software. The series was actually started on our social channels (Twitter and Instagram) a while back via the hashtag #SQLforRAMM. Individual code will appear there more regularly if you want to follow along on those platforms.
Every so often that code will be collated and posted here in this blog series.
Before getting underway, do you need a quick introduction to RAMM SQL? If so check out our earlier post here: RAMM SQL basics and writing a simple query.
Also it is worth noting, there can be lots of different ways to do things in SQL. This series is showing different code and concepts that are available, and these things are not necessarily the only way to do something.
Ok, let’s jump in!
005: Order results with ORDER BY
This query returns a list of the road id, road name and road type attributes, from the roadnames table. Every row will be returned by the query, so depending on the size of your network, your results window could be short or rather lengthy.
In this query we introduce the ORDER BY road_name ASC line. This will order our results by road name in alphatbetial (A to Z) order.
-- Steal this SQL
-- #SQLforRAMM
-- @thedatastack
SELECT road_id, road_name, road_type
FROM roadnames
ORDER BY road_name ASC
;
Note: sometimes the display settings in the RAMM SQL app can override the ORDER BY line in this code. So if you have manually sorted one of the results columns already, things may look a little different in your results.
006: Return non-Local Authority carriageway sections.
This code will return all the carriageway sections, where the asset owner is something other than Local Authority (the default owner in a NZ local road database).
You can easily the substitute the L with other asset owner codes.
Steal this SQL code to try it out in RAMM SQL.
-- Steal this SQL
-- #SQLforRAMM
-- @thedatastack
SELECT *
FROM carr_way
WHERE asset_owner <> 'L'
;
007: Using NOT IN to filter carriageway sections
Building on the previous entry in the series, this time we use NOT IN instead of <>.
This will exclude carriageway sections where the asset owner is local authority.
NOT IN can be a tidy little solution where you are wanting to filter on more than one value or code - and we will see this in action in the next bit of code.
In the mean time, steal this SQL code to try it out in RAMM SQL.
-- Steal this SQL
-- #SQLforRAMM
-- @thedatastack
SELECT *
FROM carr_way
WHERE asset_owner NOT IN ('L')
;
008: Excluding multiple asset owners
In this code, we are continuing to build on the NOT IN concept from our previous entry. This time round we are excluding multiple ownership types (local authority AND private) in one go, through the use of comma separated values.
Steal this SQL code to try it out in RAMM SQL.
-- Steal this SQL
-- #SQLforRAMM
-- @thedatastack
SELECT *
FROM carr_way
WHERE asset_owner NOT IN ('L', 'P')
;
009: Getting the total length of carriageway sections
In this script we use the SUM keyword. This will add all the values together from the length_m column in the RAMM carrigeway table (carr_way). This will return you the entire 'network length' in metres (i.e. the sum of every single carriageway section record in your database).
Steal this SQL code to try it out in RAMM SQL.
-- Steal this SQL
-- #SQLforRAMM
-- @thedatastack
SELECT SUM(length_m) AS network_length
FROM carr_way
;
010: Getting subtotals with GROUP BY
In this script we build on the previous code by adding a GROUP BY to our SUM of the length_m column.
This will return you the entire 'network length' in metres (i.e. the sum of every single carriageway section record in your database), but this time as subtotals for each of the different carriageway hierarchies used on your network.
Steal this SQL code to try it out in RAMM SQL.
-- Steal this SQL
-- #SQLforRAMM
-- @thedatastack
SELECT cway_hierarchy, SUM(length_m) AS network_length
FROM carr_way
GROUP BY cway_hierarchy
;
