I have a PostGis database created with osm2pgsql. I am looking for a way to add the country name in which a point is located to the point row. It must work also for countries with enclaves, such as France (Llívia). In this case, osm2pgsql creates several polygons from the border relation: a polygon with the outer border and and the enclave, and for each of the both countries a polygon with the enclave only. A simple ST_Within or ST_Contains does not work in this case. I found I way to do the job:
update planet_osm_point p set country= (SELECT distinct poly.name from planet_osm_point point inner join planet_osm_polygon poly on (st_within(point.way, poly.way) and (st_nrings(poly.way)=1 or not st_within(point.way, st_makepolygon(ST_InteriorRingN(poly.way,1)))) and not exists (select * from planet_osm_polygon other where other.admin_level='2' and other.name=poly.name and st_contains(st_makepolygon(st_exteriorring(other.way)), poly.way) and other.way_area>poly.way_area)) where poly.boundary='administrative' and poly.admin_level='2' and point.osm_id=p.osm_id) where p.name is not null;
This seems a bit complex to me and takes a lot of processing time. Is there any simpler and faster way to do this?