How to join two tables using date, time, lat and lon as common field?

Question

I am sorry if the question is elementary but I did not find any clear discussion or guide to understand how to run a query in postgresql. So that a pointer to another website would be also useful. Anyhow, I have two tables:

Table 1
id|day|time|lat|lon|
1a|2010-09-12|4:35:04|44.66038|10.74039|
2a|2010-09-12|13:04:38|44.65472|10.76201|
3a|2010-09-12,15:41:55,44.65450|10.76198|
4a|2010-09-12|15:43:54|44.65469|10.76197|
5a|2010-09-12|15:44:26|44.65476|10.76199|
6a|2010-09-12|15:45:40|44.65480|10.76202|
7a|2010-09-18|07:22:00|44.65480|10.76202|
8a|2010-09-18|07:23:57|44.65477|10.76186|
9a|2010-09-18|07:47:32|44.65470|10.76199|

Table 2
id|day|time|lat|lon|depart/arrive
1b|2010-09-12|4:35:04|44.66038|10.74039|D
2b|2010-09-12|15:45:40|44.65480|10.76202|A
3b|2010-09-18|07:47:32|44.65470|10.76199|D

Table 2 has been obtained from table 1 and it includes an additional column: depart/arrive. This column indicates if the coordinate (or row) is the first coordinate of a path (D) or the last coordinate (A).

Using postgres, I would like to use as common field of the two tables "day", "time", "lat", "lon" in order to obtain a new table 3 that includes all the fields of table 1 plus the "depart/arrive" field of table 2.

Table 3
id|day|time|lat|lon|depart/arrive
1a|2010-09-12|4:35:04|44.66038|10.74039|D
2a|2010-09-12|13:04:38|44.65472|10.76201|NULL
3a|2010-09-12,15:41:55,44.65450|10.76198|NULL
4a|2010-09-12|15:43:54|44.65469|10.76197|NULL
5a|2010-09-12|15:44:26|44.65476|10.76199|NULL
6a|2010-09-12|15:45:40|44.65480|10.76202|A
7a|2010-09-18|07:22:00|44.65480|10.76202|NULL
8a|2010-09-18|07:23:57|44.65477|10.76186|NULL
9a|2010-09-18|07:47:32|44.65470|10.76199|D

I thank you in advance.

Answer 1

Everyone learning SQL should know about the LEFT JOIN. It basically joins everything from the left table to the "joins" on the right side. Where there are no matches, the right data is null, but the left side still is queried. Wikipedia has a good article on Joins.

SELECT A.id, A.day, A.time, A.lat, A.lon, B.depart_arrive
FROM table_1 AS A
LEFT JOIN table_2 AS B ON A.id = B.id AND A.day = B.day AND A.time = B.time AND
                          A.lat = B.lat AND A.lon = B.lon
ORDER BY A.id, A.day, A.time, A.lat, A.lon, B.depart_arrive;

This isn't "table_3", but you can SELECT ... INTO table_3 FROM ..., or create a view, so the joined data is always up-to-date in something that looks like a table.

---

On a side note, rather than storing two columns for day and time, it is more powerful to use one column for timestamp (with time zone). Same for your location data, which could be stored as a PostgreSQL point type, or PostGIS geometry or geography types. These will add other GIS features, and can improver the searching performance with an optimized index.

Answer 2

you have the right idea but would need another field to concat them all into.
or since you are in posgres perhaps a view with the fields concat.
once you have the field the join will be completed as lynxlynxlynx shows.

EDIT:

1. create a composite pk and fk. It may work from what I read.
2. use the join

Answer 3

Thank you all. The query works and I could visualize the result in the data output windows. In order to obtain table_3 I used SELECT A.id, A.day, A.time, A.lat, A.lon, B.depart_arrive INTO table_3 ...