Search code examples
postgresqlpostgispostgresql-11

How to apply PostGIS ST_Within to all rows in a column


I'm trying to use PostGIS to determine what political district (polygon) each geo-location took place in. In one table I have the shapefile loaded into a table called 113th_congress_shp, and in the other I have a list of events that took place given in the form POINT(long lat) in a table called gdelt_full_geo. My goal is to be able to apply ST_Within to each event in a particular column and have it return the political district that it applies to.

I'm coming from more of an R background so I am used to being able to do vector operations on an entire column. So, I am wondering how to do that in PostgreSQL. Using the following code I am able to get one row:

SELECT statefp, cd113fp, geoid, namelsad
FROM public."113th_congress_shp"
WHERE ST_Within(ST_SetSRID((SELECT action_coord FROM public.gdelt_full_geo LIMIT 1), 4269)::geometry, geom);

However, when I do:

SELECT statefp, cd113fp, geoid, namelsad
FROM public."113th_congress_shp"
WHERE ST_Within(ST_SetSRID((SELECT action_coord FROM public.gdelt_full_geo), 4269)::geometry, geom);

I get the following error:

"ERROR: more than one row returned by a subquery used as an expression SQL state: 21000"

When I do the following, I get every globaleventid from the gdelt_full_geo table where the lat long falls within a polygon from the 113th_congress_shp table. However, I am unable to join the specific statefp and namelsad that the point falls into from the 113th_congress_shp table to the globaleventid from the gdelt_full_geo table so that I know what polygon the eventid belongs to.

SELECT a.globaleventid
FROM public.gdelt_test a
WHERE EXISTS (
        SELECT x.statefp, x.namelsad
        FROM public."113th_congress_shp" x
        WHERE ST_Within((
                ST_SetSRID(a.action_coord , 4269))::geometry
                , x.geom)
        ) 
        ;

Solution

  • After the suggestion from @wildplasser I was able to start digging in the right direction and was able to achieve what I wanted, such that all geoeventid's were joined to their respective congressional districts by doing:

    SELECT a.globaleventid, x.statefp, x.namelsad
    FROM public.gdelt_full_113 a
    JOIN public."113th_congress_shp" x
    ON ST_Within(ST_SetSRID(a.action_coord , 4269)::geometry, geom)