Blame - docs/0.9.8/geo/quickstart.html - incubator-asterixdb-site

<li><a href="../sqlpp/filters.html" title="Filter-Based LSM Index Acceleration"><span class="none"></span>Filter-Based LSM Index Acceleration</a></li>

58

<li><a href="../sqlpp/fulltext.html" title="Support of Full-text Queries"><span class="none"></span>Support of Full-text Queries</a></li>

59

<li><a href="../sqlpp/similarity.html" title="Support of Similarity Queries"><span class="none"></span>Support of Similarity Queries</a></li>

60

<li class="active"><a href="#"><span class="none"></span>GIS Support Overview</a></li>

61

<li><a href="../geo/functions.html" title="GIS Functions"><span class="none"></span>GIS Functions</a></li>

62

<li><a href="../interval_join.html" title="Support of Interval Joins"><span class="none"></span>Support of Interval Joins</a></li>

63

<li><a href="../spatial_join.html" title="Support of Spatial Joins"><span class="none"></span>Support of Spatial Joins</a></li>

64

<li><a href="../sqlpp/arrayindex.html" title="Support of Array Indexes"><span class="none"></span>Support of Array Indexes</a></li>

65

<li class="nav-header">Deprecated</li>

66

<li><a href="../aql/primer.html" title="AsterixDB Primer: Using AQL"><span class="none"></span>AsterixDB Primer: Using AQL</a></li>

67

<li><a href="../aql/manual.html" title="Queries: The Asterix Query Language (AQL)"><span class="none"></span>Queries: The Asterix Query Language (AQL)</a></li>

68

<li><a href="../aql/builtins.html" title="Queries: Builtin Functions (AQL)"><span class="none"></span>Queries: Builtin Functions (AQL)</a></li>

</ul>

<hr />

</div>

</div>

</div>

<!--

! Licensed to the Apache Software Foundation (ASF) under one

83

! or more contributor license agreements. See the NOTICE file

84

! distributed with this work for additional information

85

! regarding copyright ownership. The ASF licenses this file

86

! to you under the Apache License, Version 2.0 (the

87

! "License"); you may not use this file except in compliance

88

! with the License. You may obtain a copy of the License at

89

!

90

! http://www.apache.org/licenses/LICENSE-2.0

91

!

92

! Unless required by applicable law or agreed to in writing,

93

! software distributed under the License is distributed on an

94

! "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY

95

! KIND, either express or implied. See the License for the

96

! specific language governing permissions and limitations

97

! under the License.

98

!-->

99

<h1>Getting Started with GIS in AsterixDB (DRAFT)</h1>

100

101

<h2><a name="Table_of_Contents"></a><a name="toc" id="toc">Table of Contents</a></h2>

102

<ul>

103

104

<li><a href="#Introduction">Introduction</a></li>

105

<li><a href="#create">Create a GIS data type</a></li>

106

<li><a href="#update">Insert geometry data</a></li>

107

<li><a href="#query">Query geometries</a></li>

108

<li><a href="#query2">Spatial analysis function</a></li>

109

<li><a href="#aggre">Spatial aggregate example</a></li>

110

<li><a href="#range">Range query</a></li>

111

<li><a href="#knn">K Nearest Neighbor (KNN) query</a></li>

112

<li><a href="#joint">Spatial join query</a></li>

113

</ul></div>

114

115

<h2><a name="Introduction" id="Introduction">Introduction</a></h2>

116

<p>This page provides a simple guide to the OGC-compliant geometry functionality in AsterixDB. Internally, AsterixDB relies on the open source library <a class="externalLink" href="https://github.com/Esri/geometry-api-java">Esri/geometry-api-java</a> that provides OGC-geometry feature processing. Currently, the AsterixDB geometry library supports <a class="externalLink" href="https://tools.ietf.org/html/rfc7946">GeoJSON</a>, <a class="externalLink" href="http://docs.opengeospatial.org/is/12-063r5/12-063r5.html">Well known Text</a> and <a class="externalLink" href="http://portal.opengeospatial.org/files/?artifact_id=25354">Well known Binary formats</a>. For a complete list of all the functions, please check the <a href="functions.md">AsterixDB GIS functions page</a>. Here are some detailed examples.</p></div>

117

118

