Coordinate System Data

---

The following user-defined type is used to store coordinate system data:

Type	SQL type	Description
builtin.gis_coordinate	BINARY(8)	This type has an x and a y component in a flat coordinate system. See BUILTIN.GIS_COORDINATE.

BUILTIN.GIS_COORDINATE

The distinct user-defined type builtin.gis_coordinate is used to store points in a two dimensional coordinate system. This type has an x and a y component, both represented by an integer value.

The following routines belong to the user-defined type:

Routine	Routine type	Description
BUILTIN.GIS_COORDINATE(x,y)	Function (constructor)	Creates an instance of the type.
X	Instance method	Used to retrieve the x unit of the point.
Y	Instance method	Used to retrieve the y unit of the point.
INSIDE_RECTANGLE(ll,ur)	Instance method	Method that returns whether a point is inside a rectangular area of the coordinate system. The ll parameter is for the lower left corner of the rectangle, and the ur parameter is for the upper right corner. May use indexes when available.

Example

Create a table and insert a few values

 create table coordinates (id integer primary key, point builtin.gis_coordinate);
 

Use builtin.gis_coordinate to insert values.

 insert into coordinates values (1, builtin.gis_coordinate(25,15));
 insert into coordinates values (2, builtin.gis_coordinate(30,40));
 insert into coordinates values (3, builtin.gis_coordinate(-3,33));
 insert into coordinates values (4, builtin.gis_coordinate(-40,-55));
 insert into coordinates values (5, builtin.gis_coordinate(115,25));
 insert into coordinates values (6, builtin.gis_coordinate(5,125));
 insert into coordinates values (7, builtin.gis_coordinate(-5,125));
 insert into coordinates values (8, builtin.gis_coordinate(0,25));
 insert into coordinates values (9, builtin.gis_coordinate(76,-1));
 insert into coordinates values (10, builtin.gis_coordinate(100,100));
 

Add an index to ensure search performance.

 create index coordsx on coordinates (point);

Read the inserted data, as it is

Use no conversion, just read raw data.

 SQL>select * from coordinates;
          id point
 =========== ================
           1 C0000000000001EB
           2 C0000000000009D4
           3 9555555555555D53
           4 3FFFFFFFFFFFF1C2
           5 C000000000001787
           6 C000000000002AB3
           7 9555555555557FE7
           8 C000000000000282
           9 6AAAAAAAAAAABAFA
          10 C000000000003C30
 
                  10 rows found

Read the x and y values

Use the x and y methods to return more readable points.

 SQL>select id, point.x() as x, point.y() as y from coordinates;
          id           x           y
 =========== =========== ===========
           1          25          15
           2          30          40
           3          -3          33
           4         -40         -55
           5         115          25
           6           5         125
           7          -5         125
           8           0          25
           9          76          -1
          10         100         100
 
                  10 rows found

Find the points inside an rectangle

Use the inside_rectangle method to find points inside an rectangle.

 SQL>select id, point.x() as x, point.y() as y from coordinates
 SQL&where point.inside_rectangle(builtin.gis_coordinate(0,0),
 SQL&                             builtin.gis_coordinate(100,100));
          id           x           y
 =========== =========== ===========
           1          25          15
           8           0          25
           2          30          40
          10         100         100
 
                   4 rows found
 

Find the same points, but order them by the x coordinate.

 SQL>select id, point.x() as x, point.y() as y from coordinates
 SQL&where point.inside_rectangle(builtin.gis_coordinate(0,0),
 SQL&                             builtin.gis_coordinate(100,100)) 
 SQL&order by point.x;
          id           x           y
 =========== =========== ===========
           8           0          25
           1          25          15
           2          30          40
          10         100         100
 
                   4 rows found

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se