vyQZZCEe

1# -*- coding: utf-8 -*-
2
3import io
4import json
5import re
6import sqlite3
7import sys
8import time
9import os
10import zlib
11from PIL import Image
12import numpy as np
13
14def mbtiles_setup(cur):
15    cur.execute("""
16        CREATE TABLE tiles (
17            zoom_level integer,
18            tile_column integer,
19            tile_row integer,
20            tile_data blob
21        );
22        """)
23    cur.execute("""
24        CREATE TABLE metadata (
25            name text,
26            value text
27        );
28        """)
29    cur.execute("""
30        CREATE TABLE grids (
31            zoom_level integer,
32            tile_column integer,
33            tile_row integer,
34            grid blob
35        );
36        """)
37    cur.execute("""
38        CREATE TABLE grid_data (
39            zoom_level integer,
40            tile_column integer,
41            tile_row integer,
42            key_name text,
43            key_json text
44        );
45        """)
46    cur.execute("""
47        CREATE UNIQUE INDEX name ON metadata (
48            name
49        );
50        """)
51    cur.execute("""
52        CREATE UNIQUE INDEX tile_index ON tiles (
53            zoom_level, tile_column, tile_row
54        );
55        """)
56
57def mbtiles_connect(mbtiles_file, silent):
58    try:
59        con = sqlite3.connect(mbtiles_file)
60        return con
61    except Exception as e:
62        if not silent:
63            print("Error: Could not connect to database")
64            print(e)
65        sys.exit(1)
66
67def optimize_connection(cur):
68    cur.execute("PRAGMA synchronous=0")
69    cur.execute("PRAGMA locking_mode=EXCLUSIVE")
70    cur.execute("PRAGMA journal_mode=DELETE")
71
72def compression_prepare(cur, silent):
73    if not silent: 
74        print('Debug: Prepare database compression.')
75        
76    cur.execute("""
77        CREATE TABLE IF NOT EXISTS images (
78            tile_data blob,
79            tile_id integer
80        );
81        """)
82    
83    cur.execute("""
84        CREATE TABLE IF NOT EXISTS map (
85            zoom_level integer,
86            tile_column integer,
87            tile_row integer,
88            tile_id integer
89        );
90        """)
91
92def optimize_database(cur, silent):
93    if not silent: 
94        print('Debug: analyzing db')
95    cur.execute("ANALYZE;")
96    if not silent: 
97        print('Debug: cleaning db')
98    cur.isolation_level = None
99    cur.execute('VACUUM')
100    cur.isolation_level = ''
101
102def compression_do(cur, con, chunk, silent):
103    if not silent:
104        print('Debug: Making database compression.')
105        
106    overlapping = 0
107    unique = 0
108    total = 0
109    cur.execute("SELECT COUNT(zoom_level) FROM tiles")
110    res = cur.fetchone()
111    total_tiles = res[0]
112    last_id = 0
113    logging.debug("%d total tiles to fetch" % total_tiles)
114    
115    for i in range(total_tiles // chunk + 1):
116        logging.debug("%d / %d rounds done" % (i, (total_tiles / chunk)))
117        ids = []
118        files = []
119        start = time.time()
120        
121        query = """
122            SELECT
123                zoom_level,
124                tile_column,
125                tile_row,
126                tile_data
127            FROM tiles
128            WHERE rowid > ?
129            AND rowid <= ?
130            """
131        cur.execute(query, ((i * chunk), ((i + 1) * chunk)))
132        
133        print("Debug: select: %s" % (time.time() - start))
134        rows = cur.fetchall()
135        for r in rows:
136            total = total + 1
137            if r[3] in files:
138                overlapping = overlapping + 1
139                start = time.time()
140                print("Debug: insert: %s" % (time.time() - start))
141                query = """
142                    INSERT INTO map (
143                        zoom_level,
144                        tile_column,
145                        tile_row,
146                        tile_id
147                    ) VALUES (?, ?, ?, ?)
148                    """
149                cur.execute(query, (r[0], r[1], r[2], ids[files.index(r[3])]))
150                
151            else:
152                unique = unique + 1
153                last_id += 1
154                ids.append(last_id)
155                files.append(r[3])
156                start = time.time()
157                query = """
158                    INSERT INTO images (
159                        tile_id,
160                        tile_data
161                    ) VALUES (?, ?)
162                    """
163                cur.execute(query, (str(last_id), sqlite3.Binary(r[3])))
164                
165                print("Debug: insert into images: %s" % (time.time() - start))
166                start = time.time()
167                
168                query = """
169                    INSERT INTO map (
170                        zoom_level,
171                        tile_column,
172                        tile_row,
173                        tile_id
174                    ) VALUES (?, ?, ?, ?)
175                    """
176                cur.execute(query, (r[0], r[1], r[2], last_id))
177                print("Debug: insert into map: %s" % (time.time() - start))
178        con.commit()
179
180def compression_finalize(cur):
181    print("Debug: Finalizing database compression.")
182    cur.execute("DROP TABLE tiles;")
183    cur.execute("""
184        CREATE VIEW tiles AS
185        SELECT
186            map.zoom_level AS zoom_level,
187            map.tile_column AS tile_column,
188            map.tile_row AS tile_row,
189            images.tile_data AS tile_data
190        FROM map
191        JOIN images
192        ON images.tile_id = map.tile_id;
193        """)
194    cur.execute("""
195        CREATE UNIQUE INDEX map_index ON map (
196            zoom_level,
197            tile_column,
198            tile_row
199        );
200        """)
201    cur.execute("""
202        CREATE UNIQUE INDEX images_id ON images (
203            tile_id
204        );
205        """)
206    cur.execute("VACUUM;")
207    cur.execute("ANALYZE;")
208
209def get_dirs(path):
210    return [name for name in os.listdir(path)
211        if os.path.isdir(os.path.join(path, name))]
212
213def scan_files(path2tiles, cur, image_format, t1, silent):
214    num_scn_tiles = 0
215    num_ins_tiles = 0
216    msg = ''
217    print(path2tiles)
218    for zoom_dir in get_dirs(path2tiles):
219        z = int(zoom_dir)
220        #if z < 12:
221        if True:
222            print("Zoom level: {:}".format(zoom_dir))
223            for row_dir in get_dirs(os.path.join(path2tiles, zoom_dir)):
224                x = int(row_dir)
225                for current_file in os.listdir(os.path.join(path2tiles, zoom_dir, row_dir)):
226                    if current_file == '.DS_Store' and not silent:
227                        print("Warning: Your OS is MacOS,and the .DS_Store file will be ignored.")
228                    else:
229                        file_name, ext = current_file.split('.',1)
230                        if (ext.lower() == image_format):
231                            num_scn_tiles += 1
232                            imgfname = os.path.join(path2tiles, zoom_dir, row_dir, current_file)
233                            img = Image.open(imgfname, "r")
234                            r,g,b,a = img.split()
235                            if a.getextrema() != (0,0):
236                                file_content = io.BytesIO()
237                                img.save(file_content, format='PNG')
238                                file_content = file_content.getvalue()
239                                y = int(file_name)
240                                cur.execute("""
241                                    INSERT INTO tiles (
242                                        zoom_level,
243                                        tile_column,
244                                        tile_row,
245                                        tile_data
246                                    ) VALUES (?, ?, ?, ?);
247                                    """, (z, x, y, sqlite3.Binary(file_content)))
248                                num_ins_tiles += 1
249                            if (num_scn_tiles % 1000) == 0:
250                                for c in msg: sys.stdout.write(chr(8))
251                                td = (time.time()-t1) / 60
252                                msg1 = "Elapsed: {:,.1f} min.".format(td)
253                                msg2 = "{:,} tiles scanned.".format(num_scn_tiles)
254                                msg3 = "{:,} tiles inserted.".format(num_ins_tiles)
255                                msg = msg1 + " " + msg2 + " " + msg3
256                                print(msg)
257    return num_scn_tiles, num_ins_tiles
258
259def disk_to_mbtiles(directory_path, mbtiles_file, silent, compression):
260    if not silent:
261        print("Info: Importing disk to MBTiles")
262        print("Debug: %s --> %s" % (directory_path, mbtiles_file))
263        print('')
264    con = mbtiles_connect(mbtiles_file, silent)
265    cur = con.cursor()
266    optimize_connection(cur)
267    mbtiles_setup(cur)
268    image_format = 'png'
269    t1 = time.time()
270    num_scn_tiles, num_ins_tiles = scan_files(directory_path, cur, image_format, t1, silent)
271    t2 = time.time()                          
272    if not silent:
273        print('')
274        msg = 'Debug: {:,.1f} min elapsed'.format((t2-t1)/60)
275        print(msg)
276        msg = 'Debug: {:,} tiles scanned'.format(num_scn_tiles)
277        print(msg)
278        msg = 'Debug: {:,} tiles inserted'.format(num_ins_tiles)
279        print(msg)
280    if compression:
281        compression_prepare(cur)
282        compression_do(cur, con, 256, silent)
283        compression_finalize(cur)
284    optimize_database(con, silent)
285
286if __name__ == '__main__':
287    trg = 'flood-35-yamaguchi-h23'
288    path2tiles = os.path.join('g:\\', 'projects', 'maps', 'output', trg, trg)
289    mbtiles_file = trg + '.db'
290    silent = False
291    compression = False
292    disk_to_mbtiles(path2tiles, mbtiles_file, silent, compression)