0

I am trying to make an apache superset chart with map box view. I have to set latitude and longitude columns. But these data are in a postgresql + postgis database. So, latitude and longitude are in the same column location. An sql query would be like this:

SELECT ST_X(location), ST_Y(location) FROM Address

How can I make superset get latitude with the function ST_X()?

TylerH
  • 20,799
  • 66
  • 75
  • 101
fares
  • 83
  • 2
  • 7
  • I did't get your question very well. You want to list **only** the longitude values for all records, is it? – Jim Jones Mar 18 '18 at 12:27

2 Answers2

0

Mapbox also supports other geometry flavours, such as WKT (Well Known Text) and GeoJSON, so it is normally not necessary to split them in X,Y or Y,X pairs.

To retrieve GeoJSON from PostgreSQL (+PostGIS):

SELECT ST_AsGeoJSON(location) FROM t;

And as WKT:

SELECT ST_AsText(location) FROM t;
Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • My issue is with apache superset charts. If I understood well, I have to select a column for latitudes and a column for longitudes. And it seems that I can not use these functions for querying. – fares Mar 18 '18 at 13:15
  • Your query is actually right! Maybe you're missing an alias for each column, so that the system can distinguish them better? `SELECT ST_X(location) AS lon, ST_Y(location) AS lat FROM Address` – Jim Jones Mar 18 '18 at 13:17
  • Thanks but my problem is with apache superset. The chart module with mapbox template does not seem to accept these kind of queries. I only found a form where I select a column for latitudes and a column for longitudes. And I want to make it accept a query like what we wrote. – fares Mar 18 '18 at 15:55
0

Superset makes it possible to create "virtual" columns in tables.So I created columns latitude with this expression: ST_X(location) And that worked thanks to this superset team: https://github.com/apache/incubator-superset/issues/4640#event-1527353388

fares
  • 83
  • 2
  • 7