def load_data(dsn, threads): """ Copy data into the word and placex table. """ sel = selectors.DefaultSelector() # Then copy data from place to placex in <threads - 1> chunks. place_threads = max(1, threads - 1) for imod in range(place_threads): conn = DBConnection(dsn) conn.connect() conn.perform( pysql.SQL("""INSERT INTO placex ({columns}) SELECT {columns} FROM place WHERE osm_id % {total} = {mod} AND NOT (class='place' and (type='houses' or type='postcode')) AND ST_IsValid(geometry) """).format(columns=_COPY_COLUMNS, total=pysql.Literal(place_threads), mod=pysql.Literal(imod))) sel.register(conn, selectors.EVENT_READ, conn) # Address interpolations go into another table. conn = DBConnection(dsn) conn.connect() conn.perform( """INSERT INTO location_property_osmline (osm_id, address, linegeo) SELECT osm_id, address, geometry FROM place WHERE class='place' and type='houses' and osm_type='W' and ST_GeometryType(geometry) = 'ST_LineString' """) sel.register(conn, selectors.EVENT_READ, conn) # Now wait for all of them to finish. todo = place_threads + 1 while todo > 0: for key, _ in sel.select(1): conn = key.data sel.unregister(conn) conn.wait() conn.close() todo -= 1 print('.', end='', flush=True) print('\n') with connect(dsn) as conn: with conn.cursor() as cur: cur.execute('ANALYSE')
def load_data(dsn, data_dir, threads): """ Copy data into the word and placex table. """ # Pre-calculate the most important terms in the word list. db_utils.execute_file(dsn, data_dir / 'words.sql') sel = selectors.DefaultSelector() # Then copy data from place to placex in <threads - 1> chunks. place_threads = max(1, threads - 1) for imod in range(place_threads): conn = DBConnection(dsn) conn.connect() conn.perform("""INSERT INTO placex ({0}) SELECT {0} FROM place WHERE osm_id % {1} = {2} AND NOT (class='place' and type='houses') AND ST_IsValid(geometry) """.format(_COPY_COLUMNS, place_threads, imod)) sel.register(conn, selectors.EVENT_READ, conn) # Address interpolations go into another table. conn = DBConnection(dsn) conn.connect() conn.perform("""INSERT INTO location_property_osmline (osm_id, address, linegeo) SELECT osm_id, address, geometry FROM place WHERE class='place' and type='houses' and osm_type='W' and ST_GeometryType(geometry) = 'ST_LineString' """) sel.register(conn, selectors.EVENT_READ, conn) # Now wait for all of them to finish. todo = place_threads + 1 while todo > 0: for key, _ in sel.select(1): conn = key.data sel.unregister(conn) conn.wait() conn.close() todo -= 1 print('.', end='', flush=True) print('\n') with connect(dsn) as conn: with conn.cursor() as cur: cur.execute('ANALYSE')
def add_tiger_data(data_dir, config, threads): """ Import tiger data from directory or tar file `data dir`. """ dsn = config.get_libpq_dsn() sql_files, tar = handle_tarfile_or_directory(data_dir) if not sql_files: return with connect(dsn) as conn: sql = SQLPreprocessor(conn, config) sql.run_sql_file(conn, 'tiger_import_start.sql') # Reading sql_files and then for each file line handling # sql_query in <threads - 1> chunks. sel = selectors.DefaultSelector() place_threads = max(1, threads - 1) # Creates a pool of database connections for _ in range(place_threads): conn = DBConnection(dsn) conn.connect() sel.register(conn, selectors.EVENT_WRITE, conn) for sql_file in sql_files: if not tar: file = open(sql_file) else: file = tar.extractfile(sql_file) handle_threaded_sql_statements(sel, file) # Unregistering pool of database connections handle_unregister_connection_pool(sel, place_threads) if tar: tar.close() print('\n') LOG.warning("Creating indexes on Tiger data") with connect(dsn) as conn: sql = SQLPreprocessor(conn, config) sql.run_sql_file(conn, 'tiger_import_finish.sql')