Route measures

[Duncan]

Hi

 

I am attempting to create a function to mimic ESRI linear referencing hatching to label measures along a road.

 

It seems that the only way to do this is to create a point table with points located at the measures along the line.

I haven't found an easy way to export values from the measures using ESRI products, but have managed to do this

with Spatialite using

 

SELECT PK_UID, ROAD, ROAD_NAME, START, END,
ST_Line_Interpolate_Equidistant_Points(Geometry, ST_Length(Geometry) / CvtFromKm((END - START)) * 10) AS Points,

ST_Length(Geometry) / CvtFromKm(END_SLK - START_SLK) * 10  AS Interval
FROM Network;

 

which works very nicely.

 

The measure is a reference, hence the interval adjustment and herein lies the problem.

 

The query returns a MULTIPOINT geometry, but I need a single point to display the M value or a field derived from it.

I have tried CastToPoint and CastToSingle, but both return NULL so I am obviously misunderstanding the functions.

 

I would also like to recalibrate the M values and/or create a field derived from it which progressively adds 0.01 intervals to the START value.

Presumably this would need to be done before exploding the MULTIPOINT geometries, but perhaps could be related by the road and original PK_UID and ordered by M.

The alternative could be Line_Locate_Point which I use for single points but I am not sure how the query would work against a table of points

 

I had an idea that this might be a job for WITH RECURSIVE mentioned in a previous post, but not yet available

 

Any help would be appreciated

 

Thanks

[Jukka Rahkonen]

Hi,

Castopoint does not work because cast only converts one field into some other format but after the cast it will still be one field which belongs to one feature . You want to explode the multipoint geometry into many individual points. There is a dedicated tool in spatialite-gui 1.7.1 for this named "separating elementary Geometries" which explodes the geometries and writes them into a new table. However, it may not help you because you would probably like do it from the command line with SQL.

-Jukka Rahkonen-

[a.fu...@lqt.it]

On Fri, 6 Jun 2014 00:27:33 -0700 (PDT), Jukka Rahkonen wrote:
> There is a dedicated tool in
> spatialite-gui 1.7.1 for this named "separating elementary
> Geometries"
> which explodes the geometries and writes them into a new table.
> However, it may not help you because you would probably like do it
> from the command line with SQL.
>

using the spatialite CLI tool you could use the .elemgeo
"dot macro" in order to perform the same identical task;
and invoking any "dot macro" from within any SQL batch
script is a standard feature supported by the CLI tool.

alternatively a more exciting way to perform a task like
this by simply using pure SQL statements (WITH RECURSIVE
clause) is now available, but it strictly requires using
a very recent version of SQLite (3.8.3 or any following);
here you'll find a practical example:

https://groups.google.com/forum/#!topic/spatialite-users/A5wu8E7KDCc

bye Sandro

[Duncan]

 

Hi

Thanks for the responses. I am actually using the spatialite-gui 1.7.1 and separating elementary Geometries worked well. I thought I had tried that, but maybe not.

I notice that (in my test geom anyway) that the M values are set to 0. Not that it matters much in this case as I need to set the measure values in a field anyway.

I'm looking forward to trying WITH RECURSIVE when the new version arrives. That should sort the last part of the job

regards

Duncan

[Duncan]

On Friday, June 6, 2014 at 10:42:16 AM UTC+8, Duncan wrote:

Hi

 

I am attempting to create a function to mimic ESRI linear referencing hatching to label measures along a road.

 

Hi again. I have revisited this  now that WITH RECURSIVE is available and using Sandro's example to extract coordinates from

 

https://groups.google.com/forum/?fromgroups#!searchin/spatialite-users/recursive/spatialite-users/A5wu8E7KDCc/gS3bneDxrn0J

 

I have to say that I'm struggling with the intricacies of the query, but I now have a nice pointM geometry table using

 

SELECT id, ne, n AS pointN, ST_X(pt) AS X, ST_Y(pt) AS Y, pt,
MakePointM(X(pt), Y(pt), ss + (es - ss) * Line_Locate_Point(Transform(geom, 3112), Transform(pt, 3112)), 4283) AS PointM
FROM (WITH RECURSIVE item(n, ne, ss, es, id, pt, geom) AS
(VALUES(0, NULL, 0, 0, NULL, NULL, NULL)
  UNION 
  SELECT n+1, nw.ne, nw.ss, nw.es, nw.id, ST_PointN(nw.geom, n+1), nw.geom
  FROM item, (SELECT pk_uid AS id, NETWORK_ELEMENT as ne, START_SLK AS ss, END_SLK AS es, geometry AS geom
              FROM Network WHERE ROAD = 'M008' AND ss > 97) AS nw   /*Where*/
  WHERE n < ST_NumPoints(nw.geom))
  SELECT n, ne, ss, es, id, pt, geom FROM item)
GROUP BY ne, id, pointN
HAVING id IS NOT NULL;

What I'd like to do is then recreate the line data by NETWORK_ELEMENT from the pointM geometry. Is this possible within this query or should I create a temp table?

Presumably I would need to use WITH RECURSIVE again to create the line as there is no MakeLine(ID, ptN) function that I could find.

Thanks

Duncan