The pre-defined schema BUILTIN contains user-defined types and methods used to store and search spatial data in an efficient manner. The functionality allows positions to be indexed and searched quickly.
There are two basic groups of spatial data:
Geographical data, i.e. positions on the surface of the Earth. See Geographical Data.
•Coordinate system data, i.e. positions in a two-dimensional plane. See Coordinate System Data.
The following user-defined types are used to store geographical data:
|
Type |
SQL type |
Description |
|---|---|---|
|
BUILTIN.GIS_LATITUDE |
BINARY(4) |
A distinct user-defined type that stores latitude values. |
|
BUILTIN.GIS_LONGITUDE |
BINARY(4) |
A distinct user-defined type that stores longitude values. |
|
BUILTIN.GIS_LOCATION |
BINARY(8) |
A distinct user-defined type that is used to store a location on Earth. It has a latitude and a longitude component. |
The builtin.gis_latitude data type is used to store latitude values. Valid values are between -90° and 90°. Negative values denote south latitudes, and positive values north latitudes.
The following routines belong to the user-defined type:
|
Routine |
Routine type |
Description |
|---|---|---|
|
BUILTIN.GIS_LATITUDE |
Function (constructor) |
Creates an instance of the type, with decimal input. |
|
Instance method |
Returns data as a decimal value. |
|
|
Instance method |
Returns data as a double precision value. |
|
|
Instance method |
Returns data as text, DDMMSS.ssss format, with a leading N for north or S for south. |
|
|
AS_TEXT(fmt) |
Instance method |
Returns data as text, on a format specified by the fmt parameter: |
Example
Create a table and insert a few values
create table latitudes (lat builtin.gis_latitude, description varchar(40));
Use builtin.gis_latitude to convert input values.
insert into latitudes values (builtin.gis_latitude(0),'Equator');
insert into latitudes values (builtin.gis_latitude(66.5619),'Arctic Circle');
insert into latitudes values (builtin.gis_latitude(-23.4389),
'Tropic of Capricorn');
Add an index to ensure search performance.
create index latx on latitudes (lat);
Read the latitude values, without any conversion
SQL>select * from latitudes;
lat description
======== ========================================
80000000 Equator
A7AC8A38 Arctic Circle
720781F8 Tropic of Capricorn
3 rows found
Read the latitude values as decimal
Use the as_decimal method to return the data as a decimal value.
SQL>select lat.as_decimal(), description from latitudes;
description
=========== ========================================
0.0000000 Equator
66.5619000 Arctic Circle
-23.4389000 Tropic of Capricorn
3 rows found
Read the latitude values as double precision
Use the as_double method to return the data as a double precision value.
SQL>select lat.as_double(), description from latitudes;
description
======================== ========================================
0.0000000000000000E+000 Equator
6.6561899999999994E+001 Arctic Circle
-2.3438900000000000E+001 Tropic of Capricorn
3 rows found
Return the latitude values as character, default format
Use the as_text method to return the data as DDMMSS.ssss text, with a leading N for north or S for south.
SQL>select lat.as_text(), description from latitudes;
description
==================== ========================================
N000000.0000 Equator
N663339.9000 Arctic Circle
S232616.9000 Tropic of Capricorn
3 rows found
Return the latitude values as character, N/S format
The as_text method with input value 1 will return data as DDMMSS.ssss text, with N for north and S for south.
SQL>select lat.as_text(1), description from latitudes;
description
==================== ========================================
N000000.0000 Equator
N663339.9000 Arctic Circle
S232616.9000 Tropic of Capricorn
3 rows found
Return the latitude values as character, +/- format
The as_text method with input value 2 will return the data as DDMMSS.ssss text, with + for north and - for south.
SQL>select lat.as_text(2), description from latitudes;
description
==================== ========================================
+000000.0000 Equator
+663339.9000 Arctic Circle
-232616.9000 Tropic of Capricorn
3 rows found
Return the latitude values as character, traditional format
The as_text method with input value 3 will return the data as character, with ° for degrees, ' for minutes and '' for seconds.
SQL>select lat.as_text(3), description from latitudes;
description
==================== ========================================
00°00'00.0000''N Equator
66°33'39.9000''N Arctic Circle
23°26'16.9000''S Tropic of Capricorn
3 rows found
SELECT the latitude values north of latitude N60
Use builtin.gis_latitude for input values.
SQL>select lat.as_decimal(), description
SQL&from latitudes
SQL&where lat > builtin.gis_latitude(60);
description
=========== ========================================
66.5619000 Arctic Circle
1 row found
The builtin.gis_longitude data type is used to store longitude values. Valid values are between -180° and 180°. Negative values denote west longitudes, and positive values east longitudes.
The following routines belong to the user-defined type:
|
Routine |
Routine type |
Description |
|---|---|---|
|
BUILTIN.GIS_LONGITUDE |
Function (constructor) |
Creates an instance of the type, with decimal input. |
|
Instance method |
Returns data as a decimal value. |
|
|
Instance method |
Returns data as a double precision value. |
|
|
Instance method |
Returns data as text, DDDMMSS.ssss format, with a leading E for east and W for west. |
|
|
AS_TEXT(fmt) |
Instance method |
Returns data as text, on a format specified by the fmt parameter: |
Examples
Create a table and insert a few values
create table longitudes (long builtin.gis_longitude, description varchar(40));
Use builtin.gis_longitude to convert input values.
insert into longitudes values (builtin.gis_longitude(0),'Prime Meridian');
insert into longitudes values (builtin.gis_longitude(-110.0),'Saskatchewan, W');
insert into longitudes values (builtin.gis_longitude(141.0),'South Australia, E');
Add an index to ensure search performance.
create index longx on longitudes (long);
Read the longitude values, without any conversion
SQL>select * from longitudes;
long description
======== ========================================
80000000 Prime Meridian
3E6F5500 Saskatchewan, W
D40AE480 South Australia, E
3 rows found
Read the longitude values as decimal
Use the as_decimal method to return the data as decimal.
SQL>select long.as_decimal(), description from longitudes;
description
============ ========================================
0.0000000 Prime Meridian
-110.0000000 Saskatchewan, W
141.0000000 South Australia, E
3 rows found
Read the longitude values as decimal
Use the as_double method to return the data as double precision.
SQL>select long.as_decimal(), description from longitudes;
description
============ ========================================
0.0000000 Prime Meridian
-110.0000000 Saskatchewan, W
141.0000000 South Australia, E
3 rows found
Return the longitude values as character, default format
The as_text method with input value 1 will return data as DDDMMSS.ssss text, with a leading E for east and W for west.
SQL>select long.as_text(), description from longitudes;
description
==================== ========================================
E0000000.0000 Prime Meridian
W1100000.0000 Saskatchewan, W
E1410000.0000 South Australia, E
3 rows found
Return the longitude values as character, E/W format
Use the as_text method to return the data as DDDMMSS.ssss text, with a leading E for east and W for west.
SQL>select long.as_text(1), description from longitudes;
description
==================== ========================================
E0000000.0000 Prime Meridian
W1100000.0000 Saskatchewan, W
E1410000.0000 South Australia, E
3 rows found
Return the longitude values as character, +/- format
The as_text method with input value 2 will return the data as DDDMMSS.ssss text, with a leading + for east and - for west.
SQL>select long.as_text(2), description from longitudes;
description
==================== ========================================
+0000000.0000 Prime Meridian
-1100000.0000 Saskatchewan, W
+1410000.0000 South Australia, E
3 rows found
Return the longitude values as character, traditional format
The as_text method with input value 3 will return the data as DDD°MM'SS.ssss'' text, with a trailing E for east and W for west.
SQL>select long.as_text(3), description from longitudes;
description
==================== ========================================
000°00'00.0000''E Prime Meridian
110°00'00.0000''W Saskatchewan, W
141°00'00.0000''E South Australia, E
3 rows found
SELECT the longitude values between longitude W60 and W30
Use builtin.gis_longitude for input values.
SQL>select long.as_decimal(), description
SQL&from longitudes
SQL&where long between builtin.gis_longitude(-120)
SQL& and builtin.gis_longitude(-90);
description
============ ========================================
-110.0000000 Saskatchewan, W
1 row found
The distinct user-defined type builtin.gis_location is used to store a location on Earth. It has a latitude component and a longitude component. (See BUILTIN.GIS_LATITUDE and BUILTIN.GIS_LONGITUDE for details.)
The following routines belong to the user-defined type:
|
Routine |
Routine type |
Description |
|---|---|---|
|
BUILTIN.GIS_LOCATION(lat,long) |
Function (constructor) |
Creates an instance of the type. The lat parameter is for latitude, the long parameter is for longitude. |
|
Instance method |
Returns “latitude, longitude” data as DDMMSS.ssss values text, with N/S and E/W notation. |
|
|
AS_TEXT(fmt) |
Instance method |
Returns “latitude, longitude” data on a format specified by the fmt parameter: |
|
LATITUDE |
Instance method |
Used to retrieve the latitude part of the location. |
|
LONGITUDE |
Instance method |
Used to retrieve the longitude part of the location. |
|
Instance method |
Method that returns whether a location is inside a rectangular area of the map. 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 locations (location builtin.gis_location, place nvarchar(30));
Use builtin.gis_location for input values.
insert into locations values
(builtin.gis_location(40.752134,-73.974638),'Chrysler Building');
insert into locations values
(builtin.gis_location(40.6892,-74.0445),'Statue of Liberty');
insert into locations values
(builtin.gis_location(40.735681,-73.99043),'Union Square');
insert into locations values
(builtin.gis_location(40.829167,-73.926389),'Yankee Stadium');
insert into locations values
(builtin.gis_location(40.756,-73.987),'Times Square');
insert into locations values
(builtin.gis_location(40.767778,-73.971667),'Central Park Zoo');
insert into locations values
(builtin.gis_location(40.729861,-73.991434),'Astor Place');
insert into locations values
(builtin.gis_location(40.779447,-73.96311),'Metropolitan Museum');
insert into locations values
(builtin.gis_location(40.782975,-73.958992),'Guggenheim Museum');
insert into locations values
(builtin.gis_location(40.703717,-74.016094),'Battery Park');
Add an index to ensure search performance.
create index locx on locations (location);
Read the inserted data, as it is
Use no conversion, just read raw data.
SQL>select * from locations;
location place
================ ==============================
938574C831D14FB0 Chrysler Building
93857151CDB2ED40 Statue of Liberty
9385743BF532D198 Union Square
9385767D45C6367C Yankee Stadium
9385749CB7EE7100 Times Square
938574E0D98B7224 Central Park Zoo
93857439F8594B58 Astor Place
938574EC2E0A0838 Metropolitan Museum
938574ECFAA0FE28 Guggenheim Museum
9385740CA864BB18 Battery Park
10 rows found
Read the inserted data, as text
Use the as_text method to return more readable locations.
SQL>select location.as_text(), place from locations;
place
======================================== ==============================
N404507.1340,W0735826.6380 Chrysler Building
N404120.2000,W0740238.5000 Statue of Liberty
N404406.6810,W0735924.4300 Union Square
N404944.1670,W0735533.3890 Yankee Stadium
N404521.0000,W0735913.0000 Times Square
N404601.7780,W0735815.6670 Central Park Zoo
N404344.8610,W0735927.4340 Astor Place
N404644.4470,W0735746.1100 Metropolitan Museum
N404655.9750,W0735728.9920 Guggenheim Museum
N404210.7170,W0740057.0940 Battery Park
10 rows found
Read the inserted data, as decimal
Use the as_decimal methods to return the locations’ latitude and longitude components.
SQL>select location.latitude().as_decimal(),
SQL> location.longitude().as_decimal(),
SQL> place
SQL&from locations;
place
=========== ============ ==============================
40.7521340 -73.9746380 Chrysler Building
40.6892000 -74.0445000 Statue of Liberty
40.7356810 -73.9904300 Union Square
40.8291670 -73.9263890 Yankee Stadium
40.7560000 -73.9870000 Times Square
40.7677780 -73.9716670 Central Park Zoo
40.7298610 -73.9914340 Astor Place
40.7794470 -73.9631100 Metropolitan Museum
40.7829750 -73.9589920 Guggenheim Museum
40.7037170 -74.0160940 Battery Park
10 rows found
Find the locations inside an area
Use the inside_rectangle method to find locations. Remember that builtin.gis_location wants decimal input!
SQL>select location.as_text(1), place from locations
SQL&where location.inside_rectangle(builtin.gis_location(40.75,-74.0),
SQL& builtin.gis_location(40.80,-73.0));
place
======================================== ==============================
N404507.1340,W0735826.6380 Chrysler Building
N404521.0000,W0735913.0000 Times Square
N404601.7780,W0735815.6670 Central Park Zoo
N404644.4470,W0735746.1100 Metropolitan Museum
N404655.9750,W0735728.9920 Guggenheim Museum
5 rows found
Find the same locations, but order them from south to north.
SQL>select location.as_text(1), place from locations
SQL&where location.inside_rectangle(builtin.gis_location(40.75,-74.0),
SQL& builtin.gis_location(40.80,-73.0))
SQL&order by location.latitude;
place
======================================== ==============================
N404507.1340,W0735826.6380 Chrysler Building
N404521.0000,W0735913.0000 Times Square
N404601.7780,W0735815.6670 Central Park Zoo
N404644.4470,W0735746.1100 Metropolitan Museum
N404655.9750,W0735728.9920 Guggenheim Museum
5 rows found
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. |
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. |
|
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