Create GIS database in PostgreSQL

1. Install PostgreSQL database (for example 9.6)
2. Install postgis module (for example 2.5.1)
3. Start pgAdmin 4
4. Create database
5. Enable GIS features in database with following command:
CREATE EXTENSION postgis;
6. Now shapefile can be loaded with PostGIS 2.0 shapefile tool
7. Set GEO input format (for example Rijksdriehoek)
SELECT UpdateGeometrySRID(‘wegvakken’,’geom’, 28992);
8. Create WGS84 geog column and fill it.
ALTER TABLE wegvakken ADD COLUMN geog geography(geometry);
UPDATE wegvakken SET geog = ST_Transform(geom, 4326);
CREATE INDEX wegvakken_geog_idx ON wegvakken USING GIST (geog);
9. Now pgAdmin4 can show GEO layer with OpenStreetMap layer as base.

10. Set center point of RijksDriehoek (Onze Lieve Vrouwetoren (‘Lange Jan’) in Amersfoort.)
SELECT * FROM wegvakken ORDER BY geom <-> ‘SRID=28992;POINT(155000 463000)’::geometry LIMIT 1;

Experia box v8 inbound traffic fails

My Experia Box v8 worked fine last 3 years but now it is failing every few days. All inbound traffic is then blocked (my websites are not accessible anymore) because it can not reach the KPN update server. See below TR069 error. If this occur the router blocks automatic all inbound traffic, preventing any access to my servers. I called the KPN helpdesk and they are willing to replace my old Expiria Box v8 with a v10. Looking forward to receive this new router as soon as possible.

Update 04-01-2018:
I received a new router. Too bad they sent a v8 version and not the promised v10. So i called the service desk again. They promised me again to send a v10, next week.

Update 09-01-2018:
Today i received the Experia box v10. Installed it directly. So now all services are back to normal 🙂

01/02/2019 12:33:39 TR069:Inform Fail!!(Invalid URL or ACS unreachable)
01/02/2019 12:33:39 TR069:Remove host rijswijk1.cust.kpn.net dns record.
01/02/2019 12:33:39 TR069:Sending 1 BOOT inform.

PlaatSign 1.1

The next version of PlaatSign is released and contain the following changes:
– Added parameter field to script content form.
– The parameter will used during execution of script
– Uploading script more the ones is now alowed again
– Added KerkinGouda demo script
– Added PlaatSoft demo script
– Update copyright banners

Click here to download the latest version.

New website Look-and-Feel

Today this website has been renewed. The layout has been given a modern dark look. The website is now also suitable for tablets and smartphones. Depending on the device type, the look and feel of the information is adjusted. In the short term, the server hardware on which this website is running will also be modernized so that the interaction is also accelerated. 

Raspberry Pi Tower


My Raspberry Pi Tower is growing steadily. At this moment I have 4 Raspberry Pi’s (mini computer) running. One Pi is designed as an Access Point to the Internet and provides the SSL offloading. Furthermore, he routes the HTTP traffic via an Apache Reverse Proxy to the underlying three Pi’s. These Pi’s are arranged as web server and provide 22 websites. The advantage of this solution is that the total energy consumption (including gigabit switch) is approximately 30 Watt and the websites still provide a good response.

The following domains are hosted on the above hardware. Each domain also has a number of subdomains
PlaatSoft.nl
PlaatResort.nl
WarQuest.nl
KerkInGouda.nl
StichtingOpenHuisGouda.nl

PlaatSign 1.0

The next version of PlaatSign is released and contain the following changes:

  • Added energy day consumption script
  • Added energy month consumption script
  • Added energy year consumption script
  • Added gouda weatherstation script
  • Added broodkruimels script
  • Improve file use on disk
  • Added feature to enable / disable content
  • Added plaatenergy database settings to settings page
  • Updated copyrights banner
  • Added new version check

Click here to download the latest version.

PlaatEnergy 1.4

The next version of PlaatEnergy is released and contain the following changes:

– Added energy1_detail table to measure 3 phases (voltage, current and power) of 380VAC connection
– Added voltage day chart
– Added current day chart
– Added power day chart
– Added extra energy setting to switch on/off detail information

Click here to download the latest version.

Windows 10 upgrade takes hours

This afternoon my Quad Core 3GHZ Desktop finally received the Windows 10 creator patch. It took my desktop more than 4 hours to install it. Why is a Windows 10 patch always so slow? If i upgrade a Raspberry Pi Quad Core 1.2GHZ Linux kernel it’s only take 10 minutes. Strange that low end linux hardware upgrades are must faster than high end Windows hardware. I think that the Microsoft staff must go on training to learn how to patch Operating Systems fast and without reboots 🙂

Raspberry Pi Noobs cleanup

If you want to have a nice and clean Raspberry Pi Noobs installation. Execute te followings commands to remove unless tools:

sudo apt-get purge wolfram-engine (Reduce image size with 683MB)
sudo apt-get purge sonic-pi (Reduce image size with 151MB)
sudo apt-get purge scratch (Reduce image size with 93MB)
sudo apt-get autoclean

This initial Noobs image size will now be arround 3GB 🙂

Google Charts zoom in dataset example

google-chartsI took me a while to understand how Google Charts can be extended with a zoom/drill in data set feature. You have to add then google.visualization.events.addListener method which fetch the selected datapoint of a graph. With some extra javascript code you can reload the chart with a more detailed dataset. See example below!

<script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["bar"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {

      var options = {
          bars: "vertical",
          bar: {groupWidth: "90%"},
          legend: { position: "none"},
          vAxis: {format: "decimal"},
          isStacked:true,colors: ["#0066cc", "#808080"],vAxis: { format:"decimal", viewWindow: { min: 0, max: ".round($max_forecast+100)." } }, series: {
            3: {
                targetAxisIndex: 1
            }
          }
        };

        var data = google.visualization.arrayToDataTable([['','Low (kWh)','Normal (kWh)','Solar (kWh)','Forecast (kWh)'],['2006',0,0,0,0],['2007',0,0,0,0],['2008',0,0,0,0],['2009',0,0,0,0],['2010',0,0,0,0],['2011',0,0,0,0],['2012',0,0,0,0],['2013',0,0,0,0],['2014',0,0,0,0],['2015',0,0,0,0],['2016',20,20,11,278.75]]);
        var chart = new google.charts.Bar(document.getElementById("chart_div"));
        chart.draw(data, google.charts.Bar.convertOptions(options));

        google.visualization.events.addListener(chart, "select", selectHandler);

        function selectHandler(e)     {
           var year = data.getValue(chart.getSelection()[0].row, 0);
           link("pid=30&eid=88&date="+year+"-1-1");
        }
      }
function link(value)
{
	var form = document.forms['plaatenergy'];
	var newInput = document.createElement('input');
	newInput.setAttribute('type','hidden');
	newInput.setAttribute('name','token');
	newInput.setAttribute('value',value);
	form.appendChild(newInput);		
		
	form.submit();
}

PlaatProtect 0.3

This is the next release of PlaatProtect. The release contain the following changes and bug fixes:
– Improve new version detection script
– Improve php cron job. Now no output is created anymore
– Added battery chart for all zwave devices
– Added temperature chart for all zwave devices
– Added humidity chart for all zwave devices
– Added lumaniance chart for all zwave devices
– Added location of zwave sensors on home page
– Protect scenario and panic buttons against accidental click
– Bugfix: Fix critical bug in event.php in sleep and away mode
– Bugfix: Temperature above 25.5c is now correctly measured

Click here to download the latest version.

Java KnightsQuest 0.3

This evening the third beta is released of KnightQuest. It contain the following new features:
– Added 3 screen resolutions.
– Increase amount of maps to 60.
– Improve game play. Now maps must be unlocked.
– Pawn can now move two land tills per turn.
– Added harbors so soldiers can travel faster.
– Game settings and progress are now stored on disk.
– Bugfix: Second game initialization is working fine.
– Bugfix: Now autmatic next turn detection is working fine.

Click here to download the latest version.

Java KnightsQuest 0.2

This evening the second beta is released of KnightQuest. It contain the following new features:
– Added human player functionality.
– Added information box on game screen.
– Improve bots behavior.
– Added new background.
– Improve help, credits, release notes and donate page.
– Added intro movie.
– Improve network detection.
– Music can now be switched off / on.

Click here to download the latest version.