paleolimbot commented on issue #686: URL: https://github.com/apache/sedona-db/issues/686#issuecomment-4005726632
Here's a slightly smaller one where spilling doesn't really enter into it: find the steepest spots given a mesh of elevation values (i.e., find points between 10 and 100 m away and calculate all the slopes). This one correctly identifies the cliff-like parts of Nova Scotia (and a few quarries / cell towers too). ```python # pip install "apache-sedona[db]" import sedona.db sd = sedona.db.connect() sd.options.interactive = True # curl -L https://github.com/geoarrow/geoarrow-data/releases/download/v0.2.0/ns-water_elevation.parquet -o elevation.parquet sd.read_parquet("elevation.parquet").to_view("elevation") sd.sql(""" SELECT ST_Distance(l.geometry, r.geometry) AS dist, abs(ST_Z(l.geometry) - ST_Z(r.geometry)) AS dz, l.geometry AS geometry FROM elevation as l JOIN elevation as r ON ST_DWithin(l.geometry, r.geometry, 100) WHERE ST_Distance(l.geometry, r.geometry) > 10 """).to_view("knn_slopes", overwrite=True) # 3.0s result = sd.sql(""" SELECT dz / dist AS slope, dist, geometry FROM knn_slopes ORDER BY dz / dist DESC LIMIT 10000 """).to_memtable() result ``` DuckDB is slightly faster at this one: ```python import duckdb duckdb.load_extension("spatial") duckdb.sql(""" SELECT ST_Distance(l.geometry, r.geometry) AS dist, abs(ST_Z(l.geometry) - ST_Z(r.geometry)) AS dz, l.geometry AS geometry FROM "elevation.parquet" as l JOIN "elevation.parquet" as r ON ST_DWithin(l.geometry, r.geometry, 100) WHERE ST_Distance(l.geometry, r.geometry) > 10 """).to_view("knn_slopes") # 2.4s result = duckdb.sql(""" SELECT dz / dist AS slope, dist, geometry FROM knn_slopes ORDER BY dz / dist DESC LIMIT 10000 """).to_arrow_table() ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
