CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecordpostgis
svoop
svoop

Reputation: 3454

Get minimal geo distance from joined table

I have two tables projects and locations. A project can have many locations by use of the locations.project_id reference. Furthermore, locations feature the columns locations.latitude and locations.longitude.

I'm now looking for an efficient way to query all projects and their minimal distance to a fixed point. Say, there are two projects in the table each with two locations, I'd like to get the two projects and the distance to the one location which is closest to the fixed point.

I've created a .geography function for convenience:

CREATE FUNCTION geography(locations) RETURNS GEOGRAPHY AS $$
  SELECT ST_GeographyFromText(
    'SRID=4326;POINT(' || $1.latitude || ' ' || $1.longitude || ')'
  )
$$ LANGUAGE SQL

Now the following example uses "Zurich (47.36865, 8.539183)" as the fixed point:

SELECT 
  ST_Distance(locations.geography, ST_GeographyFromText('SRID=4326;POINT(47.36865 8.539183)')) AS distance 
  FROM "projects"
  INNER JOIN "locations" ON "locations"."project_id" = "projects"."id"

The query works, however, it returns the distance to the location with the oldest creation date and not the nearest one. Any idea how to tackle this?

UPDATE

My question is all wrong. What I try to achieve is quite easy with CTE, but that's another story. My apologies for the noise.

Upvotes: 0

Views: 204

Answers (1)

Mike T
Mike T

Reputation: 43612

PostGIS uses the axis order: longitude, latitude (think X, Y). Your distances are probably not what you expect, as you are currently searching in Somalia, not Switzerland.

Furthermore, your approach is really inefficient, since you are constantly re-generation geography types and have no spatial index. Consider moving the data into a spatial type, and removing redundant columns, e.g.:

ALTER TABLE locations ADD COLUMN geog geography(Point,4326);
UPDATE locations SET
    geog=ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)::geography;
CREATE INDEX locations_geog_idx ON locations USING gist (geog);
ALTER TABLE locations DROP COLUMN latitude;
ALTER TABLE locations DROP COLUMN longitude;

You can always access and view the data using ST_X for longitude, or ST_AsText or ST_AsLatLonText(geog::geometry) for strings like 47°22'7.140"N 8°32'21.059"E. Now your query:

SELECT 
  ST_Distance(locations.geog,
              ST_SetSRID(ST_MakePoint(8.539183, 47.36865), 4326)::geography)'))
    AS distance 
FROM "projects"
INNER JOIN "locations" ON "locations"."project_id" = "projects"."id";

Upvotes: 1

Related Questions