Geospatial queries for Point and LineString
How to perform geospatial queries for Point and LineString.
Perform geospatial queries for Point and LineString.
Create a CQL table with geospatial columns
For geospatial field types, enclose the data type name in single quotes when
creating or altering a table. For
example:
CREATE TABLE test ( id text PRIMARY KEY, point 'PointType', linestring 'LineStringType');
Create a search index
Create a basic search index. Geospatial types will be included in the created index, indexed as SpatialRecursivePrefixTreeFieldType, and no lenient mode required for PolygonType.
Inserting or updating geospatial data
To insert or update data in the database, specify geotypes in the INSERT or UPDATE command.
For
example:
INSERT INTO test (id, point, linestring) VALUES ('1', 'POINT(5 50)', 'LINESTRING (30 10, 10 30, 40 40)' ); INSERT INTO test (id, point, linestring) VALUES ('2', 'POINT(100 100)', 'LINESTRING (50 20, 20 40, 50 50)' );
Querying geospatial data
Find points within a 10 unit radius from point (4,
49):
SELECT * FROM test WHERE solr_query= '{ "q":"*:*", "fq":"point:\"IsWithin(BUFFER(POINT(4.0 49.0), 10.0))\"" }';
id | linestring | point | solr_query
----+----------------------------------------------+------------------+------------
1 | LINESTRING (30.0 10.0, 10.0 30.0, 40.0 40.0) | POINT (5.0 50.0) | null
(1 rows)
Find linestring that contains the point (10,
30):
SELECT * FROM test WHERE solr_query='linestring:"Intersects(POINT(10 30))"';
id | linestring | point | solr_query
----+----------------------------------------------+------------------+------------
1 | LINESTRING (30.0 10.0, 10.0 30.0, 40.0 40.0) | POINT (5.0 50.0) | null
(1 rows)
Find all points on a linestring and return a
heatmap:
SELECT * FROM test WHERE solr_query='{"q":"id:*", "facet": {"heatmap": "point"}}';
facet_heatmaps
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{
"point":{
"gridLevel":4,
"columns":16,
"rows":16,
"minX":-1000.0,
"maxX":1000.0,
"minY":-1000.0,
"maxY":1000.0,
"counts_ints2D":[
null,
null,
null,
[0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0],
null,
[0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0],
[0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0],
[0,0,0,0,0,0,0,0,7,3,0,0,0,0,0,0],
null,
null,
null,
null,
null,
null,
null,
null
]
}
}
See
the full list of parameters: https://lucene.apache.org/solr/guide/6_6/spatial-search.html#SpatialSearch-HeatmapFaceting.