Home > Miscellaneous > Création d’une étiquette DPE depuis Oracle

Création d’une étiquette DPE depuis Oracle

September 2, 2012 Leave a comment Go to comments

As the title announces, exceptionally, this article is written in French. It describes how to generate the french version of the Energy Performance Certificate (EPC) chart from Oracle database, both as a BLOB image (using Java) and SVG file (using SQL/XML functions).
Translation available upon request ;)

En France, selon la règlementation en vigueur, le Diagnostic de Performance Énergétique (DPE) inclut une représentation visuelle de la performance énergétique d’un bâtiment sous la forme d’une étiquette marquant sa consommation annuelle sur une échelle de valeurs.

J’ai retenu deux méthodes assez différentes pour créer cette représentation depuis Oracle :

  • Une procédure stockée Java générant une image (format PNG)
  • Une simple requête SQL utilisant les fonctions SQL/XML pour créer un fichier au format SVG

1. Les données source

L’arrêté du 15 septembre 2006 relatif au diagnostic de performance énergétique (Article Annexe 3 – §3.1 et 3.2), disponible ici, décrit la norme à suivre pour la création de l’étiquette : couleur des bandes, du texte et plages de consommation liées à chaque classe.

Ces données, une fois chargées dans une table, seront utilisées pour générer les représentations graphiques correspondantes.

CREATE TABLE dpe_label_item (
  type_id     NUMBER(4)
, item_id     NUMBER(2)
, item_name   VARCHAR2(30)
, bg_color    VARCHAR2(6)
, fg_color    VARCHAR2(6)
, label_text  VARCHAR2(5)
, min_value   NUMBER
, max_value   NUMBER
);

Par exemple, pour le type 1, concernant les bâtiments d’habitation :

SQL> select * from dpe_label_item where type_id = 1 order by item_id;
 
TYPE_ID ITEM_ID ITEM_NAME       BG_COLOR FG_COLOR LABEL_TEXT  MIN_VALUE  MAX_VALUE
------- ------- --------------- -------- -------- ---------- ---------- ----------
      1       1 Bande A         00FF00   000000   A                             50
      1       2 Bande B         4DFF00   000000   B                  51         90
      1       3 Bande C         B3FF00   000000   C                  91        150
      1       4 Bande D         FFFF00   000000   D                 151        230
      1       5 Bande E         FFB300   000000   E                 231        330
      1       6 Bande F         FF4D00   000000   F                 331        450
      1       7 Bande G         FF0000   FFFFFF   G                 451 
 
7 rows selected
 

NB : pour faciliter l’utilisation des couleurs décrites dans la règlementation (données en quadrichromie CMJN), je les ai converties directement en RVB hexadécimal : FG_COLOR pour la couleur du texte et BG_COLOR pour le fond.

 

2. Génération de l’image à l’aide d’une procédure stockée Java

L’idée est d’utiliser les classes standards de Java AWT (java.awt.*) pour dessiner une à une les bandes de couleur, puis placer l’étiquette portant la valeur sur l’échelle.
L’image correspondante est ensuite retournée à Oracle sous le format PNG, dans un BLOB.

Bien que la classe Java ait été créée pour un fonctionnement dans la base de données côté serveur, elle peut être adaptée facilement pour un usage externe (client) en modifiant la méthode getBlob().
Également pour des raisons de portabilité, le programme ne repose pas sur une connexion à la base pour obtenir les données source, elles sont passées via un document XML ayant la structure suivante :

SQL> SELECT XMLSerialize(document
  2           XMLElement("Etiquette",
  3             XMLElement("Couleur"
  4             , XMLAttributes('FFFFFF' as "texte", '000000' as "fond")
  5             )
  6           , XMLForest(100 as "Valeur", 24 as "Taille")
  7           , XMLElement("Bandes"
  8             , XMLAgg(
  9                 XMLElement("Bande",
 10                   XMLAttributes(item_id as "id", item_name as "nom")
 11                 , XMLElement("Couleur",
 12                     XMLAttributes(fg_color as "texte", bg_color as "fond")
 13                   )
 14                 , XMLElement("Texte", label_text)
 15                 , XMLElement("Min", min_value)
 16                 , XMLElement("Max", max_value)
 17                 )
 18                 order by item_id
 19               )
 20             )
 21           )
 22           as clob indent
 23         ) as config_xmldoc
 24  FROM dev.dpe_label_item
 25  WHERE type_id = 1
 26  ;
 
