Spatial join points and polygons in PostGIS

[المشرف العام]

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.wordpres...ta-to-polygon-data-using-qgis-or-postgis-sql/), but to no avail:

SELECT pts.*, grid.gid as gridIDFROM "tweets" AS ptsINNER JOIN "grid_jakarta" AS gridON st_within(pts.location, grid.the_geom) AS resultNote, 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.

Thanks in advance!



أكثر...