PostGIS has a set of box data types to manage the bounding box of a geometry. The set of box types includes the box2d, box3d and box3d_extent types. A box is composed of x_min, y_min, x_max and y_max coordinates. Those data types aren't OGC valid and they don't exist in JASPA. JASPA uses a standard geometry data type instead of using box types.

SELECT ST_AsText(ST_GeomFromText('BOX(0 0,10 10)'));
                st_astext
-----------------------------------------
 POLYGON ((0 0, 0 10, 10 10, 10 0, 0 0))
(1 row)

SELECT ST_AsText(ST_GeomFromText('BOX3D(0 0 -5,10 10 5)'));
                      st_astext
------------------------------------------------------
 POLYGON ((0 0 -5, 0 10 5, 10 10 5, 10 0 -5, 0 0 -5))
(1 row)

SELECT ST_Area(ST_GeomFromText('BOX(0 0,10 10)'));
 st_area
---------
     100
(1 row)

The OGC specifications SFS 1.0 (1999) and SFS 1.1.0 (2005) were 2D with no support for Z coordinate.

The OGC 1.2.0 SFS specification, appeared in 2006, develops the previous specifications to support Z and M (measure) coordinates. ISO SQL-MM Part 3 also supports Z and M Coordinates.

SELECT ST_AsEWKT(
 ST_GeomFromEWKT('POINT(1 2)')) as ga;

POINT (1 2)

POINT(1 2)

SELECT ST_AsEWKT(
 ST_GeomFromEWKT('POINT(1 2 3)')) as ga;

POINT(1 2 3)

POINT(1 2 3)

SELECT ST_AsEWKT(
 ST_GeomFromEWKT('POINTM(1 2 4)')) as ga;

POINT (1 2 0 4)

POINTM(1 2 4)

SELECT ST_AsEWKT(
 ST_GeomFromEWKT('POINT(1 2 3 4)')) as ga;

POINT (1 2 3 4)

POINT(1 2 3 4)

SELECT ST_AsEWKT(ST_GeomFromText('LINESTRING(-3.52 41.0 20,2.33 48.86 30)',4326)) as ga;
--Result
                        ga
---------------------------------------------------
 SRID=4326;LINESTRING (-3.52 41 20, 2.33 48.86 30)
(1 row)


SELECT ST_AsText(ST_GeomFromText('LINESTRING(-3.52 41.0 20,2.33 48.86 30)',4326)) as ga;
--Result
                   ga
-----------------------------------------
 LINESTRING (-3.52 41 20, 2.33 48.86 30)
(1 row)

SELECT ST_AsEWKT(ST_GeomFromText('LINESTRING(-3.52 41.0 20,2.33 48.86 30)',4326)) as ga;
--Result
WARNING:  OGC WKT expected, EWKT provided - use GeomFromEWKT() for this
                       ga
-------------------------------------------------
 SRID=4326;LINESTRING(-3.52 41 20,2.33 48.86 30)
(1 row)

SELECT ST_AsText(ST_GeomFromText('LINESTRING(-3.52 41.0 20,2.33 48.86 30)',4326)) as ga;
--Result
WARNING:  OGC WKT expected, EWKT provided - use GeomFromEWKT() for this
               ga
---------------------------------
 LINESTRING(-3.52 41,2.33 48.86)
(1 row)

1.2.3. Mixed Dimension Geometries

JASPA can handle with mix dimensionality in a geometry. JASPA would associate the coordinate dimension of the first point to the entire geometry. For it, JASPA will drop the unnecessary coordinates or will add zeros to the unknown coordinates.

	Keep in mind that every vertex in a geometry should have the same dimensionality. This is just a solution for an undesirable mistake.

