[postgis-users] Calculate the distance between a point and a line

[Paul Ramsey]

(a) you have the longitude and latitude reversed in your POINT
(b) use ST_DistanceSpheroid() or cast to geography instead of doing that heinous mercator hack

P

> On Jan 6, 2021, at 6:10 AM, Luca Bertoncello <lucabert at lucabert.de> wrote:
> 
> Hi list!
> 
> I'm new here and I use Postgis with OpenStreetMap data to render maps in ICAO-style for using in a App I'm currently develop.
> 
> My problem: I have a Postgis-Database with the OpenStreetMap-data for Europe.
> From other source (Eurocontrol) I have a list of reporting points, some of them will be used by hobby pilots, too. These are reporting points at (or near) the country boundaries.
> And I want to get a list of these (and just these!) point.
> 
> Let's assume, the points are max 20 Km from the boundary, I tried to calculate the distance between a point (OMELO, coordinates 50.8379138888889 14.0007583333333) and the boundaries:
> 
> SELECT
>  name,
>  admin_level,
>  ST_Distance(ST_Transform('SRID=4326;POINT(50.8379138888889 14.0007583333333)'::geometry, 3857), way) * cosd(42.3521) as dist
> FROM planet_osm_roads
> WHERE boundary = 'administrative'
>  AND admin_level = '2'
>  AND osm_id < 0
> ORDER BY admin_level DESC;
> 
> Unfortunately I only get values that make no sense, since OMELO is a couple of Km to the Czech Republic, but I get:
> 
>    name     | admin_level |       dist
> -------------+-------------+------------------
> Česko       | 2           | 4812054.73000158
> Deutschland | 2           | 4812054.73000158
> Česko       | 2           |  4812014.2751998
> Deutschland | 2           |  4812014.2751998
> Česko       | 2           | 4801322.76686319
> Deutschland | 2           | 4801322.76686319
> Česko       | 2           | 4798301.61335419
> Deutschland | 2           | 4793808.13783476
> Česko       | 2           | 4786576.73402148
> Deutschland | 2           | 4785266.87451821
> Česko       | 2           | 4775493.77594454
> Deutschland | 2           | 4775493.77594454
> Česko       | 2           | 4746495.15621187
> Deutschland | 2           | 4746495.15621187
> Česko       | 2           | 4775724.71111992
> Deutschland | 2           | 4767387.92338493
> Polska      | 2           | 4748955.14857684
> Polska      | 2           |  4759696.4876026
> Deutschland | 2           |  4759696.4876026
> Deutschland | 2           |  4849836.9269982
> Polska      | 2           |  4849836.9269982
> Polska      | 2           | 4797231.65721602
> Deutschland | 2           | 4797231.65721602
> Polska      | 2           | 4890476.28917314
> Deutschland | 2           | 4890476.28917314
> Polska      | 2           | 4938293.09438962
> Deutschland | 2           | 4938293.09438962
> Deutschland | 2           | 5002527.32842157
> Polska      | 2           | 5002527.32842157
> (29 Zeilen)
> 
> Can someone help me?
> 
> Thank you very very much!
> Luca Bertoncello
> (lucabert at lucabert.de)
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users