0

I'm trying to write something agnostic to whichever database vendor is chosen between MySQL, SQL Server, Oracle, and PostgreSQL. However, I see inconsistent results in functions that return measures. In this instance, it's the Area/STArea/SDO_Area functions. I have a simple table with a geometry column, I've inserted the following Polygon in there:

POLYGON((20.0 20.0, 20.0 22.0, 22.0 22.0, 22.0 20.0, 20.0 20.0))

The SRID is 4326 and I've written 4 select statements, one for each database to retrieve the area.

MySQL

SELECT ST_AREA(spatial_column) FROM spatial_table

Returns: 46045765517.41474

Oracle

SELECT SDO_GEOM.SDO_AREA(spatial_column) FROM spatial_table

Returns: 46045755446.0611

SQL Server

SELECT spatial_column.STArea() FROM spatial_table

Returns: 4

PostgreSQL

SELECT ST_AREA(spatial_column) FROM spatial_table

Returns: 4

I have all the relevant spatial indexes for each database, the bounding boxes, etc. In this instance which is right? And how do I make the ones that aren't, behave the same way? I've read competing information on whether this should be square meters or just a Cartesian area and I'm struggling to understand which it should be.

Improve this question
9
  • 1
    Very strange but in Postgres, using the second parameter to ST_Area (boolean use_spheroid=true) as either true or false gives me square meters. Take it away and it's back to 4.
    – mxcolin
    Commented Sep 17, 2022 at 18:02
  • 1
    Don't measure area in lat lon
    – Ian Turton
    Commented Sep 17, 2022 at 19:19
  • 1
    The question here is what should be returned and how to make them the same. It’s not a conceptual question.
    – mxcolin
    Commented Sep 17, 2022 at 21:08
  • re postgis.net/docs/ST_Area.html see the differences between the two versions.
    – bugmenot123
    Commented Sep 17, 2022 at 21:21
  • Yeah I see it now, it treats it as a geography with the second argument. So the following: "For geometry types a 2D Cartesian (planar) area is computed, with units specified by the SRID". That seems to make sense in that 4326 units would be decimal degrees, which makes me lean towards 4 as the right answer, which makes me think Oracle and MySQL are in fact wrong.
    – mxcolin
    Commented Sep 18, 2022 at 0:29

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.