[postgis-users] Getting data from table with interval

[Paul Ramsey]

Yes, the "distances" you got when you ran the calculation in geometry space were wrong. They were both wrong in unit terms (meaningless units vs meters) and also in picking the right elevation point (since there's a N/S compression effect in raw lon/lat coordinates working in cartesian space). 

Trust the geography result.

P

> On Jan 7, 2022, at 12:26 PM, Luca Bertoncello <lucabert at lucabert.de> wrote:
> 
> Am 07.01.2022 um 21:20 schrieb Paul Ramsey:
>> Yep, I cut'n'pasted the wrong example.
>> 
>> 
>> EXPLAIN ANALYZE
>> SELECT *, 
>>  latlng <-> '0101000020E6100000E17A14AE47412C40BC74931804A64940' AS dist 
>> FROM elevation2x2 
>> ORDER BY dist LIMIT 5;
> 
> OK, this works very fast, but I think it is not correct, since:
> 
> 
> gis=# SELECT *, latlng::geometry <->
> '0101000020E6100000E17A14AE47412C40BC74931804A64940' AS dist FROM
> elevation2x2 ORDER BY dist LIMIT 5;
>                       latlng                       |      lat      |
>  lng      | elevation |        dist
> ----------------------------------------------------+---------------+---------------+-----------+---------------------
> 0101000020E61000004F56055403432C40EE4F2FDEF2A44940 | 51.2886617404 |
> 14.1308847672 |   150.692 | 0.00899906784923333
> 0101000020E6100000A60AD619D7432C40F986CCD93EA74940 | 51.3066055535 |
> 14.1325004648 |   148.309 |  0.0108291877007207
> 0101000020E6100000ACA6BFE42C352C406BB3F20960A74940 | 51.3076183734 |
> 14.1038581356 |   182.166 |  0.0259169366625343
> 0101000020E6100000427FAA945A342C404919860A14A54940 | 51.2896741061 |
> 14.1022535761 |   159.048 |  0.0262878420790376
> 0101000020E6100000468595C22F422C40CADBBFE1A6A24940 | 51.2707178294 |
> 14.1292706306 |   182.139 |  0.0263417467941872
> (5 Zeilen)
> 
> gis=# SELECT *, latlng <->
> '0101000020E6100000E17A14AE47412C40BC74931804A64940' AS dist FROM
> elevation2x2 ORDER BY dist LIMIT 5;
>                       latlng                       |      lat      |
>  lng      | elevation |       dist
> ----------------------------------------------------+---------------+---------------+-----------+------------------
> 0101000020E61000004F56055403432C40EE4F2FDEF2A44940 | 51.2886617404 |
> 14.1308847672 |   150.692 | 956.579554462767
> 0101000020E6100000A60AD619D7432C40F986CCD93EA74940 | 51.3066055535 |
> 14.1325004648 |   148.309 | 1123.24047983734
> 0101000020E6100000427FAA945A342C404919860A14A54940 | 51.2896741061 |
> 14.1022535761 |   159.048 | 1935.27916308932
> 0101000020E6100000ACA6BFE42C352C406BB3F20960A74940 | 51.3076183734 |
> 14.1038581356 |   182.166 | 2023.72752091526
> 0101000020E61000005E0B48ECAB512C40D058DA76D1A44940 | 51.2876423422 |
> 14.1595147932 |    161.46 |  2457.3374782712
> (5 Zeilen)
> 
> Or I miss something?
> 
> Thanks
> Luca Bertoncello
> (lucabert at lucabert.de)
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users