Multipolygon is a multipart geometry structure that contains an array of polygons where each polygon can contain an array of lines:
In this example, we are returning id of the line, (x,y) coordinates of the point in the line. In out case we are joining line row with a table of coordinates that are represented as an array of coordinates in line.value column. CROSS APPLY implicitly know that it should join primary row with all dynamically generated rows extracted from the value cell in this row. You don't need to specify ON condition like in JOIN. generated by some table value function such as OPENJSON in our case) You should use it to join a primary row(s) with a dynamically generated table (i.e. It is very similar to standard JOIN with two differences: Now we would need additional CROSS APPLY OPENJSON part where we will pass array of coordinates end expand this array to sub-table.ĬROSS APPLY is operator that you would use in most of the cases when you are dealing with opening JSON sub-arrays. Then we are going to open array of coordinates in the value column that belongs to each line. Key column contains an index of the line string. First we are going to open an array of line strings on the $.coordinates path and return array of coordinates belonging to each line string. In this case we need to open json in two steps:ġ. Polygon can also have more than one line string (each line string is represented with a separate line string in the $.coordinates array): Results are shown in the following table: $.coordinates) and open all (x,y) points in this array: In this case, we will reference first element in the $.coordinates array (i.e. OPENJSON enables you to find coordinates of points that belong to polygon. ,, ,, ]įormat is same as in the line string however, in polygons we assume that first and last points are the same. In the simplest case we can have only one line string in the set: Polygon represents a set of independent line strings. In this example we are converting coordinates to int type. In order to fetch coordinates of points you should take elements 0 and 1 from the array using paths $ and $. We can seek into the array of coordinates (on path $.coordinates), and return (x,y) pairs of each point: Each coordinate is represented as a two-number array, as it is shown in the following example: Line strings are represented as an array of 2D coordinates.
Goal of this post is not only to show how to parse GeoJSON, but also to see how you can parse some real-world nested JSON structures. GeoJSON types that will be described here are: In this post we will see how you can parse various types of GeoJSON objects and extract their coordinates. Sql Server 2016 enables you to parse GeoJson format using OPENJSON function.
GeoJSON is commonly used format for representing various geo-objects such as lines, polygons, etc. Parsing GeoJSON format in Sql Server 2016