geo_intersect

Determines geometries that intersect with a GeoJSON geometry.

boolean geo_intersect(any*, any*)

The first and the second parameters any* can be any geometric object.

The function determines if two geometries that are specified as parameters have any points in common. If any of the two parameters does not return a single valid geometry object, and if it can be detected at compile time then the function raises an error.

The runtime behavior is as follows:

• Returns false if any parameter returns 0 or more than 1 item.
• Returns NULL if any parameter returns NULL.
• Returns false if any parameter (at runtime) returns an item that is not a valid geometry object.

If both parameters return a single geometry object each, the function returns true if the 2 geometries have any points in common; otherwise false.

Example: Texas is considering regulating access to the underground water supply. An aquifer is an underground layer of water-bearing permeable rock, rock fractures, or unconsolidated materials. The government wants to impose new regulations for locations that are very close to an aquifer.

The coordinates of the aquifer have already been mapped. You want to know all counties in the Texas state that intersect with that aquifer so that you can notify the county government for each affected county to participate in talks for the new regulations.

SELECT t.poi.county AS County_needs_regulation,
t.poi.contact AS Contact_phone
FROM PointsOfInterest t WHERE
geo_intersect(
    t.poi.location,
    { 

     "type" : "polygon",
      "coordinates": [
          [
            [-97.668457031249, 29.34387539941801],
            [-95.207519531258, 29.19053283229458],
            [-92.900390625653, 30.37287518811801],
            [-94.636230468752, 32.21280106801518],
            [-97.778320312522, 32.45415593941475],
            [-99.799804687541, 31.18460913574325],
            [-97.668457031249, 29.34387539941801]
          ]
        ]
    }
);

Explanation:

• The above query fetches the locations which intersect with the location of the aquifer. That is if the location coordinates have any points in common with the location of the aquifer.
• You use geo_intersect to see if the coordinates of the location have any points common with the coordinates of the aquifer that are specified.

Result:

{"County_needs_regulation":"Tarrant","Contact_phone":"469 745 5687"}
{"County_needs_regulation":"Kinga","Contact_phone":"469 384 7612"}