1.1 --- a/latlon--0.7--0.8.sql	Thu Oct 20 21:44:17 2016 +0200
     1.2 +++ b/latlon--0.7--0.8.sql	Fri Oct 21 12:51:37 2016 +0200
     1.3 @@ -0,0 +1,124 @@
     1.4 +
     1.5 +CREATE FUNCTION GeoJSON_LinearRing_vertices(jsonb, text = 'epoint_lonlat')
     1.6 +  RETURNS SETOF jsonb
     1.7 +  LANGUAGE sql IMMUTABLE STRICT AS $$
     1.8 +    SELECT "result" FROM
     1.9 +      ( SELECT jsonb_array_length($1) - 1 ) AS "lastindex_row" ("lastindex")
    1.10 +      CROSS JOIN LATERAL jsonb_array_elements(
    1.11 +        CASE WHEN
    1.12 +          coords_to_epoint(
    1.13 +            ($1->0->>0)::float8,
    1.14 +            ($1->0->>1)::float8,
    1.15 +            $2
    1.16 +          ) = coords_to_epoint(
    1.17 +            ($1->"lastindex"->>0)::float8,
    1.18 +            ($1->"lastindex"->>1)::float8,
    1.19 +            $2
    1.20 +          )
    1.21 +        THEN
    1.22 +          $1 - "lastindex"
    1.23 +        ELSE
    1.24 +          $1
    1.25 +        END
    1.26 +      ) AS "result_row" ("result")
    1.27 +  $$;
    1.28 +
    1.29 +-- NOTE: drop function to throw error if indices depend on it
    1.30 +--       (they need to be rebuilt)
    1.31 +DROP FUNCTION GeoJSON_to_ecluster(jsonb, text);
    1.32 +
    1.33 +CREATE FUNCTION GeoJSON_to_ecluster(jsonb, text = 'epoint_lonlat')
    1.34 +  RETURNS ecluster
    1.35 +  LANGUAGE sql IMMUTABLE STRICT AS $$
    1.36 +    SELECT CASE $1->>'type'
    1.37 +    WHEN 'Point' THEN
    1.38 +      coords_to_epoint(
    1.39 +        ($1->'coordinates'->>0)::float8,
    1.40 +        ($1->'coordinates'->>1)::float8,
    1.41 +        $2
    1.42 +      )::ecluster
    1.43 +    WHEN 'MultiPoint' THEN
    1.44 +      ( SELECT ecluster_create_multipoint(array_agg(
    1.45 +          coords_to_epoint(
    1.46 +            ("coord"->>0)::float8,
    1.47 +            ("coord"->>1)::float8,
    1.48 +            $2
    1.49 +          )
    1.50 +        ))
    1.51 +        FROM jsonb_array_elements($1->'coordinates') AS "coord"
    1.52 +      )
    1.53 +    WHEN 'LineString' THEN
    1.54 +      ( SELECT ecluster_create_path(array_agg(
    1.55 +          coords_to_epoint(
    1.56 +            ("coord"->>0)::float8,
    1.57 +            ("coord"->>1)::float8,
    1.58 +            $2
    1.59 +          )
    1.60 +        ))
    1.61 +        FROM jsonb_array_elements($1->'coordinates') AS "coord"
    1.62 +      )
    1.63 +    WHEN 'MultiLineString' THEN
    1.64 +      ( SELECT ecluster_concat(array_agg(
    1.65 +          ( SELECT ecluster_create_path(array_agg(
    1.66 +              coords_to_epoint(
    1.67 +                ("coord"->>0)::float8,
    1.68 +                ("coord"->>1)::float8,
    1.69 +                $2
    1.70 +              )
    1.71 +            ))
    1.72 +            FROM jsonb_array_elements("coord_array") AS "coord"
    1.73 +          )
    1.74 +        ))
    1.75 +        FROM jsonb_array_elements($1->'coordinates') AS "coord_array"
    1.76 +      )
    1.77 +    WHEN 'Polygon' THEN
    1.78 +      ( SELECT ecluster_concat(array_agg(
    1.79 +          ( SELECT ecluster_create_polygon(array_agg(
    1.80 +              coords_to_epoint(
    1.81 +                ("coord"->>0)::float8,
    1.82 +                ("coord"->>1)::float8,
    1.83 +                $2
    1.84 +              )
    1.85 +            ))
    1.86 +            FROM GeoJSON_LinearRing_vertices("coord_array", $2) AS "coord"
    1.87 +          )
    1.88 +        ))
    1.89 +        FROM jsonb_array_elements($1->'coordinates') AS "coord_array"
    1.90 +      )
    1.91 +    WHEN 'MultiPolygon' THEN
    1.92 +      ( SELECT ecluster_concat(array_agg(
    1.93 +          ( SELECT ecluster_concat(array_agg(
    1.94 +              ( SELECT ecluster_create_polygon(array_agg(
    1.95 +                  coords_to_epoint(
    1.96 +                    ("coord"->>0)::float8,
    1.97 +                    ("coord"->>1)::float8,
    1.98 +                    $2
    1.99 +                  )
   1.100 +                ))
   1.101 +                FROM GeoJSON_LinearRing_vertices("coord_array", $2) AS "coord"
   1.102 +              )
   1.103 +            ))
   1.104 +            FROM jsonb_array_elements("coord_array_array") AS "coord_array"
   1.105 +          )
   1.106 +        ))
   1.107 +        FROM jsonb_array_elements($1->'coordinates') AS "coord_array_array"
   1.108 +      )
   1.109 +    WHEN 'GeometryCollection' THEN
   1.110 +      ( SELECT ecluster_concat(array_agg(
   1.111 +          GeoJSON_to_ecluster("geometry", $2)
   1.112 +        ))
   1.113 +        FROM jsonb_array_elements($1->'geometries') AS "geometry"
   1.114 +      )
   1.115 +    WHEN 'Feature' THEN
   1.116 +      GeoJSON_to_ecluster($1->'geometry', $2)
   1.117 +    WHEN 'FeatureCollection' THEN
   1.118 +      ( SELECT ecluster_concat(array_agg(
   1.119 +          GeoJSON_to_ecluster("feature", $2)
   1.120 +        ))
   1.121 +        FROM jsonb_array_elements($1->'features') AS "feature"
   1.122 +      )
   1.123 +    ELSE
   1.124 +      NULL
   1.125 +    END
   1.126 +  $$;
   1.127 +

     2.1 --- a/latlon--0.8.sql	Thu Oct 20 21:44:17 2016 +0200
     2.2 +++ b/latlon--0.8.sql	Fri Oct 21 12:51:37 2016 +0200
     2.3 @@ -1520,7 +1520,7 @@
     2.4  -- other data storage formats --
     2.5  --------------------------------
     2.6  
     2.7 -CREATE FUNCTION coords_to_epoint(float8, float8, text = 'epoint_lonlat')
     2.8 +CREATE FUNCTION coords_to_epoint(float8, float8, text = 'epoint')
     2.9    RETURNS epoint
    2.10    LANGUAGE plpgsql IMMUTABLE STRICT AS $$
    2.11      DECLARE
    2.12 @@ -1539,14 +1539,38 @@
    2.13      END;
    2.14    $$;
    2.15  
    2.16 +CREATE FUNCTION GeoJSON_LinearRing_vertices(jsonb, text = 'epoint_lonlat')
    2.17 +  RETURNS SETOF jsonb
    2.18 +  LANGUAGE sql IMMUTABLE STRICT AS $$
    2.19 +    SELECT "result" FROM
    2.20 +      ( SELECT jsonb_array_length($1) - 1 ) AS "lastindex_row" ("lastindex")
    2.21 +      CROSS JOIN LATERAL jsonb_array_elements(
    2.22 +        CASE WHEN
    2.23 +          coords_to_epoint(
    2.24 +            ($1->0->>0)::float8,
    2.25 +            ($1->0->>1)::float8,
    2.26 +            $2
    2.27 +          ) = coords_to_epoint(
    2.28 +            ($1->"lastindex"->>0)::float8,
    2.29 +            ($1->"lastindex"->>1)::float8,
    2.30 +            $2
    2.31 +          )
    2.32 +        THEN
    2.33 +          $1 - "lastindex"
    2.34 +        ELSE
    2.35 +          $1
    2.36 +        END
    2.37 +      ) AS "result_row" ("result")
    2.38 +  $$;
    2.39 +
    2.40  CREATE FUNCTION GeoJSON_to_epoint(jsonb, text = 'epoint_lonlat')
    2.41    RETURNS epoint
    2.42    LANGUAGE sql IMMUTABLE STRICT AS $$
    2.43      SELECT CASE
    2.44      WHEN $1->>'type' = 'Point' THEN
    2.45        coords_to_epoint(
    2.46 +        ($1->'coordinates'->>0)::float8,
    2.47          ($1->'coordinates'->>1)::float8,
    2.48 -        ($1->'coordinates'->>0)::float8,
    2.49          $2
    2.50        )
    2.51      WHEN $1->>'type' = 'Feature' THEN
    2.52 @@ -1562,15 +1586,15 @@
    2.53      SELECT CASE $1->>'type'
    2.54      WHEN 'Point' THEN
    2.55        coords_to_epoint(
    2.56 +        ($1->'coordinates'->>0)::float8,
    2.57          ($1->'coordinates'->>1)::float8,
    2.58 -        ($1->'coordinates'->>0)::float8,
    2.59          $2
    2.60        )::ecluster
    2.61      WHEN 'MultiPoint' THEN
    2.62        ( SELECT ecluster_create_multipoint(array_agg(
    2.63            coords_to_epoint(
    2.64 +            ("coord"->>0)::float8,
    2.65              ("coord"->>1)::float8,
    2.66 -            ("coord"->>0)::float8,
    2.67              $2
    2.68            )
    2.69          ))
    2.70 @@ -1579,8 +1603,8 @@
    2.71      WHEN 'LineString' THEN
    2.72        ( SELECT ecluster_create_path(array_agg(
    2.73            coords_to_epoint(
    2.74 +            ("coord"->>0)::float8,
    2.75              ("coord"->>1)::float8,
    2.76 -            ("coord"->>0)::float8,
    2.77              $2
    2.78            )
    2.79          ))
    2.80 @@ -1590,8 +1614,8 @@
    2.81        ( SELECT ecluster_concat(array_agg(
    2.82            ( SELECT ecluster_create_path(array_agg(
    2.83                coords_to_epoint(
    2.84 +                ("coord"->>0)::float8,
    2.85                  ("coord"->>1)::float8,
    2.86 -                ("coord"->>0)::float8,
    2.87                  $2
    2.88                )
    2.89              ))
    2.90 @@ -1604,12 +1628,12 @@
    2.91        ( SELECT ecluster_concat(array_agg(
    2.92            ( SELECT ecluster_create_polygon(array_agg(
    2.93                coords_to_epoint(
    2.94 +                ("coord"->>0)::float8,
    2.95                  ("coord"->>1)::float8,
    2.96 -                ("coord"->>0)::float8,
    2.97                  $2
    2.98                )
    2.99              ))
   2.100 -            FROM jsonb_array_elements("coord_array") AS "coord"
   2.101 +            FROM GeoJSON_LinearRing_vertices("coord_array", $2) AS "coord"
   2.102            )
   2.103          ))
   2.104          FROM jsonb_array_elements($1->'coordinates') AS "coord_array"
   2.105 @@ -1619,12 +1643,12 @@
   2.106            ( SELECT ecluster_concat(array_agg(
   2.107                ( SELECT ecluster_create_polygon(array_agg(
   2.108                    coords_to_epoint(
   2.109 +                    ("coord"->>0)::float8,
   2.110                      ("coord"->>1)::float8,
   2.111 -                    ("coord"->>0)::float8,
   2.112                      $2
   2.113                    )
   2.114                  ))
   2.115 -                FROM jsonb_array_elements("coord_array") AS "coord"
   2.116 +                FROM GeoJSON_LinearRing_vertices("coord_array", $2) AS "coord"
   2.117                )
   2.118              ))
   2.119              FROM jsonb_array_elements("coord_array_array") AS "coord_array"
   2.120 @@ -1632,6 +1656,12 @@
   2.121          ))
   2.122          FROM jsonb_array_elements($1->'coordinates') AS "coord_array_array"
   2.123        )
   2.124 +    WHEN 'GeometryCollection' THEN
   2.125 +      ( SELECT ecluster_concat(array_agg(
   2.126 +          GeoJSON_to_ecluster("geometry", $2)
   2.127 +        ))
   2.128 +        FROM jsonb_array_elements($1->'geometries') AS "geometry"
   2.129 +      )
   2.130      WHEN 'Feature' THEN
   2.131        GeoJSON_to_ecluster($1->'geometry', $2)
   2.132      WHEN 'FeatureCollection' THEN