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