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('0102000020E610000021000000E17A14AE47412C40BC74931804A64940C8EC8E1F823B2C4053FC9D125EA54940009A83E4BC352C4026C39403B8A449407C7BEEFCF72F2C404EB878EB11A449406F89CB68332A2C40C9CA4ACA6BA3494054BB16286F242C4083E90BA0C5A24940E607CC3AAB1E2C405503BD6C1FA249402965E7A0E7182C400C075F3079A1494064C8645A24132C4058E3F2EAD2A0494023264067610D2C40E286799C2CA049403B7275C79E072C4032E0F344869F4940C29F007BDC012C40CADD62E4DF9E494018A1DD811AFC2B40156EC77A399E4940E56708DC58F62B40647F2208939D494016E57C8997F02B400400758CEC9C4940DE08378AD6EA2B4024DEBF07469C4940BAC232DE15E52B40DF07047A9F9B494071016C8555DF2B40476B42E3F89A494010B3DE7F95D92B4054F67B43529A4940E9C486CDD5D32B40EF96B19AAB9949409F23606E16CE2B40E53AE4E80499494018BB666257C82B40FCCF142E5E9849408B7696A998C22B40E343446AB79749406F40EB43DABC2B403484739D109749408B0261311CB72B40787EA3C769964940F7A5F3715EB12B402820D5E8C29549400A139F05A1AB2B40A35609011C9549406F315FECE3A52B403C0F41107594494018E82F2627A02B402F377D16CE934940481D0DB36A9A2B40A7BBBE132
793494086B6F292AE942B40BE89060880924940B498DCC5F28E2B40778E55F3D8914940F0A7C64B37892B40C5B6ACD531914940'::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
([email protected])
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
