Timeline for Geographically closest 2 airports between 2 cities
Current License: CC BY-SA 3.0
7 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 18, 2017 at 1:20 | comment | added | shotdsherrif | Finally, db given to me only had lat/long values but I was able to use st_makepoint() to create a single field - POINT with a geometry datatype - with the precise location value. | |
| Mar 18, 2017 at 1:20 | comment | added | shotdsherrif | I was using cities to deal with smaller datasets when I was testing. The objective was to run it on countries, though the logic is the same either way. The WITH syntax is more accessible I agree, but building it that way creates a huge sequential scan. Even the way I did it, countries with lots of airports (USA & Germany for example) could take 10+ seconds to compute. I believe the subquery and calling min(st_distance(city1, city2)) is more efficient. | |
| Mar 17, 2017 at 15:19 | comment | added | John Powell | That may be true. But, in general, it is always better to write things as though it were. Our website at work was fantastic in testing. After a few weeks it was taking people 30 seconds to log in, as certain indexes had never been added, and weren't a problem in complex joins on tables with a few hundred entries. Pedantic, but you get my point :-) | |
| Mar 17, 2017 at 15:01 | comment | added | csd |
@JohnBarça: In this particular case, I don't think performance is a huge concern. I'm assuming that there are only a few airports in any given city (less than 100, say), and that there is an index on the city column (so that the FirstCityAirports and SecondCityAirports sub-queries are index scans). But your point is valid... if either of those assumptions is not true then this query will take forever.
|
|
| Mar 17, 2017 at 14:50 | comment | added | John Powell | I know this from bitter experience :D. | |
| Mar 17, 2017 at 14:45 | comment | added | John Powell | Given the absence of any distance operators, either ST_DWithin or the more recent <-> and <#> operators, the above query would be hideously inefficient on any non-trivial sized table, as it wouldn't use any spatial indexes. You would end up calculating all distances and then doing a sort, just to select the closest. Trust me, if you did this for all points to all other points on a table of 100 million rows, you would be waiting a few days. | |
| Mar 17, 2017 at 14:38 | history | answered | csd | CC BY-SA 3.0 |