Subscribe to this thread
Home - General / All posts - Joining point geometries to form a line
#14-Jan-21 07:01

I am trying to retrieve lots of old line data stored in CSV files with each record having:

LineID, PointID, Longitude, Latitude

The end of a line has 0,0 for Longitude and Latitude.

I wish to import these CSV files into Manifold and generate lines/areas from these sequences of points grouped by LineID.

I have read the documentation about importing these files and creating geocoded tables, which yield point geometries for each point.

I attach an example IceShelves.map with the following components:

1. imported data as a geocoded table - ice_shelves

2. the associated drawing - ice_shelves Drawing

3. a Query that endeavours to merge all the points in to lines

4. the resulting table and drawing from the query. - IceShelfPoints

The query groups the points into objects which also have point geometries. If i turn the points into lines and merge these then the lines (dots) are unconnected.

This seems like such a basic request but I have searched and read and searched and read, trying lots of things but not getting any closer. Only to see the description of how geocoding tables for lines and areas as difficult because of not having a standard.

I look forward to being pointed in the right direction. sorry for the trouble.

Attachments:
IceShelves.map

Dimitri


6,436 post(s)
#14-Jan-21 11:29

A simple approach:

1. Import the CSV and delete all records with 0,0 for latitude and longitude. Those don't add anything.

2. Create a drawing of points. There are example topics that show how to do that. Each point now has a LineID attribute

3. With the focus on that drawing in the Transform pane choose Merge : points and group by LineID, saving the result into a new drawing called Merged points.

4. Open the Merged points drawing. All the "points" are still there, except now that instead of being separate point objects they are part of multipoints, where each multipoint object has all the former "points" that have the same LineID.

5. You can now use the Transform pane to Convert : line to convert those multipoints into lines if you make the following modification:

Press the Edit Query button, and in the query that is written, change the line

GeomConvertToLine([Geom])

into

GeomConvertToLine( GeomConvertToPoint([Geom], FALSE) )

Make that modification, and then run the query by pressing the ! button in the main toolbar. You get a drawing of lines, with each line having the same LineID as the multipoint that was used to create it.

What's going on is that the Merge template uses a function to merge points grouped by LineID, but in the multipoints it creates each point as a separate branch. If you create lines from those, you create as many zero length lines as you have branches. So you have to collapse the multipoints of many branches into multipoints with only one branch each.

The quickest way to do that is to use the GeomConvertToPoint function with a FALSE argument (meaning, don't create separate branches) to convert the mulitipoints with branches into multipoints without branches (see the user manual for arguments to that function). Do that inside of the GeomConvertToLine function so it all happens in one go.

A caveat: The above is a risky method because it depends on the points appearing in the initial table in the order in which they should be used to define a line, for each LineID. That only will work if the CSV file presents all the points in the desired order, and if that order isn't changed in the table. You can't assume tables have any particular order, so taking advantage of the order in which records just happened to be added when the table was created is risky, although it does work in the case of simple things like importing a CSV.

A more elegant approach, if the PointID field gives the order of the points to be used as vertices for each particular LineID, would be to modify the query that does the Merge grouping by LineID to order the points being merged by their PointID. No doubt the SQL folks in this forum could provide an example that would be more elegant than whatever I could hack up. :-)

#14-Jan-21 22:40

Thanks Dimitri

Perfect!!!!

BTW - PointID does give the sort order of the points within line so a sort routine would be valuable.

Thanks heaps!!!

adamw


9,480 post(s)
#18-Jan-21 16:01

The sorting can be done using a query, indeed. Eg:

--SQL9

 

FUNCTION MergePoints(@t TABLEGEOM AS (

  SELECT GeomMergePoints(GeomMakePoint(

    VectorMakeX2([longitude][latitude]))

  ) FROM @t

END;

 

SELECT [iceshelf],

  GeomConvertToLine(GeomConvertToPoint(MergePoints(

    (COLLECT [longitude][latitude] ORDER BY [point])

  ), FALSE)) AS [geom]

INTO [temp]

FROM [ice_shelves] GROUP BY [iceshelf];

 

ALTER TABLE [temp] (

  ADD [mfd_id] INT64,

  ADD INDEX [mfd_id_x] BTREE ([mfd_id]),

  ADD INDEX [geom_x] RTREE ([geom]),

  ADD PROPERTY 'FieldCoordSystem.Geom' CoordSystemDefaultLatLon()

);

 

CREATE DRAWING [temp_d] (

  PROPERTY 'Table' '[temp]',

  PROPERTY 'FieldGeom' 'geom'

);

This will take the data in the 'ice_shelves' table, group points for each unique value of 'iceshelf', order these points by 'point', merge each ordered list into a line, then output all such lines into the new 'temp' table. Then, the query will add an identity field and index to make the table editable, add a spatial index to make it usable for a drawing, and will create a drawing named 'temp_d'.

Hope this helps.

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