Re: Geometry vs Geography (what to use)

On Tue, Apr 5, 2016 at 7:16 PM, Lee Hachadoorian <
Lee(dot)Hachadoorian+L(at)gmail(dot)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(at)lists(dot)osgeo(dot)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