1

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.

9
  • Do you get expected results from: SELECT transform(geomfromtext('POINT(' || x1 || ' ' || y1 || ')',4326),32633) from tablex; ??
    – Micha
    Sep 13, 2013 at 9:27
  • Yes, in my postgis database it is working. x1 and y1 are the values taken from the database query. Sep 13, 2013 at 10:20
  • What about in Spatialite?
    – Micha
    Sep 15, 2013 at 5:42
  • The result in the column distance is NULL and not a result. If I do not apply transformation, then I get a result, but the it is in degree and not in metres, as I already wrote in my posting. Sep 15, 2013 at 13:27
  • If you do ONLY the Transform() in Spatialite, without the Distance() function, do you get correct results?
    – Micha
    Sep 15, 2013 at 18:16

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.