I am interested in generating a polygon grid shapefile for the landmass of the world. What I have presently is two tables in PostgreSQL:

Table 1: Coastal area shapefile (http://openstreetmapdata.com/data/coastlines): Consists of Linestrings column names : gid, fid, geom

Table 2: Polygon grid shapefile created for the above Table 1 with the help of QGIS Grid is (1 lat x 1 lon) in size.
column names : gid_grid, id_grid, coord_grid, geom_grid

Now I want to find the polygon grids in Table 2 which are in the land area (even if a small part of coastal line falls inside the grid, it has to be marked as land area).

My initial thought is to find the polygon grids which intercept with any of the coastal lines, and mark it as a land area (using PostGIS queries). The problem is, it will not find any polygon grids which totally falls inside the land area.

Any suggestions please?

1 Answer 1


You're looking for linestrings that intersect polygons; ST_Intersects is the filter function you want to use e.g.

SELECT linestrings.geom
  FROM linestrings, polygons
 WHERE ST_Intersects(linestrings.geom, polygons.geom) AND polygons.gid_grid = [gid_grid]

Fill in the gid_grid of the polygon you're interested in at the moment. You can get a whole table of the intersections at once with a spatial join:

SELECT linestrings.gid AS linestringID, polygons.gid AS xpolygonsID
  FROM linestrings
 INNER JOIN polygons
    ON ST_Intersects(linestrings.geom, polygons.geom)

As described in the PostGIS reference:

Overlaps, Touches, Within all imply spatial intersection. If any of the aforementioned returns true, then the geometries also spatially intersect. Disjoint implies false for spatial intersection.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.