[postgis-users] Help getting point in country

Luca Bertoncello lucabert at lucabert.de
Thu Apr 7 05:55:16 PDT 2022

Hi list!

I have a little think error and I'm not sure how to solve my problem...

So, I have a table:

gis=# \d reporting_point_ec
                       Tabelle »public.reporting_point_ec«
   Spalte   |          Typ          | Sortierfolge | NULL erlaubt? | 
  name      | text                  |              |               |
  coords    | geography(Point,4326) |              |               |
  latitude  | numeric(16,13)        |              |               |
  longitude | numeric(16,13)        |              |               |
     "idx_reportingpoint" btree (coords)

this contains the reportings point in Europe.
I want to join it with planet_osm_roads in order to create a new table 
where all the reporting points have a column with the country.
So my query:

CREATE TABLE ifr_reportingpoints (name, country, country_code, latitude, 
longitude) AS
     reporting_point_ec.name AS reppoint, planet_osm_roads.name AS 
country, planet_osm_roads.tags->'ISO3166-1' AS country_code,
     reporting_point_ec.latitude, reporting_point_ec.longitude
   FROM planet_osm_roads, reporting_point_ec
   WHERE planet_osm_roads.boundary = 'administrative'
     AND planet_osm_roads.admin_level = '2'
     AND planet_osm_roads.osm_id < 0
     AND ST_Within(reporting_point_ec.coords::geometry, way);

Unfortunately it does not work... :(
No error, but no data given, too...

I'm pretty sure, the problem is the last condition (ST_Within...).
Can someone help me?

Thanks a lot
Luca Bertoncello
(lucabert at lucabert.de)

More information about the postgis-users mailing list