search

GeoNet / fdsn

FDSN Web Services
MIT License
17 stars 15 forks source link

Fix query cross 180 meridian issue for event service. #221

Closed junghao closed 2 years ago

junghao commented 3 years ago

As stated in https://github.com/GeoNet/fdsn/issues/212.

Below are results to query against 2 points on the different side of meridian.

The existing query

fdsn=> select ST_X(origin_geom::GEOMETRY),ST_Y(origin_geom::GEOMETRY), ST_Distance(origin_geom::GEOMETRY, ST_SetSRID(ST_Makepoint(-179.40,-40.57), 4326)) from fdsn.event;
     st_x     |     st_y     |    st_distance
--------------+--------------+--------------------
  176.3257242 | -40.57806609 |  355.7257242914494
 -176.3257242 | -40.57806609 | 3.0742863816296597
(2 rows)

As you can see the above results, the distance between -179.40 to 176.32 is 355.

Below is the result of updated SQL query:

fdsn=> select ST_X(origin_geom::GEOMETRY),ST_Y(origin_geom::GEOMETRY), ST_Distance(ST_ShiftLongitude(origin_geom::GEOMETRY), ST_ShiftLongitude(ST_SetSRID(ST_Makepoint(-179.40,-40.57), 4326))) from fdsn.event;
     st_x     |     st_y     |    st_distance
--------------+--------------+--------------------
  176.3257242 | -40.57806609 | 4.2742834108507015
 -176.3257242 | -40.57806609 | 3.0742863816296597
(2 rows)

which has the correct results.