I have a set of lottery retail locations, latitude and longitude, that I transformed to points and added the geometry column like this:
ALTER TABLE lottery.retailers ADD COLUMN geom geometry(Point, 4326);
SET geom = ST_SetSRID(ST_MakePoint(retailers.lon, retailers.lat), 4326);
That seemed to work well.
I also have a set of polygons that are the county boundaries of Iowa, USA. Both tables are set to SRID 4326. https://geodata.iowa.gov/dataset/county-boundaries-iowa
I am trying to count the number of retail locations in each county with this query, but it just gives me a count of 0 for each county listed:
count(retailers.retailer_id) as total
LEFT JOIN lottery.retailers ON ST_Contains(county.geom, retailers.geom)
GROUP BY county.county
Obviously, I am doing something wrong here, but it doesn't appear to be far off. I feared that the coordinate points aren't right because I transformed them from addresses, but I double-checked 10-15 of them and they were all right. Any idea how I can adjust this query to get the count?