1

I know Geography(4326) is an unbounded, and unprojected, and covers the entire world with ellipsoidal coordinates. I get that the math on this system is done on a spheroid. I also get that with a geometry the math is done on a Cartesian grid. What I really would like to understand is how SRS 4326 can be subject to both ellipsoidal calculations, and Cartesian calculations without reprojection (like ST_Transform)?

SELECT ST_Distance( ST_MakePoint(0,0)::geography, ST_MakePoint(1,1)::geography );
   st_distance   
-----------------
 156899.56829134
(1 row)

-- same result as ST_MakePoint(x,y)::geography::geometry
-- same result as ST_SetSRID( ST_MakePoint(x,y)::geometry, 4326 )
-- same result as ST_SetSRID( ST_MakePoint(x,y), 4326 )
SELECT ST_Distance( ST_MakePoint(0,0)::geometry, ST_MakePoint(1,1)::geometry );
   st_distance   
-----------------
 1.4142135623731
(1 row)

By extension, MySQL does not support nearly the amount of Geographic operations as PostGIS. Worse, it determines the type of calculation (geometric vs geographic) based not the type (as with PostGIS's geometry vs geography) but based merely as the SRS ID marker. For any operations that do not support a geodetic reference system, you have to use a Cartesian reference system (which is obviously major suck because MySQL can't reproject). But why if it has these two shortcomings (fewer geodetic calculations), and an inability to reproject can it not do a geometric calculation (Cartesian reference system) with SRS ID 4326? Lastly, is there any SRS ID that is not geodetic that behaves like PostgreSQL geometry type in SRS ID 4326, so I can use it with MySQL?

|improve this question
  • I started with an answer, but surrendered to it becoming a beast... anyway, consider the GEOGRAPHY type in PostGIS to be 'only' a container for the implied ellipsoidal math; it is no projection and its use not limited to a single geodetic datum (e.g. WGS84 - EPSG:4326). if a geographic CRS is used in a cartesian context, its units (degree of Lon/Lat) are projected as if they were planar units, with parallels of constant distance, leaving this 'projection' (Equirectangular - Plate Carée) heavily distorted and useless for anything analytical (neither conformal nor equal area). – ThingumaBob Jan 18 '19 at 9:23
  • 2
    ...but you can do cartesian algebra on it just fine. useless and with dangerously misleading results, but possible ,) – ThingumaBob Jan 18 '19 at 9:30
  • Interesting to see that the PostGIS documentation says One restriction is that it only supports WGS 84 long lat (SRID:4326). because 4326 is a code of the EPSG database, and EPSG:4326 is lat lon. – nmtoken Jan 18 '19 at 11:01
  • 1
    @mmtoken "Prior to PostGIS 2.2, the geography type only supported WGS 84 long lat (SRID:4326). For PostGIS 2.2 and above, any long/lat based spatial reference system defined in the spatial_ref_sys table can be used. You can even add your own custom spheroidal spatial reference system as described in "Geography type is not limited to earth." " - current docs. – ThingumaBob Jan 18 '19 at 11:36
  • 1
    I did a lot of beta testing of MySQL spatial functions a few years back, see, lenzg.net/archives/2009/07.html. I quickly realized that MySQL's focus was elsewhere and once Oracle took over, they were never going to promote an open source version of something that would compete with Oracle Spatial. So, I did the sensible thing and switched to Postgres/Postgis. You could always rewrite proj4 as MySQL functions :D. – John Powell Jan 18 '19 at 12:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.