Linked point geometries to linestrings in Spatialite

Question

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.

Answer 1

Okay, figured this one out. Seems I need a LEFT OUTER JOIN on the same table. I can't create a table with a geometry column immediately, but I can synthesize one and add/recover the geometry later.

My field names have changed a bit from the question, as I now use the fields defined in the documentation. A query that works (acting on the table “nearby”) is:

SELECT s.callsign||'-'||s.linkid AS LINKNAME,
       s.callsign,
       s.linkid,
       s.licensee,
       s.location AS STARTLOC,
       e.location AS ENDLOC,
       s.rx,
       s.tx,
       'LINESTRING('||x(s.geom)||' '||y(s.geom)||','||x(e.geom)||' '||y(e.geom)||')' AS WKT
FROM nearby AS s
LEFT OUTER JOIN nearby AS e ON s.linkid=e.callsign
WHERE s.callsign IS NOT NULL
  AND s.linkid IS NOT NULL
  AND s.callsign<>s.linkid
  AND s.TX>890
  AND e.callsign IS NOT NULL
  AND e.linkid IS NOT NULL
  AND s.callsign=e.linkid
GROUP BY s.geom
ORDER BY s.location,
         s.callsign;

There are a few other non-obvious constraints in the query, but I hope you get the gist of it.

If anyone wishes to process these data files, I've written a Perl script to do so: scruss/taflmunge.

NB: These straight lines will only be accurate for very short links, as the earth's curvature should be taken into account. Microwave links are geodesic lines, so should really be plotted as multi-point arcs, calculated using something like Geod's npts function.