I had a request for some “spider diagrams” showing the connections between service centres and their customers and was given some sample data of about 140000 records.
The data contained a customer ID and customer coordinates and a service centre ID. Using another table of service centres I was able to add and update for each record the service centre coordinates (eastings and northings on the British National Grid EPSG:27700).
In QGIS there is a plugin that can use a table in this format (2 x IDs and 2 x coordinate pairs) to generate spider diagrams. I couldn’t get it to work with the latest version of QGIS 1.9.0 as it threw out some python errors:
An error has occured while executing Python code: Traceback (most recent call last): File "C:\Users\xxxxx/.qgis2/python/plugins\rt_qspider\dialog.py", line 136, in accept print x2d_toconv, y2d_toconv IOError: [Errno 9] Bad file descriptor
So, a simple query to select the service description, the office ID and to make lines out of the coordinate pairs.
SELECT sourcedesc, servicedesc, officeid, ST_MakeLine(ST_MakePoint(cust_x, cust_y),ST_MakePoint(office_x, office_y)) FROM service_calls WHERE officeid IS NOT NULL;
To convert this into a table with indexes to add to QGIS do:
CREATE TABLE service_spider AS SELECT sourcedesc, servicedesc, officeid, ST_MakeLine(ST_MakePoint(mapeast, mapnorth),ST_MakePoint(o_easting, o_northing)) FROM access_calls WHERE officeid IS NOT NULL;
ALTER TABLE service_spider ADD COLUMN "objectid" SERIAL;
ALTER TABLE service_spider ADD CONSTRAINT service_spider_pkey PRIMARY KEY(objectid);
CREATE INDEX service_spider_geom_idx ON service_spider USING gist (st_makeline);