0

I spend a comparison of functions of spatial analysis and Oraсle postgresql. There are two tables with the same coordinate system - tech_bridge.

oracle query

select sum(SDO_GEOM.SDO_AREA(t.gdo_geometry,1)) from geo.tech_bridge t

result : 167615.3831

postgresql query

select sum(ST_Area(t.gdo_geometry, true)) from geo.tech_bridge t

result : 167508.4431

why the results are different?

1

The two area calculation sums are within 0.0638% of each other, which is pretty close.

It looks like you are calculating the area of a geodesic polygons, which is a complicated problem, with several different algorithms, each with a slightly different result. I can point out the algorithms used for different versions of PostGIS, but the algorithm for Oracle Spatial is locked away from our eyes.

0

The other thing to watch is this: are the two shapes strictly identical, i.e with the exact same number of decimals ?

When you query a geometry in Oracle with sqlplus, you will only see up to 10 characters (including sign and decimal separator), so you may not see all decimals. If you then do something like cut and paste the output to insert it into another table, you would be missing precision. Do

SQL> set numwidth 50

to see all decimals, then compare that to what you have on PostGIS.

Are the shapes in projected or geodetic coordinates ? Projected coordinates should show no or a very small difference.

For geodetic computations, differences in algorithms in the two platform may explain the difference you observe. I assume that you run both databases on the same platform ? Otherwise differences in math libraries, or in the floating point processing can also play a role. Note that Oracle uses the full description of the geodetic system (i.e. the ellipsoid and datum) for its calculations. I assume PostGIS does the same.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.