sheinbergon / dremio-udf-gis Goto Github PK

The following query with uploaded data makes dremio crash. We did change the max limit of a single field using "alter system set limits.single_field_size_bytes = 2147483647".

SELECT cdda.siteName,cdda.cddaRegionCode,nuts.NUTS_ID
    FROM {dremioroot}."reference".CDDA."v-001"."cdda" as cdda
LEFT JOIN {dremioroot}."reference"."Eurostat"."Nuts1M"."v-001"."Nuts" as nuts
    ON LENGTH(nuts.NUTS_ID)>4 and --ST_Intersects(cdda.__bbox,nuts.__bbox)=true 
WHERE ST_Intersects(nuts.geo_value,cdda.geo_value)=true

Cdda.zip
Nuts.zip

FlightInternalError: Flight returned internal error, with message: ExecutionSetupException: One or more nodes lost connectivity during query. Identified nodes were [dremio-executor-0.dremio-cluster-pod.dremio01.svc.cluster.local:0].. gRPC client debug context: UNKNOWN:Error received from peer ipv4:10.50.7.14:32010 {grpc_message:"ExecutionSetupException: One or more nodes lost connectivity during query. Identified nodes were [dremio-executor-0.dremio-cluster-pod.dremio01.svc.cluster.local:0].", grpc_status:13, created_time:"2023-02-17T14:28:43.164194859+00:00"}. Client context: IOError: Server never sent a data message. Detail: Internal

Would it be possible to add the geometry validation functions. Such as ST_IsValid, ST_IsValidDetail, ST_IsValidReason and ST_MakeValid? The European Environment Agency could use this excellent work in our data reporting tool making spatial validation as well possible.

I am facing an error when tryng to transform geometries that include doughnouts (holes)
One example is:
SELECT ST_Transform( ST_GeomFromText('POLYGON ((3301892.7081 2305424.6332, 3297641.4146 2293854.3149, 3311252.1571 2288743.8019, 3322410.9401 2280361.8837, 3322085.273 2275041.252, 3318903.5687 2269530.8181, 3315052.0551 2262870.2843, 3313598.2675 2255611.9221, 3304356.0358 2255544.4336, 3299995.8432 2251719.8828, 3300298.3817 2248820.3076, 3304266.6753 2247578.2129, 3301776.1263 2238082.5652, 3283221.0582 2241578.9782, 3280799.4261 2245695.1826, 3281142.5189 2253859.4624, 3276773.4563 2256390.7988, 3270589.2771 2251898.2402, 3268897.3401 2260428.3755, 3263191.4445 2269557.4174, 3249571.2601 2278693.3564, 3249773.3834 2281355.1971, 3250227.7287 2287323.6797, 3247573.221 2288352.4905, 3242476.1795 2290332.0758, 3243878.6509 2296591.2115, 3246594.6023 2298063.0509, 3249026.1976 2297136.9709, 3259898.26 2293011.2743, 3262495.8129 2295521.9721, 3260589.2171 2300629.207, 3263318.1856 2300530.7404, 3264794.3518 2302409.892, 3262145.1912 2304764.0635, 3262200.7379 2307365.4, 3260625.93 2307497.7578, 3256755.1595 2303490.781, 3252545.7222 2307836.9957, 3253121.686 2318845.3297, 3255442.84 2321747.7637, 3260779.9173 2319719.2841, 3268846.0956 2323205.8617, 3271079.6228 2317750.6139, 3270372.2206 2315806.1767, 3268797.8507 2311480.5666, 3279698.4348 2303347.7128, 3292652.3093 2299850.1394, 3294313.4361 2305713.7012, 3301892.7081 2305424.6332), (3270969.4748 2271758.2972, 3272874.4151 2267708.8757, 3275976.4969 2266056.1655, 3276737.0802 2262242.684, 3280734.3288 2261171.7626, 3285660.5305 2261369.8463, 3286896.4781 2258685.4039, 3291253.9138 2258101.4314, 3293829.2967 2256241.5158, 3296529.1289 2256778.6072, 3296722.7488 2260852.6801, 3292037.8003 2260114.8086, 3290215.1655 2263282.8501, 3294438.177 2267457.5003, 3291842.5399 2271360.9896, 3288472.2535 2271349.9963, 3281847.6405 2275670.851, 3278803.0097 2276290.0461, 3273515.1866 2277201.8981, 3270969.4748 2271758.2972))',3035),4326)

The error is:
IllegalArgumentException: Points of LinearRing do not form a closed linestring

Hello @sheinbergon

Thank you for the effort and this amazing tool.
I was trying it and bumped into this error!

I'm using Dremio 20.1, fresh installation in a VM.

Would also be great to see more usage examples.
Thanks a lot and good work!

Got the following error "IllegalArgumentException: Points of LinearRing do not form a closed linestring" when running the query:

SELECT ST_Transform(ST_GeomFromWKB(FROM_HEX(geom)),2279)
FROM "analysis_operations"."on_demand"."project_request"
LIMIT 10

Hello @sheinbergon

This is not an issue as such but rather tool usage need. I have the tool working in my dremio cluster and now would want to do real world query from a relational database, for instance say:

SELECT col_a, col_b, ST_AsGeoJSON(geom_col) FROM my.data.space WHERE attribute='attribute value' or

SELECT col_a, col_b, ST_AsGeoJSON(geom_col) FROM my.data.space WHERE ST_Contains(geom_col, geom)

I tried something similar but there seem to be a knowledge gap in what I am doing. This is how my dataset looks with a geom column encoded as WKB

I'm using natural earth dataset in PostgreSQL/PostGIS database. This is my query and execution error!

Dremio was compiled in "JDK1.8", so I tried to compile "dremio-udf-gis" myself.

After modifying part of the code, I'm in jdk1.8 compilation succeeded. Removed unsupported syntax in 1.8.

But in actual use, it is wrong: