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