CONFIG_XMLDOC
-----------------------------------------------------------------
<Etiquette>
  <Couleur texte="FFFFFF" fond="000000"/>
  <Valeur>100</Valeur>
  <Taille>24</Taille>
  <Bandes>
    <Bande id="1" nom="Bande A">
      <Couleur texte="000000" fond="00FF00"/>
      <Texte>A</Texte>
      <Min/>
      <Max>50</Max>
    </Bande>
    <Bande id="2" nom="Bande B">
      <Couleur texte="000000" fond="4DFF00"/>
      <Texte>B</Texte>
      <Min>51</Min>
      <Max>90</Max>
    </Bande>
    <Bande id="3" nom="Bande C">
      <Couleur texte="000000" fond="B3FF00"/>
      <Texte>C</Texte>
      <Min>91</Min>
      <Max>150</Max>
    </Bande>
    <Bande id="4" nom="Bande D">
      <Couleur texte="000000" fond="FFFF00"/>
      <Texte>D</Texte>
      <Min>151</Min>
      <Max>230</Max>
    </Bande>
    <Bande id="5" nom="Bande E">
      <Couleur texte="000000" fond="FFB300"/>
      <Texte>E</Texte>
      <Min>231</Min>
      <Max>330</Max>
    </Bande>
    <Bande id="6" nom="Bande F">
      <Couleur texte="000000" fond="FF4D00"/>
      <Texte>F</Texte>
      <Min>331</Min>
      <Max>450</Max>
    </Bande>
    <Bande id="7" nom="Bande G">
      <Couleur texte="FFFFFF" fond="FF0000"/>
      <Texte>G</Texte>
      <Min>451</Min>
      <Max/>
    </Bande>
  </Bandes>
</Etiquette>
 

Ce document est lu avec XPath pour construire une instance de la classe LabelDescription.

Voici le code complet, testé sur Oracle Release 11.2 (donc JDK 1.5) :

create or replace and compile java source named dpe_label_impl as

import java.awt.Graphics2D;
import java.awt.RenderingHints;
import java.awt.Polygon;
import java.awt.BasicStroke;
import java.awt.Stroke;
import java.awt.Color;
import java.awt.Font;
import java.awt.image.BufferedImage;

import org.w3c.dom.Document;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.xpath.*;

import javax.imageio.*;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;

import java.sql.*;
import oracle.sql.BLOB;
import oracle.sql.CLOB;
import oracle.jdbc.OracleDriver;

public class DPEMainClass {

    Font labelFont;
    Font textFont;
    
    int  fontSizePt;
    int  fontSizePx;
    int  halfHeight;
    int  height;
    int  width;
    int  heightSpace;
    int  widthExtent;
    int  textBaseLine;
    int  textAdvance;
    int  vLOffset;
    int  vLWidth;
    
    LabelDescription ld;

    public DPEMainClass(InputStream is) throws IOException, ParserConfigurationException, 
            SAXException, XPathExpressionException {

        ld = new LabelDescription(is);

        fontSizePt = ld.baseFontSize;

        fontSizePx = Math.round( fontSizePt * 3f/4 );
        halfHeight = Math.round( fontSizePx * 5f/4 );
        height = 2 * halfHeight;
        width = 2 * height;
        heightSpace = Math.round( halfHeight / 3f );
        widthExtent = Math.round( halfHeight * 4f/3 );
        textBaseLine = Math.round((height + fontSizePx) / 2f);
        textAdvance = fontSizePx - (halfHeight / 4);
        vLWidth = width + widthExtent * 0;

        labelFont = new Font("Arial", Font.BOLD, fontSizePt);
        textFont = new Font("Arial", Font.PLAIN, fontSizePt - 4);
    }

