PostGIS convert meters to degree using epsg 5186

Question

I have a few questions about geometry and geography in PostGIS.

I am currently using PostGIS and Postgresql.

Most of my spatial data is from Korea which is basically latitude and longitude.

For testing, I have created two tables with the same latitude and longitude data but different data types, one for geography with SRID 4326 and the other for geometry with SRID 5186.

create table geometry_stores
(
    id       serial primary key,
    location geometry(POINT, 5186) not null
);

create table geography_stores
(
    id       serial primary key,
    location geography(POINT, 4326) not null
);

You can find more details of EPSG 5186 on this link https://epsg.io/5186

Here is a list of question I have got:

1. PostGIS has this method

   ST_DWithin(geometry g1, geometry g2, double precision distance_of_srid);

   is distance_of_srid a unit of EPSG? Is there any way I can convert meters (e.g. 1km) to distance_of_srid with EPSG 5186?

2. I understand that geography calculation measures the distances between points as true paths over a sphere while geometry calculation measures the distances between points as true paths over a Cartesian plane. Then if I give exactly the same distance to the following queries, they are supposed to yield different results or same results? because my understanding is that geometry with SRID 5186 is already projected with distortion of earth, then they should yield the same results?

   select *
   from geography_stores
   where st_dwithin(location, st_setsrid(st_point(126.970769, 37.555479), 4326), same_distance_meter)
   

   select *
   from geometry_stores
   where st_dwithin(location, st_setsrid(st_point(126.970769, 37.555479), 5186), same_distance_degree)
   

3. When I calculate distance on the geometry table with the following query, it gives me a degree, not meters. Is there any way I can convert this degree to meters with consideration of distortion of the earth?

   select st_distance(location, st_setsrid(st_point(126.970769, 37.555479), 5186))
   from geometry_stores
   where id = 1;
   

   I have tried with this query but got some error of Only lon/lat coordinate systems are supported in geography. Where: SQL function "st_distancesphere" during inlining

   select st_distancesphere(location, st_setsrid(st_point(126.970769, 37.555479), 5186))
   from geometry_stores
   where id = 1;
   

I have read documents at the PostGIS website and some questions in StackOverflow but still got those three questions. Thank you guys for your help.

---

------------------------------- UPDATED -----------------------------------------

1. The column for my spatial data is geometry(POINT, 5186) so the table definition would be as following. Note that it is not geometry(POINT, 4326) not to convert it to geometry(POINT, 5186) on calculation. Should I store my data in geometry(POINT, 4326) and convert it on calculation?

create table geometry_stores
(
    id       serial primary key,
    location geometry(POINT, 5186) not null
);

2. I executed following query and got results as follows:

select st_distance(st_setsrid(st_makepoint(126.808183, 37.463557), 4326)::geography,
                   st_setsrid(st_makepoint(126.970769, 37.555479), 4326)::geography);


st_distance
--------------
17627.3138509

select st_distance(st_setsrid(st_makepoint(126.808183, 37.463557), 5186)::geometry,
                   st_setsrid(st_makepoint(126.970769, 37.555479), 5186)::geometry)

st_distance
--------------
0.186772218169622

It seems that the second's query gives me degree while the first one gives me meters. Am I doing something wrong in my query, please?

3. For st_within, I populated 3M data in geometry_stores table and the spatial data spread over at least 10km. I executed the following query.

select *
from users
where st_dwithin(location, st_setsrid(st_point(126.970769, 37.555479), 5186), 0.001)

This query gives me 158 rows and geometry viewer displays as per picture below.

Let's execute the same query with distance 1 not 0.0001

select *
from users
where st_dwithin(location, st_setsrid(st_point(126.970769, 37.555479), 5186), 1)

This query gives me 32792923 rows which is all data in the table.

Considering that the spatial data spread over at least 10km, it seems that the st_within query calculates the distance between two geometries with a unit (degeree) of EPSG5186 not meters. Then, I'd like to know if I can convert meters to the unit (degree) of EPSG5186 because I'd like to query with meters, not degree which I don't know how far a unit (degree) of EPSG5186 is.

Answer 1

Is distance_of_srid a unit of EPSG?

Yes. Distances using geometry type geometries are calculated using the unit of measurement from the corresponding Spatial Reference System.

Is there any way I can convert meters (e.g. 1km) to distance_of_srid with EPSG 5186?

