Skip to main content

Bulk import

IMPORT runs a bulk load on the server as a background job: the statement returns a job id immediately, progress streams on the x_jobs channel, and the same job can be driven from any SQL client, the bundled xcon-db-import CLI, or the admin console's Import page.

IMPORT OSM FROM 'https://download.geofabrik.de/europe/turkey-latest.osm.pbf' INTO osm_nodes;
-- → one row: the job id (imp-…)

SHOW IMPORTS;             -- id, kind, table, state, rows, bytes, elapsed, error
CANCEL IMPORT 'imp-…';    -- the worker stops at its next block
LISTEN x_jobs;            -- ≤1 progress event/second + one terminal event, JSON payload
  • The source is a server-side path or an http(s) URL — the server streams it; nothing is uploaded through the client.
  • The job runs in the session's database (-db / the console's database selector). Admins and the database owner may import; one import per database runs at a time.
  • Jobs are not persisted: a node restart forgets running imports.

OSM​

IMPORT OSM reads .osm.pbf nodes (ways/relations are skipped) into a FLEXIBLE rows table created on first use: osm_id INT, pos GEO, and a curated set of tag columns (name, amenity, shop, highway, addr_*, … — keys mapped to identifier rules: addr:city → addr_city). OSM's long-tail tags fold into one tags JSON column instead of exploding the schema — Türkiye alone carries thousands of distinct keys. Options, combinable in one WITH (...):

  • ALL NODES — keep tagless nodes (way skeletons, most of the file)
  • ALL TAGS — a column per tag instead of allowlist + tags JSON

The result is immediately queryable with the spatial filters:

SELECT name FROM osm_nodes WHERE GEO_DWITHIN(pos, 41.0151, 28.9795, 5000);

OSM highways​

IMPORT OSM HIGHWAYS is the roads pass: one row per node of every highway-tagged way — way_id, seq, pos GEO, plus highway, name, maxspeed, oneway, ref, lanes, surface as present. Nearest-road / speed-limit queries are then one statement:

IMPORT OSM HIGHWAYS FROM '/data/turkey-latest.osm.pbf' INTO roads;

SELECT maxspeed, name, geo_distance FROM roads
  WHERE GEO_DWITHIN(pos, 41.0086, 28.9802, 200)
  ORDER BY GEO_DISTANCE LIMIT 1;

Ways carry node ids, not coordinates, so this pass caches node cells in memory (~50 B/node): regional extracts fit comfortably; a planet-scale highways run is refused with a clear error.

CLI​

xcon-db-import -addr host:5432 -user admin -pass … -db geo \
    -src https://download.geofabrik.de/europe/turkey-latest.osm.pbf \
    -table osm_nodes

Live progress renders from LISTEN x_jobs; Ctrl-C issues CANCEL IMPORT server-side and waits for the terminal event.