    private int getLabelDescSize() {
        return this.ld.getSize();
    }

    private int getWidth() {
        int n = getLabelDescSize();
        return 10 + width + widthExtent*(n-1) + 2 * height + width + 10;
    }

    private int getHeight() {
        int n = getLabelDescSize();
        return 10 + (height + heightSpace)*(n-1) + height + 10;
    }

    private void drawItem(Graphics2D g, int x, int y, int w,
            LabelDescription.ItemRow r, Integer val) {

        Polygon p2 = new Polygon();
        p2.addPoint(x, y);
        p2.addPoint(x + w, y);
        p2.addPoint(x + w + halfHeight, y + halfHeight);
        p2.addPoint(x + w, y + height);
        p2.addPoint(x, y + height);
        g.setColor(new Color(r.bgColor));
        g.fillPolygon(p2);
        g.setColor(new Color(r.fgColor));
        g.setFont(labelFont);
        g.drawString(r.label, x + w - textAdvance, y + textBaseLine);

        g.setFont(textFont);
        g.drawString(r.getString(), x + textAdvance, y + textBaseLine);

        if (r.isInRange(val)) {
            Integer offset = r.getOffset(val);
            Stroke s = g.getStroke();

            final BasicStroke dashed =
                new BasicStroke(1.2f, BasicStroke.CAP_ROUND, BasicStroke.JOIN_MITER,
                        10.0f, new float[]{3.0f}, 0.0f);

            g.setStroke(dashed);
            g.setColor(Color.BLACK);
            g.drawLine(vLOffset - halfHeight, y + offset + halfHeight, x + w + halfHeight, y + offset + halfHeight);
            g.setStroke(s);

            Polygon p3 = new Polygon();
            p3.addPoint(vLOffset, y + offset);
            p3.addPoint(vLOffset + vLWidth, y + offset);
            p3.addPoint(vLOffset + vLWidth, y + offset + height);
            p3.addPoint(vLOffset, y + offset + height);
            p3.addPoint(vLOffset - halfHeight, y + offset + halfHeight);
            g.setColor(new Color(ld.bgColor));
            g.fillPolygon(p3);
            g.setColor(new Color(ld.fgColor));
            g.setFont(labelFont);
            g.drawString(val.toString(), vLOffset + 5, y + offset + textBaseLine);
            g.setFont(textFont);
        }
    }

    public void paint(Graphics2D g) {
        g.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON);
        int bandCount = ld.itemRowList.size();
        vLOffset = 10 + width + widthExtent * (bandCount - 1) + 2 * height;

