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!

  • What do the 2m, etc represent. Pairwise distances between each geometry? – John Powell Dec 27 '16 at 17:33
  • @JohnBarça yes. the distance from a to b (row 1) is 2 metres. The distance between a and c (row 1) is 3 metres and so on... – Stophface Dec 27 '16 at 17:39
  • While it is trivial to calculate all pair wise distances, doing crosstabs in Postgres is really painful ,see tablefunc docs for how. You would probably be better off using pandas, possibly in conjunction with geopandas for the distances. See the pandas crosstab funcs. – John Powell Dec 27 '16 at 17:51
  • @JohnBarça Yep. I thought it might be better to use python (numpy/scipy) to do this... – Stophface Dec 27 '16 at 17:53
  • Pandas, rather than numpy/scipy, but, yes. Sadly, I have the world's worst internet connection, and don't have time to write this up. – John Powell Dec 27 '16 at 17:54

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,

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

  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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.