3

I have a table with towns (polygons) and another with company assets (points). I am trying to get the town these assets are located in spatially.

I have some point geometry that is contained within two polygons but I want to return only the inner polygon. I am using PostgreSQL and all of the functions (i.e. St_Intersects, St_contains) are giving me the same duplicate records where the point is showing up in both Smithville and Smithville - Village. How do I get only the inner polygon? .Sample Picture

 select distinct c.id,t.division,t.city,t.state from compequip c, a.geom g, 
---geometry table a.geom tg, ----geometry table a.towns t, a.station s
 where c.id = g.id and tg.id = t.id and c.stationid = s.stationid 
 and st_intersects(st_makevalid(st_curvetoline(g.geom)), st_curvetoline(tg.geom)) 
  • 3
    Do you have any code to show what you have already tried? – JonasPedersen Dec 5 '16 at 20:46
  • I am going to take a guess here, have not tried this. but maybe within the select query do something like min(ST_AREA(geom)) – ziggy Dec 5 '16 at 20:48
  • In SELECT clause use (select name from towns where ST_Intersects(a.way,b.way) order by ST_Area(way) asc limit 1) as town_name - this should do. – Jendrusk Dec 6 '16 at 7:57
  • @JonasPedersen select distinct c.id,t.division,t.city,t.state from compequip c, a.geom g, ---geometry table a.geom tg, ----geometry table a.towns t, a.station s where c.id = g.id and tg.id = t.id and c.stationid = s.stationid and st_intersects(st_makevalid(st_curvetoline(g.geom)), st_curvetoline(tg.geom)) – LinuxInfant Dec 6 '16 at 12:34
1

Something similar to this should work:

SELECT DISTINCT ON (compequip.id), compequip.*, a.*
FROM compequip
LEFT JOIN a
ON ST_within(compequip.geom, a.geom)
ORDER BY compequip.id, ST_Area(a.geom)

Needless to say you'll have to plug in the fields you actually want and whatever you need to do to make sure your geometry column is valid. This way you're joining in the geometry, then getting only the row with the smallest geometry.

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.