# SQL syntax for geospatial data **URL:** \ **Category:** SQL\ **Created:** [December 13, 2024, 8:10pm UTC](https://community.cratedb.com/t/sql-syntax-for-geospatial-data/1930 "2024-12-13T20:10:48Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![MirtoBusico](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/mirtobusico/32/1322_2.png) [@MirtoBusico](https://community.cratedb.com/u/MirtoBusico)\ **Post date:** [December 13, 2024, 8:10pm UTC](https://community.cratedb.com/t/sql-syntax-for-geospatial-data/1930/1 "2024-12-13T20:10:48Z")
Hi all, I’m trying the lesson [Hands-on: Geospatial Data](https://learn.cratedb.com/cratedb-fundamentals?lesson=handson-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 ```auto 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 ```auto 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 ```auto 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> ``` ---
**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\ **Post date:** [December 13, 2024, 9:32pm UTC](https://community.cratedb.com/t/sql-syntax-for-geospatial-data/1930/2 "2024-12-13T21:32:46Z")
Dear Mirto, thanks for reporting this problem on the [Academy](https://learn.cratedb.com/). It looks like the query using INTERSECTS is also included within the Jupyter Notebook [CrateDB Multi-Model Data Workshop](https://github.com/crate/academy-fundamentals-course/blob/main/notebooks/multi_model_data/multi_model_data.ipynb). There, I was able to spot the function is called [intersects()](https://cratedb.com/docs/crate/reference/en/latest/general/builtins/scalar.html#intersects-geo-shape-geo-shape) (plural form). ```python query = f""" SELECT name, boundaries FROM community_areas WHERE intersects ('{json.dumps(trip_geometry)}'::object, boundaries) """ ``` Regarding the second query, the documentation about [within()](https://cratedb.com/docs/crate/reference/en/latest/general/builtins/scalar-functions.html#within-shape1-shape2) demonstrates it works well when using the WKT syntax for describing locations. Would that work for you? Also in this regard, when you are about to explore CrateDB’s geospatial features, we can also recommend those documentation pages to learn about more details. - Overview: [Geospatial search with CrateDB](https://cratedb.com/docs/guide/feature/search/geo/) - Reference: [Querying geospatial data with CrateDB](https://cratedb.com/docs/crate/reference/en/latest/general/dql/geo.html) We hope this helps to unblock you from learning CrateDB. Enjoy, and keep up that spirit reporting any issues to us, it is well appreciated. With kind regards, Andreas. ---
**Author:** ![MirtoBusico](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/mirtobusico/32/1322_2.png) [@MirtoBusico](https://community.cratedb.com/u/MirtoBusico)\ **Post date:** [December 14, 2024, 6:57pm UTC](https://community.cratedb.com/t/sql-syntax-for-geospatial-data/1930/3 "2024-12-14T18:57:17Z")
Looking carefully to a working query in the course ```auto cr> SELECT name, details['population'] AS population FROM community_areas WHERE intersects ( 'LINESTRING (-87.90753846894022 41.9807787186139, -87.72939445102593 41.97508268795004, -87.55763552335945 41.97982941555023, -87.47846040428642 41.92427055787562, -87.51102436455034 41.837749425166244, -87.59225264192574 41.800574 50627759, -87.64801197528328 41.770600684095974, -87.69988112259261 41.7819633786835, -87.7530469985847 41.78583107072223)', boundaries ) AND details['population'] > 30000 ORDER BY population DESC; +------------------------+------------+ | name | population | +------------------------+------------+ | UPTOWN | 57182 | | EDGEWATER | 56296 | | CHICAGO LAWN | 55931 | | ALBANY PARK | 48396 | | LINCOLN SQUARE | 40494 | | NORWOOD PARK | 38303 | | GARFIELD RIDGE | 35439 | | WEST LAWN | 33662 | | GREATER GRAND CROSSING | 31471 | +------------------------+------------+ SELECT 9 rows in set (0.189 sec) cr> ``` I was able to determine the correct syntax: **a string inside single quote containing POINT, a space and two numbers separated by a space inside parenthesis**. And now the two query work correctly. using intersects ```auto cr> SELECT name, details['population'] AS population FROM community_areas WHERE intersects ( 'POINT (-87.69637103521424 41.9580826841845)', boundaries ) ORDER BY population DESC; +-------------+------------+ | name | population | +-------------+------------+ | IRVING PARK | 51940 | +-------------+------------+ SELECT 1 row in set (0.053 sec) cr> ``` and using within ```auto cr> SELECT name, details['population'] AS population FROM community_areas WHERE within ( 'POINT (-87.69637103521424 41.9580826841845)', boundaries ) ORDER BY population DESC; +-------------+------------+ | name | population | +-------------+------------+ | IRVING PARK | 51940 | +-------------+------------+ SELECT 1 row in set (0.040 sec) cr> ``` Thanks for your time ---
**Author:** ![simon\_prickett](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/simon_prickett/32/1223_2.png) [@simon\_prickett](https://community.cratedb.com/u/simon_prickett)\ **Post date:** [December 16, 2024, 10:17am UTC](https://community.cratedb.com/t/sql-syntax-for-geospatial-data/1930/4 "2024-12-16T10:17:45Z")
Hi there - I’ve taken a look at the hands-on content here and updated the queries so that they wrap within the width of the page… the horizontal scroll bar wasn’t showing up until you hover over the query in the previous version which may have made the syntax look truncated. Sorry about that. Simon.