Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

Geographical 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.
See BUILTIN.GIS_LATITUDE.

builtin.gis_longitude

BINARY(4)

A distinct user-defined type that stores longitude values.
See BUILTIN.GIS_LONGITUDE.

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.
See BUILTIN.GIS_LOCATION.

BUILTIN.GIS_LATITUDE

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.

AS_DECIMAL

Instance method
Returns data as a decimal value.

AS_TEXT

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:
1 = DDMMSS.ssss format, with a leading + for north and - for south
2 = DDMMSS.ssss format, with a leading N for north and S for south
3 = DD°MM'SS.ssss'' format, with a trailing N for north and S for south.

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
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

BUILTIN.GIS_LONGITUDE

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.

AS_DECIMAL

Instance method
Returns data as a decimal value.

AS_TEXT

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:
1 = DDDMMSS.ssss format, with a leading + for east and - for west
2 = DDDMMSS.ssss format, with a leading E for east and W for west
3 = DDD°MM'SS.ssss'' format, with a trailing E for east and W for west.

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
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

BUILTIN.GIS_LOCATION

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.

AS_TEXT

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:
1 = DDMMSS.ssss formats, with + for east and north, and - for west and south
2 = DDMMSS.ssss formats, with leading N for north and S for south, and E for east and W for west
3 = DD°MM'SS.ssss'' formats, with trailing N for north and S for south, and trailing E for east and W for west

LATITUDE

Instance method
Used to retrieve the latitude part of the location.

LONGITUDE

Instance method
Used to retrieve the longitude part of the location.

INSIDE_RECTANGLE(ll,ur)

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
Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX