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) UPDATE lottery.retailers 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:
Select county.county, count(retailers.retailer_id) as total from lottery.county 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 on how I can adjust this query to get the count?