<h2><a name="Create_a_GIS_data_type"></a><a name="create" id="create">Create a GIS data type</a></h2>

<div>

<div>

<pre class="source">DROP DATAVERSE GISTest IF EXISTS;

123

CREATE DATAVERSE GISTest;

124

USE GISTest;

125

126

CREATE TYPE GeometryType AS{

127

id : int,

128

myGeometry : geometry

129

};

130

131

CREATE DATASET Geometries (GeometryType) PRIMARY KEY id;

</pre></div></div>

</div>

<h2><a name="Insert_geometry_data"></a><a name="update" id="update">Insert geometry data</a></h2>

<div>

<div>

<pre class="source">USE GISTest;

140

141

INSERT INTO Geometries ([

142

{"id": 123, "myGeometry": st_geom_from_geojson({"type":"Point","coordinates":[-118.4,33.93]})},

143

{"id": 124, "myGeometry": st_geom_from_geojson({"type":"Polygon","coordinates":[[[8.7599721,49.7103028],[8.759997,49.7102752],[8.7600145,49.7102818],[8.7600762,49.7102133],[8.760178,49.7102516],[8.7600914,49.7103478],[8.7599721,49.7103028]]]})},

144

{"id": 126, "myGeometry": st_geom_from_geojson({"type":"LineString","coordinates":[[-69.1991349,-12.6006222],[-69.199136,-12.599842],[-69.1982979,-12.5998268],[-69.1982598,-12.599869],[-69.1982188,-12.5998698],[-69.19817,-12.5998707],[-69.198125,-12.5998218],[-69.1973024,-12.5998133],[-69.1972972,-12.6003109],[-69.197394,-12.6003514],[-69.1973906,-12.6009231],[-69.1975115,-12.601026],[-69.1975081,-12.6010968]]})},

145

{"id": 127, "myGeometry": st_geom_from_geojson({"type": "MultiPoint","coordinates": [[10, 40], [40, 30], [20, 20], [30, 10]]})},

146

{"id": 128, "myGeometry": st_geom_from_geojson({"type": "MultiLineString","coordinates": [[[10, 10], [20, 20], [10, 40]],[[40, 40], [30, 30], [40, 20], [30, 10]]]})},

147

{"id": 129, "myGeometry": st_geom_from_geojson({"type": "MultiPolygon","coordinates": [[[[40, 40], [20, 45], [45, 30], [40, 40]]],[[[20, 35], [10, 30], [10, 10], [30, 5], [45, 20], [20, 35]],[[30, 20], [20, 15], [20, 25], [30, 20]]]]})},

148

{"id": 130, "myGeometry": st_make_point(-71.1043443253471, 42.3150676015829)},

149

{"id": 131, "myGeometry": st_make_point(1.0,2.0,3.0)},

150

{"id": 132, "myGeometry": st_make_point(1.0,2.0,3.0,4.0)},

151

{"id": 133, "myGeometry": st_geom_from_text('POLYGON((743238 2967416,743238 2967450,743265 2967450,743265.625 2967416,743238 2967416))')},

152

{"id": 134, "myGeometry": st_geom_from_wkb(hex("0102000000020000001F85EB51B87E5CC0D34D621058994340105839B4C87E5CC0295C8FC2F5984340"))},

153

{"id": 135, "myGeometry": st_line_from_multipoint(st_geom_from_text('MULTIPOINT(1 2 , 4 5 , 7 8 )'))},

154

{"id": 136, "myGeometry": st_make_envelope(10, 10, 11, 11, 4326)},

155

{"id": 137, "myGeometry": st_geom_from_text("POLYGON ((35 10, 45 45, 15 40, 10 20, 35 10),(20 30, 35 35, 30 20, 20 30))")}

]);

</pre></div></div>

</div>

<h2><a name="Query_geometries"></a><a name="query" id="query">Query geometries</a></h2>

<div>

<div>

<pre class="source">USE GISTest;

165

166

FROM Geometries SELECT *;

</pre></div></div>

<p>result:</p>

<div>

<div>

<pre class="source">{ "Geometries": { "id": 124, "myGeometry": {"type":"Polygon","coordinates":[[[8.7599721,49.7103028],[8.759997,49.7102752],[8.7600145,49.7102818],[8.7600762,49.7102133],[8.760178,49.7102516],[8.7600914,49.7103478],[8.7599721,49.7103028]]],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}} } }

