[GIS] Spatial join points and polygons in PostGIS

postgissql

I have two spatial data sets in PostGIS tables:

  1. polygon grid (table: grid_jakarta)
  2. points (table:tweets)

For each point in 'tweets', I want to retrieve the reelvant attribute 'gridID' from the spatially corresponding polygon contained in 'grid_Jakarta'. Then I want to generate a new table that holds both. I assume this is a spatial join?

This is the query I have tried (based on a tutorial: https://datamodelprototype.wordpress.com/2014/01/27/linking-point-data-to-polygon-data-using-qgis-or-postgis-sql/), but to no avail:

SELECT pts.*, grid.gid as gridID
FROM "tweets" AS pts
INNER JOIN "grid_jakarta" AS grid
ON st_within(pts.location, grid.the_geom) AS result

Note, I need to use PostGIS due to the large quantity of points and polygons.

I have also checked previous QAs (e.g How to perform a spatial join of point and polygon layers in PostGIS?), but these aren't trying to achieve exactly what I'm trying to achieve.

Best Answer

The most natural way to do this is with a cross join, using ST_Contains in the where clause to restrict the result. ST_DWithin checks for things within a certain distance of any geometry, not for containment, as you need.

CREATE TABLE grid_tweets AS
SELECT pts.*, grid.gid as gridID
FROM 
    "tweets" AS pts, "grid_jakarta" grid
WHERE ST_Contains( grid.the_geom, pts.location)

You will want to make sure that you have a spatial index on the grid table. You will probably end up doing a full table scan on the tweets table anyway, so a spatial index on that is unlikely to help.

Related Question