How to create spider diagrams (hub lines) in PostGIS?

Question

I'm rather new to PostGIS but I have done a lot of reading. One thing that I have been searching for is a function that allows users to create a "spider diagram" (also called "desire lines" or "hub lines") in PostGIS.

As an example, draw lines from a grocery store (point) to all geocoded customer addresses (point) of that grocery store. Of course, though, imagine that there were hundreds of stores and thousands of customers. I would imagine that each store would have a unique ID that each customer point would have embedded within a field so that the software knows the origin-destination connection.

Is there a simple ST_ function that will accomplish this task, or does this require combining multiple queries to accomplish the task?

Answer 1

Assuming that your database schema looks like this:

table customer:                     table shops:
customer_id | shop_id | the_geom    shop_id | the_geom
--------------------------------    ------------------
       1000 |     100 |    ...          100 |    ...
       1001 |     100 |    ...          101 |    ...
       1002 |     101 |    ...

The following statement should create a "spider diagram" for shop 100:

SELECT c.customer_id, c.shop_id, ST_MakeLine(c.the_geom,s.the_geom)
FROM customer as c JOIN shops as s
     ON c.shop_id = s.shop_id
WHERE shop_id = 100

Doc: http://postgis.net/docs/ST_MakeLine.html

Answer 2

The question is more or less answered, I just would like to add a few comments to point out the flexibility and power of doing things like this with spatial sql

As I read the question it can be divided in two questions. One GIS question and one sql question about how to combine rows in different tables.

The gis-part can as mentioned before best be solved with ST_MakeLine, if it is two points that you want to connect with your line. If it would have been lines or polygons you could use ST_Shortestline instead to get a line from the geometries between their closest points. That will work with both points, lines and polygons.

The other part is about how to connect the right store point with the right customer point.

That is a basic database question. Understanding that will give a lot more joy to using PostGIS.

The most easy situation is one store with many customers. Then you only need one customer table and your store-point and it would look something like:

Select ST_Makeline(customers.the_geom, yourPoint) from custmers;

If you have your only store in a table it would look like this:

Select ST_Makeline(customers.the_geom, stores.the_geom) from custmers, stores;

But if have many stores and each customer are only using one store, you will get a solution that looks like the other answers here, for example:

Select ST_Makeline(customers.the_geom, stores.the_geom) 
 from custmers, stores
   where customers.storeid = stores.id;

PostgreSQL will handle that query the same way as:

    Select ST_Makeline(customers.the_geom, stores.the_geom) 
     from custmers inner join stores 
      on customers.storeid = stores.id;

But in the real world you will probably get in the situation that most customers are using many stores. In earlier examples we have had the relation one store to many customers, now we have a many to many situation. Many customers using many stores.

Then there is a bad solution that you sometimes see. People add one new customer row for each store he uses. That will cause many problems if you for instance want to change his address you have to do it in many places. The more correct database of doing it is to make a "link table". I don't know if that is the right word in english but anyway. That is a table that holds all specific data for the customers relation to each store he uses. So then you have three tables. One with stores and all attributes belonging to the store like when it is open and the phone number to get in contact. Then you have the customer table maybe with address and phone number to the customer. Then you have this "link table" that should include an id for the store, the id for the customer and maybe extra information like frequency of visiting that particular store, or the distance to the store, or this line you are creating (but distance and line is better to create on the fly when you need it). Then with this new table your query would look something like:

Select ST_Makeline(customers.the_geom, stores.the_geom) 
  from customers inner join  
     c_s_link_table on customers.id = c_s_link_table.customerid 
     inner join stores 
     on c_s_link_table.storeid = stores.id;

Then you have the full flexibility. Let's say for instance that you add to your linktable how many times a month the customer visits that store. Then you can write a query like this:

Select ST_Makeline(customers.the_geom, stores.the_geom), 
c_s_link_table.visits_per_month from
 customers inner join c_s_link_table on customers.id = c_s_link_table.customerid 
  inner join stores on c_s_link_table.storeid = stores.id;

then you can use that attribute, visits_per_month to define the thicknes of your lines on the map illustrating how many visits each store gets.

Answer 3

One way to do the spider digram in PostGIS is by using the ST_MakeLine function. See example below:

SELECT ST_MakeLine(a.the_geom, b.the_geom), a.id as customer_id, a.store_id 
FROM customers a, stores b WHERE a.store_id = b.id