174

{ "Geometries": { "id": 126, "myGeometry": {"type":"LineString","coordinates":[[-69.1991349,-12.6006222],[-69.199136,-12.599842],[-69.1982979,-12.5998268],[-69.1982598,-12.599869],[-69.1982188,-12.5998698],[-69.19817,-12.5998707],[-69.198125,-12.5998218],[-69.1973024,-12.5998133],[-69.1972972,-12.6003109],[-69.197394,-12.6003514],[-69.1973906,-12.6009231],[-69.1975115,-12.601026],[-69.1975081,-12.6010968]],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}} } }

175

{ "Geometries": { "id": 128, "myGeometry": {"type":"MultiLineString","coordinates":[[[10,10],[20,20],[10,40]],[[40,40],[30,30],[40,20],[30,10]]],"crs":null} } }

176

{ "Geometries": { "id": 132, "myGeometry": {"type":"Point","coordinates":[1,2,3,4],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}} } }

177

{ "Geometries": { "id": 133, "myGeometry": {"type":"Polygon","coordinates":[[[743238,2967416],[743265.625,2967416],[743265,2967450],[743238,2967450],[743238,2967416]]],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}} } }

178

{ "Geometries": { "id": 134, "myGeometry": {"type":"LineString","coordinates":[[-113.98,39.198],[-113.981,39.195]],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}} } }

179

{ "Geometries": { "id": 135, "myGeometry": {"type":"LineString","coordinates":[[1,2],[4,5],[7,8]],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}} } }

180

{ "Geometries": { "id": 136, "myGeometry": {"type":"Polygon","coordinates":[[[10,10],[11,10],[11,11],[10,11],[10,10]]],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}} } }

181

{ "Geometries": { "id": 123, "myGeometry": {"type":"Point","coordinates":[-118.4,33.93],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}} } }

182

{ "Geometries": { "id": 127, "myGeometry": {"type":"MultiPoint","coordinates":[[10,40],[40,30],[20,20],[30,10]],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}} } }

183

{ "Geometries": { "id": 129, "myGeometry": {"type":"MultiPolygon","coordinates":[[[[40,40],[20,45],[45,30],[40,40]]],[[[20,35],[10,30],[10,10],[30,5],[45,20],[20,35]],[[30,20],[20,15],[20,25],[30,20]]]],"crs":null} } }

184

{ "Geometries": { "id": 130, "myGeometry": {"type":"Point","coordinates":[-71.1043443253471,42.3150676015829],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}} } }

185

{ "Geometries": { "id": 131, "myGeometry": {"type":"Point","coordinates":[1,2,3],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}} } }

186

{ "Geometries": { "id": 137, "myGeometry": {"type":"Polygon","coordinates":[[[35,10],[45,45],[15,40],[10,20],[35,10]],[[20,30],[35,35],[30,20],[20,30]]],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}} } }

</pre></div></div>

</div>

<h2><a name="Spatial_analysis_functions"></a><a name="query2" id="query2">Spatial analysis functions</a></h2>

191

<p>The following query filters out only the geometries of type “Polygon” and displays the geometry in the Well known text format along with the area of the relevant geometry.</p>

<div>

<div>

<pre class="source">USE GISTest;

196

197

FROM Geometries as geo

198

WHERE geometry_type(geo.myGeometry)='Polygon'

199

SELECT VALUE {"Polygon":st_as_text(geo.myGeometry), "Area":st_area(geo.myGeometry)};

</pre></div></div>

<p>result:</p>

<div>

<div>

<pre class="source">{ "Polygon": "POLYGON ((8.7599721 49.7103028, 8.759997 49.7102752, 8.7600145 49.7102818, 8.7600762 49.7102133, 8.760178 49.7102516, 8.7600914 49.7103478, 8.7599721 49.7103028))", "Area": 1.3755215000294761E-8 }

207

{ "Polygon": "POLYGON ((743238 2967416, 743265.625 2967416, 743265 2967450, 743238 2967450, 743238 2967416))", "Area": 928.625 }

208

{ "Polygon": "POLYGON ((10 10, 11 10, 11 11, 10 11, 10 10))", "Area": 1.0 }

209

