OGR – How to Select by Location Using ogr2ogr with SQLite

gdalogrogr2ogrsqlite

I have a shapefile containing polygons and a shapefile containing points. Now I would like to select the polygons that contain points.

How can I do this from the command line using ogr2ogr?

Best Answer

At the following link all SQL functions that test spatial relationships are described: http://www.gaia-gis.it/spatialite-2.4.0/spatialite-sql-2.4.html#p12.

The following spatial relationships can be used within a query

ST_Equals - ST_Disjoint - ST_Touches - ST_Within - ST_Overlaps - ST_Crosses - ST_Intersects - ST_Contains - ST_Relate

For this type of request you can do this using the ST_Containts query of sqlite. Which can be adopted in a ogr2ogr command as follows:

ogr2ogr -f "ESRI Shapefile" selection_polygon.shp shapefile_polygon.shp -dialect sqlite -sql "SELECT polygon.Geometry, polygon.id FROM shapefile_polygon polygon, 'shapefile_point.shp'.shapefile_point point WHERE ST_Contains(polygon.geometry, point.geometry) GROUP BY polygon.id"

Example

Picture of shapefile_polygon.shp and shapefile_point.shp: enter image description here And picture of selection_polygon.shp and shapefile_point.shp: enter image description here

Expanded options of the spatial relationships:

  • ST_Equals Returns true if the interior and the boundary of the two geometries are spatially equal
  • ST_Disjoint Returns true if the boundaries and interior do not intersect
  • ST_Touches Returns true if the boundaries intersect but the interiors do not
  • ST_Within Returns true if the interior of the given geometry does not intersect with the exterior of another geometry
  • ST_Overlaps Returns true if the interiors of two geometries have non-empty intersection
  • ST_Crosses Returns true if the interiors of the geometries intersect but the boundaries do not
  • ST_Intersects Returns true if the interiors of the geometries intersect
  • ST_Contains Tests if the given geometry contains another geometry
  • ST_Relate Returns true if this geometry is spatially related to another geometry
Related Question