I have been parsing Canada's radio system frequency open data. It's a single large table per province, containing point geometries for each station callsign. Where a point-to-point link is defined, the record's linkid contains the remote station call sign. Here are a couple of very truncated and simulated records by way of explanation:
callsign linkid locname geometry ========== ====== ============== =========================== CGX934 XLQ644 APSLEY, ON POINT(-78.090833 44.755556) XLQ644 CGX934 NEPHTON, ON POINT(-78.010833 44.622222) CIN256 CIJ933 MISSISSAUGA POINT(-79.593611 43.642778) CIJ933 CIN256 NAKINA ON POINT(-86.839167 50.170833)
Is it possible to use Spatialite to query the DB to return linestrings of the links? Something like:
linkname geometry ================== ====================================================== CGX934-XLQ644 LINESTRING(-78.090833 44.755556, -78.010833 44.622222) CIN256-CIJ933 LINESTRING(-79.593611 43.642778, -86.839167 50.170833)
Currently I'm manually linking using a Perl program on the text table, which may produce pretty results, but is slow and not very reliable.