        for (int i = 0; i<bandCount; i++) {
            drawItem(g, 10, 10 + (height + heightSpace) * i, width + widthExtent * i,
            ld.itemRowList.get(i), ld.value);
        }
    }

    private final class LabelDescription {

        private Integer bgColor;
        private Integer fgColor;
        private String  label;
        private Integer value;
        private Integer baseFontSize;
        private ArrayList<ItemRow>  itemRowList;

        private final class ItemRow {
            int     id;
            String  name;
            Integer bgColor;
            Integer fgColor;
            String  label;
            Integer loValue;
            Integer hiValue;

            private boolean isInRange(Integer val) {
                return ( (this.loValue == null) || (val >= this.loValue) )
                     & ( (this.hiValue == null) || (val <= this.hiValue) );
            }

            private String getString() {
                String s;
                if (this.loValue == null) {
                  s = "\u2264 " + this.hiValue.toString();
                } else if (this.hiValue == null) {
                  s = "\u2265 " + this.loValue.toString();
                } else {
                  s = this.loValue.toString() + " à " + this.hiValue.toString();
                }
                return s;
            }

            private Integer getOffset(Integer val) {
                Integer offset = 0;
                if (this.loValue != null & this.hiValue != null) {
                  offset = height * (val - this.loValue) / (this.hiValue - this.loValue) - halfHeight;
                }
                return offset;
            }
        }

        private int getSize() {
            return this.itemRowList.size();
        }

        private void addRow(ItemRow mr) {
            this.itemRowList.add( mr );
        }

        private LabelDescription(InputStream xmlStream) throws ParserConfigurationException, IOException,
                SAXException, XPathExpressionException {

            DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
            DocumentBuilder db = dbf.newDocumentBuilder();
            Document doc = db.parse(xmlStream);

            itemRowList = new ArrayList<ItemRow>();
            ItemRow mr;
            String tmp;

            XPath xp = XPathFactory.newInstance().newXPath();

            this.bgColor = Integer.parseInt(xp.evaluate("/Etiquette/Couleur/@fond", doc), 16);
            this.fgColor = Integer.parseInt(xp.evaluate("/Etiquette/Couleur/@texte", doc), 16);
            this.value   = Integer.parseInt(xp.evaluate("/Etiquette/Valeur", doc));
            this.baseFontSize = Integer.parseInt(xp.evaluate("/Etiquette/Taille", doc));

            XPathExpression xpeId = xp.compile("@id");
            XPathExpression xpeName = xp.compile("@nom");
            XPathExpression xpeBgColor = xp.compile("Couleur/@fond");
            XPathExpression xpeFgColor = xp.compile("Couleur/@texte");
            XPathExpression xpeLabel = xp.compile("Texte");
            XPathExpression xpeMin = xp.compile("Min");
            XPathExpression xpeMax = xp.compile("Max");

            NodeList nl = (NodeList) xp.evaluate("/Etiquette/Bandes/Bande", doc, XPathConstants.NODESET);

            for (int i=0; i < nl.getLength(); i++) {
                mr = new ItemRow();

                mr.id = Integer.parseInt( xpeId.evaluate(nl.item(i)) );
                mr.name = xpeName.evaluate(nl.item(i));
                mr.bgColor = Integer.parseInt( xpeBgColor.evaluate(nl.item(i)), 16 );
                mr.fgColor = Integer.parseInt( xpeFgColor.evaluate(nl.item(i)), 16 );
                mr.label   = xpeLabel.evaluate(nl.item(i));

                tmp = xpeMin.evaluate(nl.item(i));
                mr.loValue = (tmp.length()!=0) ? Integer.parseInt(tmp) : null;
                tmp = xpeMax.evaluate(nl.item(i));
                mr.hiValue = (tmp.length()!=0) ? Integer.parseInt(tmp) : null;

                addRow(mr);
            }
        }
    }

    public static BLOB getBlob(CLOB labelDesc) throws ParserConfigurationException, IOException,
            SAXException, XPathExpressionException, SQLException {

        DPEMainClass mc = new DPEMainClass(labelDesc.getAsciiStream());
        int w = mc.getWidth();
        int h = mc.getHeight();

        BufferedImage image = new BufferedImage(w, h, BufferedImage.TYPE_INT_RGB);
        Graphics2D gi = image.createGraphics();
        gi.setColor(Color.WHITE);
        gi.fillRect(0, 0, w, h);
        mc.paint(gi);
        gi.dispose();

        Connection conn = (new OracleDriver()).defaultConnection();
        BLOB result = BLOB.createTemporary(conn, true, BLOB.DURATION_CALL);
        ImageIO.write(image,"PNG", result.setBinaryStream(0L));
        
        return result;
    }
}

Et voici la fonction PL/SQL interfaçant la méthode getBlob() :

create or replace function get_dpe_image (label_desc in clob)
return blob
is
language java name 'DPEMainClass.getBlob(oracle.sql.CLOB) return oracle.sql.BLOB';

 

Outre le paramétrage des plages, le document XML indique la valeur à positionner sur l’échelle de consommations (élément /Etiquette/Valeur) ainsi que la taille souhaitée (élément /Etiquette/Taille) qui fait référence en pratique à la taille de la police de caractères utilisée.

