StarTree Cloud Coding Competition With Grand Prizes

Storing Geospatial objects

To learn how to store Geospatial data in Apache Pinot, watch the following video, or complete the tutorial below, starting with Prerequites.

To learn about geospatial indexing, see the Geospatial indexing developer guide.

Pinot Version	1.0.0
Code	startreedata/pinot-recipes/geospatial

Prerequisites

To follow the code examples in this guide, you must install Docker (opens in a new tab) locally and download recipes.

Navigate to recipe

If you haven't already, download recipes.
In terminal, go to the recipe by running the following command:

cd pinot-recipes/recipes/geospatial

Launch Pinot Cluster

You can spin up a Pinot Cluster by running the following command:

docker-compose up

This command will run a single instance of the Pinot Controller, Pinot Server, Pinot Broker, Kafka, and Zookeeper. You can find the docker-compose.yml (opens in a new tab) file on GitHub.

Generating Geospatial data

This recipe contains a data generator that produces JSON documents that contain various geospatial objects in Well-known text (WKT) (opens in a new tab) format.

You'll need to first install the following dependencies:

pip install geofactory faker geojson shapely

Once that's done you can run the data generator and grab just the first generated document, by running the following command:

python datagen.py 2>/dev/null | head -n1 | jq

Output is shown below:

{
  "tsString": 1678119130444,
  "uuid": "77a67adb-f9db-4e07-afa0-a59c2e27b3d0",
  "count": 908,
  "polygonString": "POLYGON ((62.00015 -32.338507, 128.785854 44.676913, 45.782208
72.58999, 62.00015 -32.338507))",
  "multiPolygonString": "MULTIPOLYGON (((-156.911813 53.429702, -41.450202 -21.951131,
7.988808 -0.515531, -33.662937 -41.782691, -104.939426 46.981135, -166.669848 -1.230373,
142.162691 -31.02204, -156.911813 53.429702)), ((55.485853 -15.824756, 49.151331
15.311564, -19.514604 41.509102, -118.56698 82.180807, 23.312518 -44.361666, -119.523485
87.590606, 86.006041 -75.833495, -123.597585 60.50805, 26.552532 -25.661127, 125.905494
45.329903, 55.485853 -15.824756)), ((-97.775848 23.71736, -12.923769 6.144659,
-157.132715 84.533912, -65.101377 -48.554279, 130.157698 -43.126393, 82.894933 31.505981,
88.876909 -80.368662, -97.775848 23.71736)), ((77.334307 18.595837, 120.613052 -63.95572,
-144.775823 47.697498, -22.514493 73.205054, 179.842676 -19.196558, 77.334307
18.595837)), ((-22.966058 -83.846852, 79.822455 72.20039, -4.942435 89.554717, 97.447234
-79.204027, -20.398943 18.112453, 43.921047 73.510537, -15.824053 -52.55715, -63.75579
-11.576211, 30.079239 -23.282294, -22.966058 -83.846852)), ((-78.440918 14.778834,
-100.955781 9.000371, -126.927461 -10.596274, -101.563431 -65.955052, 144.488354
84.141029, -0.517424 -68.166871, 143.073386 -58.519274, -87.063505 -8.517407, 52.864293
-39.634933, 71.382693 -23.895989, -157.117149 48.370833, -78.440918 14.778834)),
((129.485682 -37.023947, 146.354911 -32.264661, 38.373808 53.792189, 163.996137
76.326083, 150.2438 68.221784, 118.460847 -28.845576, -83.330716 2.700953, 0.302329
-17.033323, 129.485682 -37.023947)), ((109.0075 31.381868, -119.15326 -88.312268,
68.191132 42.722532, 9.471264 -77.189739, 48.404414 50.660832, -59.398768 -82.253626,
37.209834 13.802957, 1.361337 17.779388, 130.120281 -27.930155, -51.280126 -82.602895,
109.0075 31.381868)), ((81.324979 64.682666, 125.893131 -17.517728, 149.267106 84.919949,
-16.241389 -69.639628, 16.296239 32.932744, 81.324979 64.682666)), ((158.236415
15.068143, -19.047701 -43.09086, -92.883841 -55.723258, 90.540215 88.231732, -152.310498
62.972025, -60.100767 25.862447, -81.475891 9.735632, 83.042314 36.722952, 158.236415
15.068143)))",
  "pointString": "POINT (-124.782406 84.460566)",
  "lineStringString": "LINESTRING (0.169988 29.481278, 131.871624 -3.540022)",
  "geometryCollectionString": "GEOMETRYCOLLECTION (LINESTRING (-31.27193 -56.023134,
