script/max_points_per_tile_zoom.pl untested

filter: Statementstmt 0.0% 0 / 65 Branchbran 0.0% 0 / 4 Conditioncond n/a MC/DCmcdc n/a Subroutinesub 0.0% 0 / 8 Podpod 0.0% 0 / 2 totaltotal 0.0% 0 / 79 CC 2 SCAR 10.6
CC
2
Cov
0%
max_points_per_tile
6.0
get_points
2.0
BEGIN
2.0
CRAP 2.9
1 use strict;
2 use warnings;
3 use Geo::Heatmap::USNaviguide_Google_Tiles;
4 use Data::Dumper;
5 use DBI;
6 use YAML;
7  
8 my $dbh = DBI->connect("dbi:Pg:dbname=gisdb", 'gisdb', 'gisdb', {AutoCommit => 0});
9  
10 ## where are most of my points:
11 # we use ST_GeoHash with a length of 5 (about city size)
12 # to group the points
13 # as there may be points which ST_GeoHash can not handle we check the borders
14 # where most of the points are ie points with the same hash
15 # select the Box (ST_Extent) covering all this points
16 # to be more sure one could use more rows
17  
18  
19 my $sth = $dbh->prepare(qq(
20 with geohash as (
21 select ST_GeoHash(geom::geometry, 5) st_geohash,
22 geom
23 from geodata where
24 not(St_X(geom) < -180 or St_Y(geom)< -90 or St_X(geom) > 180 or St_Y(geom) > 90)
25 )
26 SELECT ST_Extent(geom) as extent FROM geohash
27 where st_geohash =
28 (select st_geohash from
29 (select st_geohash, count(*) c
30 from geohash
31 group by st_geohash
32 order by c desc limit 1) max_geohash)
33 )
34 );
35  
36 $sth->execute();
37 my @p;
38 my @r = $sth->fetchrow;
39 my ($lats, $lngw, $latn, $lnge) = ($r[0] =~/BOX\((.+?) (.+?),(.+?) (.+?)\)/);
40 $sth->finish;
41  
42 print STDERR "$lats, $lngw, $latn, $lnge\n";
43  
44 ## 48.164089, 16.3476612, 48.2080069, 16.391593
45 ## my $lats = 48.164089;
46 ## my $latn = 48.2080069;
47 ## my $lngw = 16.3476612;
48 ## my $lnge = 16.391593;
49  
50 my @gt;
51  
52 my $max_per_level = {};
53 for (my $zoomlevel = 0; $zoomlevel <=18; $zoomlevel++)
54 {
55 my $max = 0;
56 $max_per_level->{$zoomlevel} = 0;
57 @gt = Google_Tiles($lats, $lngw, $latn, $lnge, $zoomlevel);
58
59 foreach my $tile (@gt) {
60 $max = max_points_per_tile($tile->{NAMEX}, $tile->{NAMEY}, $zoomlevel);
61 $max_per_level->{$zoomlevel} = $max if $max > $max_per_level->{$zoomlevel};
62 }
63 printf STDERR "Tile found [%s] Zoom [%s] max points [%s]\n", scalar @gt, $zoomlevel, $max;
64 }
65 print Dump $max_per_level;
66  
67 sub max_points_per_tile {
68 my ($x, $y, $z) = @_;
69  
70 my $value = &Google_Tile_Factors($z, 0) ;
71  
72 my %r = Google_Tile_Calc($value, $y, $x);
73  
74 my @density;
75 my $bin = 8;
76 my $ps = get_points(\%r);
77 my $max = 0;
78
79 foreach my $p (@$ps) {
80 my @d = Google_Coord_to_Pix($value, $p->[0], $p->[1]);
81 my $ix = $d[1] - $r{PXW};
82 my $iy = $d[0] - $r{PYN};
83 $density[int($ix/$bin)][int($iy/$bin)] ++;
84 $max = $density[int($ix/$bin)][int($iy/$bin)] if $density[int($ix/$bin)][int($iy/$bin)] > $max;
85 }
86 return $max;
87 }
88  
89 sub get_points {
90 my $r = shift;
91 my $sth = $dbh->prepare( qq(select ST_AsEWKT(geom) from geodata
92 where geom &&
93 ST_SetSRID(ST_MakeBox2D(ST_Point($r->{LATN}, $r->{LNGW}),
94 ST_Point($r->{LATS}, $r->{LNGE})
95 ),4326))
96 );
97  
98 $sth->execute();
99 my @p;
100 while (my @r = $sth->fetchrow) {
101 my ($x, $y) = ($r[0] =~/POINT\((.+?) (.+?)\)/);
102 push (@p, [$x ,$y]);
103 }
104 $sth->finish;
105 return \@p;
106 }
107