Exemple d’utilisation :

SQL> select get_dpe_image(
  2           xmlserialize(document
  3             xmlelement("Etiquette",
  4               xmlelement("Couleur", xmlattributes('FFFFFF' as "texte", '000000' as "fond"))
  5             , xmlforest(100 as "Valeur", 24 as "Taille")
  6             , xmlelement("Bandes"
  7               , xmlagg(
  8                   xmlelement("Bande",
  9                     xmlattributes(item_id as "id", item_name as "nom")
 10                   , xmlelement("Couleur",
 11                       xmlattributes(fg_color as "texte", bg_color as "fond")
 12                     )
 13                   , xmlelement("Texte", label_text)
 14                   , xmlelement("Min", min_value)
 15                   , xmlelement("Max", max_value)
 16                   )
 17                   order by item_id
 18                 )
 19               )
 20             )
 21             as clob indent
 22           )
 23         ) as blob_image
 24  from dpe_label_item
 25  where type_id = 1
 26  ;
 
BLOB_IMAGE
----------
<BLOB>
 

Le BLOB retourné par la fonction contient notre image au format PNG :

 

3. Génération au format SVG

Puisque SVG (Scalable Vector Graphics) est basé sur XML, il est facile de générer le contenu à l’aide des fonctions SQL/XML fournies par Oracle.
Le point de départ est toujours la table de paramétrage DPE_LABEL_ITEM décrite précédemment :

DECLARE

  res clob;
  val number := 200;

BEGIN
 
  select xmlserialize(document
           xmlelement("svg",
             xmlattributes('http://www.w3.org/2000/svg' as "xmlns")
           , xmlelement("style", 
               xmlattributes('text/css' as "type")
             , xmlcdata(
'path {fill-opacity:1;stroke:none;}
text {font-style:normal;font-weight:normal;font-family:Arial;fill-opacity:1;stroke:none;}
text.t1 {font-size:20px;}
text.t2 {font-size:12px;}
line.c1 {stroke:black;stroke-width:1;stroke-opacity:1;stroke-dasharray: 5,3;}'
               )
             )
           , xmlelement("g",
               xmlattributes('scale(2)' as "transform")  
             , xmlagg(
                 xmlconcat(
                   case when min_value is null and val <= max_value 
                          or val between min_value and max_value
                          or max_value is null and val >= min_value
                        then xmlconcat(
                               xmlelement("path",
                                 xmlattributes(
                                   '#000000' as "fill"
                                 , 'm 210,' || 
                                   to_char(10 + 35*(item_id-1) + nvl(round(30*(val-min_value)/(max_value-min_value)), 15)) ||
                                   ' 15,-15 60,0 0,30 -60,0 z' as "d"
                                 )
                               )
                             , xmlelement("text",
                                 xmlattributes(
                                   't1' as "class"
                                 , '#FFFFFF' as "fill"
                                 , 10+210+7 as "x"
                                 , 10 + 35*(item_id-1) - 15 + 22 + nvl(round(30*(val-min_value)/(max_value-min_value)), 15) as "y"
                                 )
                               , to_char(val)
                               )
                             , xmlelement("line",
                                 xmlattributes(
                                   15 + 10 + 60+20*(item_id-1) as "x1"
                                 , 10 + 35*(item_id-1) + nvl(round(30*(val-min_value)/(max_value-min_value)), 15) as "y1"
                                 , 210 as "x2"
                                 , 10 + 35*(item_id-1) + nvl(round(30*(val-min_value)/(max_value-min_value)), 15) as "y2"
                                 , 'c1' as "class"
                                 )
                               )
                             )
                   end  
                 , xmlelement("path",
                     xmlattributes(
                       '#'||bg_color as "fill"
                     , 'm ' || 
                       '10,' || to_char(10+35*(item_id-1)) ||' '|| 
                       to_char(60+20*(item_id-1))  || ',0' ||' '||
                       '15,15' ||' '||
                       '-15,15' ||' '||
                       to_char(-(60+20*(item_id-1))) || ',0' || 
                       ' z' as "d"
                     ) 
                   )
                 , xmlelement("text",
                     xmlattributes(
                       't1' as "class"
                     , '#' || fg_color as "fill"
                     , 10+60+20*(item_id-1)-12 as "x"
                     , 10+35*(item_id-1)+22 as "y"
                     )
                   , label_text
                   ) 
                 , xmlelement(noentityescaping "text",
                     xmlattributes(
                       't2' as "class" 
                     ,  '#' || fg_color as "fill"
                     , 15 as "x"
                     , 10+35*(item_id-1)+22 as "y"
                     )
                   , case when min_value is null then '≤'||' '||max_value
                          when max_value is null then '≥'||' '||min_value
                          else min_value || ' à ' || max_value
                     end
                   )  
                 )
               )
             )
           )
           as clob indent
         ) 
  into res
  from dpe_label_item
  ;

  dbms_xslprocessor.clob2file(res, 'TEST_DIR', 'svgtest.svg');

