I have the following query which I want to apply on my from postgis to spatialite imported database. The problem is that the result of the distance is always null, while the other selected coloumns are containing the expected values. So how can I use this query in spatial lite? It should calculate the distance from the given point to all in the databases stored points and sort them by the distance.
"select x1, y1, source, st_distance(st_transform(st_geomfromtext('POINT(' || x1 || ' ' || y1 || ')',4326),32633),st_transform(st_geomfromtext('POINT(15.442161 47.068170)',4326),32633)) from tablex order by st_distance asc limit 1;"
I tried it in spatialite with the following query:
"select x1, y1, source, distance(transform(geomfromtext('POINT(' || x1 || ' ' || y1 || ')',4326),32633),transform(geomfromtext('POINT(15.442161 47.068170)',4326),32633)) from tablex order by distance asc limit 1;"
If I do not use the transform function I get the right result, but the distance is in degrees and not in metres, as I would need it.