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

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