Getting a count of points in a multipolygon?

Question

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?

Answer 1

So, if I understand your question correctly run this code:

SELECT b.id, b.county, b.geom, count(a.id) as cnt FROM county b LEFT JOIN retailers a ON ST_Contains(b.geom, a.geom) GROUP BY b.id ORDER BY b.id

Be sure to convert the SRID into one common system, with ST_Transform()...