Creating a table with country boundaries

Hi!

I need to have a table with all country boundaries, in order to get the country from a given point.
I supposed I can use planet_osm_roads and filter by admin_level = ‘2’, but so I have > 15000 names, and Europe does not have 15000 countries…
I see, the names are duplicated, so I’m sure I must use other parameters to filter.

Could someone suggest me a way?

Thanks a lot
Luca

Basically (you seem to have a osm2pgsql schema DB)

SELECT * FROM planet_osm_polygon where boundary='administrative' and admin_level='2'; 

everything else depends essentially on what you want to do with the data.

Hi,

thank you very much.
Really don’t know why I wanted to use planet_osm_roads…

Another question: could you suggest me how to get the short name (eg. DE, UK, PL, IT) for the country, too?

Thanks
Luca

So, I got it with:


CREATE TABLE country_admin_boundaries AS
SELECT
    NEXTVAL('country_admin_boundaries_seq') AS id,
    way,
    name,
    tags->'name:en' AS name_en,
    tags->'ISO3166-1' AS country_code
  FROM planet_osm_polygon
WHERE planet_osm_polygon.boundary = 'administrative' AND admin_level='2' AND planet_osm_polygon.osm_id < 0;

Now the question: why does this query not return any data?

select name, name_en, country_code from country_admin_boundaries where ST_Contains(way, ST_MakePoint(14.68193347, 51.04952025));

Thanks a lot
Luca

That will only work if you imported the data without reprojecting to EPSG:3857 (which is the default), if you didn’t (explicitly didn’t reproject), you need to project the point to EPSG:4326 first.

So you mean I should do that?


select name, name_en, country_code from country_admin_boundaries where ST_Contains(way, 'SRID=4326;POINT(14.0007583333333 50.8379138888889)'::geometry);

unfortunately it does not work… No dataset are returned…

Thanks
Luca

So, I got it…
I created the help table so:


CREATE TABLE country_admin_boundaries AS
SELECT
    NEXTVAL('country_admin_boundaries_seq') AS id,
    ST_Transform(way, 4326) AS way,
    name,
    tags->'name:en' AS name_en,
    tags->'ISO3166-1' AS country_code
  FROM planet_osm_polygon
WHERE planet_osm_polygon.boundary = 'administrative' AND admin_level='2' AND planet_osm_polygon.osm_id < 0;
ALTER TABLE country_admin_boundaries OWNER TO _renderd;
ALTER TABLE country_admin_boundaries ADD PRIMARY KEY (id);
CREATE INDEX idx_name ON country_admin_boundaries (name);
CREATE INDEX idx_countrycode ON country_admin_boundaries (country_code);

And I can search the data with:


select name, name_en, country_code from country_admin_boundaries where ST_Contains(way, 'SRID=4326;POINT(15.7846666667 50.8926666667)'::geometry);

Hope helps someone other… :wink:

Regards
Luca