Counting points/coordinates that lie within a bounding box

Question

I have 2 tables. The first table contains following columns: Start_latitude, start_longitude, end_latitude, end_longitude, sum. The sum column is empty and needs to be filled based on second table.

The second table contains 3 columns: point_latitude, point_longitude

Table 1

-------------------------
|45 | 50 | 46 | 51 | null|
----|---------------------
|45 | 54 | 46 | 57 | null|
--------------------------

Table2:

---------------
| 45.5 | 55.2 |
---------------
| 45.8 | 50.6 |
---------------
| 45.2 | 56   |
---------------

The null values in table1-row1 would be 1 while in row2 it would be 2. It is the count of number of points that lie within the bounding box.

I can do it in python by writing functions to read values between dataframes. How can this be done in Postgresql. This is a sample problem statement that I came up with for my situation.

Don't you have any ID columns in your tables ? – Joakim Danielson Oct 28 '18 at 20:42 — Joakim Danielson, Oct 28 '18 at 20:42

Joakim Danielson · Accepted Answer · 2018-10-28T21:09:44.103

Update This version was tested on PostgreSql 9.3 using SQL Fiddle

UPDATE table1 a
SET sum = sub.point_count
FROM (SELECT a.start_lat, a.end_lat, a.start_lon, a.end_lon, COUNT(*) as point_count
      FROM table1 a, table2 b
      WHERE b.point_lat BETWEEN start_lat AND a.end_lat
        AND b.point_lon BETWEEN a.start_lon AND a.end_lon
      GROUP BY a.start_lat, a.end_lat, a.start_lon, a.end_lon) as sub
WHERE a.start_lat = sub.start_lat
  AND a.end_lat = sub.end_lat
  AND a.start_lon = sub.start_lon
  AND a.end_lon = sub.end_lon;

Original answer

Here is my solution, it is tested on MySQL but there is nothing specific about this code so it should work on PostgreSql as well

UPDATE table1 a,
  (SELECT a.start_lat, a.end_lat, a.start_lon, a.end_lon, COUNT(*) as count
   FROM table1 a, table2 b
   WHERE b.point_lat BETWEEN start_lat AND a.end_lat
   AND b.point_lon BETWEEN a.start_lon AND a.end_lon
   GROUP BY a.start_lat, a.end_lat, a.start_lon, a.end_lon) as sub
SET sum = count
WHERE a.start_lat = sub.start_lat
  AND a.end_lat = sub.end_lat
  AND a.start_lon = sub.start_lon
  AND a.end_lon = sub.end_lon

Note that this query would be much shorter if table1 contained a PK Id column.

Counting points/coordinates that lie within a bounding box

1 Answers1