Subscribe to this thread
Home - General / All posts - Connecting points on the same route
CuDomagoj70 post(s)
#26-Jun-18 18:46

I have a problem I can not solve. u Have Manifold 8 and Radian Studio.

I have a set of points (addresses) in SHP and in the csv I have the points that are rounded up by that row (date and time).

It's as if you have a car tour point, but you do not know which point you belong to which track.

I want to get lines that would tie the points in the same street (StreetCode) by the tour sequence and then add the number of that route.

The points cyclic repeated every 3-4 days.

There is also a line representing the axis of the street, but I do not know if and how to use it.

Every idea is welcome. They can also put the card data if you want to try it.

CuDomagoj70 post(s)
#27-Jun-18 08:01

Ha, here is is Map, and picture with relation for data.

How to do that in Radian Studio or in Manifold 8?

Attachments:
Location.map
Manifold01.jpg

adamw

8,090 post(s)
#27-Jun-18 16:03

I am not sure I am understanding what you are after completely, so let's do an intermediate step.

This query in Manifold 8:

--SQL

SELECT vd.[CustomerID], vd.[DateTime], lgis.[StreetCode], lgis.[StreetName]

FROM ([VisitData] vd

  INNER JOIN [Location_CRM] lcrm ON vd.[LocationID] = lcrm.[LocationID])

  INNER JOIN [LocationGIS Table] lgis ON lcrm.[StreetID] = lgis.[StreetID]

    AND lcrm.[LocationPoint] = lgis.[LocationPoint]

ORDER BY vd.[CustomerID], vd.[DateTime]

...will produce a list of locations for each customer ([CustomerID]) in the order they were presumably visited (ORDER BY ...[DateTime]) citing the street for each ([StreetID] and [StreetName] because I thought it would help look at the data).

What's next? Do you want to take each customer and produce a line of the locations (each location is a point = has a geom) he visited on each street he was in (so, if customer X went to several locations on street A, then to several locations on street B, then to some more locations on street A, we should produce three lines for him)?

CuDomagoj70 post(s)
#28-Jun-18 09:11

Thanks for this first step.

There are two workflows which i must follow.

1) connect with line each visited customer location in the street, but if another street is connected to the current street, it must be the same line for this day.

2) Second workflow is simple SQL where i must find customer location not visited this day.

adamw

8,090 post(s)
#28-Jun-18 15:49

See attached MAP file for a take on 1.

There is a new drawing named [Streets] and a query named [Compose Streets] that populates it. The query groups locations visited for each street on each particular day and creates a line for these locations in order they were visited. I don't know if you need a separate line for each customer, if you do, this is easy to add. I do not completely understand what you mean by "if another street is connected to the current street, it must be the same line for this day".

The query:

--SQL

 

INSERT INTO [Streets] ([YMD][StreetCode][Geom (I)])

SELECT [YMD][StreetCode], ConvertToLine(AllCoords([Geom (I)])) AS [Geom]

FROM (

 

SELECT Left(vd.[DateTime], 10) AS [YMD],

  lgis.[StreetCode], lgis.[Geom (I)]

FROM ([VisitData] vd

  INNER JOIN [Location_CRM] lcrm ON vd.[LocationID] = lcrm.[LocationID])

  INNER JOIN [LocationGIS Table] lgis ON lcrm.[StreetID] = lgis.[StreetID]

    AND lcrm.[LocationPoint] = lgis.[LocationPoint]

ORDER BY vd.[DateTime]

 

GROUP BY [YMD][StreetCode]

You can overlay the lines with other layers in the map. The lines zig-zag a lot, so I checked whether those zig-zags are in the data (see my other query), and they indeed are.

Hope this helps a bit.

Attachments:
Location-mod.map

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.