According to the documentation, the unit of EPSG:5186 is already metres, so you don't have to convert anything. Bur also keep in mind that distances using geography type geometries are also calculated using metres, e.g.

SELECT 
  ST_Distance(
   'SRID=4326;POINT(127.49 36.65)'::geometry,
   'SRID=4326;POINT(128.06 36.43)'::geometry) AS geometry_distance,
  ST_Distance(
   'SRID=4326;POINT(127.49 36.65)'::geography,
   'SRID=4326;POINT(128.06 36.43)'::geography) AS geography_distance
   ;
 geometry_distance | geography_distance 
-------------------+--------------------
 0.610982814815612 |     56578.57823391
(1 Zeile)

Then if I give exactly the same distance to the following queries, they are supposed to yield different results or same results? because my understanding is that geometry with SRID 5186 is already projected with distortion of earth, then they should yield the same results?

The results will differ. They might have the same unit of measurement, but they aren't projected on the same surface. The following example transforms the coordinates from 4326 to 5186 and calculates the distance:

SELECT 
  ST_Distance(
   'SRID=4326;POINT(127.49 36.65)'::geography,
   'SRID=4326;POINT(128.06 36.43)'::geography),
  ST_Distance(
   ST_Transform('SRID=4326;POINT(127.49 36.65)'::geometry,5186),
   ST_Transform('SRID=4326;POINT(128.06 36.43)'::geometry,5186));

  st_distance   |   st_distance    
----------------+------------------
 56578.57823391 | 56582.0899018353
(1 Zeile)

When I calculate distance on the geometry table with the following query, it gives me a degree, not meters. Is there any way I can convert this degree to meters with consideration of distortion of the earth?

Isn't the data type geography what you're looking for? As the documentation says:

Regardless which spatial reference system you use, the units returned by the measurement (ST_Distance, ST_Length, ST_Perimeter, ST_Area) and for input of ST_DWithin are in meters.

Just for fun, the following query calculates the distance between two points explicitly defining the 4326 spheroid, using ST_DistanceSpheroid, and casting the coordinates from geometry to geography, which basically does the same:

SELECT 
  ST_DistanceSpheroid(  
   'POINT(127.49 36.65)',
   'POINT(128.06 36.43)',
   'SPHEROID["WGS 84",6378137,298.257223563]'),
  ST_Distance( 
   'SRID=4326;POINT(127.49 36.65)'::geography,
   'SRID=4326;POINT(128.06 36.43)'::geography);

 st_distancespheroid |  st_distance   
---------------------+----------------
    56578.5782339123 | 56578.57823391

Regarding when to use geometry or geography the documentation says:

"The type you choose should be conditioned on the expected working area of the application you are building. Will your data span the globe or a large continental area, or is it local to a state, county or municipality?"

Things to consider:

• use case covers small area: stick to geometry and use a SRS that better suits your area.
• use case covers large areas (countries/continents): use geography - although it might be a bit slower.
• Do the functions you wanna use support geography? Most PostGIS functions do not support it! Check this matrix for more details. If the functions you wanna use do not support geography, you have no other choice but to use geometry ;-) Since your use case mostly covers Korea, I see no problem in using EPSG 5186.

EDIT: Regarding the question Update.

You cannot simply change the SRID of geometries to get it transformed into another reference system! What you've done was to get a WGS84 coordinate pairs and simply exchange its SRID, which is not the way it works. You have to always use ST_Transform for that. Take a look what the coordinates look like after you apply it:

SELECT 
  ST_AsText(ST_Transform('SRID=4326;POINT(126.808183 37.463557)'::geometry,5186));

                st_astext                 
------------------------------------------
 POINT(183030.248454493 540476.713582621)
(1 Zeile)

It means that POINT(183030.248454493 540476.713582621) and POINT(126.808183 37.463557) are the same coordinate pairs, but in different reference systems. The following query will make it clear that both geography and 5186 return results in metres:

SELECT 
  --Transforming from 4326 to 5186 and calculating the distance
  ST_Distance(
    ST_Transform('SRID=4326;POINT(126.808183 37.463557)'::geometry, 5186),
    ST_Transform('SRID=4326;POINT(126.970769 37.555479)'::geometry, 5186)),
  -- Distance using geography
  ST_Distance(
    'SRID=4326;POINT(126.808183 37.463557)'::geography,
    'SRID=4326;POINT(126.970769 37.555479)'::geography);

   st_distance    |  st_distance  
------------------+---------------
 17627.3383377316 | 17627.3138509