Difference between revisions of "SimpleGraph-Tutorial/Geo"

From BITPlan Wiki
Jump to navigation Jump to search
 
(6 intermediate revisions by the same user not shown)
Line 17: Line 17:
 
=== First step: Loading data from Bundesnetzagentur ===
 
=== First step: Loading data from Bundesnetzagentur ===
 
The [https://www.bundesnetzagentur.de/DE/Sachgebiete/ElektrizitaetundGas/Unternehmen_Institutionen/HandelundVertrieb/Ladesaeulenkarte/Ladesaeulenkarte_node.html ChargingStation Map of Bundesnetzagentur] is based on the Date from the [https://www.bundesnetzagentur.de/SharedDocs/Downloads/DE/Sachgebiete/Energie/Unternehmen_Institutionen/HandelundVertrieb/Ladesaeulen/Ladesaeulenkarte_Datenbankauszug20.xlsx Excel file]
 
The [https://www.bundesnetzagentur.de/DE/Sachgebiete/ElektrizitaetundGas/Unternehmen_Institutionen/HandelundVertrieb/Ladesaeulenkarte/Ladesaeulenkarte_node.html ChargingStation Map of Bundesnetzagentur] is based on the Date from the [https://www.bundesnetzagentur.de/SharedDocs/Downloads/DE/Sachgebiete/Energie/Unternehmen_Institutionen/HandelundVertrieb/Ladesaeulen/Ladesaeulenkarte_Datenbankauszug20.xlsx Excel file]
 +
[https://www.bundesnetzagentur.de/SharedDocs/Downloads/DE/Sachgebiete/Energie/Unternehmen_Institutionen/HandelundVertrieb/Ladesaeulen/Ladesaeulenkarte_Datenbankauszug20.xlsx?__blob=publicationFile&v=2 Download]
 +
 +
Let's read it with our {{Link|target=SimpleGraph-Excel}} Module.
 +
<source lang='java'>
 +
package com.bitplan.simplegraph.geotutorial;
 +
 +
import static org.junit.Assert.assertEquals;
 +
 +
import java.io.File;
 +
 +
import org.junit.Test;
 +
 +
import com.bitplan.simplegraph.core.SimpleNode;
 +
import com.bitplan.simplegraph.excel.ExcelSystem;
 +
 +
public class TestChargingStations {
 +
 +
  boolean debug = true;
 +
 +
  /**
 +
  * test reading the list of registered german charging stations from
 +
  * Bundesnetzagentur
 +
  *
 +
  * @throws Exception
 +
  */
 +
  @Test
 +
  public void testBundesnetzagentur() throws Exception {
 +
    // The original file has some superfluous sheets and the title row is not
 +
    // in the first line so we downloaded and adapted it a bit to avoid to do this in software e.g.
 +
    // as outlined in https://stackoverflow.com/questions/1834971/removing-a-row-from-an-excel-sheet-with-apache-poi-hssf
 +
    // String url = "https://www.bundesnetzagentur.de/SharedDocs/Downloads/DE/Sachgebiete/Energie/Unternehmen_Institutionen/HandelundVertrieb/Ladesaeulen/Ladesaeulenkarte_Datenbankauszug20.xlsx?__blob=publicationFile&v=2";
 +
    File excelFile = new File(
 +
        "src/test/data/Bundesnetzagentur/Ladesaeulenkarte_Datenbankauszug20.xlsx");
 +
    ExcelSystem es = new ExcelSystem();
 +
    es.connect();
 +
    es.moveTo(excelFile.toURI().toString());
 +
    long count = es.g().V().count().next().longValue();
 +
    assertEquals(7733, count);
 +
    if (debug)
 +
      SimpleNode.dumpGraph(es.graph());
 +
      //es.g().V().has("sheetname").out("rows").forEachRemaining(v -> {
 +
      //  SimpleNode.printDebug.accept(v);
 +
      // });
 +
  }
 +
 +
}
 +
 +
</source>
 +
=== Second step: check data agains OpenChargeMap API ===
 +
Let's check the result comparing to the entries in the OpenchargeMap API.
 +
The URL for the API is explained by the following example:
 +
<pre>
 +
http://api.openchargemap.io/v2/poi/?output=json&latitude=50.598&longitude=7.434&maxresults=10
 +
</pre>
 +
==== JUnit Testcase to check the first 20 stations against the API ====
 +
<source lang='java'>
 +
@Test
 +
  public void testOpenChargeMapApi() throws Exception {
 +
    ExcelSystem es=this.getBundesnetzAgenturChargingStations();
 +
    // number of stations to check
 +
    int limit=20;
 +
    // number of stations nearby to get from open chargemap api
 +
    int maxresults = 3;
 +
    es.g().V().has("row").limit(limit).forEachRemaining(v -> {
 +
      if (debug)
 +
        SimpleNode.printDebug.accept(v);
 +
      String address = v.property("Adresse").value().toString();
 +
      String ziploc = v.property("Postleitzahl Ort").value().toString();
 +
      double lat = Double
 +
          .parseDouble(v.property("Breitengrad [DG]").value().toString());
 +
      double lon = Double
 +
          .parseDouble(v.property("Längengrad [DG]").value().toString());
 +
      System.out.println(
 +
          String.format("%30s %30s %.4f %.4f", ziploc, address, lat, lon));
 +
   
 +
      String apiUrl = String.format(Locale.ENGLISH,
 +
          "http://api.openchargemap.io/v2/poi/?output=json&latitude=%.4f8&longitude=%.4f&maxresults=10",
 +
          lat, lon, maxresults);
 +
      JsonSystem js = new JsonSystem();
 +
      try {
 +
        js.connect();
 +
      } catch (Exception e) {
 +
        fail(e.getMessage());
 +
      }
 +
      js.moveTo(apiUrl);
 +
 +
      GraphTraversal<Vertex, Vertex> addressesByDistance = js.g().V()
 +
          .hasLabel("AddressInfo").order().by("Distance");
 +
      assertTrue(addressesByDistance.hasNext());
 +
      Vertex av = addressesByDistance.next();
 +
      double maxDist = 1.0;
 +
      Number adistance = (Number) av.property("Distance").value();
 +
      Number alat = (Number) av.property("Latitude").value();
 +
      Number alon = (Number) av.property("Longitude").value();
 +
      // assertTrue(adistance.doubleValue()<maxDist);
 +
      String azip = av.property("Postcode").value().toString();
 +
      String acity = av.property("Town").value().toString();
 +
      String aadr = av.property("AddressLine1").value().toString();
 +
 +
      System.out.println(String.format("%30s %30s %.4f %.4f: %.3f km",
 +
          azip + " " + acity, aadr, alat.doubleValue(), alon.doubleValue(),
 +
          adistance.doubleValue()));
 +
 +
      if (debug) {
 +
        SimpleNode.printDebug.accept(av);
 +
        SimpleNode.dumpGraph(js.graph());
 +
      }
 +
 +
    });
 +
 +
  }
 +
 +
</source>
 +
==== Result ====
 +
<pre>
 +
                  71732 Tamm            Frankfurter Str. 8 48,9255 9,1507
 +
                    71732 Tamm          Frankfurter Straße 10 48,9257 9,1498: 0,043 km
 +
                  12103 Berlin              Ringbahnstr. 131 52,4686 13,3753
 +
                  12103 Berlin                Alboinstraße 5 52,4695 13,3713: 0,177 km
 +
              64295 Darmstadt        Bessunger Str. 127     49,8588 8,6418
 +
              64295 Darmstadt                    Am Pelz 75 49,8520 8,6340: 0,586 km
 +
                01067 Dresden            Hamburger Straße 30 51,0599 13,6989
 +
                01139 Dresden      Scharfenberger Straße 66 51,0725 13,6880: 0,988 km
 +
              53340 Meckenheim                  Hauptstr. 79 50,6214 7,0223
 +
              53340 Meckenheim          Siebengebirgsring 6A 50,6253 7,0438: 0,981 km
 +
          97769 Bad Brückenau                    Gänsrain 2 50,3085 9,7904
 +
          97769 Bad Brückenau                Buchwaldstrasse 50,3066 9,7859: 0,241 km
 +
          92355 Stadt Velburg                  Stadtplatz 3 49,2328 11,6713
 +
                92355 Velburg                  Stadtplatz 3 49,2328 11,6712: 0,009 km
 +
          45661 Recklinghausen                Dieselstrasse 8 51,5700 7,1999
 +
          45661 Recklinghausen                Dieselstraße 8 51,5699 7,1996: 0,017 km
 +
              86720 Nördlingen                Bleichgraben 3 48,8555 10,4924
 +
              86720 Nördlingen                Bleichgraben 3 48,8555 10,4919: 0,022 km
 +
                36093 Künzell          Unterer Ortesweg 19  50,5425 9,7093
 +
                36093 Künzell            Unterer Ortesweg 19 50,5425 9,7093: 0,008 km
 +
            78601 Mahlstetten            Lippachtalstraße 30 48,0751 8,8441
 +
                78589 Dürbheim                  Jahnstraße 8 48,0562 8,7897: 2,834 km
 +
            26655 Westerstede                  Burgstraße 6 53,2656 7,9350
 +
            26655 Westerstede                Zum Stadtpark 2 53,2564 7,9259: 0,746 km
 +
                    71732 Tamm                Asperger Str. 4 48,9195 9,1278
 +
    74321 Bietigheim-Bissingen                Borsigstraße 6 48,9479 9,1352: 1,986 km
 +
              21640 Horneburg                Am Poggenphl 2 53,5041 9,5901
 +
              21640 Horneburg                Am Poggenpohl 2 53,5041 9,5901: 0,003 km
 +
                  12103 Berlin              Ringbahnstr. 131 52,4686 13,3753
 +
                  12103 Berlin                Alboinstraße 5 52,4695 13,3713: 0,177 km
 +
              64295 Darmstadt              Holzhofallee 33b 49,8646 8,6379
 +
              64283 Darmstadt            Hindenburgstraße 1 49,8712 8,6416: 0,479 km
 +
                01069 Dresden                  Lennéstraße 1 51,0437 13,7542
 +
                01069 Dresden                Prager Straße 2 51,0422 13,7352: 0,833 km
 +
              53340 Meckenheim            Siebengebirgsring 4 50,6250 7,0433
 +
              53340 Meckenheim          Siebengebirgsring 6A 50,6253 7,0438: 0,029 km
 +
          45661 Recklinghausen                Dieselstrasse 8 51,5700 7,1999
 +
          45661 Recklinghausen                Dieselstraße 8 51,5699 7,1996: 0,017 km
 +
          97769 Bad Brückenau      Ernst Putz Str. Parkplatz 50,3086 9,7860
 +
          97769 Bad Brückenau                Buchwaldstrasse 50,3066 9,7859: 0,144 km
 +
</pre>
 +
=== Step 3 - use a Rule Engine to check results ===
 +
We'd now like to rate the results for precision and errors.
 +
We'll use [https://dzone.com/articles/rulebook-a-simple-rules-engine-that-leverages-java The Java Rulebook library] for this purpose.

Latest revision as of 18:59, 12 February 2019

OsProject
id  com.bitplan.simplegraph-tutorial-geo
state  
owner  BITPlan
title  SimpleGraph Geo Tutorial
url  https://github.com/BITPlan/com.bitplan.simplegraph-tutorial-geo
version  0.0.1
description  
date  
since  
until  

SimpleGraph Geo Showcase

Openly available Geographic data from different APIs seems to be a good basis for showcasing the SimpleGraph approach.

Charging Stations example

We'd like to gather data on Charging stations from different sources:

  1. Bundesnetzagentur (Excel File)
  2. Openstreetmap (API)
  3. OpenchargeMap (API)

First step: Loading data from Bundesnetzagentur

The ChargingStation Map of Bundesnetzagentur is based on the Date from the Excel file Download

Let's read it with our SimpleGraph-Excel Module. 

package com.bitplan.simplegraph.geotutorial;

import static org.junit.Assert.assertEquals;

import java.io.File;

import org.junit.Test;

import com.bitplan.simplegraph.core.SimpleNode;
import com.bitplan.simplegraph.excel.ExcelSystem;

public class TestChargingStations {

  boolean debug = true;

  /**
   * test reading the list of registered german charging stations from
   * Bundesnetzagentur
   * 
   * @throws Exception
   */
  @Test
  public void testBundesnetzagentur() throws Exception {
    // The original file has some superfluous sheets and the title row is not
    // in the first line so we downloaded and adapted it a bit to avoid to do this in software e.g.
    // as outlined in https://stackoverflow.com/questions/1834971/removing-a-row-from-an-excel-sheet-with-apache-poi-hssf
    // String url = "https://www.bundesnetzagentur.de/SharedDocs/Downloads/DE/Sachgebiete/Energie/Unternehmen_Institutionen/HandelundVertrieb/Ladesaeulen/Ladesaeulenkarte_Datenbankauszug20.xlsx?__blob=publicationFile&v=2";
    File excelFile = new File(
        "src/test/data/Bundesnetzagentur/Ladesaeulenkarte_Datenbankauszug20.xlsx");
    ExcelSystem es = new ExcelSystem();
    es.connect();
    es.moveTo(excelFile.toURI().toString());
    long count = es.g().V().count().next().longValue();
    assertEquals(7733, count);
    if (debug)
      SimpleNode.dumpGraph(es.graph());
      //es.g().V().has("sheetname").out("rows").forEachRemaining(v -> {
      //  SimpleNode.printDebug.accept(v);
      // });
  }

}

Second step: check data agains OpenChargeMap API

Let's check the result comparing to the entries in the OpenchargeMap API. The URL for the API is explained by the following example: 

http://api.openchargemap.io/v2/poi/?output=json&latitude=50.598&longitude=7.434&maxresults=10

JUnit Testcase to check the first 20 stations against the API

 @Test
  public void testOpenChargeMapApi() throws Exception {
    ExcelSystem es=this.getBundesnetzAgenturChargingStations();
    // number of stations to check
    int limit=20;
    // number of stations nearby to get from open chargemap api
    int maxresults = 3;
    es.g().V().has("row").limit(limit).forEachRemaining(v -> {
      if (debug)
        SimpleNode.printDebug.accept(v);
      String address = v.property("Adresse").value().toString();
      String ziploc = v.property("Postleitzahl Ort").value().toString();
      double lat = Double
          .parseDouble(v.property("Breitengrad [DG]").value().toString());
      double lon = Double
          .parseDouble(v.property("Längengrad [DG]").value().toString());
      System.out.println(
          String.format("%30s %30s %.4f %.4f", ziploc, address, lat, lon));
     
      String apiUrl = String.format(Locale.ENGLISH,
          "http://api.openchargemap.io/v2/poi/?output=json&latitude=%.4f8&longitude=%.4f&maxresults=10",
          lat, lon, maxresults);
      JsonSystem js = new JsonSystem();
      try {
        js.connect();
      } catch (Exception e) {
        fail(e.getMessage());
      }
      js.moveTo(apiUrl);

      GraphTraversal<Vertex, Vertex> addressesByDistance = js.g().V()
          .hasLabel("AddressInfo").order().by("Distance");
      assertTrue(addressesByDistance.hasNext());
      Vertex av = addressesByDistance.next();
      double maxDist = 1.0;
      Number adistance = (Number) av.property("Distance").value();
      Number alat = (Number) av.property("Latitude").value();
      Number alon = (Number) av.property("Longitude").value();
      // assertTrue(adistance.doubleValue()<maxDist);
      String azip = av.property("Postcode").value().toString();
      String acity = av.property("Town").value().toString();
      String aadr = av.property("AddressLine1").value().toString();

      System.out.println(String.format("%30s %30s %.4f %.4f: %.3f km",
          azip + " " + acity, aadr, alat.doubleValue(), alon.doubleValue(),
          adistance.doubleValue()));

      if (debug) {
        SimpleNode.printDebug.accept(av);
        SimpleNode.dumpGraph(js.graph());
      }

    });

  }

Result

                   71732 Tamm             Frankfurter Str. 8 48,9255 9,1507
                    71732 Tamm          Frankfurter Straße 10 48,9257 9,1498: 0,043 km
                  12103 Berlin               Ringbahnstr. 131 52,4686 13,3753
                  12103 Berlin                 Alboinstraße 5 52,4695 13,3713: 0,177 km
               64295 Darmstadt         Bessunger Str. 127     49,8588 8,6418
               64295 Darmstadt                     Am Pelz 75 49,8520 8,6340: 0,586 km
                 01067 Dresden            Hamburger Straße 30 51,0599 13,6989
                 01139 Dresden       Scharfenberger Straße 66 51,0725 13,6880: 0,988 km
              53340 Meckenheim                   Hauptstr. 79 50,6214 7,0223
              53340 Meckenheim           Siebengebirgsring 6A 50,6253 7,0438: 0,981 km
           97769 Bad Brückenau                     Gänsrain 2 50,3085 9,7904
           97769 Bad Brückenau                Buchwaldstrasse 50,3066 9,7859: 0,241 km
           92355 Stadt Velburg                   Stadtplatz 3 49,2328 11,6713
                 92355 Velburg                   Stadtplatz 3 49,2328 11,6712: 0,009 km
          45661 Recklinghausen                Dieselstrasse 8 51,5700 7,1999
          45661 Recklinghausen                 Dieselstraße 8 51,5699 7,1996: 0,017 km
              86720 Nördlingen                 Bleichgraben 3 48,8555 10,4924
              86720 Nördlingen                 Bleichgraben 3 48,8555 10,4919: 0,022 km
                 36093 Künzell           Unterer Ortesweg 19  50,5425 9,7093
                 36093 Künzell            Unterer Ortesweg 19 50,5425 9,7093: 0,008 km
             78601 Mahlstetten            Lippachtalstraße 30 48,0751 8,8441
                78589 Dürbheim                   Jahnstraße 8 48,0562 8,7897: 2,834 km
             26655 Westerstede                   Burgstraße 6 53,2656 7,9350
             26655 Westerstede                Zum Stadtpark 2 53,2564 7,9259: 0,746 km
                    71732 Tamm                Asperger Str. 4 48,9195 9,1278
    74321 Bietigheim-Bissingen                 Borsigstraße 6 48,9479 9,1352: 1,986 km
               21640 Horneburg                 Am Poggenphl 2 53,5041 9,5901
               21640 Horneburg                Am Poggenpohl 2 53,5041 9,5901: 0,003 km
                  12103 Berlin               Ringbahnstr. 131 52,4686 13,3753
                  12103 Berlin                 Alboinstraße 5 52,4695 13,3713: 0,177 km
               64295 Darmstadt               Holzhofallee 33b 49,8646 8,6379
               64283 Darmstadt             Hindenburgstraße 1 49,8712 8,6416: 0,479 km
                 01069 Dresden                  Lennéstraße 1 51,0437 13,7542
                 01069 Dresden                Prager Straße 2 51,0422 13,7352: 0,833 km
              53340 Meckenheim            Siebengebirgsring 4 50,6250 7,0433
              53340 Meckenheim           Siebengebirgsring 6A 50,6253 7,0438: 0,029 km
          45661 Recklinghausen                Dieselstrasse 8 51,5700 7,1999
          45661 Recklinghausen                 Dieselstraße 8 51,5699 7,1996: 0,017 km
           97769 Bad Brückenau      Ernst Putz Str. Parkplatz 50,3086 9,7860
           97769 Bad Brückenau                Buchwaldstrasse 50,3066 9,7859: 0,144 km

Step 3 - use a Rule Engine to check results

We'd now like to rate the results for precision and errors. We'll use The Java Rulebook library for this purpose.

Retrieved from "https://wiki.bitplan.com/index.php?title=SimpleGraph-Tutorial/Geo&oldid=7986"