sheinbergon / dremio-udf-gis

OGC/GIS functions and extensions for Dremio

Dremio Geo-Spatial Extensions

What you get

• Widespread OGC implementation for SQL (adheres to PostGIS standards)
  • Supported input formats: WKT, WKB (HEX or BINARY)
    • Supported output formats: WKT, WKB, GeoJSON
• Easily installable Maven-Central/Github artifacts shaded jar artifact
• Dremio CE version compatibility (new versions will be released with each community edition)
• Up-2-date Proj4J & JTS geometry based implementation

Sponsorship

Enjoying my work? A show of support would be much obliged 😁

  

Installation

• Take the shaded jar for the desired version and place inside your Dremio installation ($DREMIO_HOME/jars/3rdparty)
• Restart your Dremio server(s)
• Rejoice! (and see the WIKI for detailed usage instructions)

Version Compatbility

Library Version	Dremio Version	Status
0.2.x	20.1.0	Legacy
0.3.x	21.1.1	Legacy
0.4.x	21.2.0	Legacy
0.5.x	22.0.0	Legacy
0.6.x	22.1.1	Legacy
0.7.x	23.0.1	Legacy
0.8.x	23.1.0	Legacy
0.9.x	24.0.0	Maintained
0.10.x	24.1.0	Maintained
0.11.x	24.2.6	Maintained
0.12.x	24.3.0	Maintained

Usage Notes

As opposed to PostGIS, Dremio is only a query engine based on existing/projected data sources/lakes.
That means that Geometry is not a natively supported data type, and you can only access it if
it's being properly projected from the data sources (For example, PostGIS Geometry is read as an EWKB HEX encoded string).

In order to successfully use the provided GIS functions, you must first make sure the geometry is in WKB (BINARY) format. If it's not, you need to decode it:

• if the input is in WKT format, use ST_GeomFromText
• if the input is a HEX encodedWKB, use Dremio's FROM_HEX

This library uses Dremios' Arrow buffers (ArrowBuf) to maintain geometry data in binary (WKB) format (for performance and efficiency)
when interchanging it between GIS functions, which is of course undecipherable for the naked eye. When running queries from the UI,
WKB output will always be base64 encoded.

In order to resolve Data back to human-readable format (WKT), use ST_AsText/ST_AsGeoJson

Example:

SELECT ST_AsText(
    ST_Difference(
      ST_GeomFromText('LINESTRING(50 100, 50 200)'),
        ST_GeomFromText('LINESTRING(50 50, 50 150)')
))

Roadmap

• Frequent version/dependency updates
• Add more OGC/PostGIS matching functionality
• Add Geography type support

Noteworthy Mentions

Work in this repository was originally based on the following sources:

• Apache Drill GIS Functionality
• Christy Haragan's initial port

dremio-udf-gis's Issues

Add Geometry Validation functions from PostGIS

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.

UnsupportedClassVersionError: STGeomFromText has been compiled by a more recent version of the Java Runtime

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!

CompileException: Line **, Column **: Unknown variable or type "FunctionHelpersXL"

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!

IllegalArgumentException: Points of LinearRing do not form a closed linestring

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

CompileException: Unknown variable or type "FunctionHelpersXL".

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:

We got an unexpected error after performing an intersect between two spatial datasets.

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

ST_Transform error with doughnuts

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