PlaatService installation manual

step 1: Setup debian buster server

Set hostname
step 2: sudo vi /etc/hostname

Set hostname, domainname
step 3: sudo vi /etc/hosts

Set fix ip address and gateway ip address
step 4: sudo vi /etc/dhcpcd.conf

Install java
step 5: sudo apt-get install galternatives openjdk-8-jdk

Install mysql
step 6: sudo apt-get install mariadb-server

Create database
step 7: sudo mysql -u root
CREATE DATABASE plaatservice;
CREATE USER ‘plaatservice’@’localhost’ IDENTIFIED BY ‘plaatservice’;
GRANT ALL PRIVILEGES ON plaatservice.* TO ‘plaatservice’@’localhost’;
FLUSH PRIVILEGES;

Deploy service
step 8: cp plaatservice.jar /home/pi

Start Service
step 9: sudo nohup java -jar plaatservice.jar

PlaatProtect 0.5

This is the next release of PlaatProtect. The release contain the following changes and bug fixes:
– Added zigbee sensor inventory detection
– Added zigbee motion sensor support
– Added zigbee battery view
– Added daily database backup
– Improve table look-and-feel
– Improve email alarm notification
– Improve hue bulb alarm notification

Click here to download the latest version.

PlaatProtect 0.4

This is the next release of PlaatProtect. The release contain the following changes and bug fixes:
– Added system name setting
– Login username can now also be defined
– Sensors views can now be enabled/disable in settings
– Improve all views. Now all sensors data is showed in one chart
– Improve main menu
– Improve cron job
– Added zigbee lightbulb inventory detection
– Improve database table structure

Click here to download the latest version.

PlaatSign action needed after OS upgrade

I notest that after a Raspberry Pi Operating System upgrade the automatic console login is disabled by default. PlaatSign needs this option to start the HDMI output after a reboot. To enable this feature again, do the following steps:

– login the raspberry pi
– type “sudo raspi-config”
– Select option 3 “Boot Options”
– Select option B1 “Desktop / CLI”
– Select option B2 “Console Autologin”
– Select option “OK”
– Then reboot the Raspberry Pi.
– Now PlaatSign is working fine again.

GeoServer + PostgreSQL + OpenStreetMap

To load an openstreetmap datasource in PostgreSQL/PostGIS use the following command.

# osm2pgsql –slim –username XXX –password –database streetmap ./zuid-holland-latest.osm.pbf

osm2pgsql version 0.96.0 (64 bit id space)

Password:
Allocating memory for sparse node cache
Node-cache: cache=800MB, maxblocks=12800*65536, allocation method=9
Mid: pgsql, cache=800
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels
Using built-in tag processing pipeline
Using projection SRS 3857 (Spherical Mercator)
Setting up table: planet_osm_point
Setting up table: planet_osm_line
Setting up table: planet_osm_polygon
Setting up table: planet_osm_roads

Reading in file: ./zuid-holland-latest.osm.pbf
Using PBF parser.
Processing: Node(15439k 270.9k/s) Way(2349k 71.20k/s) Relation(17020 1547.27/s) parse time: 101s
Node stats: total(15439302), max(6336341363) in 57s
Way stats: total(2349653), max(676644693) in 33s
Relation stats: total(17679), max(9386429) in 11s
Sorting data and creating indexes for planet_osm_point
Stopping table: planet_osm_nodes
Sorting data and creating indexes for planet_osm_roads
Sorting data and creating indexes for planet_osm_polygon
Stopping table: planet_osm_ways
Sorting data and creating indexes for planet_osm_line
Stopping table: planet_osm_rels
Stopped table: planet_osm_nodes in 0s
Building index on table: planet_osm_ways
Building index on table: planet_osm_rels
Copying planet_osm_roads to cluster by geometry finished
Creating geometry index on planet_osm_roads
Stopped table: planet_osm_rels in 2s
Creating osm_id index on planet_osm_roads
Creating indexes on planet_osm_roads finished
All indexes on planet_osm_roads created in 3s
Completed planet_osm_roads
Copying planet_osm_line to cluster by geometry finished
Creating geometry index on planet_osm_line
Copying planet_osm_point to cluster by geometry finished
Creating geometry index on planet_osm_point
Creating osm_id index on planet_osm_line
Creating indexes on planet_osm_line finished
All indexes on planet_osm_line created in 22s
Completed planet_osm_line
Creating osm_id index on planet_osm_point
Creating indexes on planet_osm_point finished
All indexes on planet_osm_point created in 65s
Completed planet_osm_point
Stopped table: planet_osm_ways in 98s
Copying planet_osm_polygon to cluster by geometry finished
Creating geometry index on planet_osm_polygon
Creating osm_id index on planet_osm_polygon
Creating indexes on planet_osm_polygon finished
All indexes on planet_osm_polygon created in 252s
Completed planet_osm_polygon

Osm2pgsql took 355s overall
node cache: stored: 15439302(100.00%), storage efficiency: 50.00% (dense blocks: 0, sparse nodes: 15439302), hit rate: 100.00%

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;

11. select ST_X(geom), ST_Y(geom) from shapefile

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.