[postgis-users] Getting data from table with interval

[Luca Bertoncello]

Am 07.01.2022 um 21:31 schrieb Luca Bertoncello:
> Am 07.01.2022 um 21:28 schrieb 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.
> 
> OK, then I'll make a query to get the points from the other query I
> already got...

OK, it seems, today I cannot think correctly...

So I have the data of the points with:

SELECT ST_DumpPoints(ST_Segmentize(ST_MakeLine(ST_Point(14.1275,
51.297), ST_Point(13.768, 51.134333333333))::geography, 1000)::geometry);

Then I can get the nearest point in the table with:

SELECT *, latlng <->
'0101000020E6100000E17A14AE47412C40BC74931804A64940' AS dist FROM
elevation2x2 ORDER BY dist LIMIT 1;

I tried to join the both queries so:

SELECT lat, lng, elevation, latlng <-> line.point AS dist
FROM elevation2x2,
(SELECT (ST_DumpPoints(ST_Segmentize(ST_MakeLine(ST_Point(14.1275,
51.297), ST_Point(13.768, 51.134333333333))::geography,
1000)::geometry)).geom AS point) line
ORDER BY dist LIMIT 1;

but it needs huge time (after 3 minutes not completed yet)...
Explain says:

 Limit  (cost=413235579.62..413235579.63 rows=1 width=28)
   ->  Sort  (cost=413235579.62..417061544.62 rows=1530386000 width=28)
         Sort Key: ((elevation2x2.latlng <->
((((st_dumppoints('0102000020E610000021000000E17A14AE47412C40BC74931804A64940C8EC8E1F823B2C4053FC9D125EA54940009A83E4BC352C4026C39403B8A449407C7BEEFCF72F2C404EB878EB11A449406F89CB68332A2C40C9CA4ACA6BA3494054BB16286F242C4083E90BA0C5A24940E607CC3AAB1E2C405503BD6C1FA249402965E7A0E7182C400C075F3079A1494064C8645A24132C4058E3F2EAD2A0494023264067610D2C40E286799C2CA049403B7275C79E072C4032E0F344869F4940C29F007BDC012C40CADD62E4DF9E494018A1DD811AFC2B40156EC77A399E4940E56708DC58F62B40647F2208939D494016E57C8997F02B400400758CEC9C4940DE08378AD6EA2B4024DEBF07469C4940BAC232DE15E52B40DF07047A9F9B494071016C8555DF2B40476B42E3F89A494010B3DE7F95D92B4054F67B43529A4940E9C486CDD5D32B40EF96B19AAB9949409F23606E16CE2B40E53AE4E80499494018BB666257C82B40FCCF142E5E9849408B7696A998C22B40E343446AB79749406F40EB43DABC2B403484739D109749408B0261311CB72B40787EA3C769964940F7A5F3715EB12B402820D5E8C29549400A139F05A1AB2B40A35609011C9549406F315FECE3A52B403C0F41107594494018E82F2627A02B402F377D16CE934940481D0DB36A9A2B40A7BBBE132793494086B6F292AE942B40BE89060880924940B498DCC5F28E2B40778E55F3D8914940F0A7C64B37892B40C5B6ACD531914940'::geometry))).geom))::geography))
         ->  Nested Loop  (cost=0.00..405583649.62 rows=1530386000 width=28)
               ->  Seq Scan on elevation2x2  (cost=0.00..31081.86
rows=1530386 width=52)
               ->  Materialize  (cost=0.00..280.26 rows=1000 width=32)
                     ->  Result  (cost=0.00..265.26 rows=1000 width=32)
                           ->  ProjectSet  (cost=0.00..5.27 rows=1000
width=32)
                                 ->  Result  (cost=0.00..0.01 rows=1
width=0)

So, I'm sure I did something wrong, since I didn't expected a sequencial
scan on elevation2x2...

Does someone have an idea?

Thanks
Luca Bertoncello
(lucabert at lucabert.de)