1

I need to make a distance matrix from all the nodes in the Canary Islands to every airport in the region. I'm following the method set here but it brings the same node for all airports. Trying the method here has brought me similar problems. The query I'm running is as follows

create table aeropuertos_canarias as (SELECT * from rt_aerodromo_p WHERE cod_iata in ('LPA', 'VDE', 'FUE', 'GMZ', 'SPC', 'ACE', 'TFN', 'TFS'));
ALTER TABLE aeropuertos_canarias
   ADD COLUMN nearest_node integer;
select * from aeropuertos_canarias

CREATE TABLE temp AS
   SELECT a.nombre, b.id, min(a.dist)
   FROM
     (SELECT airport.nombre, 
             min(ST_Distance(ST_SetSRID(airport.geom, 4326), ST_SetSRID(node.the_geom,4326))) AS dist
      FROM aeropuertos_canarias as airport, edges_can_noded_vertices_pgr as node
      GROUP BY airport.nombre) AS a,
     (SELECT airport.nombre, node.id, 
             ST_Distance(ST_SetSRID(airport.geom, 4326), ST_SetSRID(node.the_geom,4326)) AS dist
      FROM aeropuertos_canarias as airport, edges_can_noded_vertices_pgr as node) AS b
   WHERE a.dist = b. dist
         AND a.nombre = b.nombre
   GROUP BY a.nombre, b.id;
select * from temp;

However, this brings me the following result.

"Aeropuerto de El Hierro"   74411   3625064.97072886
"Aeropuerto de Fuerteventura"   74411   3625066.16827048
"Aeropuerto de Gran Canaria"    74411   3625065.96589645
"Aeropuerto de La Gomera"   74411   3625065.07375698
"Aeropuerto de La Palma"    74411   3625064.29973004
"Aeropuerto de Lanzarote - César Manrique"  74411   3625065.83955541
"Aeropuerto de Los Rodeos"  74411   3625065.05111873
"Aropuerto Reina Sofía" 74411   3625065.34297412

Which is pretty obviously not true. Any idea about what I'm doing wrong and where will be most welcome.

dkastl
  • 4,786
  • 17
  • 21
marcbosch
  • 157
  • 8

1 Answers1

1

ST_Distance returns distances in the geometries projection unit, so in degrees since you are explicitly using 4326. This is the issue, because the returned distances are much higher than 180.

It points to the fact that one, or both, of your coordinates are not in 4326 but in a different projected CRS. You must not use ST_SetSRID, which keeps the numerical values and assign the given projection, but instead use ST_Transform to change the numerical values so they fit the given projection.

Also, as a side note, use ST_DistanceSphere if you decide to use 4326, else the returned distance is meaningless.

JGH
  • 41,794
  • 3
  • 43
  • 89
  • It worked perfectly, thank you so much. Plus, the airport layer did not even have a SRID but the coordinates pointed at lon/lat, so, what I did was UpdateGeometrySRID('airports', 'geom', 4326) and then use ST_Transform afterwards. – marcbosch Feb 25 '21 at 16:44