Re: Geometry vs Geography (what to use) - Mailing list pgsql-sql

On Mon, Apr 4, 2016 at 5:20 PM, Michael Moore <michaeljmoore@gmail.com> wrote:

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?

Just reading this article[1] makes me think that you might get better spherical calculations for lat/long using the Geography data type.

Regarding ST_DWithin itself, I'm confused about your convertion to radians. The docs I'm looking at say that the third parameter to that function is meters, not radians. (And radians aren't a measure of distance anyway, as I'm sure you know, but which is also confusing me in your message). So are you just converting 50 miles to meters? 

Also, maybe your conversion factor is accurate, and it is possible that the *Oracle side* is using a linear projection (treating lat/long like a cartesian grid), and you are doing proper spherical math on the Pg side, and that's why your results are close but not quite right?

I hope this helps your work.. Best,

Steve

[1] http://gis.stackexchange.com/questions/26082/what-is-the-difference-between-geometric-and-geographic-columns