Re: Geometry vs Geography (what to use) - Mailing list 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 University
http://geospatial.commons.gc.cuny.edu
http://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