I'm working with SRID=32636
I have a point = (2076155.32235105 4828109.18280588)
I'm using this post: How to add 2 points with distance between them (SRID = 32636)? to create 3 points with different distances.
I created the 3 points (source point + 3 different points with 15, 30, 70 meters from source point) with the following queries:
select st_asText(st_project('POINT(2076155.32235105 4828109.18280588)', 15, radians(30)))
I got: POINT(35.322429431595 30.8173112915666)
select st_asText(st_project('POINT(2076155.32235105 4828109.18280588)', 30, radians(30)))
I got: POINT(35.3225078132829 30.8174284630566)
select st_asText(st_project('POINT(2076155.32235105 4828109.18280588)', 70, radians(30)))
I got: POINT(35.3227168320474 30.8177409201211)
when I'm checking the distances between the source point and each other I got incorrect or strange distances:
select st_distance('POINT(2076155.32235105 4828109.18280588)','POINT (2076155.32235105 4828109.18280588)')
got: 0 (seems ok - no distance between source point to same source point)
select st_distance('POINT(2076155.32235105 4828109.18280588)','POINT(35.322429431595 30.8173112915666)')
got 5255531.84344186 (I expect to get 15)
select st_distance('POINT(2076155.32235105 4828109.18280588)','POINT(35.3225078132829 30.8174284630566)')
got 5255531.84330326 (I expect to get 30)
What is wrong ?
why the points I got don't give me the points with the distance I want from source point ?
How can I fix it ?
You're mixing spatial reference systems and data types. If you take a look at this query of yours, one can notice that the points are clearly in two distinct SRS:
select st_distance('POINT(2076155.32235105 4828109.18280588)',
'POINT(35.3225078132829 30.8174284630566)')
In order to get the result of ST_Distance
in meters you have to first convert the geometries to geography
- there are other more complex ways. Since we cannot convert from a projected SRS to a lon/lat one, we need to first ST_Transform
your coordinates to a lon/lat SRS and from there convert it to geography
. After that all distance operations will be calculated in metres. The following query shows exactly what I am talking about:
WITH j (start_point,geo15,geo30,geo75) AS (
VALUES (ST_Transform('SRID=32636;POINT(2076155.32235105 4828109.18280588)'::geometry,4326)::geography,
ST_Project(ST_Transform('SRID=32636;POINT(2076155.32235105 4828109.18280588)'::geometry,4326)::geography, 15, radians(30)),
ST_Project(ST_Transform('SRID=32636;POINT(2076155.32235105 4828109.18280588)'::geometry,4326)::geography, 30, radians(30)),
ST_Project(ST_Transform('SRID=32636;POINT(2076155.32235105 4828109.18280588)'::geometry,4326)::geography, 75, radians(30)))
)
SELECT
ST_Distance(start_point,geo15),
ST_Distance(start_point,geo30),
ST_Distance(start_point,geo75)
FROM j;
st_distance | st_distance | st_distance
-------------+-------------+-------------
15 | 30 | 75