Checking Distances in a Single Table

At work we have two identical GPS units.  One had to be sent in for service so we wanted to check to make sure it was back in working order.  We collected a series of points with both units.  I took the data collected and put it into a Postgres table; the table ended up with three fields:  set_id which identified the point as collected on each unit, unit_name to show which unit collected the point, and geom for the geometry of the point itself.  Here’s the query I came up with to compare the data:

SELECT DISTINCT a.set_id, ST_Distance(a.geom, b.geom) As dist
FROM test_pts As a JOIN test_pts As b USING (set_id)
WHERE a.unit_name <> b.unit_name
ORDER BY dist DESC;

Too often I forget about self-joins.  They made this query quick to write and easy to understand.

Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: