### Makefile for getting better CHT cell coverage for Wuyaokeng
## Author: Dan Jacobson https://www.jidanni.org/
## Copyright: https://www.gnu.org/licenses/gpl.html
## Created: 2024-03-07T05:05:45+0000
## Last-Updated: 2024-03-30T23:12:29+0000
##     Update #: 461
##
## This file is all hastily hacked up!

# We are going to draw a slice of a circle, simulating a cell tower antenna coverage icon!
U = ../../../geo/house_numbering/grids/utilities
include $U/m1.makefile
SHELL = /bin/bash
T=120.86771 24.18613#CHT 6627 cell tower
t=/tmp/ddd
s=sqlite3
db=db.sqlite
# file:///usr/share/doc/sqlite3/lang_corefunc.html
# file:///usr/share/doc/sqlite3/lang_datefunc.html
CO=	-oo X_POSSIBLE_NAMES=lon \
	-oo Y_POSSIBLE_NAMES=lat
KO=-f LIBKML

NS.vik:
NS.kmz:pt2wedge
	radius=100 ./$<
	ogrmerge.py -overwrite_ds -f LIBKML -o $@ ??.kml #too chatty
NS.vik:
%.vi:%.gpx
	killall -w viking||:
	cd /tmp && nohup viking -e $(PWD)/$? & sleep 11
# /usr/share/doc/gpsbabel/html/fmt_kml.html
# /usr/share/doc/gpsbabel/html/fmt_gpx.html

# ~/Downloads/gdal-docs-master/drivers/vector/gpx.html
# ~/Downloads/gdal-docs-master/drivers/vector/kml.html
# ~/Downloads/gdal-docs-master/drivers/vector/libkml.html
chtpoor.vi:
chtpoor.gpx:
%.gpx:%.csv
	gpsbabel -i unicsv -f $< -o gpx -F -
#	ogr2ogr $@ $< $(CO) -dsco GPX_USE_EXTENSIONS=YES
chtpoor.csv:$(db) #Makefile
#	echo "SELECT lon,lat,CONCAT(substr(short_cell_id,3),rsrp) AS Name
	echo "SELECT * \
	FROM t1 WHERE mnc = 92 AND net_type = 'LTE' AND (0 + rsrp) >= -105;"|\
	$s -csv -header $< > $@

oldF=\
short_cell_id,\
          rnc,\
          psc,\
          asu,\
          dbm,\
           ta,\
      bearing,\
       device,\
         rsrp,\
         rsrq,\
         rssi,\
	measured_at,\
        speed,\
        arfcn
F=\
substr(short_cell_id,3) AS id,\
         rsrp
ruo.vik: #See CHT's combined reception all along the route:
ruo.csv:$(db) Makefile
	echo "SELECT lon,lat,rsrp AS Name FROM t1 WHERE device = \
	'samsung SM-A136U';"|$s -csv -header $< > $@
ruo6:$(db)
	echo "SELECT $F FROM t1 WHERE id = 6627 \
	ORDER BY measured_at;"
col=column --table --separator=,
qq:$(db)
	echo "SELECT $F FROM t1 WHERE mnc = 92 \
	ORDER BY measured_at LIMIT 9999;"|$s -csv $?|$(col)
WW=mnc,lac,short_cell_id,rnc,psc,device,rsrp,measured_at
aday:$(db)
	echo "SELECT $(WW) FROM t1 ORDER BY measured_at;"|$s -csv $?|column --table --separator=,
q:$(db)
	echo "SELECT * FROM t1 WHERE mnc = 92 AND 0 + rsrp < -105 \
	ORDER BY rsrp DESC LIMIT 1;"|$s $? -line
$(db): ~/trips/2024/TowerCollector/2024-03-22-*
	set -uxe $^; { zcat $$1|sed 1q; zcat $$@|grep 2024-03-21T|perl -F, -anwle \
	'if ($$F[11]=~/120\.8[5-7]/ && $$F[11] > 120.855){print;}'; } > $t
	echo .import $t t1 | $s -csv $@
158_159.vik:
158_159.kmz:6627.kmz
	./pt2wedge
#	ogr2ogr 6627.kmz $< -f LIBKML -oo X_POSSIBLE_NAMES=field_1 \
	-oo Y_POSSIBLE_NAMES=field_2 -sql 'SELECT field_3 AS Name FROM "6627"'
#	for i in 15??.kml $<; do \
	ogr2ogr -f LIBKML -append 6627.kmz $$i; done
	ogrmerge.py -overwrite_ds -f LIBKML -o $@ 15??.kml $< #too chatty
oldNS.kmz:pt2wedge
	radius=100 N=24.182203\ 120.865779 S=24.181395\ 120.865800 ./$<
	ogrmerge.py -overwrite_ds -f LIBKML -o $@ ??.kml #too chatty
6627.csv:
	echo $T|perl -anwle 'print join ",", @F, "6627";' > $@
# https://gdal.org/drivers/vector/csv.html#building-line-geometries
clean:
	rm *.csv *.kml *.sqlite
# To visit
tour.vik:
/tmp/tour.csv:tour.csv
	echo X,Y,Name > $@
	cat $^ >> $@
tour.csv:Makefile; echo "$(tour_points)"|tr \; \\n|perl -anwle 'print join ",", @F[1,0,2];' > $@
.PRECIOUS: %.gpx %.kmz
tour_points=\
24.181770 120.866170 上埤枝39;\
24.186130 120.867710 6627;\
24.194537 120.871208 369巷;\
24.190397 120.871878 4K;\
24.189585 120.874045 5K;\
24.192110 120.874936 中埤枝130;\
24.198109 120.876878 慶福枝58聰;\
24.199469 120.875655 慶福枝63