[GIS] Distance Matrix PostGis

distancedistance matrixpostgispython

Assuming I have a table or 2 dimensional array with only geometries and names (all in all 5k elements)

name     the_geom
a        polygon_geometry....
b        polygon_geometry....
c        polygon_geometry....
d        polygon_geometry....
e        polygon_geometry....
f        polygon_geometry....
...      ...

Is there a way to get the distances of all geometries to all geometries within one table.

    a    b    c    d    e    f    .... 
a   -    2m   3m   2m   2m   2m
b   2m   -    3m   5m   1m   9m
c   9m   3m   -    2m   7m   2m
d   ..   ..   ..   -    ..   ..
e   ..   ..   ..  ..    -    ..
f   ..   ..   ..  ..    ..   -
....

All hints are welcomed. Python hints as well!

Best Answer

As others have said, I don't think this pivot can be done in PostgreSQL however the distance calculation certainly can be. First you need a simple table of the distances, because ST_Distance and all distance calculations are commutative you can do something like this,

SELECT
  t1.geom_name AS t1,
  t2.geom_name AS t2,
  ST_Distance(t1.geom, t2.geom)
FROM table AS t1
INNER JOIN table AS t2 on (t1.geom<t2.geom);

You may want to get the entire list with duplicate tuples too (if it makes it easier to assemble this grid in your desired language).

SELECT
  t1.geom_name AS t1,
  t2.geom_name AS t2,
  ST_Distance(t1.geom, t2.geom)
FROM table AS t1
CROSS JOIN table AS t2;

From there in python you have to recreate the grid. Or, something else like \crosstabview in psql 9.6.

Related Question