{ "Polygon": "POLYGON ((35 10, 45 45, 15 40, 10 20, 35 10), (20 30, 35 35, 30 20, 20 30))", "Area": 675.0 }

</pre></div></div>

</div>

<h2><a name="Spatial_aggregate_example"></a><a name="query2" id="query2">Spatial aggregate example</a></h2>

214

<p><tt>st_union</tt> function has been implemented both as a normal and an aggregate function. The following query shows how to query the aggregate version of this function:</p>

<div>

<div>

<pre class="source">USE GISTest;

219

220

st_union((SELECT VALUE gbu.myGeometry FROM Geometries as gbu));

</pre></div></div>

<p>result:</p>

<div>

<div>

<pre class="source">{"type":"MultiPolygon","coordinates":[[[[10,10],[30,5],[35,10],[45,20],[38.90243902439025,23.65853658536585],[41.34146341463415,32.19512195121951],[45,30],[42.27272727272727,35.45454545454545],[45,45],[30,42.5],[20,45],[25.434782608695656,41.73913043478261],[15,40],[12.857142857142858,31.428571428571427],[10,30],[10,20],[10,11],[10,10]],[[32.5,27.5],[25.357142857142858,31.785714285714285],[35,35],[32.5,27.5]],[[20,15],[20,16],[21.11111111111111,15.555555555555555],[20,15]]],[[[8.7600762,49.7102133],[8.760178,49.7102516],[8.7600914,49.7103478],[8.7599721,49.7103028],[8.759997,49.7102752],[8.7600145,49.7102818],[8.7600762,49.7102133]]],[[[743238,2967416],[743265.625,2967416],[743265,2967450],[743238,2967450],[743238,2967416]]]],"crs":null}

</pre></div></div>

</div>

<h2><a name="Range_query"></a><a name="range" id="range">Range query</a></h2>

<div>

<div>

<pre class="source">USE GISTest;

236

237

FROM Geometries geo

238

WHERE st_intersects(geo.myGeometry, st_geom_from_text("POLYGON((1 1,5 1,5 5,1 5,1 1),(2 2, 3 2, 3 3, 2 3,2 2))"))

239

SELECT VALUE geo.myGeometry;

</pre></div></div>

<p>result:</p>

<div>

<div>

<pre class="source">{"type":"Point","coordinates":[1,2,3,4],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}}

247

{"type":"LineString","coordinates":[[1,2],[4,5],[7,8]],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}}

248

{"type":"Point","coordinates":[1,2,3],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}}

</pre></div></div>

</div>

<h2><a name="K_Nearest_Neighbor_.28KNN.29_query"></a><a name="knn" id="knn">K Nearest Neighbor (KNN) query</a></h2>

<div>

<div>

<pre class="source">USE GISTest;

257

258

FROM Geometries geo

259

SELECT VALUE geo.myGeometry

260

ORDER BY st_distance(geo.myGeometry, st_make_point(1,2))

LIMIT 5;

</pre></div></div>

<p>result:</p>

<div>

<div>

<pre class="source">{"type":"Point","coordinates":[1,2,3,4],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}}

269

{"type":"LineString","coordinates":[[1,2],[4,5],[7,8]],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}}

270

{"type":"Point","coordinates":[1,2,3],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}}

271

{"type":"Polygon","coordinates":[[[10,10],[11,10],[11,11],[10,11],[10,10]]],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}}

272

{"type":"MultiLineString","coordinates":[[[10,10],[20,20],[10,40]],[[40,40],[30,30],[40,20],[30,10]]],"crs":null}

</pre></div></div>

</div>

<h2><a name="Spatial_join_query"></a><a name="joint" id="joint">Spatial join query</a></h2>

277

<p>For the spatial join query let us create a new dataverse and two new data types:</p>

<div>

<div>

<pre class="source">DROP DATAVERSE SJTest IF EXISTS;

282

CREATE DATAVERSE SJTest;

283

USE SJTest;

284

285

CREATE TYPE StateType AS{

id : int,

name: string,

boundary : geometry

};

CREATE DATASET States (StateType) PRIMARY KEY id;

292

293

CREATE TYPE POIType AS {

id : int,

longitude : double,

latitude : double

};

CREATE DATASET POIS (POIType) PRIMARY KEY id;

300

</pre></div></div>

