How to get distance for the nearest city from Two different table and combining it into 1 column in PostgreSQL

Question

i was stuck for days in this schema now.

I am trying to populate distance column in a different table from other 2 tables. Inside the table there are lat, long, city id, distance, and location id.

This is the current table that i wanted to populate

This is the two tables that i can get to calculate the distance from

LocationID are the same as ID in the first table To calculate the distance to the nearest city i calculate it using lat long, this is what my code look like for the nearest distance

select location_id, distance
from (SELECT  t.table1.location_id as location_id,
                ( 6371 * acos( cos( radians(6.414478) ) * 
                    cos( radians(t.table1.latitude::float8) ) * 
                        cos( radians(t.table1.longitude::float8) - radians(12.466646) )
                            + sin( radians(6.414478) ) * sin( radians(t.table1.latitude::float8) ) ) ) AS distance 
    FROM t.table1
    INNER JOIN t.table2
    on t.table1.location_id = t.table2.id
) km 
group by location_id, distance
Having distance < 2000 
order by distance limit 20;

but the table only returns null value

I'm using PostgreSQL for this code and the application used for visualising is metabase.

@BjarniRagnarsson sure the table with the empty row in column city id and distance are table t, the table consisting of tag, user id, status, etc.. are the table1 and the table consisting of city, city ascii and country are table2 — William Adriel, Dec 18 '19 at 07:58
As the longitude and latitude columns in table1 are left justified, they seem to be varchar colums (you need to check the definition of the table). You can convert to double like this: ```t."table1".latitude::float8```. The right side in join should probably be "table2 instead of "table1". The limit clause should be 20 I suspect. (0.20 is truncated to 0 to return no rows). Try this first :-) — Bjarni Ragnarsson, Dec 18 '19 at 08:59
@BjarniRagnarsson Hi i've tried fixing the changes, but now its showing this error cross-database references are not implemented: t."table1".distance Position: 236 — William Adriel, Dec 18 '19 at 09:18
`FROM t."table1"` doesn't make sense unless you have a schema named `t`. Did you mean to write an alias? e.g. `FROM ."table1" as t`? Also the column reference `t."table1".location_id` is invalid. It should either be `t.location_id` (if `t` should be a table alias` or `"table1".location_id` — , Dec 18 '19 at 09:19
@a_horse_with_no_name alias its because there's a table inside the (t) table — William Adriel, Dec 18 '19 at 09:22
Then you need to remove the schema from the column reference `"table1".location_id` instead of `t."table1".location_id` — , Dec 18 '19 at 09:33
I suspect this ```t."table1".distance``` shouldn't be there at all. Also, ```"Distance"```should be all lowercase as you are selecting the column from the subquery as distance. You should remove all the " in my opinion. Makes more readable code and I never use it unless neccessary (to account for upper/lowercase, spaces) etc. — Bjarni Ragnarsson, Dec 18 '19 at 09:37
Can you update your question with the changes you made to the code? — Bjarni Ragnarsson, Dec 19 '19 at 08:13
@BjarniRagnarsson edited the question i made with the changes to the code, but it still return null value — William Adriel, Dec 20 '19 at 06:41

score 1 · Answer 1 · answered Dec 18 '19 at 09:14

1

I would recommend you to use ST_Distance function from PostGIS extension for distance calculation instead of doing it yourself. It will be easier and definitely much faster.

answered Dec 18 '19 at 09:14

pensnarik

1,214
2
12
15

i've read about PostGIS, but i still not understand on how to use it for this example as i'm still quite new to the postgreSQL – William Adriel Dec 19 '19 at 05:14
ohh and PostGIS can't be used in Metabase – William Adriel Dec 20 '19 at 09:11
@WilliamAdriel: why can't PostGIS be used? It essentially only provides additional functions that are used in SQL statements - the same way you use other functions in your SQL statement that is then displayed by Metabase. Why is calling `radians()` possible there but, calling `st_distance()` not (provided PostGIS is installed in the Postgres database)? – Dec 20 '19 at 13:07

Bjarni Ragnarsson · Accepted Answer · 2019-12-23T08:40:26.913

0

Edited: Probably misunderstood the initial intentions.
This should work:

select d.city_id, d.distance, latitude,location_id, longitude 
from t.table1
left join lateral (
    select city_id, distance from (
        select location_id city_id, ( 6371 * acos( cos( radians(table3.latitude) ) * 
                        cos( radians(t.table1.latitude::float8) ) * 
                            cos( radians(t.table1.longitude::float8) - radians(table3.longitude) )
                                + sin( radians(table3.latitude) ) * sin( radians(t.table1.latitude::float8) ) ) ) AS distance 
        from t.table3
    ) d
    where distance < 2000
    order by distance 
    limit 1
) cities on true

Try it out.
Best regards,
Bjarni

edited Dec 23 '19 at 08:40

answered Dec 20 '19 at 07:58

Bjarni Ragnarsson

1,731
1
6
8

There is probably some error in data - illegal longitude or latitude probably. But it occurs to me that perhaps there is some misunderstanding going on. You specify 3 tables in the question but you only seem to be working with the first 2. Was the intention to calculate from table2 and table3 to populate table1 ? In that case, the lat and lng columns in table3 is in alltogether different format than in the other tables. – Bjarni Ragnarsson Dec 20 '19 at 08:58
yes the intention is to calculate table2 and table3 to populate table1. The lat long in columns in table3 are different because that's the lat long for the city, while the lat long in columns in table2 are the lat long for the location of a user – William Adriel Dec 20 '19 at 09:02
You need to convert the lat and lng values in the city table to decimal format. – Bjarni Ragnarsson Dec 20 '19 at 09:45
i've changed it to decimal format, but i still very confuse on how to compare it as there's no matching values in table2 and table3 to compare and the only thing i can think of is to use haversine to compare – William Adriel Dec 20 '19 at 10:05
I probably misunderstood something - but we got the calculations right :-) See modified answer where we add the closest city from table3. Hope this is right. You have to make the update on table1 of course. What you were looking for is probably the LATERAL join wich joins every record from table1 to table3 using values in the "current" record in table1. – Bjarni Ragnarsson Dec 20 '19 at 13:08
Hi the code works, but the city id still null and not displaying the correct city id – William Adriel Dec 23 '19 at 06:19
There is probably typo in there - need d.city_id and d.distance as edited in answer. – Bjarni Ragnarsson Dec 23 '19 at 08:41
missing FROM-clause entry for table "d" LINE 1: select d.city_id, d.distance, latitude,location_id, longitud... it still producing this error – William Adriel Dec 23 '19 at 12:04
It could be that no city is within 2000m ?. If you want only those with results, you should use "from t.table1, lateral (........ " instead of left join. You take out the "on true" also in that case. – Bjarni Ragnarsson Dec 23 '19 at 12:26
Found the error, instead of using d, it need to use cities as the from clause – William Adriel Dec 23 '19 at 13:15

How to get distance for the nearest city from Two different table and combining it into 1 column in PostgreSQL

2 Answers2