3.3 Compute building's shadow - orbisgis/h2gis GitHub Wiki
This use case aims to compute the building's shadow for different hours of a day.
Here, the demonstration is located on the city of Nantes (France), around the "Ecole Centrale" (POINT(-1.54544 47.24961) in WGS84).
We compute the building's shadow, using the BUILDINGS data from IGN. The EPSG code of this layer is 2154 (RGF Lambert 93 (a cartesian projection system)).
The building's height is defined in the column named HAUTEUR. If the height is null, we use a default value equal to 3 meters.
Shadow is computed from 8am to 5pm on january 30th 2015.
In the SQL script below, we are using two specific functions, developped in H2GIS : ST_SunPosition and ST_GeometryShadow.
-- We store the localisation point in a variable called `PLACE`
-- This coordinate is in the WGS84 system
SET @PLACE ='POINT(-1.54544 47.24961)';
-- Then, sun positions are computed and stored into a table called "sunposition"
DROP TABLE IF EXISTS sunposition;
CREATE table sunposition (id int, the_geom geometry);
INSERT INTO sunposition values(1, ST_SunPosition(@PLACE,'2015-01-30 8:00:00+01:00'));
INSERT INTO sunposition values(2, ST_SunPosition(@PLACE,'2015-01-30 9:00:00+01:00'));
INSERT INTO sunposition values(3, ST_SunPosition(@PLACE,'2015-01-30 10:00:00+01:00'));
INSERT INTO sunposition values(4, ST_SunPosition(@PLACE,'2015-01-30 11:00:00+01:00'));
INSERT INTO sunposition values(5, ST_SunPosition(@PLACE,'2015-01-30 12:00:00+01:00'));
INSERT INTO sunposition values(6, ST_SunPosition(@PLACE,'2015-01-30 13:00:00+01:00'));
INSERT INTO sunposition values(7, ST_SunPosition(@PLACE,'2015-01-30 14:00:00+01:00'));
INSERT INTO sunposition values(8, ST_SunPosition(@PLACE,'2015-01-30 15:00:00+01:00'));
INSERT INTO sunposition values(9, ST_SunPosition(@PLACE,'2015-01-30 16:00:00+01:00'));
INSERT INTO sunposition values(10, ST_SunPosition(@PLACE,'2015-01-30 17:00:00+01:00'));
-- To finish, shadows are computed for each steps
-- To avoid multipolygon issues, we use the ST_GeometryN function to keep the first polygon
DROP TABLE IF EXISTS shadow1, shadow2, shadow3, shadow4, shadow5, shadow6, shadow7, shadow8, shadow9, shadow10;
CREATE TABLE shadow1 AS SELECT ST_Geometryshadow(ST_GeometryN(a.the_geom ,1), b.the_geom, CASEWHEN(a.HAUTEUR>0, a.HAUTEUR, 3)) the_geom FROM BUILDINGS a, sunposition b WHERE b.id=1;
CREATE TABLE shadow2 AS SELECT ST_Geometryshadow(ST_GeometryN(a.the_geom ,1), b.the_geom, CASEWHEN(a.HAUTEUR>0, a.HAUTEUR, 3)) the_geom FROM BUILDINGS a, sunposition b WHERE b.id=2;
CREATE TABLE shadow3 AS SELECT ST_Geometryshadow(ST_GeometryN(a.the_geom ,1), b.the_geom, CASEWHEN(a.HAUTEUR>0, a.HAUTEUR, 3)) the_geom FROM BUILDINGS a, sunposition b WHERE b.id=3;
CREATE TABLE shadow4 AS SELECT ST_Geometryshadow(ST_GeometryN(a.the_geom ,1), b.the_geom, CASEWHEN(a.HAUTEUR>0, a.HAUTEUR, 3)) the_geom FROM BUILDINGS a, sunposition b WHERE b.id=4;
CREATE TABLE shadow5 AS SELECT ST_Geometryshadow(ST_GeometryN(a.the_geom ,1), b.the_geom ,CASEWHEN(a.HAUTEUR>0, a.HAUTEUR, 3)) the_geom FROM BUILDINGS a, sunposition b WHERE b.id=5;
CREATE TABLE shadow6 AS SELECT ST_Geometryshadow(ST_GeometryN(a.the_geom ,1), b.the_geom, CASEWHEN(a.HAUTEUR>0, a.HAUTEUR, 3)) the_geom FROM BUILDINGS a, sunposition b WHERE b.id=6;
CREATE TABLE shadow7 AS SELECT ST_Geometryshadow(ST_GeometryN(a.the_geom ,1), b.the_geom ,CASEWHEN(a.HAUTEUR>0, a.HAUTEUR, 3)) the_geom FROM BUILDINGS a, sunposition b WHERE b.id=7;
CREATE TABLE shadow8 AS SELECT ST_Geometryshadow(ST_GeometryN(a.the_geom ,1), b.the_geom ,CASEWHEN(a.HAUTEUR>0, a.HAUTEUR, 3)) the_geom FROM BUILDINGS a, sunposition b WHERE b.id=8;
CREATE TABLE shadow9 AS SELECT ST_Geometryshadow(ST_GeometryN(a.the_geom ,1), b.the_geom ,CASEWHEN(a.HAUTEUR>0, a.HAUTEUR, 3)) the_geom FROM BUILDINGS a, sunposition b WHERE b.id=9;
CREATE TABLE shadow10 AS SELECT ST_Geometryshadow(ST_GeometryN(a.the_geom ,1), b.the_geom, CASEWHEN(a.HAUTEUR>0, a.HAUTEUR, 3)) the_geom FROM BUILDINGS a, sunposition b WHERE b.id=10;