2

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 how I can adjust this query to get the count?

3
  • 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, 2020 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, 2020 at 23:12
  • Maybe you are working with different SRIDs? The polygons are in EPSG:26915 and the points are in EPSG: 4326 or ? Dec 17, 2020 at 8:21

1 Answer 1

0

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 and acknowledge you have read our privacy policy.

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