1

I have this query, which I don't know why it doesn't work, even though if I change some parameters, it works just fine. Here is the query:

SELECT round(ST_Distance_Sphere(
                       (SELECT coordinates FROM houses WHERE id = 7)),
                       (SELECT wkb_geometry FROM coastline ORDER BY wkb_geometry <->
                       (SELECT coordinates FROM houses WHERE id = 7)) LIMIT 1)
                       )) AS DISTANCE;

ERROR: syntax error at or near "LIMIT"
LINE 4: (SELECT coordinates FROM houses WHERE id = 7)) LIMIT 1)

This works:

SELECT round(ST_Distance_Sphere(
                 ((ST_GeomFromText('POINT(10.806527 54.101916)',4326))),
                 (SELECT wkb_geometry FROM coastline ORDER BY wkb_geometry <->
                 ((ST_GeomFromText('POINT(10.806527 54.101916)',4326))) LIMIT 1)
                 )) AS DISTANCE;

I tried removing LIMIT, but it leads me to problems with the parentheses, and there is where I get lost.

1 Answer 1

1

ST_Distance_Sphere has first argument (SELECT coordinates FROM houses WHERE id = 7) then you close ), so (SELECT wkb_geometry... is second argument for round, which I suppose is not what you want.

So I believe you just got lost in parentheses - try rewriting code "in ladder" so every parenthesis has closing one in same indentation...

based on your working query - are you trying to do this?

SELECT round(
  ST_Distance_Sphere(
    ((SELECT coordinates FROM houses WHERE id = 7 LIMIT 1)
  ,
  (SELECT wkb_geometry FROM coastline ORDER BY wkb_geometry <-> (SELECT coordinates FROM houses WHERE id = 7) LIMIT 1))
)) AS DISTANCE;
2
  • Yes, I'm trying something like that, but still gives an error on LIMIT 1
    – Newwt
    May 22, 2017 at 8:19
  • updated to avoid error
    – Vao Tsun
    May 22, 2017 at 8:25

Not the answer you're looking for? Browse other questions tagged or ask your own question.