-165.88589 -44.849634, -44.706247 -32.393057, 162.162153 28.42024, -110.441037 14.389923,
44.199914 75.002883, 177.923367 -74.590689, -58.204393 -83.820673, -52.530859 78.735928,
-56.632839 74.800328, -71.255105 -67.45854, 114.825914 -9.889731, 44.228169 83.936279,
-149.849434 -48.782353))"
}

You can see from this output that we have three geospatial objects - a polygon, a multi polygon, and a point. Pinot also supports line strings, multi points, multi line strings, and geometry collections. You can read more about this in the Geospatial documention (opens in a new tab).

Kafka ingestion

We're going to ingest this data into an Apache Kafka topic using the kcat (opens in a new tab) command line tool. We'll also use jq to structure the data in the key:payload structure that Kafka expects:

python datagen.py --sleep 0.0001 2>/dev/null |
jq -cr --arg sep ø '[.uuid, tostring] | join($sep)' |
kcat -P -b localhost:9092 -t events -Kø

We can check that Kafka has some data by running the following command:

docker exec -it kafka-geospatial kafka-run-class.sh \
  kafka.tools.GetOffsetShell \
  --broker-list localhost:9092 \
  --topic events

We'll see something like the following:

events:0:138780

Pinot Schema and Table

Now let's create a Pinot Schema and Table.

First, the schema:

{
    "schemaName": "events",
    "dimensionFieldSpecs": [
      {"name": "uuid", "dataType": "STRING"},
      {"name": "polygon", "dataType": "BYTES"},
      {"name": "multiPolygon", "dataType": "BYTES"},
      {"name": "point", "dataType": "BYTES"},
      {"name": "lineString", "dataType": "BYTES"},
      {"name": "geometryCollection", "dataType": "BYTES"}
    ],
    "metricFieldSpecs": [{"name": "count", "dataType": "INT"}],
    "dateTimeFieldSpecs": [
      {
        "name": "ts",
        "dataType": "TIMESTAMP",
        "format": "1:MILLISECONDS:EPOCH",
        "granularity": "1:MILLISECONDS"
      }
    ]
  }

Note that the columns for polygon, multiPolygon, and point all have have a data type of BYTES. Geospatial columns must use the BYTES type because Pinot will serialize the Geospatial objects into bytes for storage purposes.

Now for the table config:

{
    "tableName": "events",
    "tableType": "REALTIME",
    "segmentsConfig": {
      "timeColumnName": "ts",
      "schemaName": "events",
      "replication": "1",
      "replicasPerPartition": "1"
    },
    "ingestionConfig": {
      "transformConfigs": [
        {
          "columnName": "polygon",
          "transformFunction": "toSphericalGeography(ST_GeomFromText(polygonString))"
        },
        {
          "columnName": "multiPolygon",
          "transformFunction": "toSphericalGeography(ST_GeomFromText(multiPolygonString)"
        },
        {
          "columnName": "point",
          "transformFunction": "toSphericalGeography(ST_GeomFromText(pointString))"
        },
        {
          "columnName": "lineString",
          "transformFunction": "toSphericalGeography(ST_GeomFromText(lineStringString))"
        },
        {
          "columnName": "geometryCollection",
          "transformFunction": "toSphericalGeography(ST_GeomFromText(geometryCollectionString))"
        }
      ]
    },    
    "tableIndexConfig": {
      "loadMode": "MMAP",
      "streamConfigs": {
        "streamType": "kafka",
        "stream.kafka.topic.name": "events",
        "stream.kafka.broker.list": "kafka-geospatial:9093",
        "stream.kafka.consumer.type": "lowlevel",
        "stream.kafka.consumer.prop.auto.offset.reset": "smallest",
        "stream.kafka.consumer.factory.class.name": "org.apache.pinot.plugin.stream.kafka20.KafkaConsumerFactory",
        "stream.kafka.decoder.class.name": "org.apache.pinot.plugin.stream.kafka.KafkaJSONMessageDecoder",
        "realtime.segment.flush.threshold.rows":"100000",
        "realtime.segment.flush.threshold.time":"1h"
      }
    },
    "tenants": {},
    "metadata": {}
  }

