Oracle Communities

Oracle Analytics and AI

Child Item

How to visualize a Route Line?

Is it possible to implement a Route Line visualization in OAC or OAS Workbook that connects the latitude and longitude positions in order with a line using a data set that lists multiple orders, latitudes, and longitudes?

If it is possible, please explain in detail.

Find more posts tagged with

Accepted answers

Philippe Lions-Oracle

Hello @User_4VNZ5 the capability you mention is to connect a starting lat long and an ending lat long wiht a line on a map, even if your input data are only the two lat longs pairs. We are working on this feature and hoping to have it part of the product in first half 2025. In the meantime there is a viz plugin in the analytics library that will help you do this : look for lines on map extension there :

Analytics Library Extensions for DV | Oracle

let us know if issues

@Gautam Pisharam-Oracle

Gautam Pisharam-Oracle

Hi @User_4VNZ5 Here is a simple example how you can make line geometries work in Oracle Analytics, with data residing in an Oracle Database.

As an example, referring to the screenshot given below, the dataset I am using contains an 'Asset_ID' column which has unique identifiers that connects multiple locations with dynamic lines. The dynamic line is also based on the chronological sequential order we have in the 'Sequence' column.

Consider, the data is now loaded to an Oracle database associated with the following data types.

We can use the Manual Query mode in Oracle Analytics to dynamically create a dataset with lines connecting locations of each Asset ID, using a simple SQL.

Notice that, the LINE_GEOM column is created as a Geometry Data Type for each Asset _ID drawn ordered by the Sequence. (Geometry columns are identified using the Earth and Location icon symbol, in green color)

Here is the SQL used :

SELECT ASSET_ID, SDO_GEOMETRY( 'LINESTRING(' || LISTAGG(LONGITUDE || ' ' || LATITUDE, ',') WITHIN GROUP (ORDER BY SEQUENCE) || ')', 4326 ) AS LINE_GEOM FROM LINES_ASSETS GROUP BY ASSET_ID;

The dataset saved with the name Line_Assets is now used to create a workbook, and we just need to drag & drop or double click the Geometry Data Type Column to visualize the spatial object in a map.

You can optionally also use a query to draw the lines by filtering a specific set of sequences too. Example code shown below :

SELECT ASSET_ID, SDO_GEOMETRY( 'LINESTRING(' || LISTAGG(LONGITUDE || ' ' || LATITUDE, ',') WITHIN GROUP (ORDER BY SEQUENCE) || ')', 4326 ) AS LINE_GEOM FROM LINES_ASSETS WHERE SEQUENCE IN (88,90,92,94,97,99,101,104,107) GROUP BY ASSET_ID;

You can now include a second data layer with the latitude and longitude to display the sequence too to enhance the existing illustration with more detailed insight.

A brief explanation on the spatial side of the SQL Syntax :

SDO_Geometry is an Oracle Geometry function that helps you create Spatial Data Objects (SDO). In this example, we use the SDO Geometry to create a 'Linestring'. The Listagg function is used to aggregate the list of Longitudes and Latitudes to create the linestring (In the example, it aggregated the list of longitude and latitude for unique Asset_IDs ordered by sequence). You will also see reference to a unique number 4326 in the SQL, which is referring to WGS 84 coordinate system which is commonly and widely used.

Please Note : Geometry Data Type is a release as part of November 2024 in Oracle Analytics Cloud, Kindly reach out to us if you need to test this now in your existing Oracle Analytics Cloud instance.

Regards,

Gautam.

All comments

Sumanth V -Oracle

@User_4VNZ5 - Kindly refer the below note and see if it helps with your requirement:

https://docs.oracle.com/en/cloud/paas/analytics-cloud/acubi/represent-line-data-using-size-and-color-map.html

Thank you!

Philippe Lions-Oracle

Analytics Library Extensions for DV | Oracle

let us know if issues

@Gautam Pisharam-Oracle

Steve F-2

@User_4VNZ5 - Kitae,
Please update your Display name :-)

In addition, the Oracle Analytics video channel has some demos. You can click the the playlist to search within the playlist.

Examples

https://www.youtube.com/watch?v=7ubYUcsaSfY

https://www.youtube.com/watch?v=_J7OWfko73k

Kitae Lee

Thank you all.

I'm already using LinesOnMap Extensions for two lat longs pairs.
I hope that the feature to support more than two pairs will be released quickly as planned.
Thank you, Philippe Lions.

Philippe Lions-Oracle

Thanks @User_4VNZ5 , can you elaborate on the use case where you need to connect more than two lat long pairs. In particular, up to how many pairs to connect do you have a need for ?

Thanks

Kitae Lee

Our use case is for finance team to audit corporate credit card usage.
If something unusual is discovered while looking at the summary usage status and trends by team,
we will drill down and display the locations where a specific corporate credit card was used on a map according to the order of card approval.
This case is the approval history for one specific card per month, so about 70 to 80 cases occur.
So, I need about 70 to 80 pairs for this case.

Thanks

Philippe Lions-Oracle

For this number of pairs, you are probably better off creating line geometries ahead of time in the Oracle Database and then visualizing them natively in OAC. I assume each pair is sequenced chronologically on your lines, right ?

We can help you generate this today if you are not familiar with the process. One question though : is your original data in an Oracle database ?

@Gautam Pisharam-Oracle

Kitae Lee

Thanks @Philippe Lions-Oracle

Yes, my original data is in Oracle Database and each pair is sequenced chronologically.
I am not familiar with creating line geometries or line layers.
Aren't line geometries or line layers useful for fixed locations like highways or subway lines?
My pairs are not fixed, but are dynamic and random.

If it is useful for non-fixed locations,
I would appreciate it if you could tell me how to do it through a simple example.

Thanks

Philippe Lions-Oracle

thanks @User_4VNZ5 let us reach out to you by email, we will walk you through the process and see if it applies to your use case.

Gautam Pisharam-Oracle

Hi @User_4VNZ5 Here is a simple example how you can make line geometries work in Oracle Analytics, with data residing in an Oracle Database.

Consider, the data is now loaded to an Oracle database associated with the following data types.

We can use the Manual Query mode in Oracle Analytics to dynamically create a dataset with lines connecting locations of each Asset ID, using a simple SQL.

Here is the SQL used :

SELECT ASSET_ID, SDO_GEOMETRY( 'LINESTRING(' || LISTAGG(LONGITUDE || ' ' || LATITUDE, ',') WITHIN GROUP (ORDER BY SEQUENCE) || ')', 4326 ) AS LINE_GEOM FROM LINES_ASSETS GROUP BY ASSET_ID;

The dataset saved with the name Line_Assets is now used to create a workbook, and we just need to drag & drop or double click the Geometry Data Type Column to visualize the spatial object in a map.

You can optionally also use a query to draw the lines by filtering a specific set of sequences too. Example code shown below :

You can now include a second data layer with the latitude and longitude to display the sequence too to enhance the existing illustration with more detailed insight.

A brief explanation on the spatial side of the SQL Syntax :

Please Note : Geometry Data Type is a release as part of November 2024 in Oracle Analytics Cloud, Kindly reach out to us if you need to test this now in your existing Oracle Analytics Cloud instance.

Regards,

Gautam.