1st Point Coordinate Space	Example	Result JASPA
XY	SELECT ST_Asewkt( ST_GeomFromText(' GEOMETRYCOLLECTION( POINT (5 5), LINESTRING (0 0 5, 0 50))'));	GEOMETRYCOLLECTION (POINT (5 5), LINESTRING (0 0, 0 50)
XYZ	SELECT ST_Asewkt(ST_GeomFromText(' GEOMETRYCOLLECTION( POINT (5 5 10), LINESTRING (0 0, 0 50))'));	GEOMETRYCOLLECTION (POINT (5 5 10), LINESTRING (0 0 0, 0 50 0))
XYZM	SELECT ST_Asewkt(ST_GeomFromText(' GEOMETRYCOLLECTION( POINT (5 5 10 8), LINESTRING (0 0 2, 0 50))'));	GEOMETRYCOLLECTION (POINT (5 5 10 8), LINESTRING (0 0 2 0, 0 50 0 0))

PostGIS

SELECT ST_Asewkt( ST_GeomFromText('
 GEOMETRYCOLLECTION(  POINT (5 5), LINESTRING (0 0 5, 0 50))'));

--Result
ERROR:  can not mix dimensionality in a geometry

A geometry is empty if it does not have any points. "POINT EMPTY" or "LINESTRING EMPTY" are examples of empty geometries. JASPA and PostGIS address empty geometries in different ways. While JASPA converts to NULL every empty geometry, PostGIS returns GEOMETRYCOLLECTION EMPTY, regardless of the actual type of input EMPTY geometry.

SELECT ST_AsText(ST_GeomFromText('POINT EMPTY'));

--Result JASPA:
null

--Result PostGIS:
GEOMETRYCOLLECTION EMPTY

Functions

In JASPA, if the input geometry is an empty set, any function will always return as result the null value.

SELECT ST_Envelope(ST_GeomFromText('POINT EMPTY'));
--Result JASPA:
null

--Result PostGIS:
    st_envelope
--------------------
 010700000000000000
(1 row)

Spatial Intersection

Geometries that do not intersect, will return as their intersection an empty geometry. Consequently, as stated before, the result of disjoint intersections will be different in JASPA and PostGIS.

SELECT ST_astext(ST_Intersection (
ST_GeomFromText('POLYGON ((1 3, 1 8, 6 8, 6 3, 1 3))') ,
ST_GeomFromText('POLYGON((8 8, 6 10, 8 10, 8 8))')));

--Result JASPA:
null

--Result PostGIS:
GEOMETRYCOLLECTION EMPTY

Dump functions include ST_Dump, ST_DumpPoints, ST_DumpRings

Whereas JASPA Dump functions return just an array of geometries, PostGIS Dump functions create an array of geometries with an array of integers (path). The following table shows the different behaviour:

SELECT st_dump(st_geomfromtext(
 'MULTIPOINT (1 2, 3 4, 5 6)'
 ));

--Result:
         st_dump
---------------------------
 \001\001\ ... 000\000\000@
 \001\001\ ... 000\000\020@
 \001\001\ ... 000\000\030@

SELECT ST_AsText(ST_Dump 
 (ST_GeomFromText('MULTIPOINT (1 2, 3 4, 5 6)'))
 );

or

SELECT ST_AsText(geom) FROM 
 ST_Dump(ST_GeomFromText(' MULTIPOINT (1 2, 3 4, 5 6)')) 
 as geom;

--Result:

  st_Astext
-------------
POINT (1 2)
POINT (3 4)
POINT (5 6)

SELECT geom from 
 st_dump ('select st_geomfromewkt('
  'MULTIPOINT (1 2, 3 4, 5 6)'
 ')as geom');

--Result:
GEOM   
01010000 ... 00000000000040 
01010000 ... 00000000001040 
01010000 ... 00000000001840

SELECT ST_AsText(geom) from st_dump ('select 
 st_geomfromewkt('' MULTIPOINT (1 2, 3 4, 5 6)'') as geom'
 );

--Result:
JASPA.ST_ASTEXT(GEOM)   
POINT (1 2) 
POINT (3 4) 
POINT (5 6) 

SELECT ST_Dump (ST_GeomFromText
 ('MULTIPOINT (1 2, 3 4, 5 6)')
 );

--Result:
           ST_Dump
------------------------------
({1},01010000 ... 00000000040)
({2},01010000 ... 00000001040)
({3},01010000 ... 00000001840)

SELECT ST_AsText(a.geom) FROM (SELECT 
 (ST_Dump(ST_GeomFromEWKT(' MULTIPOINT (1 2, 3 4, 5 6) '))).geom 
  AS geom) AS a;

or 

SELECT ST_AsText( 
 (ST_Dump(ST_GeomFromText(' MULTIPOINT (1 2, 3 4, 5 6)' ))).geom
 );

--Result:

  st_Astext
-------------
POINT (1 2)
POINT (3 4)
POINT (5 6)

H2 database converts to uppercase the names of tables or field unless you enclose them in double quotation marks. By contrary, PostgreSQL database converts to lower case every unquotated letter.

For instance, the following SQL sentence would create in PostgreSQL a table test(id), while in H2 the table is named TEST(ID).

create table test (id serial PRIMARY KEY);

This must be considered when you pass table and column names as parameters. The following example shows the different use of the function AddGeometryColumn in PostgreSQL and H2.

--PostgreSQL
SELECT AddGeometryColumn ('','public','test','geom_point',4326,'POINT',2);

--H2
SELECT AddGeometryColumn ('PUBLIC','TEST','GEOM_POLYGON',25830,'MULTIPOLYGON',4);

Alternatively, this issue could be easily solved using double quotation marks. The result of the following SQL sentence is the same in both databases, the table is named test(id).

create table "test" ("id" serial PRIMARY KEY);

Casts are used to convert from one data type to another. In PostgreSQL database, it is allowed to define casts in user-defined types like geometries. By contrast, in H2, those casts are not allowed. So, only JASPA for PostgreSQL have defined casts to convert geometry data to others data types.

Anyway, it is advisable to avoid using casts and instead use OGC functions to transform from one data type to another. The main reason, as we've already stated, is that casts are not well supported in all databases.

This section, explains the casting behaviour for JASPA in PostgreSQL.

PostgreSQL
SELECT ST_Length('LINESTRING (1 2,3 5)'::geometry);
SELECT ST_Length(CAST('LINESTRING (1 2,3 5)' AS geometry));

--Result
    st_length
------------------
 3.60555127546399
(1 row)

An array data type is called by appending square brackets ( [ ] ) to the data type name of the array elements. The functions that support geometry arrays indicate it in their definition. For instance ST_MakeLine includes:

The way to create an array is completely different in PostgreSQL and H2. The following example shows how to construct an array for each database.

PostgreSQL

--(1st Version)--
SELECT asewkt(ST_MakeLine(Array[ST_MakePoint(0, 0), ST_MakePoint(10, 10),ST_MakePoint(15, 10)]));

--Result
             asewkt
--------------------------------
 LINESTRING (0 0, 10 10, 15 10)
(1 row)

--(2nd Version)--
SELECT ST_AsText(ST_Makeline(ST_Accum(geom))) as myline
FROM
(SELECT ST_GeomFromText('SRID=10;POINT(0 0)') as geom
union
SELECT ST_GeomFromText('SRID=10;POINT(10 10)') 
union 
SELECT ST_GeomFromText('SRID=10;POINT(15 15)') 
) as geom;

--Result
          myline
------------------------------
LINESTRING (15 15, 0 0, 10 10)

H2

To construct an array we make use of the function ST_Accum which constructs an array of geometries from a set of geometries.

--(1st Version)--
SELECT ST_AsText(ST_Makeline(st_accumagg(geom))) as myline from (select array_get(
(
st_geomfromtext('POINT(0 0)'),
st_geomfromtext('POINT(10 10)'),
st_geomfromtext('POINT(15 15)')
),x)::binary as geom from (select x from system_range(1,3) as foo) as tabla);

--Result
MYLINE  
LINESTRING (0 0, 10 10, 15 15)

--(2nd Version)--
SELECT ST_AsText(ST_Makeline(ST_Accumagg(geom))) as myline
FROM
(SELECT ST_GeomFromText('SRID=10;POINT(0 0)') as geom
union
SELECT ST_GeomFromText('SRID=10;POINT(10 10)') 
union 
SELECT ST_GeomFromText('SRID=10;POINT(15 15)') 
) as geom;

--Result
MYLINE  
LINESTRING (15 15, 0 0, 10 10)

Every aggregate function has been renamed in JASPA for H2 database by adding an "Agg" suffix. In the following table the spatial aggregate functions are listed.

H2 database doesn't support homonyms functions with the same number of parameters, even if those parameters have different types. For that reason, several functions have been renamed in H2. They are listed in the following table:

Functions without alias

The functions Difference and Expand are already defined in the H2 database. So, if you want to use those JASPA functions, you must always call the functions with the "ST_" prefix.