| 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 |