Steal this SQL: RAMM basics volume one

This is the first post in a new series of articles called ‘Steal this SQL: RAMM Basics’.

The focus here is to show some basic SQL concepts and code to use in Thinkproject’s RAMM software. It is intended to provide resources at a starter level, for those looking to get up and running with RAMM SQL for the first time, or for those after a bit of a refresher.

The series will include a mix of basic and intermediate concepts. 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 just to point out, there can be lots of different ways to do things in SQL. This series is showing different code and concepts available to you, not necessarily the only way or definitive way to do something. Lets jump in!

001: Create a list of all carriageway sections

This simple SELECT query will return all columns for all records from the carriageway (carr_way) table in RAMM.

Save the image above or copy and paste the code below;

-- Steal this SQL
-- #SQLforRAMM
-- @thedatastack

SELECT *
FROM carr_way

002: Create a list of all road names

Generate a list of Road Names and Road ID's in your network, with this simple little bit of SQL code. In this script we are querying the roadnames table and returning just two specific columns; road_id and road_name.

This will return the roads you have across all road types defined in your RAMM database.

Save the image above or copy and paste the code below;

-- Steal this SQL
-- #SQLforRAMM
-- @thedatastack

SELECT road_id, road_name
FROM roadnames

003: Get the number of road id’s for each road type

Need to know the number of “roads” for each road type in your RAMM database? Then steal this SQL code to find out.

Just remember the road names table in RAMM isn’t necessarily just for roads. So the numbers you get back could include all sorts of features for other networks such as paths, car parks and reserves. It all depends on how the database you are using has been configured!

Save the image above or copy and paste the code below;

-- Steal this SQL
-- #SQLforRAMM
-- @thedatastack

SELECT road_type, COUNT(*)
FROM roadnames
GROUP BY road_type

004: Use a SQL Alias to name a column

This code uses the concept of a SQL Alias. A SQL Alias is basically just a temporary label or name you give to a column in your SQL results. A SQL Alias can be applied to tables as well, but we will save that concept for another day.

An alias can make things a bit easier to understand. To demonstrate this, we will build on the same code above and rename our count column to something a bit more user friendly than COLUMN1. This can be done by using the keyword AS and adding in an alternate name (i.e. AS number_of_roads)

Save the image above or copy and paste the code below;

-- Steal this SQL
-- #SQLforRAMM
-- @thedatastack

SELECT road_type, COUNT(*) AS number_of_roads
FROM roadnames
GROUP BY road_type

That wraps up Volume 1 of Steal this SQL: RAMM Basics. Check back here later for Volume 2, or follow us on Twitter or Instagram to see the code as soon as it drops.