Hi all,
I’m trying the lesson Hands-on: Geospatial Data and I’m stuck in the test at the lesson end.
I tried two form of query but I receive always an error:
First using intersect
cr> SELECT
name,
details['population'] AS population
FROM community_areas
WHERE
INTERSECT (
[ 41.9580826841845, -87.69637103521424 ],
boundaries
);
SQLParseException[line 6:1: no viable alternative at input 'SELECT\nname,\ndetails['population'] AS population\nFROM community_areas\nWHERE\nINTERSECT']
The second using within
cr> SELECT
name,
details['population'] AS population
FROM community_areas
WHERE
within (
[ 41.9580826841845, -87.69637103521424 ],
boundaries
);
UnsupportedFunctionException[Unknown function: within(_array(41.9580826841845, -87.69637103521424), doc.community_areas.boundaries), no overload found for matching argument types: (double precision_array, geo_shape). Possible candidates: within(geo_shape, geo_shape):boolean, within(geo_point, geo_shape):boolean, within(geo_point, text):boolean, within(geo_point, object):boolean, within(geo_point, undefined):boolean]
What is the correct syntax to query if a point is inside a GEO_SHAPE?
BTW the community_areas table is defined as
cr> SHOW CREATE TABLE community_areas;
+------------------------------------------------------+
| SHOW CREATE TABLE doc.community_areas |
+------------------------------------------------------+
| CREATE TABLE IF NOT EXISTS "doc"."community_areas" ( |
| "areanumber" INTEGER NOT NULL, |
| "name" TEXT, |
| "details" OBJECT(STRICT) AS ( |
| "description" TEXT INDEX USING FULLTEXT WITH ( |
| analyzer = 'standard' |
| ), |
| "population" BIGINT |
| ), |
| "boundaries" GEO_SHAPE INDEX USING GEOHASH WITH ( |
| distance_error_pct = 0.025, |
| precision = '1m' |
| ), |
| PRIMARY KEY ("areanumber") |
| ) |
| CLUSTERED BY ("areanumber") INTO 6 SHARDS |
| WITH ( |
| "allocation.max_retries" = 5, |
| "blocks.metadata" = false, |
| "blocks.read" = false, |
| "blocks.read_only" = false, |
| "blocks.read_only_allow_delete" = false, |
| "blocks.write" = false, |
| codec = 'default', |
| column_policy = 'strict', |
| "mapping.total_fields.limit" = 1000, |
| max_ngram_diff = 1, |
| max_shingle_diff = 3, |
| number_of_replicas = '0-1', |
| "routing.allocation.enable" = 'all', |
| "routing.allocation.total_shards_per_node" = -1, |
| "store.type" = 'fs', |
| "translog.durability" = 'REQUEST', |
| "translog.flush_threshold_size" = 536870912, |
| "translog.sync_interval" = 5000, |
| "unassigned.node_left.delayed_timeout" = 60000, |
| "write.wait_for_active_shards" = '1' |
| ) |
+------------------------------------------------------+
SHOW 1 row in set (0.010 sec)
cr>