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)
)
;
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)