SQL syntax for geospatial data

CrateDB SQL
1

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>
1 Like
2

Dear Mirto,

thanks for reporting this problem on the Academy.

It looks like the query using INTERSECTS is also included within the Jupyter Notebook CrateDB Multi-Model Data Workshop.

There, I was able to spot the function is called intersects() (plural form).

query = f"""
  SELECT name, boundaries 
  FROM community_areas 
  WHERE intersects ('{json.dumps(trip_geometry)}'::object, boundaries)
"""

Regarding the second query, the documentation about within() 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.

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.

1 Like
3

Looking carefully to a working query in the course

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

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

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

1 Like
4

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.

1 Like