301

302

<p>Insert data into states:</p>

<div>

<div>

<pre class="source">USE SJTest;

307

308

INSERT INTO States ([

309

{"id": 1, "name": "Nebraska", "boundary": st_geom_from_text("POLYGON ((-104.05341854507101 41.1705389679833, -104.053028 43.000586999999996, -98.49855 42.99856, -98.01304599999999 42.762299, -97.306677 42.867604, -96.38600699999999 42.474495, -96.06487899999999 41.79623, -96.09200799999999 41.53391, -95.87468899999999 41.307097, -95.88534899999999 40.721092999999996, -95.30829 39.999998, -102.051744 40.003077999999995, -102.051614 41.002376999999996, -104.053249 41.001405999999996, -104.05341854507101 41.1705389679833))") },

310

{"id": 2, "name": "Washington", "boundary": st_geom_from_text("MULTIPOLYGON (((-124.732755385025 48.165328947686795, -124.676262 48.391371, -123.981032 48.164761, -123.10189199999999 48.184951999999996, -122.871992 47.993493, -122.75413 48.1447, -122.610341 47.887343, -122.784553 47.686561, -122.864651 47.804669, -123.157948 47.356235999999996, -122.874586 47.413874, -123.119681 47.385532, -122.525329 47.912335999999996, -122.54636949132416 47.317877648507704, -122.324833 47.348521, -122.43694099999999 47.661719, -122.218982 48.020275999999996, -122.383911 48.227486, -122.47892813788141 48.175746487177165, -122.388048 48.30083, -122.57760827271139 48.38291646865838, -122.505828 48.297677, -122.732358 48.226144, -122.3773 47.905941, -122.769939 48.227548, -122.60660653630984 48.395473767832804, -122.674158 48.424726, -122.425271 48.599522, -122.535803 48.776128, -122.673472 48.733081999999996, -122.75802 49.002356999999996, -117.032351 48.999188, -117.062748 46.353623999999996, -116.915989 45.995413, -118.987129 45.999855, -121.145534 45.607886, -121.533106 45.726541, -122.266701 45.543841, -122.67500799999999 45.618038999999996, -123.004233 46.133823, -124.07776799999999 46.272324, -124.06905 46.647258, -123.953699 46.378845, -123.829356 46.713356, -124.092176 46.741623999999995, -124.138225 46.905533999999996, -123.83890000000001 46.953950999999996, -124.122057 47.04165, -124.173877 46.927234999999996, -124.425195 47.738434, -124.732755385025 48.165328947686795), (-122.56199279209496 47.29381043649037, -122.683943 47.365154999999994, -122.76539771783851 47.18116187703539, -122.678476 47.102742, -122.56199279209496 47.29381043649037), (-122.77734484602688 47.19194045282469, -122.82666 47.405806999999996, -122.871472 47.276861, -122.77734484602688 47.19194045282469)), ((-122.4789801236288 48.17567493623048, -122.358963 48.054851, -122.510562 48.132207, -122.4789801236288 48.17567493623048)), ((-122.526031 47.358906, -122.457246 47.505848, -122.373627 47.388718, -122.526031 47.358906)))") },

311

{"id": 3, "name": "New Mexico", "boundary": st_geom_from_text("POLYGON ((-109.050173 31.480003999999997, -109.045223 36.999083999999996, -103.002199 37.000104, -103.064423 32.000518, -106.618486 32.000495, -106.528242 31.783147999999997, -108.208394 31.783599, -108.208573 31.333395, -109.050044 31.332501999999998, -109.050173 31.480003999999997))") },

312

{"id": 4, "name": "South Dakota", "boundary": st_geom_from_text("POLYGON ((-104.057698 44.997431, -104.045443 45.94531, -96.563672 45.935238999999996, -96.857751 45.605962, -96.45306699999999 45.298114999999996, -96.45326 43.500389999999996, -96.60285999999999 43.450907, -96.436589 43.120841999999996, -96.639704 42.737071, -96.44550799999999 42.490629999999996, -97.23786799999999 42.853139, -98.035034 42.764205, -98.49855 42.99856, -104.053028 43.000586999999996, -104.057698 44.997431))") }

]);

</pre></div></div>

<p>Insert data into POIS:</p>

317

318

<div>

319

<div>