Geometry vs Geography (what to use)

From: Michael Moore <michaeljmoore(at)gmail(dot)com>
To: postgres list <pgsql-sql(at)postgresql(dot)org>
Subject: Geometry vs Geography (what to use)
Date: 2016-04-05 00:20:24
Message-ID: CACpWLjMnn1Bpcq0r6W7ZebF1yp7TCMuWXqVQubERsuKYgh_ogA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I am converting from Oracle to postgres. We have an application that takes
a value for MILES as an input and returns a list of SCHOOLS that are within
range. This all works fine in Oracle, no problem. In Oracle the datatype of
the table field is SDO_GEOMETRY. Our DBAs have set up the same table in
Postgres with datatype geometry(Point,4326). Here is where the problem
comes in. I am trying to use the ST_DWithin function and it kind of works
except that one of the input parameters to this function is expressed in
RADIANS.

SELECT pc1.city AS city,
pc1.postalcode AS postalcode
FROM tpostalcoordinate pc1
WHERE ST_DWithin(geo_position,
(SELECT pc2.geo_position
FROM tpostalcoordinate pc2
WHERE pc2.postalcode = '95050'
AND pc2.countrycode2tcountry = 'US'),
50 * .01539)
So, in the above query I am selecting all the cities with a 50 mile radius.
It WORKS ... kind of. The thing I don't like is my screwy way of
converting radians to miles. ( miles * .01539 ). It's just a best guess
that gives me results 'almost' the same as Oracle's. The second thing I
don't like is the 'almost' the same. I'm am guessing the difference in the
result set is due to the planar calculation vs the spheroid calculations.
So my questions are

1. Should I be using GEOGRAPHY(POINT,4326) instead of GEOMETRY(POINT,4326)I
assume this would allow me to express the distance meters and it
would do spheroid
calculations which should give me results more consistent with Oracle's?
2. Any advice. Is there something else I should be doing? What did I
miss?

tia,
Mike

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Steve Midgley 2016-04-05 01:09:48 Re: Geometry vs Geography (what to use)
Previous Message Alexander Levsha 2016-04-04 08:12:17 Re: SQL query syntax question