[postgis-users] Help getting point in country

[Didier Peeters]

Hi Luca,

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.  

HTH,
Didier

> Le 7 avr. 2022 à 14:55, Luca Bertoncello <lucabert at lucabert.de> a écrit :
> 
> 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)
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220407/a64c8f54/attachment.html>