select by location using ogr2ogr sqlite

Question

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?

Answer 1

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: And picture of selection_polygon.shp and shapefile_point.shp:

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