2

In a previous question, I discovered that ST_LocateAlong along a LineStringM is not implemented the same way in Spatialite than in PostGIS.

While PostGIS interpolates between measures, this is not the case in Spatialite. In Spatialite, only vertices which exactly match the m-values are returned.

Example:

SELECT
    ST_AsText(ST_LocateAlong(ST_GeomFromText('LINESTRINGM(0 0 1, 0 1 2, 1 1.5 3, 1 2 3.5)'), 2));
  • PostGIS returns MULTIPOINT M (0 1 2)
  • Spatialite returns POINT M(0 1 2)

Both implementations return (almost) the same result, except that PostGIS is MULTIPOINT M instead of POINT M.

When the measure is not on a vertex, PostGIS interpolates it while Spatialite returns NULL:

SELECT
    ST_AsText(ST_LocateAlong(ST_GeomFromText('LINESTRINGM(0 0 1, 0 1 2, 1 1 3, 1 2 3.5)'), 2.5));
  • PostGIS returns MULTIPOINT M (0.5 1 2.5)
  • Spatialite returns NULL

Is there an (easy) way to emulate PostGIS's behaviour in Spatialite?

|improve this question
  • There shouldn´t be a difference since they use the same Library (GEOS). But in your former Question you quoted different manual descriptions for the tools, which is not the case. I tend to think it might be a problem of the virtual tables or you used or a wrong called geometry. – Matte May 28 '16 at 16:24
  • @Matte, what should be the correct behaviour: to interpolate between measures like PostGIS or to not do it like Spatialite/GEOS? I've tested it in pgAdmin for PostGIS and in the DB Manager for Spatialite so I think the error does not come from the virtual tables from the previous question. – thibautg May 28 '16 at 16:47
  • As i read the descriptions it does not interpolate...But i did not use them in a real problem, just with test features. – Matte May 28 '16 at 18:30
  • I think PostGIS's implementation (with interpolation) is infinitely more useful. In fact I don't see any use case where Spatialite's implementation (only return m-values from actual vertices) would be of any use. The use cases are to locate points on lines like car accidents on roads, maintenance works on railways, valves on water pipelines, .... which generally don't occur on an actual line vertex – thibautg May 28 '16 at 23:28
3

The implementation is obviously different between spatialite and postgis. Spatialite does not use GEOS for this operation (as of the date of writing, its open coded)

The correct behaviour is not obvious. To me, M is a measure (without defined semantics), which you can't safely interpolate. It could be used for an enumerated value (e.g. only integer values are valid), it could be a logarithmic value, or something else non-linear. The implementation in PostGIS is from some committee draft of the standard, and I don't have the actual released spec, so perhaps there is something better defined.

If you want something that interpolates in spatialite, use Z.

|improve this answer
  • Interesting, I've always thought of the M-value as a monotonic, always increasing value along a line. But you are right that the definition is not that obvious, and that it could mean something else. However, I think PostGIS's implementation is more suited to my main use case, which is to locate events/assets on railway tracks. I find it confusing that Spatialite (and therefore the Virtual Layer function in QGIS) has a different interpretation than that of PostGIS. – thibautg May 29 '16 at 0:16
  • In general, postgis and spatialite are similar, but they have different code bases and sometimes different focus, so you should assume they are different. – BradHards May 29 '16 at 2:31
  • So you use M to mean something like a pole number? – BradHards May 29 '16 at 2:32
  • Yes there are kilometer poles on the side of railway tracks and the events are located somewhere between them (for example "at km 23.893"). But the distance between km poles is not always exactly 1 km (for example if there are two tracks in parallel, the outside track in a curve will be slightly longer than the inside track). That's why it is interesting to use an interpolated M value. – thibautg May 29 '16 at 4:41
  • Thanks for the info, i guessed from the absolutly same definitions in the manuals of the functions in SpatialLite and PostGis that they implement the function the same using the same underlying topology suite. So that means the manual of PostGis is not correct as it states that it returns matching measures. – Matte May 29 '16 at 7:03

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.