Spatial join points and polygons in PostGIS?

Question

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.

Answer 1

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.