SQL and PostGIS Academy Query Correction

michellechandra commented 8 years ago

The following SQL Query for this lesson is returning an error in CARTO:

SELECT
  ST_MakeLine(
    ST_ClosestPoint(
      hwy.the_geom,
      mbm.the_geom)::geography,
    mbm.the_geom::geography
  ) AS the_geom_webmercator
FROM
  chandra.mississippi_blues_musicians AS mbm,
  chandra.highway_61 AS hwy

SELECT
  -- draw lines as the_geom_webmercator
  ST_MakeLine(
    ST_ClosestPoint(
      hwy.the_geom,
      mbm.the_geom
    )::geography,
    mbm.the_geom::geography
  ) AS the_geom_webmercator,
  -- include musician name
  mbm.name,
  -- include city name
  mbm.city,
  -- rounded-up distance birthplace is from the highway
  ceil(
    ST_Distance(
      mbm.the_geom::geography,
      hwy.the_geom::geography
    ) / 1609
  ) AS d
FROM
  mississippi_blues_musicians AS mbm,
  highway_61 AS hwy
UNION ALL
-- include highway in the same table
SELECT
  the_geom_webmercator,
  'US Route 61' AS name,
  null AS city,
  null AS d
FROM
  highway_61

Error

function st_makeline(geography, geography) does not exist

cc @ohasselblad S/B: 9721750

michellechandra commented 8 years ago

hey @ohasselblad - let me know when you have a moment to review the error occurring with the SQL for this lesson - https://github.com/CartoDB/academy/issues/403

andy-esch commented 8 years ago

@michellechandra, weird, that code is bad. It should be like below instead:

ST_Transform(
  ST_MakeLine(
    ST_ClosestPoint(
      hwy.the_geom,
      mbm.the_geom),
    mbm.the_geom
  ), 3857) AS the_geom_webmercator

@mehak-sachdeva, could you fix this in the lesson?

michellechandra commented 8 years ago

Okay, so the corrected query should be:

SELECT
ST_Transform(
  ST_MakeLine(
    ST_ClosestPoint(
      hwy.the_geom,
      mbm.the_geom),
    mbm.the_geom
  ), 3857) AS the_geom_webmercator
FROM
  mississippi_blues_musicians AS mbm,
  highway_61 AS hwy

Just tested and it works, thanks!

michellechandra commented 8 years ago

hey @ohasselblad - getting an error though when I modify the second query:

SELECT
  -- draw lines as the_geom_webmercator
 ST_Transform(
  ST_MakeLine(
    ST_ClosestPoint(
      hwy.the_geom,
      mbm.the_geom),
    mbm.the_geom
  ), 3857) AS the_geom_webmercator,
  -- include musician name
  mbm.name,
  -- include city name
  mbm.city,
  -- rounded-up distance birthplace is from the highway
  ceil(
    ST_Distance(
      mbm.the_geom::geography,
      hwy.the_geom::geography
    ) / 1609
  ) AS d
FROM
  chandra.mississippi_blues_musicians AS mbm,
  chandra.highway_61 AS hwy
UNION ALL
-- include highway in the same table
SELECT
  the_geom_webmercator,
  'US Route 61' AS name,
  null AS city,
  null AS d
FROM
  chandra.highway_61

The error is: "syntax error at or near "ALLSELECT"". Is the ST_Distance query correct?

Not sure what is wrong?

andy-esch commented 8 years ago

It seems like the comments (--) might be confusing Carto. Try removing the comment -- include highway in the same table and re-running. If that works, we should file another ticket in cartodb repo about sql queries with comments not being parsed correctly.

michellechandra commented 8 years ago

@ohasselblad - removing that comment fixed the issue

I can file a ticket in cartodb repo!

andy-esch commented 8 years ago

Make sure to describe that when it's there it concatenates the

... ALL
-- interesting comment
SELECT ...

INTO

SELECTALL

The expected behavior should be inserting a \n or space instead of a blank.

michellechandra commented 8 years ago

👍

mehak-sachdeva commented 8 years ago

On it!

CartoDB / academy

SQL and PostGIS Academy Query Correction #403

Error