I'm basically trying to do the same thing as outlined here: Finding the point closest to a point on a near feature
However, the results I'm getting with the queries suggested in the accepted answer don't make sense. Here's a screenshot of (a part of) my data, a bunch of points and lines:
The first step is to find the line closest to a given point:
select "p"."id" pid, "l"."id" lid, min(Distance("p"."geom","l"."geom")) as distance from "net_clipped" p, "ver01_l_clipped" l group by pid order by pid;
The results contain only one line id and the distance seems to be wrong:
pid lid distance 1 724 3.19983e+07 2 724 3.19995e+07 ... and so on
The actual distance of the points to the next line should be just a few meters.
The data came from two shape files that I imported into a spatialite database using QGIS' database manager. They look fine in both QGIS 2.14 and ArcMap 10.3. I'm running the queries with QSpatiaLite.
What's wrong here?