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.
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));
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.
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…
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);