The most important lines in this file are the highlighted transform config functions. The ST_GeomFromText (opens in a new tab) function creates a geometry object from a WKT representation. We then use toSphericalGeography (opens in a new tab) to convert the geometry object to a geography object. We do this conversion because some of the other spatial functions only work on geography objects.

We'll create the table by running the following:

docker run \
   --network geospatial \
   -v $PWD/config:/config \
   apachepinot/pinot:1.0.0 AddTable \
     -schemaFile /config/schema.json \
     -tableConfigFile /config/table.json \
     -controllerHost "pinot-controller-geospatial" \
    -exec

Geospatial Querying

Once that's been created, we can head over to the Pinot UI (opens in a new tab) and run some queries.

The following query returns three records where the point geometry object fits inside the polygon:

select ST_AsText(polygon) AS polygon, 
       ST_AsText(point) AS point,
	   ST_AsText(lineString) AS lineString,
       ST_AsText(multiPolygon) AS multiPolygon
from events 
limit 1

You will see something like the following output:

polygon	point	lineString	multiPolygon
POLYGON ((176.322421 81.384135, -72.119398 -70.951972, -58.286871 65.822307, 176.322421 81.384135))	POINT (-172.593476 -21.260499)	LINESTRING (-39.605948 62.059305, 34.073705 0.799658, -139.772395 -1.703213, -77.278367 -71.676314, 63.493656 -85.863652, -161.998698 86.05301, 103.930496 -81.78901, -141.297058 26.328405, -157.828781 -63.650237, 49.545413 66.653053, 114.912326 -29.891357, 173.958216 -42.149873, -116.359889 60.393419, 85.314076 -11.976479, -120.473831 66.697589, 150.728768 -39.856015, -31.338929 -84.900021, 74.282083 74.694805, 49.396736 -26.537324, 71.561692 -50.287069, -119.012218 -77.801035, 127.597265 -81.655447, -107.114193 22.611314, 94.178538 43.505075, 118.342472 -16.803067, -97.814012 57.180657, 110.73994 -78.122044, 16.147591 71.993343, 50.29945 -86.283186, 168.445289 -67.303486, -116.9526 53.27939, 158.212888 -42.378283, -37.157991 -32.764193, -164.889449 13.440015, -134.571688 57.62997, -89.047944 -2.201528, -52.243972 67.624054, 52.416225 -45.269797, 176.506387 86.991437, -149.196303 70.011257, 70.088744 -78.29957, -44.795508 -58.590711, -47.21003 9.665805, -96.362498 -20.783698, 141.348796 34.565632, -156.840115 75.323202, 134.096607 71.191333, -81.516549 48.578975, 158.280571 -5.490793, 97.363266 9.227137, -85.523465 73.474778, -111.555913 10.648888, -36.575199 -28.657333, 171.439977 -66.52512, 151.746077 70.033849, -39.321975 27.239663, 29.730991 -40.406582, 160.594528 -89.762512, -5.265885 -41.629327, 41.617094 66.55888, -98.592913 49.369489, -144.211071 24.420265, 52.189571 -68.837367, -9.914865 61.997966, 10.23137 -46.383373, 93.297765 -32.987757, 82.796126 -47.822649, 61.55297 -82.747522, -19.677935 -33.215264, -98.717133 -55.313131, 34.170884 24.717411, -28.364093 65.403633, 37.893254 21.678849, 19.42964 -59.832252, -175.414992 -25.013375, 27.075554 -52.557481, 100.274289 24.866428, 90.267502 56.376479, -48.667261 -81.214369, -142.76731 16.573549, -115.703053 81.464063, 153.528885 -20.667554, 90.225929 -25.672213, -128.323393 34.743791, 15.638979 86.634385, 163.744803 82.119113, -45.423247 -6.762204, -16.503844 -19.213761, 22.50076 -53.717847, -76.784034 -28.383604, -132.753228 -11.004929, 80.753417 63.365668, 44.35864 87.425101, 142.581096 79.079986, -58.56832 -62.495883, 89.614403 41.707819, -175.216828 -25.967614, -84.488974 50.059492, 150.978167 -30.021803, 141.519166 -29.815106, 174.976466 -10.401967, 68.75459 -39.021284, -13.610707 -7.158904, 121.922659 -4.276183, -48.599251 49.250129, 66.579595 4.110557, 153.471355 42.813721, -30.784459 10.318573, 74.242803 -54.440399, 37.685038 -25.763434, -84.29051 -26.80077, -1.141665 55.231691, 32.181186 -57.471082, -160.010391 -26.870889, -117.149801 -88.057862, -110.921811 18.714086, 176.905592 44.707417, -70.471534 -47.305341, 113.045871 70.339783, 52.964186 -15.06033, 34.265453 53.469926, -94.55798 18.381975, -140.96694 45.645717, 154.467162 -52.987886, -101.26078 -6.048333, -157.652147 56.970663, -161.747241 21.669436, 70.128824 -85.958748, -60.92666 43.865177, 113.600366 -87.019599, 28.601477 43.628361, -102.80591 69.239108, -127.880824 67.67308, -134.371475 -11.070328, 76.01444 5.430101, -27.636749 52.231239, 102.615728 -14.548348, -110.381851 -5.8436, -127.101877 12.506771, 34.996208 74.271107, -47.585258 -85.519551, -4.221139 22.972952, -45.483676 65.396761, -127.201279 30.50113, 30.584315 -12.690909, 108.639983 18.753667, 134.095324 -5.896064, -95.481747 -85.337925, 63.284157 -87.960994, -46.447019 -53.642246, -164.194369 -51.739874, -126.18765 -8.284258, -74.243517 45.440134, -6.940857 88.354842, 111.649492 55.265647, -92.972516 -26.816927, 163.056917 7.907828, 37.253209 -84.507217, 145.648711 22.596968, 11.208132 33.22198, -22.596546 24.2137, -163.936167 9.185828, -21.910874 -49.467879, 176.547395 -17.462788, 165.401513 15.75875, 94.469871 -13.71385, 106.292617 -27.721893, 27.436531 84.897763, 52.564905 -82.264782, -118.699199 59.657506, 53.014269 -3.351515, 179.592392 -79.052068, 65.636512 -43.162616, -138.54432 63.320941, 178.198024 -42.023431, -22.471663 -14.467816, 151.883527 86.665156, 93.329297 -57.429819, 40.924958 -9.829078, -16.473208 39.395481, -101.394642 -8.31141, -161.775103 -52.022231, -3.065189 -8.15484, -43.10517 77.503457, 5.008822 18.41627, -135.194192 -56.203634, -79.08717 25.626562, -78.92879 -41.49407, 34.759373 -18.882311, 39.707508 32.435879, -169.920577 52.037722, 177.623278 77.037348, -24.202141 -68.593328, -94.187542 -20.794741, -92.325811 -86.284098, -33.13556 -83.712563, -32.04029 2.446138, -3.35398 -57.230626, 110.402786 -21.628998, 99.217551 -81.759833, 7.528128 -22.2746, -12.94194 -69.696166, 94.078346 62.177684, -49.462503 -50.364804, 85.281119 67.68178, 97.210298 -80.073917, -168.435434 20.45679, -47.903143 -13.328633, 114.763862 -18.981894, 162.954226 -56.041624, -20.17656 73.308346, -8.226225 -25.990478, 173.664107 -61.546799, 53.378885 -72.671294, 72.675493 -81.961164, -58.659954 -61.727779, 84.163521 30.43018, -54.920828 -52.330514, -108.42375 -40.516123, 35.000006 -7.492885, 148.098097 10.773416, -80.569735 21.697039, -46.564841 -1.058029, -21.721257 57.72145, 116.442123 5.820636, 35.100812 27.161244, 149.07338 53.212614, -46.369173 18.640516, 70.425198 -14.140541, 124.334374 28.70613, 18.992737 30.906371, -158.070867 72.596182, -114.381232 69.155047, -36.36565 59.985806, -133.83716 74.928327, 153.825315 26.518936, -92.649884 15.67211, 143.053284 -26.435581, -3.454669 18.631335, 78.38646 89.938578, -172.892697 -11.763195, -103.942842 -63.382503, 102.301659 5.696929, -62.556128 -70.739013, -126.826924 75.169294, 34.820952 30.189624, -99.807633 -60.073439, -13.745802 6.295363, 10.873219 -3.536484, -119.406271 63.473495, -48.107288 8.873623, 47.126602 59.708142, 166.355645 -28.889928, -19.536452 -74.622536, 108.560391 -60.184906, -56.080668 47.4497, 50.574122 -68.705208, 141.169488 -18.809269, 11.130543 -4.43483, 16.557308 -67.988467, 32.349421 86.635435, -113.366747 -2.887927, 43.460338 32.373973, 99.994242 70.631828, -147.818243 -88.373823, -117.216945 -38.925164, -90.892343 -81.196641, 35.154482 55.040215, 65.816648 -21.869375, 97.692292 -19.957612, 166.718869 -54.158266, 29.110551 -13.632212, -124.287976 -9.607919, 68.175629 65.44049, -37.287023 -38.880369, -28.054498 0.419048, -161.913089 -66.222916, 59.515031 -71.673546, 31.375715 -3.447172, 55.344786 -68.45671, 157.92012 -26.944477, -119.966836 -19.054635, -173.860055 36.790733, 119.995082 4.111344, -165.032131 -19.809698, 137.045843 -56.991861, -139.77337 -34.192811, -124.423552 -54.214506, -142.064943 63.929144, 76.857867 -37.475197, -175.726286 75.506758, 165.058833 -75.242941, -43.314247 12.232717, 92.26026 49.517266, 128.353324 18.686215, -173.669048 23.745093, -176.59101 -83.372717, -73.098719 -53.419438, -128.726266 75.207587, -113.192774 -39.168939, -128.089968 -81.157911, -100.897411 74.64299, 82.960021 25.424494, 41.139373 -3.155689, 85.467937 77.57565, -23.459712 -43.734892, -176.156855 -88.697419, -121.427807 87.37934, -128.11411 -10.831903, -151.468655 -18.94763, -31.413276 -37.700218, 20.897062 2.515821, -64.052651 44.414892, 55.808771 -14.750928, 45.962241 41.142575, 177.838597 33.748472, 172.904818 61.705984, -130.812715 -20.374941, 169.761311 56.764636, -166.799536 -35.71725, 22.440631 53.019796, 155.767456 6.994134, -130.600671 28.911237, -155.854725 -26.713577, -147.140814 -13.054319, 0.782785 -38.266728, 126.42647 6.470491, -19.511867 81.218572, 106.382149 69.527747, 38.084962 -53.939124, 107.483797 -38.087513, 11.710841 47.248047, -38.991485 -61.261603, 148.485777 88.985646, 143.717362 -89.72035, 160.688311 14.286745, 166.134534 7.077319, 27.602555 58.020542, -10.476567 67.040875, -60.054115 52.800669, 29.642927 -3.94563, -168.556162 -44.065381, -124.012889 60.432089, -20.876791 19.475738, 16.029474 -69.257664, -11.144071 46.744078, -162.933583 33.241045, -15.179625 74.099368, -14.318465 -6.939889, 151.051414 9.017413, 171.99381 -44.906023, -80.859959 73.159007, -142.792435 8.18011, -140.976409 52.723868, -127.47381 -23.340656, 86.772039 -52.227518, -65.745762 32.440371, -166.923014 62.678728, 172.886104 -63.145035, 159.774644 -87.06665, 115.725745 54.26459, -11.647147 51.512377, -31.974062 -85.63315, -50.565026 66.940239, -69.511127 59.672705, 123.488879 -74.304506, -150.132103 -87.458302, -18.47177 23.196824, 99.016123 29.163321, 147.91177 -65.645829, -145.478865 45.226061, -60.066758 42.891977, -147.671947 84.938849, 2.794951 -85.370273, 11.093209 31.124251, -107.252385 77.657456, -46.548666 68.553585, -55.814377 -30.023989, -155.913369 -32.20961, 23.941898 84.537624, -165.888227 27.793287, 149.148846 -9.787427, 166.623875 -43.676539, 136.318263 3.352695, -114.308953 77.883691, 86.043547 66.028605, 78.361733 71.441873, -41.203153 -10.193338, 98.067307 54.038317, -109.00711 -75.670806, -121.323229 -6.723581, -112.353298 30.429805, -159.690727 9.520088, 47.321877 -23.60118, -56.147693 -33.552735, -116.991696 38.778968, 118.881544 21.949385, -161.067974 78.627968, 88.803174 82.778036, 4.748978 -49.699477, 152.86819 -68.192031, -104.219466 31.21515, 69.976485 -9.182857, -72.369362 -53.629506, 16.160483 -12.771051, -88.28866 78.348621, -37.006021 -25.851402, 8.886839 -49.453527, 175.794783 -74.61257, 172.113645 38.283994, -146.950977 -64.959145, 69.367783 65.585728, -44.864421 -34.193042, -20.594424 22.735414, 24.056957 74.212979, -20.929085 55.510341, -128.137054 1.998671, 98.837962 9.501703, -32.932672 34.730879, -173.914798 76.88654, -104.695177 -30.067951, 1.249288 80.466789, 28.135459 -24.382456, 167.881075 88.070917, 12.97549 18.839277, 34.062114 -31.609371, 18.138164 -19.841583, 137.451056 22.919457, 4.313829 22.642922, 20.009889 16.324082, -159.655664 -46.564397, -92.481555 43.398373, 161.039174 -47.933591, 6.641751 -71.690922, -57.538665 40.920878, -67.995105 -86.098549, 101.75818 35.789859, -55.362315 -89.170121, 83.921659 17.38134, -45.803745 -67.722691, -99.43627 2.184392, 5.294237 -7.204079, -37.946044 83.46461, 57.065937 -24.182964, 3.621356 67.282066, 72.357067 20.306202, 23.563789 -74.439909, -107.622748 -66.538578, -45.904435 5.56731, -52.269034 58.105472, 24.177395 6.887253, 88.613931 65.298503, -76.016508 40.582501, -161.874686 50.778205, 34.27361 -3.308462, 144.510238 -51.738667, -99.657585 -60.44952, -113.113152 -12.333524, 28.003655 -29.897205, -53.997717 -9.133845, -103.558446 -30.785175, -122.533364 -83.483486, -73.033271 -12.146721, -152.803983 38.280836, 23.628879 51.667085, 103.093343 -31.196026, 120.334224 30.965174, -0.616269 36.401016, 73.084527 -12.009683, -143.279591 -18.205344, -118.030258 -73.844598, -130.344 4.361144, -62.475549 -85.423046, -59.405182 56.51623, -118.271827 62.856226, 120.099665 -0.839696, 50.709207 -89.016496, -37.712643 46.15153, -86.294708 -2.244355, -167.068308 -44.072243, -149.431393 40.372074, 4.627398 18.56288, 125.841489 54.979284, 135.882672 36.945496, -17.892891 50.816449, 112.646516 28.70489, 106.829812 -69.085286, 79.38991 -27.12574, 135.816373 8.440255, -92.940445 22.045166, 122.84331 28.337848, -69.74764 70.89657, 100.896755 21.030607, 173.1541 -40.162954, 117.926312 -49.238772, 129.990801 66.098744, -21.896191 54.385665, -43.641986 34.094825, -61.470852 -7.860776, -173.546487 -5.077445, 85.72068 -17.498026, -160.583992 -62.411389, -128.263524 -42.051014, 19.501394 -73.508098, 72.401021 -63.824531, -49.621791 -27.161031, -75.119549 -27.496253, -63.32588 8.380343, -23.613898 -17.003545, 173.374833 1.562424, 80.352584 -59.280009, -109.381399 -50.55484, -109.032478 37.346156, -169.605913 29.893478, 55.863456 -68.867174, -130.750144 -46.571815, 126.319961 35.395255, -100.171253 77.498002, 93.151856 74.458816, 38.948207 -69.120969, 175.838447 -57.356315, -89.771826 -88.766139, 26.958895 51.675663, 154.200495 13.811027, 61.365623 -25.955579, -106.330825 45.492816, 111.476066 -22.589074, -126.871069 -12.200052, 86.903995 5.190294, 106.298229 -25.945498, -76.928571 30.545851, 81.057478 -70.884687, 31.000843 -71.107159, -21.729561 64.567399, -64.066302 -36.250074, -85.298096 83.918144, -115.721055 21.906689, -97.71014 83.246844, 110.161527 10.777, -134.07631 40.116966, -13.191494 8.763722, 25.010343 8.507088, 37.654915 -32.549785, -24.747328 -73.335893, 85.717725 -63.881628, 166.868101 -72.086692, 83.762913 -68.902958, -106.751841 -14.968426, 49.285616 -54.545852, 26.645822 28.28981, -46.540048 44.187205, 69.306479 21.263316, 160.492736 1.118264, 70.932013 -89.956174, -95.726051 -16.49625, -87.3876 70.01841, 77.224777 -48.719265, 109.383701 55.655501, 49.132967 -21.321772, -132.666533 -35.311585, 167.012858 13.834435, 4.916061 -39.698582, 160.904434 -29.723021, -115.470477 -89.316922, 131.295548 39.326639, 132.970503 -72.888801, -177.914336 -86.001063, 42.258197 89.366265, -131.666232 -13.893237, -48.348907 -9.49313, -131.353794 88.706052, 125.493104 -35.080884, 122.151019 -74.918886, -128.326233 -47.437449, 50.728225 -32.972187, 76.528858 89.304653, -133.229121 -88.656607, -2.804844 -28.28229, 30.91049 -35.576649, -46.742461 -33.353068, -26.555062 49.317571, 59.430729 37.88883, -163.39316 30.09422, 112.054022 -77.561769, -93.63942 31.197075, 0.122914 61.245938, 138.923203 -30.477557, 100.339118 -39.919613, 171.604843 -38.067686, -8.488512 89.275135, -41.791682 41.789816, -127.16586 -37.828658, 178.142484 66.674578, -121.601972 -14.103024, -140.442892 -68.174977, -115.753077 -48.202574, 144.830848 17.357141, 50.63297 -67.536675, 126.659103 -89.266282, -9.617554 -80.479532, 178.992147 -63.729826)	MULTIPOLYGON (((-157.138803 19.18181, 177.653922 78.908605, 73.865228 -46.498039, 29.089036 -32.451709, -113.274383 35.257241, -157.138803 19.18181)), ((137.611215 -63.422384, -11.67216 -9.776203, 104.398197 -64.689331, -126.055342 31.100282, 158.788515 65.457857, -65.819031 -54.702393, -146.544439 72.677723, 82.282191 -84.675245, 124.150138 3.74385, 160.457713 -37.333328, 13.133476 13.493448, 137.611215 -63.422384)), ((-154.215233 86.954676, 88.565588 -62.92212, -40.124738 -47.491918, -154.215233 86.954676)), ((-79.928151 -67.374268, 20.941302 -9.103939, 128.858873 39.527307, -79.928151 -67.374268)), ((60.032965 47.065862, 174.341177 7.45859, -15.993429 42.459224, 107.840963 -38.456336, 23.50499 -39.973941, 60.032965 47.065862)), ((179.573616 -62.99732, -105.100176 -54.553241, 33.50347 65.307462, 154.868816 70.518507, 137.55974 38.791215, 62.541493 -43.301786, 164.520115 -56.401969, 16.971155 81.202442, -104.242781 -31.761278, 179.573616 -62.99732)), ((-77.705909 -48.978714, 57.147473 75.757949, 7.2753 -33.73846, -107.590355 27.262775, -77.705909 -48.978714)), ((-150.351053 -6.213762, -165.185977 13.382215, -24.536012 -2.610698, -150.351053 -6.213762)), ((-42.264055 -26.62393, -66.313869 61.632505, 66.043358 58.651193, 74.838775 47.873168, 44.824576 -0.363304, 67.416108 11.688783, -101.268148 -33.536187, -125.398473 -3.515584, 109.80077 20.884828, -25.961502 -39.772112, -148.813282 68.189077, -42.264055 -26.62393)), ((36.031235 65.385556, 52.041906 -50.553593, -161.286029 -74.420833, -25.593221 -66.459849, -152.194805 5.569134, -113.5458 45.46695, -130.464924 15.134347, -128.232822 39.355035, 36.031235 65.385556)))

Query Results

We could also write another query to find the points that are within 500km of the centre of San Francisco:

SELECT uuid, ST_DISTANCE(point, ST_Point(-122, 37, 1))
FROM events
WHERE ST_DISTANCE(point, ST_Point(-122, 37, 1)) < 500000
LIMIT 10

You will see something like the following output:

uuid	st_distance(point,'80c05e8000000000004042800000000000')
c58586a1-8ab1-4e41-b3b1-c6e3ed916cb7	394531.2325195099
302f2ea0-df0f-4b59-9fd8-e73dae562d0f	160515.7426762354
23135371-8727-4a31-8179-922d725c9e7c	326358.86843014683
6373e83e-7b1c-4bd5-afaa-f44c9ee220c2	224860.30769621156
3790ea3b-51a8-41a2-8077-6fa19cb4f779	354395.6844787827
8717046e-9218-4a1f-8372-9555fd8fbe90	423105.8274571361
28aafa33-b113-436a-b388-f6413f953abe	257472.01145501286
47a4a0eb-c88b-4721-a898-c336638b8ab7	435853.70174941793

Query Results

Indexing Geospatial points Google Cloud Storage as Deep Store