Re: Geometry vs Geography (what to use)

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(at)gmail(dot)com>
wrote:

>
>
> 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
>
>