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?

  • 2
    check the polygons too, since the source is in NAD_1983_UTM_Zone_15N. Make sure you have re-projected the geometries and not simply have set the CRS – JGH Dec 16 '20 at 20:21
  • I'm sorry, but how would I go about doing that? I'm relatively ne to GIS and don't understand exactly wat you mean. – JT Cox Dec 16 '20 at 23:12
  • Maybe you are working with different SRIDs? The polygons are in EPSG:26915 and the points are in EPSG: 4326 or ? – Cyril Mikhalchenko Dec 17 '20 at 8:21

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()...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.