Solar System geometry scaled to match Our Place in Space installation in Cambridge 2022

solar_system_scaled_Cambridge_2022.geojson

-- SQL for working out the scale for a Solar System model
-- The inspiration for this comes from a prompt to check the accuracy of the planet placings
-- for the Our Place in Space art installation in Cambridge 2022.
-- https://ourplaceinspace.earth/trail/cambridge

-- This code was tested on MySQL 8.0.30-0ubuntu0.22.04.1

-- Define table for solar system bodies
drop table if exists dev_solar_system;
create table dev_solar_system (
                name varchar(20)  not null comment 'Name of body in solar system',
               color char(7)      not null comment 'Color chosen for planet',
          diameterKm int          not null comment 'Diameter in km',
     solarDistanceKm bigint       not null comment 'Distance from sun in km',
scaledDiameterMetres decimal(7,3) not null comment 'Scaled diameter of the body in metres',
scaledDistanceMetres int          not null comment 'Scaled distance from the sun in metres',
            geometry geometry         null comment 'Orbit',
primary key (name)
) comment 'Solar system scale sizes';

-- Reset
truncate dev_solar_system;

-- Fill table
-- Values from the Scale Size and Distance Spreadsheet at:
-- https://www.jpl.nasa.gov/edu/learn/project/make-a-scale-solar-system/
-- Colours based on:
-- http://curious.astro.cornell.edu/about-us/58-our-solar-system/Planets-and-dwarf-Planets/Planet-watching/249-what-color-is-each-Planet-intermediate
insert dev_solar_system (name, diameterKm, solarDistanceKm, color) values
('Sun',		1391400,	0,		'#fefb00'), -- Yellow (lemon)
('Mercury',	4879,		57900000,	'#eaeaea'), -- Gray(mercury)
('Venus',		12104, 		108200000,	'#fefc78'), -- Pale yellow (banana)
('Earth',		12756,		149600000,	'#0096ff'), -- Blue (aqua)
('Mars',		6792,		227900000,	'#941100'), -- Reddish brown (cayenne)
('Jupiter',	142984,		778600000,	'#ff9300'), -- Orange (tangerine)
('Saturn',	120536,		1433500000,	'#ffd478'), -- Pale Gold (cantaloupe)
('Uranus',	51118,		2872500000,	'#008f51'), -- Pale blue but use green (moss)
('Neptune',	49528,		4495100000,	'#009192'); -- Pale blue (teal)

-- Choose the location for the Sun
-- In Cambridge it is on Midsummer Common near this photo:
-- https://www.cyclestreets.net/location/87879/
set @sun := st_geomfromgeojson('{"type": "Point", "coordinates": [0.12861, 52.20944]}');


-- Scales
-- ======
-- Note that in the following some unusual units, i.e. Thousands of km to Metres are used to keep numbers in a sensible range.
-- That avoids having too many zeroes before or after the decimal point.


-- Scale Option 1
-- Official Scale used for the Cambridge project
-- Via email from https://ourplaceinspace.earth/ the scale is: 591 million to one.
-- set @officiaScale := 1/591000000;
set @officiaScaleThousandsKmToMetres := 1 / 591;

-- View
-- select @officiaScaleThousandsKmToMetres;
-- 0.001692



-- Scale Option 2
-- A scale based on the size chosen for the Sun

-- Choose how big is the sun in the model
set @scaledSunDiameterMetres := 2.3535;

-- Diameter in 1000s of km
select diameterKm / 1000 into @solarDiameterThousandsKm from dev_solar_system where name = 'Sun';

-- Converts 1000s of km to size in metres
set @solarDiameterBasedScaleFactorThousandsKmToMetres := @scaledSunDiameterMetres / @solarDiameterThousandsKm;

-- View
-- select @solarDiameterBasedScaleFactorThousandsKmToMetres;
-- 0.0016915 -- For diameter of 2.3535 metres
-- 0.0053903 -- For diameter of 7.5 metres



-- Scale Option 3
-- Alternative scale based on relative locations of Sun and Saturn
-- The location of Saturn is by the Chisholm Trail bridge:
-- https://www.cyclestreets.net/location/185175/
-- Location taken from photo: 151625
set @saturn := st_geomfromgeojson('{"type": "Point", "coordinates": [0.15716, 52.22035]}');

