search

stackabletech / demos

This repo contains SDP stacks and demos
https://docs.stackable.tech/home/stable/demos/
Apache License 2.0
0 stars 3 forks source link

Visualize ocean floor geo data in Superset (with Trino, from S3) #88

Closed fhennig closed 3 months ago

fhennig commented 3 months ago

Working on this: https://github.com/stackabletech/marispace-x/issues/71

Data uploaded here: https://repo.stackable.tech/repository/misc/marispace/multibeam_data_point_density_example.parquet

The coordinates use this reference frame: https://epsg.io/32632

The PR also makes a change in the superset-trino-s3 Stack which is also used by the Taxi demo. The change raises the row limit in superset, so larger queries can be made. This is necessary for this demo, to get a complete picture of the ocean floor.

fhennig commented 3 months ago

It was straightforward to adapt the taxi data demo, but now I can't find a good Chart type to plot the data.

The data would best be plotted with a surface plot, but Superset doesn't offer this.

Maybe a view of the data can be created with already regular buckets and then the heatmap type can be used. Or I look more into the geo plots, but they require lat/long coordinates and I don't have those.

fhennig commented 3 months ago

image

This is the visualization I have now. I got the asset working too.

Now I will just clean up some file naming.

fhennig commented 3 months ago

before merging, 046a0e5c6589644c790bbeba74406c8d18a1e4ff needs to be replaced with main in the file references.

fhennig commented 3 months ago

I have now attempted for multiple hours to get the data visualized in the deck.gl Heatmap plot type, but I can't get the coordinate conversion right.

What I did: use calculated columns in Superset to calculate lat/lon coordinates

This is based on the fixed distance that 1 degree does for lat, and then the 6 degree offset for the EPSG:32632 reference frame in eastern direction. But the location I get is still off:

image

(The big circle is where we get, the smaller one where we should be).

You can use this tool to calculate the actual coordinates that you should get.

I have asked north.io for more help on concrete conversion numbers to use, but they couldn't readily provide any.

I believe that this type of visualization is only useful if it is also accurate, so I will not attempt this any further. I have left all the info here in case anyone wants to pick it up again!

I think a more practical solution would be to use another service to to bulk coordinate conversion before importing the data. Neither Superset nor Trino is well equipped to work with the WGS 84 UTM based coordinates that are present in the data.

Edit: this tool might also help: https://live.osgeo.org/de/overview/gdal_overview.html also here is a website to convert, and links to JS and Python libs: https://epsg.io/transform#s_srs=4326&t_srs=3857&x=8.5500000&y=47.3666667

Edit: more info:

Moin Felix,

Mit welchen Visualisierungstools arbeitest du denn?

Allgemein würde ich dafür bestehende Transformer benutzen. Hier noch ein paar Infos, die dir vielleicht weiterhelfen könnten.

Der EPSG code für Lat/Lon ist EPSG:4326 (https://epsg.io/4326)
Wenn du Apache Sedona benutzt (https://sedona.apache.org/1.5.0/api/rdocs/reference/crs_transform.html). Vielleicht gibt es da auch etwas ähnliches, wenn du andere Apache Module benutzt.
Eine andere Möglichkeit wäre mit GDAL (https://gdal.org/programs/gdaltransform.html) und PROJ (https://proj.org/en/9.4/)

Beste Grüße, Daniel