agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedGeometry vs Geography (what to use)
8+ messages / 3 participants
[nested] [flat]
* Geometry vs Geography (what to use)
@ 2016-04-05 00:20 Michael Moore <michaeljmoore@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Michael Moore @ 2016-04-05 00:20 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Geometry vs Geography (what to use)
@ 2016-04-05 01:09 Steve Midgley <science@misuse.org>
parent: Michael Moore <michaeljmoore@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Steve Midgley @ 2016-04-05 01:09 UTC (permalink / raw)
To: 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
<http://gis.stackexchange.com/questions/26082/what-is-the-difference-between-geometric-and-geographic...;[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...
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Geometry vs Geography (what to use)
@ 2016-04-05 15:28 Michael Moore <michaeljmoore@gmail.com>
parent: Steve Midgley <science@misuse.org>
0 siblings, 1 reply; 8+ messages in thread
From: Michael Moore @ 2016-04-05 15:28 UTC (permalink / raw)
To: Steve Midgley <science@misuse.org>; +Cc: pgsql-sql
On Mon, Apr 4, 2016 at 6:09 PM, Steve Midgley <science@misuse.org> wrote:
> 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
> <http://gis.stackexchange.com/questions/26082/what-is-the-difference-between-geometric-and-geographic...;[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...
>
> Steve,
I think I read somewhere in the documents that Radians was the parameter
for Geometry and Meters is the parameter for Geography. Even with Radians,
if the radius of the circle is known then so too is it for 1 radian. I'm
guessing there is a default circle size for a geometry(Point,4326)
datatype. I'll keep digging and look at the docs you suggested.
Regards,
Mike
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Geometry vs Geography (what to use)
@ 2016-04-05 17:07 Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com>
parent: Michael Moore <michaeljmoore@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Lee Hachadoorian @ 2016-04-05 17:07 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; +Cc: pgsql-sql
Mike,
ST_DWithin takes a parameter as distance in the units of the SRID. Since
4326 is decimal degrees (latitude and longitude), you are manually
converting from degrees to miles. This conversion will only come close to
working if you are covering a pretty small area and if you look up the
conversion based on the latitude, and will *always* be hobbled that that a
degree longitude is not the same length as a degree latitude (although they
are close at the equator).
The standard way to accomplish this is to either project the coordinates to
an appropriate planar coordinate reference system, or to use the geography
data type. If you use geography, the units are in meters.
You could ask your DBAs to change the SRID of the table, but you can also
transform them as part of your query.
Option 1 (using SRID for UTM 10N, see below)
SELECT pc1.city AS city,
pc1.postalcode AS postalcode
FROM tpostalcoordinate pc1
WHERE ST_DWithin(ST_Transform(geo_position, 32610),
(SELECT ST_Transform(pc2.geo_position, 32610)
FROM tpostalcoordinate pc2
WHERE pc2.postalcode = '95050'
AND pc2.countrycode2tcountry = 'US'),
80467.2)
I've selected SRID 32610 as the appropriate UTM zone based on the searched
ZIP code 95050. If you need to do these calculations over a different area,
you would have to use a different UTM zone, or pick a wide-area CRS
(something that would apply to the continental US), with some loss of
accuracy.
Option 2 (using geography)
SELECT pc1.city AS city,
pc1.postalcode AS postalcode
FROM tpostalcoordinate pc1
WHERE ST_DWithin(geo_position::geography),
(SELECT pc2.geo_position::geography
FROM tpostalcoordinate pc2
WHERE pc2.postalcode = '95050'
AND pc2.countrycode2tcountry = 'US'),
80467.2)
Neither query has been tested.
Best,
--Lee
On Tue, Apr 5, 2016 at 11:28 AM, Michael Moore <michaeljmoore@gmail.com>
wrote:
>
>
> On Mon, Apr 4, 2016 at 6:09 PM, Steve Midgley <science@misuse.org> wrote:
>
>> 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
>> <http://gis.stackexchange.com/questions/26082/what-is-the-difference-between-geometric-and-geographic...;[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...
>>
>> Steve,
> I think I read somewhere in the documents that Radians was the parameter
> for Geometry and Meters is the parameter for Geography. Even with Radians,
> if the radius of the circle is known then so too is it for 1 radian. I'm
> guessing there is a default circle size for a geometry(Point,4326)
> datatype. I'll keep digging and look at the docs you suggested.
> Regards,
> Mike
>
>
--
Lee Hachadoorian
Asst Professor of Geography, Dartmouth College
http://freecity.commons.gc.cuny.edu/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Geometry vs Geography (what to use)
@ 2016-04-05 21:56 Michael Moore <michaeljmoore@gmail.com>
parent: Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Michael Moore @ 2016-04-05 21:56 UTC (permalink / raw)
To: Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com>; +Cc: pgsql-sql
On Tue, Apr 5, 2016 at 10:07 AM, Lee Hachadoorian <
Lee.Hachadoorian+L@gmail.com> wrote:
> Mike,
>
> ST_DWithin takes a parameter as distance in the units of the SRID. Since
> 4326 is decimal degrees (latitude and longitude), you are manually
> converting from degrees to miles. This conversion will only come close to
> working if you are covering a pretty small area and if you look up the
> conversion based on the latitude, and will *always* be hobbled that that a
> degree longitude is not the same length as a degree latitude (although they
> are close at the equator).
>
> The standard way to accomplish this is to either project the coordinates
> to an appropriate planar coordinate reference system, or to use the
> geography data type. If you use geography, the units are in meters.
>
> You could ask your DBAs to change the SRID of the table, but you can also
> transform them as part of your query.
>
> Option 1 (using SRID for UTM 10N, see below)
>
> SELECT pc1.city AS city,
> pc1.postalcode AS postalcode
> FROM tpostalcoordinate pc1
> WHERE ST_DWithin(ST_Transform(geo_position, 32610),
> (SELECT ST_Transform(pc2.geo_position, 32610)
> FROM tpostalcoordinate pc2
> WHERE pc2.postalcode = '95050'
> AND pc2.countrycode2tcountry = 'US'),
> 80467.2)
>
> I've selected SRID 32610 as the appropriate UTM zone based on the searched
> ZIP code 95050. If you need to do these calculations over a different area,
> you would have to use a different UTM zone, or pick a wide-area CRS
> (something that would apply to the continental US), with some loss of
> accuracy.
>
> Option 2 (using geography)
>
> SELECT pc1.city AS city,
> pc1.postalcode AS postalcode
> FROM tpostalcoordinate pc1
> WHERE ST_DWithin(geo_position::geography),
> (SELECT pc2.geo_position::geography
> FROM tpostalcoordinate pc2
> WHERE pc2.postalcode = '95050'
> AND pc2.countrycode2tcountry = 'US'),
> 80467.2)
>
> Neither query has been tested.
>
> Best,
> --Lee
>
> On Tue, Apr 5, 2016 at 11:28 AM, Michael Moore <michaeljmoore@gmail.com>
> wrote:
>
>>
>>
>> On Mon, Apr 4, 2016 at 6:09 PM, Steve Midgley <science@misuse.org> wrote:
>>
>>> 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
>>> <http://gis.stackexchange.com/questions/26082/what-is-the-difference-between-geometric-and-geographic...;[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...
>>>
>>> Steve,
>> I think I read somewhere in the documents that Radians was the parameter
>> for Geometry and Meters is the parameter for Geography. Even with Radians,
>> if the radius of the circle is known then so too is it for 1 radian. I'm
>> guessing there is a default circle size for a geometry(Point,4326)
>> datatype. I'll keep digging and look at the docs you suggested.
>> Regards,
>> Mike
>>
>>
>
>
> --
> Lee Hachadoorian
> Asst Professor of Geography, Dartmouth College
> http://freecity.commons.gc.cuny.edu/
>
Lee,
I tried casting to geography, but I get this:
ERROR: GetProj4StringSPI: Cannot find SRID (8307) in spatial_ref_sys
********** Error **********
So, I discovered that "select * from spatial_ref_sys;" gives no results,
meaning that the table is empty. I'll be talking with our DBAs about this.
That being as it may, I read on somebody's blog that casting to geography
can really slow things down so my plan is to add a new column like this:
alter table tpostalcoordinate add column geography_position
geography(POINT,4326) ;
then I will populate it like this:
UPDATE tpostalcoordinate set geography_position = ST_SetSRID(ST_Point(
longitude, latitude), 4326);
and build an index like:
CREATE INDEX tpostal_geo_geography_idx ON tpostalcoordinate USING
gist(geography_position);
I'll let every know how it goes.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Geometry vs Geography (what to use)
@ 2016-04-06 02:16 Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com>
parent: Michael Moore <michaeljmoore@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Lee Hachadoorian @ 2016-04-06 02:16 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; +Cc: pgsql-sql
<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
Mike,<br>
<br>
My spatial_ref_sys does not have an entry for SRID 8307 either, and
I wonder what query exactly you tried, because I'm not sure how that
SRID would appear. I thought your original data were in 4326, and
geography defaults to 4326 if an SRID is not specified (and I think
prior to PostGIS 2.1, not SRID other 4326 was possible for geography
type).<br>
<br>
Since most (recent) versions of PostGIS will be populated
spatial_ref_sys automatically during installation, the empty
spatial_ref_sys is odd. What is result of SELECT version() and
SELECT postgis_full_version()?<br>
<br>
Your statements to ALTER TABLE, UPDATE, and CREATE INDEX all look
correct. However, I would have your DBAs confirm that your PostGIS
installation is set up correctly before anything else.<br>
<br>
As an aside, you would probably get more responses from the PostGIS
Users mailing list (<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>) or
gis.stackexchange.com.<br>
<br>
Best,<br>
--Lee<br>
<br>
<br>
<div class="moz-cite-prefix">On 04/05/2016 05:56 PM, Michael Moore
wrote:<br>
</div>
<blockquote
cite="mid:CACpWLjMTtzOUj38++Jhkdj6C=UFKw6pAep7oa1-Zdqt1ROTDGw@mail.gmail.com"
type="cite">
<div dir="ltr"><br>
<div class="gmail_extra"><br>
</div>
<div class="gmail_extra">Lee,</div>
<div class="gmail_extra">I tried casting to geography, but I get
this:<br>
<div class="gmail_extra"><font color="#cc0000">ERROR:
GetProj4StringSPI: Cannot find SRID (8307) in
spatial_ref_sys</font></div>
<div class="gmail_extra"><font color="#cc0000">**********
Error **********</font></div>
<div class="gmail_extra">So, I discovered that "select * from
spatial_ref_sys;" gives no results, meaning that the table
is empty. I'll be talking with our DBAs about this. </div>
<div class="gmail_extra"><br>
</div>
<div class="gmail_extra">That being as it may, I read on
somebody's blog that casting to geography can really slow
things down so my plan is to add a new column like this:<br>
<div class="gmail_extra"><font face="monospace, monospace"
color="#0b5394">alter table tpostalcoordinate add
column geography_position geography(POINT,4326) ;</font></div>
<div class="gmail_extra"><font face="arial, helvetica,
sans-serif">then I will populate it like this:</font></div>
<div class="gmail_extra"><font face="monospace, monospace"
color="#0b5394">UPDATE tpostalcoordinate set
geography_position = ST_SetSRID(ST_Point( longitude,
latitude), 4326);</font><br>
</div>
<div class="gmail_extra"><font face="arial, helvetica,
sans-serif">and build an index like:</font><br>
<font face="monospace, monospace" color="#0b5394"> CREATE
INDEX tpostal_geo_geography_idx ON tpostalcoordinate
USING gist(geography_position);</font><br>
</div>
<div class="gmail_extra"><br>
</div>
<div class="gmail_extra">I'll let every know how it goes.</div>
<div class="gmail_extra"><font face="monospace, monospace"
color="#0b5394"><br>
</font></div>
</div>
</div>
</div>
</blockquote>
<br>
<pre class="moz-signature" cols="72">--
Lee Hachadoorian
Assistant Professor of Instruction, Geography & Urban Studies
Assistant Director, Professional Science Master's in GIS
Temple University
<a class="moz-txt-link-freetext" href="http://geospatial.commons.gc.cuny.edu">http://geospatial.commons.gc.cuny.edu</a;
<a class="moz-txt-link-freetext" href="http://freecity.commons.gc.cuny.edu">http://freecity.commons.gc.cuny.edu</a;
</pre>
</body>
</html>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Geometry vs Geography (what to use)
@ 2016-04-06 20:07 Michael Moore <michaeljmoore@gmail.com>
parent: Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Michael Moore @ 2016-04-06 20:07 UTC (permalink / raw)
To: Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com>; +Cc: pgsql-sql
On Tue, Apr 5, 2016 at 7:16 PM, Lee Hachadoorian <
Lee.Hachadoorian+L@gmail.com> wrote:
> Mike,
>
> My spatial_ref_sys does not have an entry for SRID 8307 either, and I
> wonder what query exactly you tried, because I'm not sure how that SRID
> would appear. I thought your original data were in 4326, and geography
> defaults to 4326 if an SRID is not specified (and I think prior to PostGIS
> 2.1, not SRID other 4326 was possible for geography type).
>
> Since most (recent) versions of PostGIS will be populated spatial_ref_sys
> automatically during installation, the empty spatial_ref_sys is odd. What
> is result of SELECT version() and SELECT postgis_full_version()?
>
> Your statements to ALTER TABLE, UPDATE, and CREATE INDEX all look correct.
> However, I would have your DBAs confirm that your PostGIS installation is
> set up correctly before anything else.
>
> As an aside, you would probably get more responses from the PostGIS Users
> mailing list (postgis-users@lists.osgeo.org) or gis.stackexchange.com.
>
> Best,
> --Lee
>
>
> On 04/05/2016 05:56 PM, Michael Moore wrote:
>
>
>
> Lee,
> I tried casting to geography, but I get this:
> ERROR: GetProj4StringSPI: Cannot find SRID (8307) in spatial_ref_sys
> ********** Error **********
> So, I discovered that "select * from spatial_ref_sys;" gives no results,
> meaning that the table is empty. I'll be talking with our DBAs about this.
>
> That being as it may, I read on somebody's blog that casting to geography
> can really slow things down so my plan is to add a new column like this:
> alter table tpostalcoordinate add column geography_position
> geography(POINT,4326) ;
> then I will populate it like this:
> UPDATE tpostalcoordinate set geography_position = ST_SetSRID(ST_Point(
> longitude, latitude), 4326);
> and build an index like:
> CREATE INDEX tpostal_geo_geography_idx ON tpostalcoordinate USING
> gist(geography_position);
>
> I'll let every know how it goes.
>
>
> --
> Lee Hachadoorian
> Assistant Professor of Instruction, Geography & Urban Studies
> Assistant Director, Professional Science Master's in GIS
> Temple Universityhttp://geospatial.commons.gc.cuny.eduhttp://freecity.commons.gc.cuny.edu
>
> So, for closure, here's the thing:
The DBA's were copying the geometry data from Oracle by using INSERT INTO
... SELECT FROM. The GEOMETRY DATATYPE is like a composite field, one of
those fields is the SRID. Oracle was set up to use SRID 8307. This is an
old, pre postgris SRID. As such, 8307 is not, by default, in the postgris
spatial_ref_sys table. Now, when I go to execute a postgris function
(including casting to geography) on the data that came from Oracle, the
first thing the functions do is to try to look up 8307 in spatial_ref_sys.
That's why I was getting: "ERROR: GetProj4StringSPI: Cannot find SRID
(8307) in spatial_ref_sys". My fix for this problem goes as follows:
Firstly I will change the datatype of tpostalcoordinate.geography_position from
GEOMETRY to GEOGRAPHY.
The dba's will continue to load tpostalcoordinate from Oracle but will not
load the geography_position field.
I will write an ON INSERT trigger that will populate the
geography_position field
base on latitude and longitude which are fields in the same record.
This will solve two problems
1. the SRID of the newly calculated geography_position will be 4326 not
8307
2. GEOGRAPHY is a better datatype for my needs and I will not need to
cast to GEOGRAPHY when using ST_DWithin in order to specify the distance
in meters.
Mike
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Geometry vs Geography (what to use)
@ 2016-04-06 20:39 Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com>
parent: Michael Moore <michaeljmoore@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Lee Hachadoorian @ 2016-04-06 20:39 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; +Cc: pgsql-sql
Mike,
This sounds like it will work.
For completeness I would like to point out that the ST_SetSRID function can
be used to overwrite an incorrect SRID. So, for example, you could do
something like:
INSERT INTO tpostalcoordinate (geometry_position, other_field)
SELECT ST_SetSRID(original_geom, 4326), original_other_field
FROM oracle_table
or for geography
INSERT INTO tpostalcoordinate (geography_position, other_field)
SELECT ST_SetSRID(original_geom, 4326)::geography, original_other_field
FROM oracle_table
On Wed, Apr 6, 2016 at 4:07 PM, Michael Moore <michaeljmoore@gmail.com>
wrote:
>
>
> On Tue, Apr 5, 2016 at 7:16 PM, Lee Hachadoorian <
> Lee.Hachadoorian+L@gmail.com> wrote:
>
>> Mike,
>>
>> My spatial_ref_sys does not have an entry for SRID 8307 either, and I
>> wonder what query exactly you tried, because I'm not sure how that SRID
>> would appear. I thought your original data were in 4326, and geography
>> defaults to 4326 if an SRID is not specified (and I think prior to PostGIS
>> 2.1, not SRID other 4326 was possible for geography type).
>>
>> Since most (recent) versions of PostGIS will be populated spatial_ref_sys
>> automatically during installation, the empty spatial_ref_sys is odd. What
>> is result of SELECT version() and SELECT postgis_full_version()?
>>
>> Your statements to ALTER TABLE, UPDATE, and CREATE INDEX all look
>> correct. However, I would have your DBAs confirm that your PostGIS
>> installation is set up correctly before anything else.
>>
>> As an aside, you would probably get more responses from the PostGIS Users
>> mailing list (postgis-users@lists.osgeo.org) or gis.stackexchange.com.
>>
>> Best,
>> --Lee
>>
>>
>> On 04/05/2016 05:56 PM, Michael Moore wrote:
>>
>>
>>
>> Lee,
>> I tried casting to geography, but I get this:
>> ERROR: GetProj4StringSPI: Cannot find SRID (8307) in spatial_ref_sys
>> ********** Error **********
>> So, I discovered that "select * from spatial_ref_sys;" gives no results,
>> meaning that the table is empty. I'll be talking with our DBAs about this.
>>
>> That being as it may, I read on somebody's blog that casting to geography
>> can really slow things down so my plan is to add a new column like this:
>> alter table tpostalcoordinate add column geography_position
>> geography(POINT,4326) ;
>> then I will populate it like this:
>> UPDATE tpostalcoordinate set geography_position = ST_SetSRID(ST_Point(
>> longitude, latitude), 4326);
>> and build an index like:
>> CREATE INDEX tpostal_geo_geography_idx ON tpostalcoordinate USING
>> gist(geography_position);
>>
>> I'll let every know how it goes.
>>
>>
>> --
>> Lee Hachadoorian
>> Assistant Professor of Instruction, Geography & Urban Studies
>> Assistant Director, Professional Science Master's in GIS
>> Temple Universityhttp://geospatial.commons.gc.cuny.eduhttp://freecity.commons.gc.cuny.edu
>>
>> So, for closure, here's the thing:
> The DBA's were copying the geometry data from Oracle by using INSERT INTO
> ... SELECT FROM. The GEOMETRY DATATYPE is like a composite field, one of
> those fields is the SRID. Oracle was set up to use SRID 8307. This is an
> old, pre postgris SRID. As such, 8307 is not, by default, in the postgris
> spatial_ref_sys table. Now, when I go to execute a postgris function
> (including casting to geography) on the data that came from Oracle, the
> first thing the functions do is to try to look up 8307 in spatial_ref_sys.
> That's why I was getting: "ERROR: GetProj4StringSPI: Cannot find SRID
> (8307) in spatial_ref_sys". My fix for this problem goes as follows:
> Firstly I will change the datatype of
> tpostalcoordinate.geography_position from GEOMETRY to GEOGRAPHY.
> The dba's will continue to load tpostalcoordinate from Oracle but will
> not load the geography_position field.
> I will write an ON INSERT trigger that will populate the
> geography_position field base on latitude and longitude which are fields
> in the same record.
> This will solve two problems
>
> 1. the SRID of the newly calculated geography_position will be 4326
> not 8307
> 2. GEOGRAPHY is a better datatype for my needs and I will not need to
> cast to GEOGRAPHY when using ST_DWithin in order to specify the
> distance in meters.
>
> Mike
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2016-04-06 20:39 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-04-05 00:20 Geometry vs Geography (what to use) Michael Moore <michaeljmoore@gmail.com>
2016-04-05 01:09 ` Steve Midgley <science@misuse.org>
2016-04-05 15:28 ` Michael Moore <michaeljmoore@gmail.com>
2016-04-05 17:07 ` Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com>
2016-04-05 21:56 ` Michael Moore <michaeljmoore@gmail.com>
2016-04-06 02:16 ` Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com>
2016-04-06 20:07 ` Michael Moore <michaeljmoore@gmail.com>
2016-04-06 20:39 ` Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox