A classic task in GIS is to rebuild objects from data in file formats, such as CSV, which decompose GIS objects like lines or areas into individual points. In this example we recover the original lines from a table of points that has been imported from a CSV file.
The points mark the positions of coordinates (vertices) that originally represented lines marking the boundaries of ice shelves in Antarctica. Each point has an IceShelf field giving the line of which it was a part, and a Point field giving the order of the point within that line. We use SQL and Manifold SQL functions to build lines from that table, automatically grouping points from each line in the correct order to reconstruct each line.
We open the ice_shelves table, which was imported from a CSV file. A Geom geometry field was added to create points from the Latitude and Longitude fields, using the procedure shown in the Example: Create a Drawing from a Geocoded Table topic.
We also open the ice_shelves Drawing, which was created from the ice_shelves table. The points appear to have been derived from two line objects. In the table those points associated with one of the lines have a IceShelf value of 1 while those points associated with the other line have a IceShelf value of 2.
Since records in tables cannot be considered to have any specific order, if we did not have a Point field giving the order of each point within each line, we could not form the line. Since we have that field, we can write a query that will order points by Point field, grouping within lines by using the IceShelf field.
We launch the Command Window and enter the following query:
FUNCTION MergePoints(@t TABLE) GEOM 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 [result]
FROM [ice_shelves] GROUP BY [iceshelf];
ALTER TABLE [result] (
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 [result_drawing] (
PROPERTY 'Table' '[result]',
PROPERTY 'FieldGeom' 'geom'
);
Note that SQL is case-insensitive when it comes to field names, so for faster keyboarding we can use iceshelf instead of IceShelf, and point instead of Point, as field names.
Press ! to run the query, and a new table, called result, and a new drawing, called result_drawing, are created in the Project pane.
Popping open the result table, we see it has two objects, both lines.
Popping open the result_drawing, we see the two line objects have correctly been created by taking the points in order, with sets of points grouped by ice shelf, to recreate the original line objects that show the boundaries of ice shelves in Antarctica.
The SQL we wrote consists of four queries, which are executed in sequence:
FUNCTION MergePoints(@t TABLE) GEOM 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 [result]
FROM [ice_shelves] GROUP BY [iceshelf];
ALTER TABLE [result] (
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 [result_drawing] (
PROPERTY 'Table' '[result]',
PROPERTY 'FieldGeom' 'geom'
);
The first query defines a function that will be used in the second query:
FUNCTION MergePoints(@t TABLE) GEOM AS (
SELECT GeomMergePoints(GeomMakePoint(
VectorMakeX2([longitude], [latitude]))
) FROM @t
) END;
The function packages a use of GeomMergePoints, an aggregate function, that takes a set of points from a table and combines them into a single multipoint object.
The second query is the heart of the matter:
SELECT [iceshelf],
GeomConvertToLine(GeomConvertToPoint(MergePoints(
(COLLECT [longitude], [latitude] ORDER BY [point])
), FALSE)) AS [geom]
INTO [result]
FROM [ice_shelves] GROUP BY [iceshelf];
Somewhat counter-intuitively, the GROUP BY that comes at the end of the query is the first grouping, bundling points by their iceshelf value. Within the query, the COLLECT aggregate collects the subgroup of points for each ice shelf into a table, which is ordered by the point field.
The MergePoints function we defined in the first query is used to convert that table into a multipoint. The multipoint object thus created has a branch for each point, so we use the GeomConvertToPoint function with a FALSE argument to join all the points into a single branch. We can then convert that single branch into a line using the GeomConvertToLine function.
The geom result goes INTO a new table called result, along with the iceshelf field identifying that line.
The last two queries are basically housekeeping.
ALTER TABLE [result] (
ADD [mfd_id] INT64,
ADD INDEX [mfd_id_x] BTREE ([mfd_id]),
ADD INDEX [geom_x] RTREE ([geom]),
ADD PROPERTY 'FieldCoordSystem.Geom' CoordSystemDefaultLatLon()
);
The third query adds the usual mfd_id field and index to the table, and adds an RTREE spatial index to the geometry field. It adds a coordinate system as well, using the CoordSystemDefaultLatLon() function to specify Latitude / Longitude as the coordinate system, since Latitude and Longitude coordinates were used in the original table for point locations. These simple steps make the table ready to use as a table for a drawing.
CREATE DRAWING [result_drawing] (
PROPERTY 'Table' '[result]',
PROPERTY 'FieldGeom' 'geom'
);
The fourth and final query creates the result_drawing.
Example: Create and Run a Query - See how the different parts of a command window operate when creating and running SQL queries. Includes use of the Log tab as well as the ?expression and !fullfetch commands.
Example: Create a Drawing from a Geocoded Table - A partner example to Example: Create a Geocoded Table from a Drawing A geocoded table has records with a latitude and longitude for each record. This example starts with a table containing a list of cities with a latitude and longitude field for the location of each city. We create a geom from the latitude and longitude fields using a template in the Transform pane and then we create a drawing that shows the cities as points. This example shows all the infrastructure steps involved.