Getting distance from point to nearest line string

Question

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.

Answer 1

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