So I have second level administration data of a country with shapes in a PostGIS database. Now I actually need the first level administration level representation of the country, but here I only have the name of that first level admin and the names of its subdivisions as a text file (which do not need to match completely with the names I have in the DB).

Now I would like to manually remove the admin2 data entries and replace them with the admin 1 data. However, for the geometry column I was thinking to do a ST_UNION by ids of the admin 2 areas of one admin1 area. Is this possible? Right now there is no indicator of shared admin 1 information in the DB. Something like:

ST_UNION((SELECT geom FROM states WHERE id IN (1,2,3,4)))
  • Cant you groupby some lvl? 1,2,3,4 havent got some common attribute? – BERA Aug 25 at 7:44
  • There is none. I could add a column and write an admin 1 identifier for each admin 2 area. Just would like to know if there is a way without manipulating the table. – JoeBe Aug 25 at 7:46
  • Why do not you want to GROUP BY *the name of that first level admin*? – Taras Aug 25 at 8:14
  • Move the admin 1 information into the DB (into another table if you want), so that you can at least check whether the data is consistent. There is no problem joining the tables later. – CL. Aug 25 at 8:18
  • @CL. Yes, I guess this is the best. But as I said, there is no common attribute to identify which admin 2 belongs to which admin 1. So right now my startegy is to add a column which I set NULL for each row that is not part of that country and then add an identifier of the admin1 into the other rows of that country. Then I INSERT a row with the data of the admin 1 area and its UNIONed geometry and delete the old admin2 areas. – JoeBe Aug 25 at 8:24

Your Answer

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

Browse other questions tagged or ask your own question.