Support postgis commands

jo-chemla commented 1 year ago

A clear and concise description of what the bug is.

How to reproduce the bug

Apache Superset is installed via docker-compose. I'm connecting to one of my postgis DB through superset, but when in the SQL Lab in superset (or when computing additional columns), then it seems like the postgis extension is not installed since SELECT PostGIS_Full_Version() or SELECT ST_AsGeoJSON(location) FROM db; both result in an error, No function matches the given name and argument. Is there a way to enable the postgis extension within superset? The location column stores the polygon/point in WKB format.

What I've tried:

Inside the docker-compose file, I replaced the image: postgres:14 with image: postgis/postgis:14-master
then created a superset user via the container console with adduser superset
to then connect to the DB via psql, list db via \l then connect to superset db via \c superset
and finally enable extension via CREATE EXTENSION postgis;
Running SELECT PostGIS_Full_Version(); in the container console do work, but running the same command in the sqllab within superset web ui still fails : No function matches the given name and argument types. You might need to add explicit type casts.

ST_xyz functions should be supported within the sqllab and column creation.

Actual results

ST_xyz functions should be supported within the sqllab and column creation.

Screenshots

Environment

(please complete the following information):

browser type and version: Chrome 115.0.5790.110
superset version: 2.1.0

jo-chemla commented 1 year ago

Possible useful tool: have a look at GeoAlchemy2 which could be used to parse the sqllab query instead of sqlalchemy?

rkortekaas commented 10 months ago

@jo-chemla Any news on this? I'm running into the same issue..

rusackas commented 6 months ago

If anyone wants to contribute postgis support/drivers/docs, or anyone wants to open a SIP to introduce GeoAlchemy2 into Superset as an additional layer (perhaps alongside sqlalchemy?) that would be welcomed. Right now, this thread has been silent since last year, and doesn't seem to be a bug, per se. If you want, I'm happy to reopen this and convert it to a discussion to keep the ball rolling!

jo-chemla commented 6 months ago

Thanks @rusackas for the ping, indeed not a bug and would therefore be happy to move this to a discussion/reopen, to eventually lead to a SIP.

Improving superset support for geospatial databases via GeoAlchemy to enable PostGIS - indeed probably alongside sqlalchemy - would bring lots of users/orgs from geospatial communities a'd let them connect their DBs to superset for dashboarding and interactive exploration.

Happy to get some directions or provide feedback on this matter

rusackas commented 6 months ago

Yep, if we can connect to everything we do today and more, I think people would be all for it! Reopening and moving to a Discussion thread!

apache / superset