I need to create a table of lat/lon coordinates with repeating values, in order to calculate distances between all locations.
In the DB I have the following fields to use:
| Location Code | Latitude | Longitude |
|---|---|---|
| ABC123 | 39.426413 | -77.458716 |
| DEF456 | 25.708686 | -80.378430 |
| ZYX123 | 38.734117 | -77.473075 |
What I’d like to do is have each location repeat in column 1, with column 2 having all the other locations.
| Location Code 1 | Location Code 2 | Lat 1 | Lon 1 | Lat 2 | Lon 2 |
|---|---|---|---|---|---|
| ABC123 | ABC123 | 39.426413 | -77.458716 | 39.426413 | -77.458716 |
| ABC123 | DEF456 | 39.426413 | -77.458716 | 25.708686 | -80.378430 |
| ABC123 | ZYX123 | 39.426413 | -77.458716 | 38.734117 | -77.473075 |
| DEF456 | ABC123 | 25.708686 | -80.378430 | 39.426413 | -77.458716 |
| DEF456 | DEF456 | 25.708686 | -80.378430 | 25.708686 | -80.378430 |
| DEF456 | ZYX123 | 25.708686 | -80.378430 | 38.734117 | -77.473075 |
etc.
Is there a way to do a Union or Join to get this kind of query setup?
I’ve tried a few different types of joins and nothing seems to be coming out like I’m looking for it to. Any help on this is greatly appreciated.
Andrew Moseley is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
It’s not a loop. You are not using structured programming to solve the problem. You are using Cognos Analytics, which deals with data. So what you need is a way to appropriately join the dataset to itself.
Use a CROSS JOIN, also known as a cartesian join. This is a join where you don’t relate the tables in any way. So rather than something like…
SELECT A.*
, B.*
FROM A
INNER JOIN B ON B.ID = A.ID
…you want this…
SELECT A.*
, B.*
FROM A
CROSS JOIN B
To to this in a Cognos Analytics report:
Let’s assume you have a query named Query1.
- Copy Query1. Query2 is created.
- Create a new query (Query3).
- Add a join to Query3.
- Drag Query1 and Query2 onto the boxes leading to the join.
- Open Query3 and add data items as needed.
Notice you did NOT define the join.
Now, there’s a setting in Cognos that may keep you from doing this. If you get a message stating that cross joins are not allowed, you need to do a little extra work.
- In Query1 and Query2, create a data item named
joincoland use the expression'a'. - Open the join leading to Query3 and create a join on joincol–joincol.