Spatial Illusions in APEX: Why Coordinate Systems Matter More Than You Think

In our previous article, we explored how map projections work - and why Greenland looks much bigger than it really is. We saw how projecting a round Earth onto a flat map always causes some distortion, just like trying to flatten an orange peel onto a piece of paper. Now it’s time to go a step further and talk about something just as important in the world of mapping: Coordinate Reference Systems or CRS.

What is a CRS?

A Coordinate Reference System (CRS) is a system that tells us how to describe the position of things on Earth using numbers - usually latitude and longitude (but not always).

You can think of a CRS as a set of rules that define:

• The origin (where coordinates start),

• The shape and size of the Earth (spherical, ellipsoidal, etc.),

• And how to project this curved surface onto a flat map.

In simple terms: a CRS tells your computer how to understand and draw geographic data correctly on a map.

CRS vs. Projection - What’s the Difference?

This is a common point of confusion, so let’s clarify it.

• A Projection is just one part of a CRS. It’s the mathematical method used to transform the 3D Earth onto a 2D surface.

• A CRS includes the projection, but also defines the coordinate system (like lat/lon or meters), datum, and other parameters.

How many CRSs are available?

The EPSG (European Petroleum Survey Group) database, one of the most widely used CRS collections, currently contains over 6,000 different coordinate reference systems!

What are the TOP 5 most popular CRSs?

1. EPSG:4326 - WGS 84 (World Geodetic System 1984)

• The most commonly used CRS worldwide.

• A global reference system used in GPS and most GIS applications.

• Coordinates are expressed in degrees (latitude, longitude).

2. EPSG:3857 - Web Mercator

• Standard for web maps.

• Used by Google Maps and OpenStreetMap.

• Enables fast map rendering on digital screens.

3. EPSG:32633 / EPSG:25832 - UTM Zone 33N (WGS 84 / ETRS89)

• Popular in Europe, especially in Germany, Scandinavia, and Poland.

• Provides higher accuracy than WGS 84 and is widely used in geodesy and cartography.

4. EPSG:28992 - Amersfoort / RD New

• Official CRS of the Netherlands

• Used in Dutch mapping systems for precise geographic measurements.

5. EPSG:2180 - ETRS89 / Poland CS92

• Official CRS of Poland

• Standard for geodesy and mapping in Poland.

Why are there so many CRSs? And why do the Netherlands, Germany, and Poland use different ones?

The Earth is round, but maps are flat

When representing the round Earth on a flat map, deformations occur - either in shape, distance, or area. Each country adopts a CRS that minimizes these deformations for its specific region.

Different country needs

• The Netherlands uses RD New (EPSG:28992), optimized for its small, flat topography.

• Germany uses Gauss-Krüger projections, which work well for its larger territory.

• Poland uses ETRS89 / Poland CS92 (EPSG:2180), adapted to its specific geographic shape.

Legacy systems vs. modern standards

In the past, countries developed their own CRS systems. While many now use modern standards like ETRS89, some older systems are still in use for historical data compatibility.

Higher accuracy

• Small countries can use a single CRS for the entire nation.

• Larger countries often divide their land into zones to improve measurement precision.

Legal regulations

Many countries have legal requirements for land surveying, construction, and land ownership records, which dictate the use of specific CRS systems.

SDO_GEOMETRY and SRID

SDO_GEOMETRY is a data type in Oracle used to represent geometric objects such as points, lines, and polygons within a database. It enables users to store, query, and manage spatial data efficiently.

The SDO_GEOMETRY object consists of the following attributes:

• SDO_GTYPE - specifies the geometry type (e.g., point, polygon).

• SDO_SRID - the Spatial Reference System Identifier (SRID) that defines the coordinate system.

• SDO_POINT - stores the coordinates for point geometries.

• SDO_ELEM_INFO - an array that describes the structure and composition of the geometry.

• SDO_ORDINATES - an array containing the coordinate values that define the geometry.

You can read more about the SDO_GEOMETRY type HERE. Below is an example of an SDO_GEOMETRY representation for a polygon:

SDO_GEOMETRY(
    2003, --SDO_GTYPE = SDO_POLYGON2D
    4326, --SDO_SRID = WGS84
    NULL, --SDO_POINT is null (geometry for polygon)
    SDO_ELEM_INFO_ARRAY(1, 1003, 1), --polygon description
    SDO_ORDINATE_ARRAY( --coordinates
        20.7533776504406 52.3973224996299, 
        20.7533776504406 52.0959234112862, 
        21.3046961460238 52.0959234112862, 
        21.3046961460238 52.3973224996299, 
        20.7533776504406 52.3973224996299
));

which on the map looks like this:

Now that we know how to store a polygon’s geometry in the database using the EPSG:4326 – WGS 84 coordinate system, how can we convert it to a different CRS?

The answer is simple: we can use the SDO_CS.TRANSFORM function.

SDO_CS.TRANSFORM(
     geom    IN SDO_GEOMETRY, --my geometry
     to_srid IN NUMBER --the coordinate system to be used for the transformation
 ) RETURN SDO_GEOMETRY;

After applying this function to our polygon, we can see that its coordinates change significantly depending on the coordinate system used:

Oracle APEX Map region vs different CRS

Now, I will use the geometry of my polygon with two different coordinate systems.

In previous articles, I have written extensively about creating layers, so I won’t repeat that information here. However, if this is your first time visiting this blog, I encourage you to read more about layers HERE, HERE, and HERE. 🙂

Getting to the point: for each CRS, I created a separate layer. I also set up two Map Regions to present the results more clearly.

Here’s how our polygon looks in:

• EPSG:4326 – WGS 84

• EPSG:28992 – Amersfoort / RD New

Both polygons look identical. Why? APEX automatically converts CRS

When you use a Map Region in Oracle APEX, it does something very helpful:

1. It reads the SRID from each geometry.

2. If the SRID is not 4326 (WGS 84), APEX automatically transforms the geometry to WGS 84.

3. Then it shows all the geometries on the map.

Oracle uses a built-in function called SDO_CS.TRANSFORM to do this conversion.

The map itself uses Web Mercator projection

Even though the geometries are in WGS 84 (latitude and longitude), the map background uses a projection called Web Mercator (SRID 3857).

So what happens is:

• Your polygon → converted to WGS 84 (if needed)

• Then → shown on a Web Mercator map

That’s why everything looks correct and in the right place, even when your data comes from different CRS.

Is it really that simple?

Data Types and CRS Handling

• If we store data as SDO_GEOMETRY, we don’t need to worry about CRS transformations. No matter what SRID we use, APEX automatically converts the data to the default CRS.

• If we store data as VARCHAR (longitude and latitude) or GeoJSON, we run into issues. APEX cannot apply SDO_CS.TRANSFORM to these formats, meaning we have to manually convert GeoJSON to SDO_GEOMETRY before processing.

Exporting and Importing Data

Even though APEX displays data in WGS84, this does not mean the data is actually stored in this CRS in the database.

A common scenario:

• A user interacts with the map (e.g., retrieves coordinates using the Map Clicked dynamic action).

• The system saves the data in the database.

• Later, the data is exported for other users.

For external users, the CRS in which the data is stored can be crucial - especially if they use different mapping systems.

Summary

CRS and data transformations can be tricky, but APEX helps by automatically converting and showing data in a default CRS.

Still, as developers, we should know how our spatial data is stored. It’s not just about displaying it - it also needs to be accurate and work well with other systems! 🙂