Executing a spatial query in Spark

A simple example is to find the hospitals that are within a certain distance of a given location (which is constructed using the ST_Point constructor).

// register the data frame  as a table
hospitalsDf.createOrReplaceTempView("hospitals")

The following query returns a list of the hospitals within 10 km of Grand Central Station in NYC.

SELECT name, city, state
FROM hospitals
WHERE ST_Distance(location, ST_Point(-77.574722, 43.146732)) < 10000.0

Examples of spatial functions that determine which polygon contains a point are:

1. ST_Contains(geom1, geom2) -- returns TRUE if geom2 values are completely contained by the polygons identified by geom1.
2. ST_Within(geom1, geom2) -- returns TRUE if geom1 values are within the polygons identified by geom2.
3. ST_Intersects(geom1, geom2) -- returns TRUE if geom1 and geom2 intersect spatially in any way, touching, crossing, or containing each other

Each of the following queries uses one of these functions, and returns the name of the county that contains the specified point:

SELECT NAME 
FROM counties 
WHERE 
ST_Contains(shape, ST_Point(-74.237, 42.037))

SELECT NAME
FROM counties
WHERE
ST_Within(ST_Point(-74.237, 42.037), shape)

SELECT NAME
FROM counties
WHERE
ST_Intersects(shape, ST_Point(-74.237, 42.037))

SELECT name
FROM hospitals
WHERE
ST_Contains(ST_WKTToSQL('POLYGON ((-74.0 42.0, -73.0 42.0, -73.0 43.0, -74.0 43.0, -74.0 42.0))'), location)

SELECT name 
FROM hospitals 
WHERE ST_Within(location, ST_WKTToSQL('POLYGON ((-74.0 42.0, -73.0 42.0, -73.0 43.0, -74.0 43.0, -74.0 42.0))'))

SELECT name 
FROM hospitals 
WHERE ST_Intersects(location, ST_WKTToSQL('POLYGON ((-74.0 42.0, -73.0 42.0, -73.0 43.0, -74.0 43.0, -74.0 42.0))'))

Each query returns a list of 21 hospitals. The first five hospitals are:

1. Marshall Hospital
2. Southwestern Medical Center
3. Hillcrest Hospital
4. Saint Lukes Hospital
5. Adventist Home

You can use the spatial join function to find the hospitals located within a specific county. For example, the following query returns a list of all the hospitals in Dutchess county:

SELECT c.NAME, h.name 
FROM counties AS c, hospitals AS h 
WHERE c.NAME = 'Dutchess' 
AND ST_Intersects(c.shape, h.location)

This query produces the following result set:

Alternatively, you can use the SQL JOIN ... ON notation, which is equivalent to a spatial predicate in the WHERE clause. For example, the following query produces the same result set as the previous query:

SELECT h.name, c.NAME
FROM counties AS c
JOIN hospitals AS h
ON c.NAME = 'Dutchess'
AND ST_Intersects(h.location, c.shape)

The following query returns the name of the county in which a particular hospital is located:

SELECT c.NAME, h.name
FROM hospitals AS h, counties AS c
WHERE ST_Intersects(h.location, c.shape)
AND h.name = 'Vassar Brothers Hospital'

This query produces the following result set:

"Vassar Brothers Hospital", "Dutchess"

This example queries the hospitals within each county in New York state, qualifying by the state name in the counties table.

SELECT c.NAME, h.name
FROM counties AS c, hospitals AS h
WHERE ST_Intersects(h.location, c.shape)
AND c.STATE_NAME='New York'
ORDER BY c.NAME, h.name

The result shows the first five of the 230 hospitals in New York. The same results can be obtained by rewriting the above query with using the field from hospitals table:

SELECT c.NAME, h.name
FROM hospitals AS h, counties AS c
WHERE ST_Intersects(h.location, c.shape)
AND h.state='NY'
ORDER BY c.NAME, h.name

The following example summarizes the number of hospitals per county in New York.

SELECT c.NAME, COUNT(h.name) AS hospital_count
FROM counties AS c, hospitals AS h
WHERE ST_Intersects(h.location, c.shape)
AND c.STATE_NAME='New York'
GROUP BY c.NAME

This generates a total of 45 results with the first five results (order may not be as shown below always) shown in the below table. To identify counties where the population is underserved by hospitals, an interesting metric might be the number of people per hospital in each county. Using the population for each county in the year 2000, you can calculate this number.

