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.

closed as off-topic by dezso, Colin 't Hart, McNets, joanolo, James Anderson May 24 '17 at 13:44

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – dezso, Colin 't Hart, McNets, joanolo, James Anderson
If this question can be reworded to fit the rules in the help center, please edit the question.

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;
  • Yes, I'm trying something like that, but still gives an error on LIMIT 1 – Newwt May 22 '17 at 8:19
  • updated to avoid error – Vao Tsun May 22 '17 at 8:25

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