PostGIS Spiders

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.

QGIS spider/hub diagram

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, where to for help? gis.stackexchange.com, of course and this post in particular.

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);

Leave a Reply

Your email address will not be published. Required fields are marked *