Comments (30)
sheinbergon
commented on June 5, 2024
1
Ok, it looks the external data from the geom that reaches the my extension function is not really binary, but rather the hex encoded string. Looks like when Dremio reads the data from PostgreSQL, it deserializes it to a Hex String.
So instead of the binary form of, say POINT(12.4533865 41.9032822), we get the HEX string 0101000020E610000054E57B4622E828408B074AC09EF34440
Maybe I can add ST_AsBinary, so we'll be able to do:
SELECT ST_GeomFromWKB(ST_AsBInary('0101000020E610000054E57B4622E828408B074AC09EF34440'));FROM_HEX should have solved it , but it doesn't, for some reason. I will give it a deeper look later on.
from dremio-udf-gis.
jaysnm
commented on June 5, 2024
1
It worked like a charm. Good work man. Much appreciations. I guess we can now close this issue.
from dremio-udf-gis.
sheinbergon
commented on June 5, 2024
Having more time to go over the issue
- I Wonder the source dataset WKB column is read and preserved in the same manner the ESRI-Java-library API expects it. If you could send a source data set, I'll be happy to have a look and test for myself
- Judging by the error, it seems your dremio installation is not compiling the function properly. I can confirm a simple ST_As* functions work when I test them out. If you could share your installation details, I can try and recreate on my local machine.
from dremio-udf-gis.
sheinbergon
commented on June 5, 2024
Managed to recreate it. Seems like something is being poorly parsed by janino (the compiler for these function classes).
I will check and have a look. 10x for reporting!
from dremio-udf-gis.
jaysnm
commented on June 5, 2024
Thank you @sheinbergon for the kind words and finding time to look at it. When I get experienced enough, I will help you document this tool for other to find it useful as well!
Cheers!
from dremio-udf-gis.
sheinbergon
commented on June 5, 2024
@jaysnm You can find the jar that resolves the above issue here in the link to the snapshots repository.
I took the opportunity to lower Java language level to 8 as well.
I Also added ST_GeomFromWKB function that accepts real WKB input, though I wonder its encoding/packing would match what dremio expects. If it doesn't work, you can get the data as WKT and use ST_GeomFromText.
Please let me know how it turned out.
I Started working on WIKI page here. I'll evolve it as time progresses.
from dremio-udf-gis.
sheinbergon
commented on June 5, 2024
@jaysnm have you gotten round to testing the new version?
from dremio-udf-gis.
jaysnm
commented on June 5, 2024
Hello @sheinbergon
My sincere apologies. I have not had a chance to look at the snapshot. We have been on a very long holiday since Thursday!
From the PR I see you have done massive overhaul and implemented dozens on new features. Today I have sometime to spare. Let me update you in a few. Thanks a lot.
from dremio-udf-gis.
jaysnm
commented on June 5, 2024
Hello @sheinbergon
I have had time to go through the patch and now I am able to read data from PostgreSQL when column encoding is WKT. ST_GeomFromWKB didn't work well for me. This is the error I get when I do SELECT ls_name, ST_ASGeoJSON(ST_GeomFromWKB(geom)) FROM "Open Vector Data".public."ne_110m_populated_places_simple_wkb" WHERE pop_max <= 1000
From server.log file I get
An unhandled geometry shape conversion on org.sheinbergon.dremio.udf.gis.util.FunctionHelpersXL.toGeometry():80 or may be something else! This happens for both the snapshot shared above and the v0.2.1 release
from dremio-udf-gis.
sheinbergon
commented on June 5, 2024
@jaysnm thanks for the response.
- So to begin with: Is this usable to you now (assuming you read the data as WKT on the source?)
- Can you attach a sample database dump of your PostGIS source, so I can load it and further work on it locally?
I guess remote to local WKB serialization dremio does somehow breaks the formatting.
from dremio-udf-gis.
jaysnm
commented on June 5, 2024
Thanks for quick response Idan.
Working with WKT gives the desired outputs but I'm worried I will be breaking a lot of things. Looking at shp2pgsql help, data conversion to WKT option reads
-w Output WKT instead of WKB. Note that this can result in coordinate drift.
Secondly, even after outputing my data into WKT, I have to use a text column for the geom instead of AddGeometryColumn. I am yet to test how QGIS perceives the WKT data column! However in dremio my queries just work fine on WKT column data.
from dremio-udf-gis.
sheinbergon
commented on June 5, 2024
Amazing. It'll probably take me a couple of days to inspect that. But I'll let you know.
Appreciate the cooperation!
from dremio-udf-gis.
jaysnm
commented on June 5, 2024
Amazing. It'll probably take me a couple of days to inspect that. But I'll let you know. Appreciate the cooperation!
Thanks a lot. I'm eager to see how it goes. Just ping me when done and I will do some other rounds of tests! Good work buddy!
from dremio-udf-gis.
sheinbergon
commented on June 5, 2024
@jaysnm the data is partially broken.
Some lat/long values contain 2 ., like "7.48739134.626549"
from dremio-udf-gis.
jaysnm
commented on June 5, 2024
Didn't see that. Some rows had spacial characters so I guess github algo did some cleanups to the text. I have removed a couple of rows. Let me know if this doesn't work.
SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
CREATE TABLE "ne_110m_populated_places_simple_wkb" (gid serial,
"scalerank" int2,
"natscale" int2,
"labelrank" int2,
"featurecla" varchar(50),
"name" varchar(100),
"namepar" varchar(254),
"namealt" varchar(254),
"nameascii" varchar(100),
"adm0cap" int2,
"capalt" int2,
"capin" varchar(15),
"worldcity" int2,
"megacity" int2,
"sov0name" varchar(100),
"sov_a3" varchar(3),
"adm0name" varchar(50),
"adm0_a3" varchar(3),
"adm1name" varchar(100),
"iso_a2" varchar(5),
"note" varchar(254),
"latitude" float8,
"longitude" float8,
"pop_max" float8,
"pop_min" float8,
"pop_other" float8,
"rank_max" int2,
"rank_min" int2,
"meganame" varchar(100),
"ls_name" varchar(41),
"min_zoom" float8,
"ne_id" float8);
ALTER TABLE "ne_110m_populated_places_simple_wkb" ADD PRIMARY KEY (gid);
SELECT AddGeometryColumn('','ne_110m_populated_places_simple_wkb','geom','4326','POINT',2);
COPY "ne_110m_populated_places_simple_wkb" ("scalerank","natscale","labelrank","featurecla","name","namepar","namealt","nameascii","adm0cap","capalt","capin","worldcity","megacity","sov0name","sov_a3","adm0name","adm0_a3","adm1name","iso_a2","note","latitude","longitude","pop_max","pop_min","pop_other","rank_max","rank_min","meganame","ls_name","min_zoom","ne_id",geom) FROM stdin;
8 10 3 Admin-0 capital Vatican City \N \N Vatican City 1 0 \N 1 0 Vatican VAT Vatican VAT Lazio VA \N 41.903282 12.453387 832 832 562430 2 2 \N Vatican City 7.0 1159127243 0101000020E610000054E57B4622E828408B074AC09EF34440
7 20 0 Admin-0 capital San Marino \N \N San Marino 1 0 \N 0 0 San Marino SMR San Marino SMR \N SM \N 43.936096 12.441770 29579 29000 0 7 7 \N San Marino 6.1 1159146051 0101000020E6100000DCB122B42FE228402376B7FCD1F74540
7 20 0 Admin-0 capital Vaduz \N \N Vaduz 1 0 \N 0 0 Liechtenstein LIE Liechtenstein LIE \N LI \N 47.133724 9.516670 36281 5342 33009 7 5 \N Vaduz 6.7 1159146061 0101000020E61000006DAE9AE78808234032D989DC1D914740
6 30 8 Admin-0 capital alt Lobamba \N \N Lobamba 0 1 Legislative and 0 0 eSwatini SWZ eSwatini SWZ Manzini SZ \N -26.466668 31.199997 9782 4557 0 5 4 \N Lobamba 6.0 1159146343 0101000020E61000007BCB8B0233333F40289B728577773AC0
6 30 8 Admin-0 capital Luxembourg \N \N Luxembourg 1 0 \N 0 0 Luxembourg LUX Luxembourg LUX Luxembourg LU \N 49.611660 6.130003 107260 76684 106219 9 8 \N Luxembourg 6.0 1159146437 0101000020E6100000C08D39741F8518400F2153E34ACE4840
6 30 0 Admin-0 capital Palikir \N \N Palikir 1 0 \N 0 0 Federated States of Micronesia FSM Federated States of Micronesia FSM \N FM \N 6.916644 158.149974 4645 4645 0 4 4 \N Palikir 6.0 1159149061 0101000020E6100000B237E796CCC46340765666A5A4AA1B40
6 30 0 Admin-0 capital Majuro \N \N Majuro 1 0 \N 0 0 Marshall Islands MHL Marshall Islands MHL \N MH \N 7.103004 171.380000 25400 20500 0 7 7 \N Majuro 6.0 1159149063 0101000020E610000027EF2DF6286C6540B6638FF579691C40
6 30 0 Admin-0 capital Funafuti \N \N Funafuti 1 0 \N 0 0 Tuvalu TUV Tuvalu TUV \N TV \N -8.516652 179.216647 4749 4749 0 4 4 \N Funafuti 6.0 1159149071 0101000020E6100000BE28E6C5EE66664074D3669C860821C0
6 30 0 Admin-0 capital Melekeok \N \N Melekeok 1 0 \N 0 0 Palau PLW Palau PLW \N PW \N 7.487396 134.626549 7026 7026 0 5 5 \N Melekeok 6.0 1159149073 0101000020E6100000749B70AF0CD4604059C74CFD17F31D40
6 30 0 Admin-0 capital alt Bir Lehlou \N \N Bir Lehlou 0 1 Claimed as inte 0 0 Western Sahara SAH Western Sahara SAH \N EH \N 26.119167 -9.652522 500 200 0 2 1 \N \N 6.0 1159149075 0101000020E6100000F4D3C963174E23C0B24577B5811E3A40
6 30 0 Admin-0 capital Monaco \N \N Monaco 1 0 \N 0 0 Monaco MCO Monaco MCO \N MC \N 43.739646 7.406913 36371 36371 102371 7 7 \N Monaco 5.1 1159149077 0101000020E61000002A3E99DAADA01D404210D6B5ACDE4540
6 30 0 Admin-0 capital Tarawa \N \N Tarawa 1 0 \N 0 0 Kiribati KIR Kiribati KIR \N KI \N 1.338188 173.017571 28802 22534 0 7 7 \N Tarawa 6.0 1159149079 0101000020E6100000AA6BA3F08FA06540F0A7C64B3769F53F
6 30 0 Admin-0 capital Moroni \N \N Moroni 1 0 \N 0 0 Comoros COM Comoros COM \N KM \N -11.704158 43.240244 128698 42872 0 9 7 \N Moroni 6.0 1159149083 0101000020E61000004A479451C09E45400274A95B876827C0
6 30 0 Admin-0 capital Andorra \N \N Andorra 1 0 \N 0 0 Andorra AND Andorra AND \N AD \N 42.500001 1.516486 53998 22256 53371 8 7 \N Andorra 6.0 1159149089 0101000020E61000003E78EDD28643F83F247ABE0B00404540
4 50 8 Admin-0 capital Port-of-Spain \N \N Port-of-Spain 1 0 \N 0 0 Trinidad and Tobago TTO Trinidad and Tobago TTO Port of Spain TT \N 10.651997 -61.517031 294934 49031 419082 10 7 \N Port-of-Spain 5.1 1159149307 0101000020E6100000BF428B112EC24EC0772C5B90D24D2540
4 50 0 Admin-0 capital Kigali \N \N Kigali 1 0 \N 0 1 Rwanda RWA Rwanda RWA Kigali City RW \N -1.951644 30.058586 860000 745261 1152904 11 11 Kigali Kigali 5.1 1159149385 0101000020E6100000B9814C7CFF0E3E406ED9C644EF39FFBF
4 50 8 Admin-0 capital Mbabane \N \N Mbabane 1 0 Administrative 0 0 eSwatini SWZ eSwatini SWZ Hhohho SZ \N -26.316651 31.133335 90138 76218 89979 8 8 \N Mbabane 5.6 1159149413 0101000020E6100000B3EDB43522223F409340DE0610513AC0
4 50 5 Admin-0 capital Juba \N \N Juba 0 0 \N 0 0 South Sudan SSD South Sudan SSD Central Equatoria SS \N 4.829975 31.580026 111975 111975 111975 9 9 \N Juba 5.1 1159149449 0101000020E6100000C0D7C68E7C943F40D8F6D104E5511340
4 50 8 Admin-0 capital alt The Hague \N \N The Hague 0 1 Official, legis 0 0 Kingdom of the Netherlands NLD Netherlands NLD Zuid-Holland NL \N 52.080037 4.269961 1406000 501725 688599 12 11 \N The Hague 5.6 1159149457 0101000020E61000001ABE2ABC70141140FE3C57A53E0A4A40
4 50 8 Admin-0 capital Ljubljana \N \N Ljubljana 1 0 \N 0 0 Slovenia SVN Slovenia SVN Osrednjeslovenska SI \N 46.055288 14.514969 314807 255115 256316 10 10 \N Ljubljana 5.6 1159149461 0101000020E61000005CE84A04AA072D40FC2CE0AF13074740
4 50 7 Admin-0 capital Bratislava \N \N Bratislava 1 0 \N 0 0 Slovakia SVK Slovakia SVK Bratislavský SK \N 48.150018 17.116981 423737 373687 361489 10 10 \N Bratislava 5.1 1159149463 0101000020E61000008A422674F21D31409826B6CC33134840
4 50 8 Admin-0 capital Doha \N \N Doha 1 0 \N 0 0 Qatar QAT Qatar QAT Ad Dawhah QA \N 25.286556 51.532968 1450000 731310 0 12 11 \N Doha 5.6 1159149469 0101000020E6100000AE28CA4A38C44940FB78E8BB5B493940
4 50 8 Admin-0 capital Podgorica \N \N Podgorica 1 0 \N 0 0 Montenegro MNE Montenegro MNE Podgorica ME \N 42.465973 19.266307 145850 136473 0 9 9 \N Podgorica 5.6 1159149511 0101000020E6100000FB3262B02C443340ED2AA4FCA43B4540
4 50 6 Admin-0 capital alt Sri Jawewardenepura Kotte \N \N Sri Jawewardenepura Kotte 0 1 Legislative cap 0 0 Sri Lanka LKA Sri Lanka LKA Colombo LK \N 6.900004 79.949993 115826 115826 2456292 9 9 \N Kotte 5.6 1159149593 0101000020E6100000749B70AFCCFC5340FE16539F9A991B40
4 50 5 Admin-0 capital alt Baguio City \N \N Baguio City 0 1 \N 0 0 Philippines PHL Philippines PHL Benguet PH \N 16.429991 120.569943 447824 272714 164877 10 10 \N Baguio City 5.6 1159149605 0101000020E610000039E686F079245E408B15DADE136E3040
4 50 5 Admin-0 capital alt Dodoma \N \N Dodoma 0 1 Offical capital 0 0 United Republic of Tanzania TZA Tanzania TZA Dodoma TZ \N -6.183306 35.750004 218269 180541 0 10 9 \N Dodoma 5.6 1159149731 0101000020E6100000EEF0321E00E0414015A12298B4BB18C0
4 50 7 Admin-0 capital Bern \N \N Bern 1 0 \N 0 0 Switzerland CHE Switzerland CHE Bern CH \N 46.916683 7.466976 275329 121631 267814 10 9 \N Bern 5.6 1159149737 0101000020E61000000C2252D32EDE1D402167ABDC55754740
4 50 5 Admin-0 capital alt Laayoune \N \N Laayoune 0 1 Claimed as capi 0 0 Morocco MAR Morocco MAR Laâyoune - Boujdour - Sakia El Hamra MA \N 27.149982 -13.200006 188084 176365 176365 9 9 \N Laayoune 5.6 1159149741 0101000020E61000009C035F2C67662AC0977A713D65263B40
4 50 8 Admin-0 capital Pristina \N \N Pristina 1 0 \N 0 0 Kosovo KOS Kosovo KOS Pristina -99 \N 42.666710 21.165984 465186 198214 261783 10 9 \N Pristina 5.6 1159149757 0101000020E6100000432674F27D2A3540F1F67BBD56554540
4 50 8 Admin-0 capital Roseau \N \N Roseau 1 0 \N 0 0 Dominica DMA Dominica DMA Saint George DM \N 15.301016 -61.387013 23336 16571 23336 7 6 \N Roseau 5.1 1159149847 0101000020E61000003D1059A489B14EC0F02B7BB71E9A2E40
4 50 8 Admin-0 capital Djibouti \N \N Djibouti 1 0 \N 0 0 Djibouti DJI Djibouti DJI Djibouti DJ \N 11.595015 43.148002 923000 604013 335001 11 11 \N Djibouti 5.6 1159149869 0101000020E61000004FA03EB8F1924540A54A94BDA5302740
4 50 6 Admin-0 capital alt Putrajaya \N \N Putrajaya 0 1 Administrative 0 0 Malaysia MYS Malaysia MYS Selangor MY \N 2.914020 101.701947 67964 50000 956431 8 7 \N Putrajaya 5.6 1159149883 0101000020E61000009F211CB3EC6C5940F42BE79CE94F0740
4 50 2 Admin-0 capital alt Kyoto \N \N Kyoto 0 1 Official capita 0 1 Japan JPN Japan JPN Kyoto JP \N 35.031938 135.748052 1805000 1459640 1827367 12 12 Kyoto Kyoto 5.1 1159149967 0101000020E61000008826F50AF0F76040897F338C16844140
4 50 8 Admin-0 capital Banjul \N \N Banjul 1 0 \N 0 0 Gambia, The GMB The Gambia GMB Banjul GM \N 13.453877 -16.591702 43094 34589 581300 7 7 \N Banjul 5.6 1159149997 0101000020E6100000807EDFBF799730C08CD8278062E82A40
4 50 8 Admin-0 capital Skopje \N \N Skopje 1 0 \N 0 0 North Macedonia MKD North Macedonia MKD Centar MK \N 42.000006 21.433462 494087 474889 491890 10 10 \N Skopje 5.6 1159150073 0101000020E61000003A933655F76E354077A62B3300004540
4 50 8 Admin-0 capital Bridgetown \N \N Bridgetown 1 0 \N 0 0 Barbados BRB Barbados BRB Saint Michael BB \N 13.102003 -59.616527 191152 96578 191814 9 8 \N Bridgetown 5.6 1159150339 0101000020E61000002FCECE58EACE4DC0C5364EAF39342A40
4 50 8 Admin-0 capital alt Porto-Novo \N \N Porto-Novo 0 1 Official capita 0 0 Benin BEN Benin BEN Ouémé BJ \N 6.483311 2.616626 300000 234168 806945 10 10 \N Porto-Novo 5.6 1159150345 0101000020E61000008509A359D9EE04402F302B14E9EE1940
4 50 8 Admin-0 capital Bujumbura \N \N Bujumbura 1 0 \N 0 0 Burundi BDI Burundi BDI Bujumbura Mairie BI \N -3.376087 29.360006 331700 331700 1208361 10 10 \N Bujumbura 5.6 1159150427 0101000020E61000004ADC195C295C3D40BF8D83013A020BC0
4 50 0 Admin-0 capital Kingstown \N \N Kingstown 1 0 \N 0 0 Saint Vincent and the Grenadines VCT Saint Vincent and the Grenadines VCT \N VC \N 13.148279 -61.212062 49485 24518 0 7 7 \N Kingstown 5.1 1159150431 0101000020E61000000B5B58DC249B4EC05861E932EB4B2A40
\.
CREATE INDEX ON "ne_110m_populated_places_simple_wkb" USING GIST ("geom");
COMMIT;
ANALYZE "ne_110m_populated_places_simple_wkb";
from dremio-udf-gis.
jaysnm
commented on June 5, 2024
Truncated version of the WKT
SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
CREATE TABLE "ne_110m_populated_places_simple_wkt" (gid serial,
"scalerank" int2,
"natscale" int2,
"labelrank" int2,
"featurecla" varchar(50),
"name" varchar(100),
"namepar" varchar(254),
"namealt" varchar(254),
"nameascii" varchar(100),
"adm0cap" int2,
"capalt" int2,
"capin" varchar(15),
"worldcity" int2,
"megacity" int2,
"sov0name" varchar(100),
"sov_a3" varchar(3),
"adm0name" varchar(50),
"adm0_a3" varchar(3),
"adm1name" varchar(100),
"iso_a2" varchar(5),
"note" varchar(254),
"latitude" float8,
"longitude" float8,
"pop_max" float8,
"pop_min" float8,
"pop_other" float8,
"rank_max" int2,
"rank_min" int2,
"meganame" varchar(100),
"ls_name" varchar(41),
"min_zoom" float8,
"ne_id" float8,
"geom" text);
ALTER TABLE "ne_110m_populated_places_simple_wkt" ADD PRIMARY KEY (gid);
COPY "ne_110m_populated_places_simple_wkt" ("scalerank","natscale","labelrank","featurecla","name","namepar","namealt","nameascii","adm0cap","capalt","capin","worldcity","megacity","sov0name","sov_a3","adm0name","adm0_a3","adm1name","iso_a2","note","latitude","longitude","pop_max","pop_min","pop_other","rank_max","rank_min","meganame","ls_name","min_zoom","ne_id","geom") FROM stdin;
8 10 3 Admin-0 capital Vatican City \N \N Vatican City 1 0 \N 1 0 Vatican VAT Vatican VAT Lazio VA \N 41.903282 12.453387 832 832 562430 2 2 \N Vatican City 7.0 1159127243 POINT(12.4533865 41.9032822)
7 20 0 Admin-0 capital San Marino \N \N San Marino 1 0 \N 0 0 San Marino SMR San Marino SMR \N SM \N 43.936096 12.441770 29579 29000 0 7 7 \N San Marino 6.1 1159146051 POINT(12.4417702 43.9360958)
7 20 0 Admin-0 capital Vaduz \N \N Vaduz 1 0 \N 0 0 Liechtenstein LIE Liechtenstein LIE \N LI \N 47.133724 9.516670 36281 5342 33009 7 5 \N Vaduz 6.7 1159146061 POINT(9.5166695 47.1337238)
6 30 8 Admin-0 capital alt Lobamba \N \N Lobamba 0 1 Legislative and 0 0 eSwatini SWZ eSwatini SWZ Manzini SZ \N -26.466668 31.199997 9782 4557 0 5 4 \N Lobamba 6.0 1159146343 POINT(31.1999971 -26.4666675)
6 30 8 Admin-0 capital Luxembourg \N \N Luxembourg 1 0 \N 0 0 Luxembourg LUX Luxembourg LUX Luxembourg LU \N 49.611660 6.130003 107260 76684 106219 9 8 \N Luxembourg 6.0 1159146437 POINT(6.1300028 49.6116604)
6 30 0 Admin-0 capital Palikir \N \N Palikir 1 0 \N 0 0 Federated States of Micronesia FSM Federated States of Micronesia FSM \N FM \N 6.916644 158.149974 4645 4645 0 4 4 \N Palikir 6.0 1159149061 POINT(158.1499743 6.9166437)
6 30 0 Admin-0 capital Majuro \N \N Majuro 1 0 \N 0 0 Marshall Islands MHL Marshall Islands MHL \N MH \N 7.103004 171.380000 25400 20500 0 7 7 \N Majuro 6.0 1159149063 POINT(171.3800002 7.1030043)
6 30 0 Admin-0 capital Funafuti \N \N Funafuti 1 0 \N 0 0 Tuvalu TUV Tuvalu TUV \N TV \N -8.516652 179.216647 4749 4749 0 4 4 \N Funafuti 6.0 1159149071 POINT(179.2166471 -8.516652)
6 30 0 Admin-0 capital Melekeok \N \N Melekeok 1 0 \N 0 0 Palau PLW Palau PLW \N PW \N 7.487396 134.626549 7026 7026 0 5 5 \N Melekeok 6.0 1159149073 POINT(134.6265485 7.4873962)
6 30 0 Admin-0 capital alt Bir Lehlou \N \N Bir Lehlou 0 1 Claimed as inte 0 0 Western Sahara SAH Western Sahara SAH \N EH \N 26.119167 -9.652522 500 200 0 2 1 \N \N 6.0 1159149075 POINT(-9.6525222 26.1191667)
6 30 0 Admin-0 capital Monaco \N \N Monaco 1 0 \N 0 0 Monaco MCO Monaco MCO \N MC \N 43.739646 7.406913 36371 36371 102371 7 7 \N Monaco 5.1 1159149077 POINT(7.4069132 43.7396457)
6 30 0 Admin-0 capital Tarawa \N \N Tarawa 1 0 \N 0 0 Kiribati KIR Kiribati KIR \N KI \N 1.338188 173.017571 28802 22534 0 7 7 \N Tarawa 6.0 1159149079 POINT(173.0175708 1.3381875)
6 30 0 Admin-0 capital Moroni \N \N Moroni 1 0 \N 0 0 Comoros COM Comoros COM \N KM \N -11.704158 43.240244 128698 42872 0 9 7 \N Moroni 6.0 1159149083 POINT(43.2402441 -11.7041577)
6 30 0 Admin-0 capital Andorra \N \N Andorra 1 0 \N 0 0 Andorra AND Andorra AND \N AD \N 42.500001 1.516486 53998 22256 53371 8 7 \N Andorra 6.0 1159149089 POINT(1.516486 42.5000014)
4 50 8 Admin-0 capital Port-of-Spain \N \N Port-of-Spain 1 0 \N 0 0 Trinidad and Tobago TTO Trinidad and Tobago TTO Port of Spain TT \N 10.651997 -61.517031 294934 49031 419082 10 7 \N Port-of-Spain 5.1 1159149307 POINT(-61.5170309 10.6519971)
4 50 0 Admin-0 capital Kigali \N \N Kigali 1 0 \N 0 1 Rwanda RWA Rwanda RWA Kigali City RW \N -1.951644 30.058586 860000 745261 1152904 11 11 Kigali Kigali 5.1 1159149385 POINT(30.0585859 -1.9516442)
4 50 8 Admin-0 capital Mbabane \N \N Mbabane 1 0 Administrative 0 0 eSwatini SWZ eSwatini SWZ Hhohho SZ \N -26.316651 31.133335 90138 76218 89979 8 8 \N Mbabane 5.6 1159149413 POINT(31.1333345 -26.3166508)
4 50 5 Admin-0 capital Juba \N \N Juba 0 0 \N 0 0 South Sudan SSD South Sudan SSD Central Equatoria SS \N 4.829975 31.580026 111975 111975 111975 9 9 \N Juba 5.1 1159149449 POINT(31.5800256 4.8299752)
4 50 8 Admin-0 capital alt The Hague \N \N The Hague 0 1 Official, legis 0 0 Kingdom of the Netherlands NLD Netherlands NLD Zuid-Holland NL \N 52.080037 4.269961 1406000 501725 688599 12 11 \N The Hague 5.6 1159149457 POINT(4.2699613 52.0800368)
4 50 8 Admin-0 capital Ljubljana \N \N Ljubljana 1 0 \N 0 0 Slovenia SVN Slovenia SVN Osrednjeslovenska SI \N 46.055288 14.514969 314807 255115 256316 10 10 \N Ljubljana 5.6 1159149461 POINT(14.514969 46.0552883)
4 50 7 Admin-0 capital Bratislava \N \N Bratislava 1 0 \N 0 0 Slovakia SVK Slovakia SVK Bratislavský SK \N 48.150018 17.116981 423737 373687 361489 10 10 \N Bratislava 5.1 1159149463 POINT(17.1169808 48.1500183)
4 50 8 Admin-0 capital Doha \N \N Doha 1 0 \N 0 0 Qatar QAT Qatar QAT Ad Dawhah QA \N 25.286556 51.532968 1450000 731310 0 12 11 \N Doha 5.6 1159149469 POINT(51.5329679 25.286556)
4 50 8 Admin-0 capital Podgorica \N \N Podgorica 1 0 \N 0 0 Montenegro MNE Montenegro MNE Podgorica ME \N 42.465973 19.266307 145850 136473 0 9 9 \N Podgorica 5.6 1159149511 POINT(19.2663069 42.4659725)
4 50 6 Admin-0 capital alt Sri Jawewardenepura Kotte \N \N Sri Jawewardenepura Kotte 0 1 Legislative cap 0 0 Sri Lanka LKA Sri Lanka LKA Colombo LK \N 6.900004 79.949993 115826 115826 2456292 9 9 \N Kotte 5.6 1159149593 POINT(79.949993 6.9000039)
4 50 5 Admin-0 capital alt Baguio City \N \N Baguio City 0 1 \N 0 0 Philippines PHL Philippines PHL Benguet PH \N 16.429991 120.569943 447824 272714 164877 10 10 \N Baguio City 5.6 1159149605 POINT(120.5699426 16.4299907)
4 50 5 Admin-0 capital alt Dodoma \N \N Dodoma 0 1 Offical capital 0 0 United Republic of Tanzania TZA Tanzania TZA Dodoma TZ \N -6.183306 35.750004 218269 180541 0 10 9 \N Dodoma 5.6 1159149731 POINT(35.7500036 -6.1833061)
4 50 7 Admin-0 capital Bern \N \N Bern 1 0 \N 0 0 Switzerland CHE Switzerland CHE Bern CH \N 46.916683 7.466976 275329 121631 267814 10 9 \N Bern 5.6 1159149737 POINT(7.4669755 46.9166828)
4 50 5 Admin-0 capital alt Laayoune \N \N Laayoune 0 1 Claimed as capi 0 0 Morocco MAR Morocco MAR Laâyoune - Boujdour - Sakia El Hamra MA \N 27.149982 -13.200006 188084 176365 176365 9 9 \N Laayoune 5.6 1159149741 POINT(-13.2000059 27.1499823)
4 50 8 Admin-0 capital Pristina \N \N Pristina 1 0 \N 0 0 Kosovo KOS Kosovo KOS Pristina -99 \N 42.666710 21.165984 465186 198214 261783 10 9 \N Pristina 5.6 1159149757 POINT(21.1659843 42.6667096)
4 50 8 Admin-0 capital Roseau \N \N Roseau 1 0 \N 0 0 Dominica DMA Dominica DMA Saint George DM \N 15.301016 -61.387013 23336 16571 23336 7 6 \N Roseau 5.1 1159149847 POINT(-61.387013 15.3010156)
4 50 8 Admin-0 capital Djibouti \N \N Djibouti 1 0 \N 0 0 Djibouti DJI Djibouti DJI Djibouti DJ \N 11.595015 43.148002 923000 604013 335001 11 11 \N Djibouti 5.6 1159149869 POINT(43.1480017 11.5950145)
4 50 6 Admin-0 capital alt Putrajaya \N \N Putrajaya 0 1 Administrative 0 0 Malaysia MYS Malaysia MYS Selangor MY \N 2.914020 101.701947 67964 50000 956431 8 7 \N Putrajaya 5.6 1159149883 POINT(101.701947 2.9140198)
4 50 2 Admin-0 capital alt Kyoto \N \N Kyoto 0 1 Official capita 0 1 Japan JPN Japan JPN Kyoto JP \N 35.031938 135.748052 1805000 1459640 1827367 12 12 Kyoto Kyoto 5.1 1159149967 POINT(135.7480521 35.0319381)
4 50 8 Admin-0 capital Banjul \N \N Banjul 1 0 \N 0 0 Gambia, The GMB The Gambia GMB Banjul GM \N 13.453877 -16.591702 43094 34589 581300 7 7 \N Banjul 5.6 1159149997 POINT(-16.5917015 13.4538765)
4 50 8 Admin-0 capital Skopje \N \N Skopje 1 0 \N 0 0 North Macedonia MKD North Macedonia MKD Centar MK \N 42.000006 21.433462 494087 474889 491890 10 10 \N Skopje 5.6 1159150073 POINT(21.4334615 42.0000061)
4 50 8 Admin-0 capital Bridgetown \N \N Bridgetown 1 0 \N 0 0 Barbados BRB Barbados BRB Saint Michael BB \N 13.102003 -59.616527 191152 96578 191814 9 8 \N Bridgetown 5.6 1159150339 POINT(-59.6165267 13.1020026)
4 50 8 Admin-0 capital alt Porto-Novo \N \N Porto-Novo 0 1 Official capita 0 0 Benin BEN Benin BEN Ouémé BJ \N 6.483311 2.616626 300000 234168 806945 10 10 \N Porto-Novo 5.6 1159150345 POINT(2.6166255 6.483311)
4 50 8 Admin-0 capital Bujumbura \N \N Bujumbura 1 0 \N 0 0 Burundi BDI Burundi BDI Bujumbura Mairie BI \N -3.376087 29.360006 331700 331700 1208361 10 10 \N Bujumbura 5.6 1159150427 POINT(29.3600061 -3.3760872)
4 50 0 Admin-0 capital Kingstown \N \N Kingstown 1 0 \N 0 0 Saint Vincent and the Grenadines VCT Saint Vincent and the Grenadines VCT \N VC \N 13.148279 -61.212062 49485 24518 0 7 7 \N Kingstown 5.1 1159150431 POINT(-61.2120624 13.1482788)
4 50 0 Admin-0 capital Castries \N \N Castries 1 0 \N 0 0 Saint Lucia LCA Saint Lucia LCA \N LC \N 14.001974 -61.000008 37963 10634 0 7 6 \N Castries 5.6 1159150433 POINT(-61.0000082 14.0019735)
4 50 0 Admin-0 capital Basseterre \N \N Basseterre 1 0 \N 0 0 Saint Kitts and Nevis KNA Saint Kitts and Nevis KNA \N KN \N 17.302031 -62.717009 21887 15500 21887 7 6 \N Basseterre 5.6 1159150435 POINT(-62.7170093 17.3020305)
\.
COMMIT;
ANALYZE "ne_110m_populated_places_simple_wkt";
from dremio-udf-gis.
jaysnm
commented on June 5, 2024
I removed the previous two chunks to save space and avoid confusion. If for some reason you need all the dataset you can pull it from Natural Earth and use this doc to load it into database.
from dremio-udf-gis.
sheinbergon
commented on June 5, 2024
@jaysnm OK, so the problem is:
- Dremio maps geom as string, and returns a Hex representation of geometry data.
- The Hex representation that's sent from PostGIS is not WKB compatible. Looks like it's EWKB ( There are additional 4 bytes, which I guess are dedicated to SRID)
- The function
ST_AsBinaryfound in PostgreSQL does exactly that - transforms Postgresql geom HEX (probably EWKB) to WKB data. - That's why
FROM_HEXdoesn't help, the hex string itself does not represent a standard WKB to begin with.
For a solution, looks like replacing ESRI java API with a JTS based implementation solves the issue. It's probably the right way to go, as it's a much more maintained API, with an OK license (EPL) for this use case.
This overhaul will take some time however. More updates soon.
from dremio-udf-gis.
jaysnm
commented on June 5, 2024
Impressive job. I'm surprised you were able to unearth all this in those few minutes! Can't wait to test ST_AsBinary function. And yes, I agree JTS based implementation is the way to go. Unfortunately I have no much experience in Java thus can't help in development. However, for testing and documentation you can count on me.
from dremio-udf-gis.
sheinbergon
commented on June 5, 2024
Ok, I think I got it
here's a link to a snapshot jar
To read from PostGIS WKB, run the following command:
SELECT FROM_HEX(geom) FROM "local"."public"."ne_110m_populated_places_simple_wkb"with the current JTS implementation, there's really no point in using ST_GeomFromWKB,
(unless you want to set the SRID, but we'll cover that in another time). So
SELECT ST_AsText(FROM_HEX(geom)) FROM "local"."public"."ne_110m_populated_places_simple_wkb" will just "work" OTB
PostgreSQL returns HEX String, FROM_HEX decodes that to binary, which GIS functions can pickup.
I'll finish wrapping this PR, let me know if it's working for you
from dremio-udf-gis.
jaysnm
commented on June 5, 2024
SELECT FROM_HEX(geom) FROM "local"."public"."ne_110m_populated_places_simple_wkb".
Works fine. However,
SELECT ST_AsText(geom) FROM "local"."public"."ne_110m_populated_places_simple_wkb" fails with
(java.lang.RuntimeException) org.locationtech.jts.io.ParseException: Unknown WKB type 104
org.sheinbergon.dremio.udf.gis.util.FunctionHelpersXL.toGeometry():90
com.dremio.s.ProjectorGen17.doEval():82
com.dremio.sabot.op.project.ProjectorTemplate.projectRecords():53
com.dremio.exec.expr.SplitStageExecutor.evaluateProjector():315
com.dremio.exec.expr.ExpressionSplitter.projectRecords():568
A rewrite of the query with FROM_HEX and works fine.
SELECT ST_AsText(FROM_HEX(geom)) FROM "local"."public"."ne_110m_populated_places_simple_wkb"
I then went for something fancy!
SELECT *, ST_AsText(FROM_HEX(geom)) as geometry_wkt FROM "Open Vector Data".public."ne_110m_populated_places_simple_wkb" LIMIT 10
Works fine!
SELECT *, ST_AsText(FROM_HEX(geom)) as geometry_wkt FROM "Open Vector Data".public."ne_110m_populated_places_simple_wkb" WHERE ls_name='San Marino'
Also works!
SELECT *, ST_AsText(FROM_HEX(geom)) AS geometry_wkt FROM "Open Vector Data".public."ne_110m_populated_places_simple_wkb" WHERE geom=ST_GeomFromText('POINT (31.1999971 -26.4666675)')
Somehow seems to work but fails at data records display with
[1d8c7218-5648-b199-8a2b-7b7811d5f600/0:foreman-planning] INFO c.d.e.expr.fn.FunctionErrorContext - User Error Occurred [ErrorId: 0bc8613a-a1d5-4951-b831-081aec81a454]
com.dremio.common.exceptions.UserException: Unexpected byte 0x8b at position 14 encountered while decoding UTF8 string.
at com.dremio.common.exceptions.UserException$Builder.build(UserException.java:885)
at com.dremio.exec.expr.fn.FunctionErrorContextImpl$ExceptionBuilderImpl.build(FunctionErrorContextImpl.java:95)
at com.dremio.exec.expr.fn.impl.StringFunctionUtil.utf8CharLen(StringFunctionUtil.java:165)
at com.dremio.exec.expr.fn.impl.StringFunctionUtil.getUTF8CharLength(StringFunctionUtil.java:39)
at com.dremio.exec.expr.fn.impl.gcast.CastVarBinaryVarChar.eval(CastVarBinaryVarChar.java:37)
at com.dremio.exec.expr.fn.interpreter.InterpreterEvaluator$EvalVisitor.visitFunctionHolderExpression(InterpreterEvaluator.java:304)
at com.dremio.exec.expr.fn.interpreter.InterpreterEvaluator$EvalVisitor.visitFunctionHolderExpression(InterpreterEvaluator.java:152)
at com.dremio.common.expression.FunctionHolderExpression.accept(FunctionHolderExpression.java:54)
at com.dremio.exec.expr.fn.interpreter.InterpreterEvaluator$EvalVisitor.visitFunctionHolderExpression(InterpreterEvaluator.java:237)
at com.dremio.exec.expr.fn.interpreter.InterpreterEvaluator$EvalVisitor.visitFunctionHolderExpression(InterpreterEvaluator.java:152)
at com.dremio.common.expression.FunctionHolderExpression.accept(FunctionHolderExpression.java:54)
at com.dremio.exec.expr.fn.interpreter.InterpreterEvaluator$EvalVisitor.visitFunctionHolderExpression(InterpreterEvaluator.java:237)
at com.dremio.exec.expr.fn.interpreter.InterpreterEvaluator$EvalVisitor.visitFunctionHolderExpression(InterpreterEvaluator.java:152)
at com.dremio.common.expression.FunctionHolderExpression.accept(FunctionHolderExpression.java:54)
at com.dremio.exec.expr.fn.interpreter.InterpreterEvaluator.evaluateConstantExpr(InterpreterEvaluator.java:69)
The error traceback isn't that much useful because it doesn't give clear pointers to the failure source in your extension.
SELECT ST_GeomFromText('POINT (31.1999971 -26.4666675)') outputs base64 encoded string making me think perhaps the comparison is happening between the HEX encoded string on geom column and the base64 output of ST_GeomFromText. When I ...
SELECT *, ST_AsText(FROM_HEX(geom)) AS geometry_wkt FROM "Open Vector Data".public."ne_110m_populated_places_simple_wkb" WHERE geom='0101000020E61000006DAE9AE78808234032D989DC1D914740'
My VM crashes. I'm not sure why the VM crashes but will try again on a physical server machine (My M1 chip can't run dremio).
from dremio-udf-gis.
sheinbergon
commented on June 5, 2024
SELECT FROM_HEX(geom) FROM "local"."public"."ne_110m_populated_places_simple_wkb".Works fine. However,
Great!
SELECT ST_AsText(geom) FROM "local"."public"."ne_110m_populated_places_simple_wkb"fails with(java.lang.RuntimeException) org.locationtech.jts.io.ParseException: Unknown WKB type 104 org.sheinbergon.dremio.udf.gis.util.FunctionHelpersXL.toGeometry():90 com.dremio.s.ProjectorGen17.doEval():82 com.dremio.sabot.op.project.ProjectorTemplate.projectRecords():53 com.dremio.exec.expr.SplitStageExecutor.evaluateProjector():315 com.dremio.exec.expr.ExpressionSplitter.projectRecords():568
Yes, this is expected. Dremio returns the String as HEX encoded, you need to HEX decode it first using FROM_HEX. SO this an expected behavior.
A rewrite of the query with FROM_HEX and works fine.
SELECT ST_AsText(FROM_HEX(geom)) FROM "local"."public"."ne_110m_populated_places_simple_wkb"
Great!
I then went for something fancy!
SELECT *, ST_AsText(FROM_HEX(geom)) as geometry_wkt FROM "Open Vector Data".public."ne_110m_populated_places_simple_wkb" LIMIT 10Works fine!
Great !
SELECT *, ST_AsText(FROM_HEX(geom)) as geometry_wkt FROM "Open Vector Data".public."ne_110m_populated_places_simple_wkb" WHERE ls_name='San Marino'Also works!
Great !
SELECT *, ST_AsText(FROM_HEX(geom)) AS geometry_wkt FROM "Open Vector Data".public."ne_110m_populated_places_simple_wkb" WHERE geom=ST_GeomFromText('POINT (31.1999971 -26.4666675)')Somehow seems to work but fails at data records display with
That's not how you're supposed to compare geoemetries. Please use ST_Equals 😸
[1d8c7218-5648-b199-8a2b-7b7811d5f600/0:foreman-planning] INFO c.d.e.expr.fn.FunctionErrorContext - User Error Occurred [ErrorId: 0bc8613a-a1d5-4951-b831-081aec81a454] com.dremio.common.exceptions.UserException: Unexpected byte 0x8b at position 14 encountered while decoding UTF8 string. at com.dremio.common.exceptions.UserException$Builder.build(UserException.java:885) at com.dremio.exec.expr.fn.FunctionErrorContextImpl$ExceptionBuilderImpl.build(FunctionErrorContextImpl.java:95) at com.dremio.exec.expr.fn.impl.StringFunctionUtil.utf8CharLen(StringFunctionUtil.java:165) at com.dremio.exec.expr.fn.impl.StringFunctionUtil.getUTF8CharLength(StringFunctionUtil.java:39) at com.dremio.exec.expr.fn.impl.gcast.CastVarBinaryVarChar.eval(CastVarBinaryVarChar.java:37) at com.dremio.exec.expr.fn.interpreter.InterpreterEvaluator$EvalVisitor.visitFunctionHolderExpression(InterpreterEvaluator.java:304) at com.dremio.exec.expr.fn.interpreter.InterpreterEvaluator$EvalVisitor.visitFunctionHolderExpression(InterpreterEvaluator.java:152) at com.dremio.common.expression.FunctionHolderExpression.accept(FunctionHolderExpression.java:54) at com.dremio.exec.expr.fn.interpreter.InterpreterEvaluator$EvalVisitor.visitFunctionHolderExpression(InterpreterEvaluator.java:237) at com.dremio.exec.expr.fn.interpreter.InterpreterEvaluator$EvalVisitor.visitFunctionHolderExpression(InterpreterEvaluator.java:152) at com.dremio.common.expression.FunctionHolderExpression.accept(FunctionHolderExpression.java:54) at com.dremio.exec.expr.fn.interpreter.InterpreterEvaluator$EvalVisitor.visitFunctionHolderExpression(InterpreterEvaluator.java:237) at com.dremio.exec.expr.fn.interpreter.InterpreterEvaluator$EvalVisitor.visitFunctionHolderExpression(InterpreterEvaluator.java:152) at com.dremio.common.expression.FunctionHolderExpression.accept(FunctionHolderExpression.java:54) at com.dremio.exec.expr.fn.interpreter.InterpreterEvaluator.evaluateConstantExpr(InterpreterEvaluator.java:69)The error traceback isn't that much useful because it doesn't give clear pointers to the failure source in your extension.
SELECT ST_GeomFromText('POINT (31.1999971 -26.4666675)')outputs base64 encoded string making me think perhaps the comparison is happening between the HEX encoded string on geom column and the base64 output of ST_GeomFromText. When I ...
SELECT *, ST_AsText(FROM_HEX(geom)) AS geometry_wkt FROM "Open Vector Data".public."ne_110m_populated_places_simple_wkb" WHERE geom='0101000020E61000006DAE9AE78808234032D989DC1D914740'My VM crashes. I'm not sure why the VM crashes but will try again on a physical server machine (My M1 chip can't run dremio).
I'm not really sure about the VM crash. Once we've cleared the rest of the comments are OK, can you please open a new issue for that?
from dremio-udf-gis.
jaysnm
commented on June 5, 2024
I almost became a clone :)
I have tried ST_Equals and ST_Within on WHERE clause and both fail with
(java.lang.RuntimeException) org.locationtech.jts.io.ParseException: Unknown WKB type 592
org.sheinbergon.dremio.udf.gis.util.FunctionHelpersXL.toGeometry():90
com.dremio.s.FiltererGen3.doEval():85
com.dremio.sabot.op.filter.FilterTemplate2.filterBatchNoSV():89
com.dremio.sabot.op.filter.FilterTemplate2.filterBatch():61
com.dremio.exec.expr.SplitStageExecutor$JavaTimedFilter.apply():383
com.dremio.exec.expr.SplitStageExecutor.evaluateFilter():338
com.dremio.exec.expr.ExpressionSplitter.filterData():585
com.dremio.sabot.op.filter.FilterOperator.consumeData():101
com.dremio.sabot.driver.SmartOp$SmartSingleInput.consumeData():240
com.dremio.sabot.driver.StraightPipe.pump():59
com.dremio.sabot.driver.Pipeline.doPump():108
com.dremio.sabot.driver.Pipeline.pumpOnce():98
com.dremio.sabot.exec.fragment.FragmentExecutor$DoAsPumper.run():345
com.dremio.sabot.exec.fragment.FragmentExecutor.run():294
com.dremio.sabot.exec.fragment.FragmentExecutor.access$1600():94
com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run():747
com.dremio.sabot.task.AsyncTaskWrapper.run():112
com.dremio.sabot.task.slicing.SlicingThread.mainExecutionLoop():243
com.dremio.sabot.task.slicing.SlicingThread.run():171
Caused By (org.locationtech.jts.io.ParseException) Unknown WKB type 592
org.locationtech.jts.io.WKBReader.readGeometry():282
org.locationtech.jts.io.WKBReader.read():191
org.locationtech.jts.io.WKBReader.read():178
org.sheinbergon.dremio.udf.gis.util.FunctionHelpersXL.toGeometry():88
com.dremio.s.FiltererGen3.doEval():85
com.dremio.sabot.op.filter.FilterTemplate2.filterBatchNoSV():89
com.dremio.sabot.op.filter.FilterTemplate2.filterBatch():61
Let me get some polygon and line data into my db for spatial query tests!
from dremio-udf-gis.
sheinbergon
commented on June 5, 2024
Please post the final query here
from dremio-udf-gis.
jaysnm
commented on June 5, 2024
Sorry! Here are the two queries
SELECT *, ST_AsText(FROM_HEX(geom)) AS geometry_wkt FROM "Open Vector Data".public."ne_110m_populated_places_simple_wkb" WHERE ST_Equals(geom,ST_GeomFromText('POINT (31.1999971 -26.4666675)'))
SELECT *, ST_AsText(FROM_HEX(geom)) AS geometry_wkt FROM "Open Vector Data".public."ne_110m_populated_places_simple_wkb" WHERE ST_Within(geom,ST_GeomFromText('POINT (31.1999971 -26.4666675)'))
from dremio-udf-gis.
sheinbergon
commented on June 5, 2024
Each time you work on geom data, you need to decode it from hex. So either do a subselect, or run
SELECT *, ST_AsText(FROM_HEX(geom)) AS geometry_wkt FROM "Open Vector Data".public."ne_110m_populated_places_simple_wkb" WHERE ST_Equals(FROM_HEX(geom),ST_GeomFromText('POINT (31.1999971 -26.4666675)'))from dremio-udf-gis.
sheinbergon
commented on June 5, 2024
Great. I'll create a release and update the wiki.
from dremio-udf-gis.
jaysnm
commented on June 5, 2024
Sure. Cheers!
from dremio-udf-gis.
bigfacewo
commented on June 5, 2024
@sheinbergon It would be better if the "from_hex" function is not used for format conversion, or if it can be converted automatically. I don't need to use this function when using PostGIS. Maybe the problem is that dremio can't correctly identify the type of WBK field?
from dremio-udf-gis.
sheinbergon
commented on June 5, 2024
@bigfacewo Thank you for your input.
I can't really avoid the FROM_HEX function. Dremio maps PostGIS Geometry fields as varchars, causing them to be sent as Hex-Encoded (E)WKBs. I can have the ST_GeomFromWKB take care, but that's not what the functions does according to OGC standards (it expects binary input). There's no ST_GeomFromHexWKB, so I think it's best to just stick to using FROM_HEX. I hope that clarifies my choices.
from dremio-udf-gis.
Related Issues (7)
- IllegalArgumentException: Points of LinearRing do not form a closed linestring HOT 4
- Add Geometry Validation functions from PostGIS HOT 8
- UnsupportedClassVersionError: STGeomFromText has been compiled by a more recent version of the Java Runtime HOT 8
- We got an unexpected error after performing an intersect between two spatial datasets. HOT 15
- ST_Transform error with doughnuts HOT 12
- CompileException: Unknown variable or type "FunctionHelpersXL". HOT 8
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from dremio-udf-gis.