Geojson

done
low_priority
geo
reply

(Alexandre JOURNO) #1

Hi,

I understood Looker now handles geojson features.
I typed the following query in the SQL runner, which worked well, where I create lines for each journey of a user.

WITH timeline as (
      SELECT j.id as journey_id, s.gps_longitude as longitude, s.gps_latitude as latitude
      FROM journeys as j, syntheses as s, locations as l
      WHERE l.id = j.location_id
      AND s.vehicle_id = j.vehicle_id
      AND b.status = 'ended'
      AND s.created_at < b.returned_at
      AND s.created_at > b.from
      ORDER BY s.created_at
    )
    SELECT journey_id, ST_SetSRID(ST_MakeLine(ST_MakePoint(longitude, latitude)), 4326) as geometry
    FROM timeline
    GROUP BY journey_id
    HAVING ST_NPoints(ST_MakeLine(ST_MakePoint(longitude, latitude))) > 1

When I import this query in my Looker project (as a lookml) however, it seems I can only handle the geometry I created as string and not as a geographical object I could display in a look.

Many thanks for your answer

Alexandre


(Izzy) #2

This is an interesting one…

So Looker is compatible with TopoJSON (and maybe GeoJSON, although I’ve only ever used TopoJSON files), but that requires you to actually upload a .topojsonfile to your lookml project, and then define it in the model using map_layer. We have some fairly detailed instructions here: Creating Custom Map Regions.

At first glance, it looks like you’re attempting to generate geometries from a SQL query and then display those in the map visualization, which won’t work. We do support any functions in the underlying dialect (so you can use things like ST_Distance easily), but functions that return geographical features are going to be hard for Looker to interpret. I’m sure the table of results will return some crazy json values or lat/longs for the geometry column, but Looker doesn’t know how to read geometries right out of the result set yet— Only lat/long, or property_keys that tie to your uploaded topojson (that’s how we automatically map States, countries, zipcodes, etc.).

Check out that article!

And, if anyone has done this before, feel free to prove me wrong and show that Looker can in fact do this! I’m just spitballing.