[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? |
Vorgabewert
-----------+-----------------------+--------------+---------------+-------------
name | text | | |
coords | geography(Point,4326) | | |
latitude | numeric(16,13) | | |
longitude | numeric(16,13) | | |
Indexe:
"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
SELECT
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