SELECT c.NAME, 
COUNT(h.name) AS hospital_count, 
c.POP2000 AS Population, 
c.POP2000/COUNT(h.name) AS people_per_hospital
FROM counties AS c, hospitals AS h
WHERE c.STATE_NAME='New York'
AND ST_Intersects(h.location, c.shape)
GROUP BY c.NAME, c.POP2000
ORDER BY people_per_hospital DESC

The results of this (a total of 45 records) are shown for the first five. If one had additional details such as number of beds, number of doctors per hospital, one can determine a better measure of health care capability.

A common use case for mapping applications and in particular for web mapping is to select objects that fall within a rectangular region. This can be done by creating a polygon to represent the rectangle and the ST_Intersects spatial predicate.

SELECT name
FROM hospitals
WHERE ST_Intersects(location, ST_WKTToSQL(
 'POLYGON ((-74.0 42.0, -73.0 42.0, -73.0 43.0, -74.0 43.0, -74.0 42.0))'))

Another spatial predicate that does the same is EnvelopesIntersect, which can be used to select objects whose envelope intersects a rectangular region. EnvelopesIntersect takes as a parameter the name of the spatial column, four Double values representing the lower-left, and upper-right corners of the rectangle. This spatial predicate is simpler to use and is more efficient than ST_Intersects for rectangular windows.

SELECT name
FROM hospitals
WHERE EnvelopesIntersect(location, -74.0, 42.0, -73.0, 43.0)

When building web-mapping applications with widely used web-mapping APIs from providers such as Google Maps, Yahoo! Maps, Bing Maps, and others, it is necessary to provide the longitude and latitude values to be used in placing custom markers on the map. Obtain this information with a query such as this one:

SELECT name, ST_X(location) AS longitude, ST_Y(location) AS latitude
FROM hospitals
WHERE EnvelopesIntersect(location, -74.0, 42.0, -73.0, 43.0)

Another common type of spatial query is to find things within a specified distance of a particular location. You have probably used web mapping applications to get this kind of information. You can issue SQL queries from your application for queries like:

• Finding customers within 10 miles of a store
• Finding ATMs within 500 meters of the current location
• Finding competitive stores within 10 kilometers of a proposed store location

The primary spatial function here is ST_Distance, which computes the distance between spatial values and returns a result in meters. The following query generates eight results:

SELECT name
FROM hospitals
WHERE ST_Distance(location, ST_Point(-74.237, 42.037)) < 46800
ORDER BY name

Results A different way of querying the same as above is to use the ST_Buffer, where a circular buffer is created around the given geometry and the desired geometries within that buffer are determined. The ST_Buffer function takes as parameters a spatial geometry and a distance in meters to buffer around this spatial value. The results are the same.

SELECT name
FROM hospitals
WHERE
ST_Intersects(location,
  ST_Buffer(ST_Point(-74.237, 42.037), 46800.0))
ORDER BY name

Continuing the previous examples, the distance from the specified point to each object within a 30 mile (or approximately 46800m) radius can be found with this query, the results of which are shown below.

SELECT name, ST_Distance(location, ST_Point(-74.237, 42.037)) AS distance
FROM hospitals
WHERE ST_Distance(location, ST_Point(-74.237, 42.037)) < 46800.0
ORDER BY distance

Or, ST_Buffer can be used to compute the spatial relation and then determine the distance.

SELECT name, ST_Distance(location, ST_Point(-74.237, 42.037)) AS distance
FROM hospitals
WHERE
  ST_Intersects(location,
  ST_Buffer(ST_Point(-74.237, 42.037), 46800.0))
ORDER BY distance

A key difference to be noted here is that the ST_Buffer in this package supports buffering of arbitrary geometries and can be used to compute in that manner. Note that:

• The ST_Buffer query on large geometries can be expensive.
• For a large number of geometries, the user is advised to calculate the buffers separately, store the buffers in columns, and operate on the stored buffers.

SELECT name, ST_Distance(location, ST_WKTToSQL(
 'LINESTRING (-74.0 42.0, -73.0 42.0)'))
FROM hospitals
WHERE ST_Intersects(location, ST_Buffer(ST_WKTToSQL(
 'LINESTRING (-74.0 42.0, -73.0 42.0)'), 46800.0))

This returns 31 results, the first 5 of which are: