I have two spatial data sets in PostGIS tables:
- polygon grid (table: grid_jakarta)
- 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.