-- Distance between the two
-- This function assumes an Earth's radius of: 6,370,986 meters, see: 
-- https://dev.mysql.com/doc/refman/8.0/en/spatial-convenience-functions.html#function_st-distance-sphere
select st_distance_sphere(@sun, @saturn);
-- 2292 metres

-- So the scale in 1000s of km to metres is:
select st_distance_sphere(@sun, @saturn) / (solarDistanceKm / 1000) into @saturnBasedScaleThousandsKmToMetres from dev_solar_system where name = 'Saturn';
select @saturnBasedScaleThousandsKmToMetres;
-- 0.0015992




-- Activate the chosen scale:
-- Comment in only one of these options

-- 1. Use the official scale
-- set @scaleFactorThousandsKmToMetres := @officiaScaleThousandsKmToMetres;

-- 2. Use the scale based on relative locations of Sun and Saturn
-- set @scaleFactorThousandsKmToMetres := @solarDiameterBasedScaleFactorThousandsKmToMetres;

-- 3. Use the Saturn based scale
set @scaleFactorThousandsKmToMetres := @saturnBasedScaleThousandsKmToMetres;



-- Apply the scale
update dev_solar_system
   set scaledDiameterMetres = (diameterKm / 1000)       * @scaleFactorThousandsKmToMetres,
       scaledDistanceMetres = (solarDistanceKm / 1000)  * @scaleFactorThousandsKmToMetres;

-- View
-- select * from dev_solar_system order by solarDistanceKm asc;



-- Now create planet geometries

-- All the orbits
update dev_solar_system
   set geometry = st_exteriorring(st_buffer(@sun, scaledDistanceMetres));

-- Add the sun
update dev_solar_system
   set geometry = st_buffer(@sun, scaledDiameterMetres)
 where name = 'Sun';

-- Change to SRID 0 which is necessary on versions of MySQL that don't have full suport for non-Cartesian geometries.
update dev_solar_system
   set geometry = st_srid(geometry, 0);


-- Obtain the result as geojson

-- Colours
-- Two mapping tools are supported:
-- Geojson.io uses: stroke,	stroke-width,	fill,		fill-opacity
-- LeafletJS  uses: color,	weight,		fillColor,	fillOpacity
set @strokeColor1 := 'stroke',	@strokeWidth1 := 'stroke-width',	@fillColor1 := 'fill',		@fillOpacity1 = 'fill-opacity';
set @strokeColor2 := 'color',	@strokeWidth2 := 'width',		@fillColor2 := 'fillColor',	@fillOpacity2 = 'fillOpacity';

-- Do this to avoid cropping the geometries in the group_concat()
set @@group_concat_max_len := 4194304;

-- Get the geojson
select concat('{"type": "FeatureCollection","properties": {"description": "Solar System geometry scaled to match Our Place in Space installation in Cambridge 2022."},"features": [',
        group_concat(
          concat(
          '{"type":"Feature", "properties":{"name":"', name, '"',
              /* Colouring scheme 1 */
              if(name = 'Sun',
                 concat(',"', @fillColor1,   '":"', color, '","', @strokeWidth1, '":0,"', @fillOpacity1,'":1'),
                 concat(',"', @strokeColor1, '":"', color, '","', @strokeWidth1, '":6')),
              /* Colouring scheme 2 */
              if(name = 'Sun',
                 concat(',"', @fillColor2,   '":"', color, '","', @strokeWidth2, '":0,"', @fillOpacity2,'":1'),
                 concat(',"', @strokeColor2, '":"', color, '","', @strokeWidth2, '":6')),
              '},"geometry":', 
              st_asgeojson(geometry, 6),
              '}')
	  order by solarDistanceKm asc),
        ']}') geojson
  from routing220824.dev_solar_system
\G

Cambridge installation sizes

This table shows the results of the calculations which are derived using on the NASA JPL solar system dimensions, and the location of Saturn relative to the Sun in the scaled installation for Cambridge.

name	Scaled diameter (mm)	Scaled distance (metres)
Sun	2225	0
Mercury	8	93
Venus	19	173
Earth	20	239
Mars	11	364
Jupiter	229	1245
Saturn	193	2292
Uranus	82	4594
Neptune	79	7188

These are in agreement to within a few percent of the dimensions actually used in the Cambridge installation.