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

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

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