Geospatial queries for Point and LineString
Perform geospatial queries for Point and LineString.
Defining schemas for geospatial Point and LineString types
- Table definition
-
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'); - Search index schema
-
When you create an index, geospatial types are included in the created index schema. They are indexed as
SpatialRecursivePrefixTreeFieldType, and no lenient mode is required forPolygonType. For example:<?xml version="1.0" ?> <schema name="spatial-no-jts" version="1.5"> <types> <fieldType name="string" class="solr.StrField" /> <fieldType name="boolean" class="solr.BoolField" /> <!-- When geo="false", indicate worldBounds using ENVELOPE(minX, maxX, maxY, minY) notation --> <fieldType name="rpt" class="solr.SpatialRecursivePrefixTreeFieldType" geo="false" worldBounds="ENVELOPE(-1000, 1000, 1000, -1000)" maxDistErr="0.001" distanceUnits="degrees" /> </types> <fields> <field name="id" type="string" indexed="true" stored="true" /> <field name="point" type="rpt" indexed="true" stored="true" /> <field name="linestring" type="rpt" indexed="true" stored="true" /> </fields> <uniqueKey>id</uniqueKey> </schema> - Insert or update geospatial data
-
To insert or update data in the database, specify geotypes in the
INSERTorUPDATEcommand. 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
Polygonal searches use the following geospatial predicates:
-
Intersects: Matches if the indexed value overlaps the search criteria.
-
IsWithin: Matches if the indexed value completely encapsulates the search criteria.
-
IsDisjointTo: Matches if the index value does not overlap or touch the search criteria.
For example:
-
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 ] } }
