Not able to select lat long in pin type maps [solved]

[rakesh_jb]

Hi Guys,

Please let know how to select lat long field in pin maps. Not able to select at all.

[AndrewMBaines]

In the Data Model, have you specified that the fields are Latitude and Longitude? Like this:

[rakesh_jb]

Thank you for the help. It worked.

[denix]

Hi all and sorry to "reopen" this. I have the same issue with my model. I have a geojson field with the lat/long points like this:

"{'type':'FeatureCollection','features':[{'type':'Feature','geometry':{'type':'Point','coordinates':[1.6,42.5]},'properties':{'prop0':'value0'}}]}"

using some horrible regex in the model, I extract the Latitude and Longitude in two different columns and I have declared them as "Latitude" and "Longitude" in the metadata editor of the model. However, I cannot select them

What am I doing wrong? Thanks

[AndrewMBaines]

The longitude and latitude are declared in the metadata. Doesn't matter what the column is called.
Save your model, define the metadata, then create a new question with the model as the data source.

[denix]

Thank you @AndrewMBaines . Unfortunately, this is already the case

As you can see from my image, Long and Lat are correctly defined as Longitude and Latitude in the Metadata for my model.

[AndrewMBaines]

Yes, but your question doesn't appear to be using your model. Isn't it using the SQL that was used for the model?

[denix]

Thanks for the hint. I reviewed my SQL query (I am on MariaDB by the way) and this is it:

WITH region_selected as (
  SELECT [[{{region}}#]]'no region'
    AS region_selected
),
country_list AS (
  SELECT DISTINCT
    countries.iso_2
  FROM
    countries
    LEFT JOIN region_selected ON true
  WHERE
    (
      region_selected.region_selected = 'no region'
      OR regional_group LIKE CONCAT('%', region_selected.region_selected, '%')
    )
    AND {{country}}
),
ed3001_w_countries AS (
  SELECT
    *,
    country_list.iso_2 AS country_iso_2
  FROM
    {{#280-ed300x-model}} AS ed300x_model
    INNER JOIN country_list ON ed300x_model.countries = country_list.iso_2
  WHERE
    {{theme}}
    AND {{subtheme}}
),
ed3001_w_countries_status_true AS (
  SELECT
    *
  FROM
    ed3001_w_countries
  WHERE
    commitment_value = '2'
)

SELECT
  *
FROM
  ed3001_w_countries_status_true


/*
regions list for filter:
Africa
Arab States
Asia and the Pacific
Europe and North America
Latin America and the Caribbean
*/

However, I still cannot select Long or Lat, unless I am missing something very important:

[AndrewMBaines]

Yes, you've designed your model but you're not using the model for the question.

[denix]

Ciao @AndrewMBaines thanks to your suggestions I went back to study and read documentation. at the end this is the code I have been able to produce:

WITH 
region_selected AS (
  SELECT [[{{region}}#]]'no region'
    AS region_selected
),
country_selected AS (
  SELECT DISTINCT 
    countries.iso_2,
    countries.iso_3,
    countries.title_en
  FROM 
    countries 
    LEFT JOIN region_selected ON true
  WHERE
    (
      region_selected.region_selected = 'no region'
      OR regional_group LIKE CONCAT('%', region_selected.region_selected, '%')
    )
    AND {{country}}
),
theme_selected AS (
  SELECT [[{{theme}}#]]'no theme'
    AS theme_selected
),
subtheme_selected AS (
  SELECT [[{{subtheme}}#]]'no subtheme'
    AS subtheme_selected
)

SELECT 
  ed300x.*,
  country_selected.title_en
FROM 
  {{#140-ed300x-model}} AS ed300x
  INNER JOIN country_selected ON ed300x.countries = country_selected.iso_2
WHERE 
  ed300x.indicators NOT IN (12594, 12604, 12609, 12621, 12626, 12630, 12632, 12636, 12638)
  AND ( 
    ed300x.Theme = (SELECT theme_selected FROM theme_selected)
    OR ed300x.terms = (SELECT subtheme_selected FROM subtheme_selected)
  )
  AND ed300x.commitment_value = 2
GROUP BY 
  country_selected.title_en;

it is way better, however , the problem stays the same and here I AM using the ed300x.Latitude from the model. However, they are not showing on the map options Is there a way to force the category in code?

Thanks

[AndrewMBaines]

Is that used for the model or the question? I still think you're SQL is correct, the problem is you're either using the SQL for question (instead of the model) or you've not defined the metadata in the model.

[denix]

The model has the metadata correctly set, and this is the question that SHOULD display the Latitude and Longitude for the map

[denix]

Works!

To make it work, after reading looot of documentation, I made my query looks like this:

SELECT 
  cast(xxx.Latitude as decimal(6,2)) as Latitude,
  cast(xxx.Longitude as decimal(6,2)) as Longitude,
  xxx.fieldA,
  xxx.fieldB,
  country_selected.title_en AS Country
FROM 
  {{#123-xxx-model}} AS xxx
  INNER JOIN country_selected ON xxx.countries = country_selected.iso_2

Now it works!

To find it I started debugging the values passed in the logs and I saw that these columns were set in varchar!

Maybe it should be documented somewhere that even if in the model the metadata are correctly set, the value of the result in a SQL query can be treated differently.

Thanks for your time and help. I hope this can save others struggle!