END;
/

Et voici le fichier produit :

<svg xmlns="http://www.w3.org/2000/svg">
  <style type="text/css"><![CDATA[path {fill-opacity:1;stroke:none;}
text {font-style:normal;font-weight:normal;font-family:Arial;fill-opacity:1;stroke:none;}
text.t1 {font-size:20px;}
text.t2 {font-size:12px;}
line.c1 {stroke:black;stroke-width:1;stroke-opacity:1;stroke-dasharray: 5,3;}]]></style>
  <g transform="scale(2)">
    <path fill="#00FF00" d="m 10,10 60,0 15,15 -15,15 -60,0 z"/>
    <text class="t1" fill="#000000" x="58" y="32">A</text>
    <text class="t2" fill="#000000" x="15" y="32">≤ 50</text>
    <path fill="#4DFF00" d="m 10,45 80,0 15,15 -15,15 -80,0 z"/>
    <text class="t1" fill="#000000" x="78" y="67">B</text>
    <text class="t2" fill="#000000" x="15" y="67">51 à 90</text>
    <path fill="#B3FF00" d="m 10,80 100,0 15,15 -15,15 -100,0 z"/>
    <text class="t1" fill="#000000" x="98" y="102">C</text>
    <text class="t2" fill="#000000" x="15" y="102">91 à 150</text>
    <path fill="#000000" d="m 210,134 15,-15 60,0 0,30 -60,0 z"/>
    <text class="t1" fill="#FFFFFF" x="227" y="141">200</text>
    <line x1="145" y1="134" x2="210" y2="134" class="c1"/>
    <path fill="#FFFF00" d="m 10,115 120,0 15,15 -15,15 -120,0 z"/>
    <text class="t1" fill="#000000" x="118" y="137">D</text>
    <text class="t2" fill="#000000" x="15" y="137">151 à 230</text>
    <path fill="#FFB300" d="m 10,150 140,0 15,15 -15,15 -140,0 z"/>
    <text class="t1" fill="#000000" x="138" y="172">E</text>
    <text class="t2" fill="#000000" x="15" y="172">231 à 330</text>
    <path fill="#FF4D00" d="m 10,185 160,0 15,15 -15,15 -160,0 z"/>
    <text class="t1" fill="#000000" x="158" y="207">F</text>
    <text class="t2" fill="#000000" x="15" y="207">331 à 450</text>
    <path fill="#FF0000" d="m 10,220 180,0 15,15 -15,15 -180,0 z"/>
    <text class="t1" fill="#FFFFFF" x="178" y="242">G</text>
    <text class="t2" fill="#FFFFFF" x="15" y="242">≥ 451</text>
  </g>
</svg>

Avec le rendu suivant dans un explorateur compatible : svgtest.svg

 

Advertisements
Categories: Miscellaneous Tags: , , , , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s