2

I am trying to use st_distance to find the nearest line string to a point. I have two tables in cartodb one is a table of points and the other table is poly lines. To see if I had the logic right I did a st_distance query on the points table from a lat,long point. Everything worked as planned. Then I tried creating a query that would find the closest polygon from a point in my point table.

The error I am getting is this "cross-database references are not implemented: cartodbpoint.the_geom.stdistance" and the query I am trying out is:

    SELECT cartodbpoint.cartodb_id,
    MIN(cartodbpoint.the_geom.STDistance(cartodbline.the_geom)) as MinDistance
    FROM cartodbpoint, cartodbline
    GROUP BY cartodbpoint.cartodb_id

My goal is to get the distance from that point to the nearest line string.

3

As far as I know, this the normal way to run this type of function. Instead you want something like this,

SELECT cartodbpoint.cartodb_id,
MIN(ST_Distance(cartodbpoint.the_geom, cartodbline.the_geom)) as MinDistance
FROM cartodbpoint, cartodbline
GROUP BY cartodbpoint.cartodb_id

Where ST_Distance is a function and it takes two geometries. You might try this variation to see which is faster,

SELECT c.cartodb_id,
ST_Distance(
    c.the_geom, 
    (SELECT the_geom FROM cartodbline ORDER BY the_geom <-> c.the_geom LIMIT 1)
) as MinDistance
FROM cartodbpoint c
  • Perfect Andrew thank you. The second query seems faster. Is there a way to see how long it took using the dashboard ? Or do i have to form a query to that? – CrazySpatial Dec 27 '13 at 18:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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