[postgis-users] Help getting point in country

[Luca Bertoncello]

Am 07.04.2022 um 16:59 schrieb Didier Peeters:

Hi Didier

> My guess is that by using ST_Within() you expect to find points that are
> /inside/ a line (!) and you don't find any.  The doc says ST_Within()
> "... returns TRUE if geometry A is completely inside geometry B".
> So either you use another function (like ST_DWithin), or you make a
> buffer of your lines ->  AND
> ST_Within(reporting_point_ec.coords::geometry, ST_Buffer(way, 1)) .
>  Whatever alternative you choose you will have to decide what distance
> you expect your points to be from a line (here I put 1 m), and you might
> have points that are close enough from more than one line, which would
> probably be a problem as well.  

I solved my problem in another way...
I already created a table with all country boundaries, defined so:

gis=# \d country_admin_boundaries
              Tabelle »public.country_admin_boundaries«
    Spalte    |   Typ    | Sortierfolge | NULL erlaubt? | Vorgabewert
--------------+----------+--------------+---------------+-------------
 id           | bigint   |              | not null      |
 way          | geometry |              |               |
 name         | text     |              |               |
 name_en      | text     |              |               |
 country_code | text     |              |               |
Indexe:
    "country_admin_boundaries_pkey" PRIMARY KEY, btree (id)
    "idx_countrycode" btree (country_code)
    "idx_name" btree (name)

So I can join my table with this and it worked:

CREATE TABLE ifr_reportingpoints (name, country, country_code, latitude,
longitude) AS
  SELECT
    reporting_point_ec.name AS reppoint, country_admin_boundaries.name
AS country, country_admin_boundaries.country_code AS country_code,
    reporting_point_ec.latitude, reporting_point_ec.longitude
  FROM reporting_point_ec
  JOIN country_admin_boundaries ON
(ST_Within(reporting_point_ec.coords::geometry,
country_admin_boundaries.way))
  WHERE reporting_point_ec.name NOT IN (SELECT name FROM
cb_reportingpoints);

Regards
Luca Bertoncello
(lucabert at lucabert.de)