Support postgis commands

[jo-chemla]

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]

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

[rkortekaas]

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

[rusackas]

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]

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]

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!

[vpicavet]

Hi,
We at Oslandia would be really interested in participating into more geospatial support in Superset. We use Superset intensively for our own needs and for our clients, and have extensive knowledge of PostGIS and Python.
We have studied priority elements for geospatial support in Superset, and activating geoalchemy is key to start moving things forward.

As for now, we still lack funding for this unfortunately. We have internal opensource grants we could leverage, but budgets for 2024 are already defined. We will continue to look for funding for this to see light though. Do not hesitate to reach us if you have money and need brains :-)

[ramirobarreca]

Here it is a new symptom that could speak about a version number problem that leads to different treatment at the moment of parsing any query including PostGIS functions o types.
Even though we run the same query in the sqllab within superset web ui, we have different results:

• Connecting to a PostgreSQL 14.13 database using PostGis 3.3.5 extension, it works OK.
• Connecting to a PostgreSQL 14.12 database using PostGis 3.4.1 extension, it FAILS with function NOT FOUND.
  Maybe a driver problem with a PG or PostGis version compatibility issue?

[gmsiv]

This appears to be a search path issue. If you qualify the PostGIS functions with the public schema (or the schema in which you created them) then there is no issue using them in my testing when I ran into this problem.

st_intersects(wkb_geometry,geometry) fails
public.st_intersects(wkb_geometry,geometry) succeeds