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

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
defaultsto 4326 if an SRID is not specified (and I think prior to PostGIS 2.1, not SRID other 4326 was possible for
geographytype).<br /><br /> Since most (recent) versions of PostGIS will be populated spatial_ref_sys automatically
duringinstallation, 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,
Iwould have your DBAs confirm that your PostGIS installation is set up correctly before anything else.<br /><br /> As
anaside, 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><blockquotecite="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
castingto geography, but I get this:<br /><div class="gmail_extra"><font color="#cc0000">ERROR:  GetProj4StringSPI:
Cannotfind SRID (8307) in spatial_ref_sys</font></div><div class="gmail_extra"><font color="#cc0000">********** Error
**********</font></div><divclass="gmail_extra">So, I discovered that "select * from spatial_ref_sys;" gives no results,
meaningthat the table is empty. I'll be talking with our DBAs about this. </div><div class="gmail_extra"><br
/></div><divclass="gmail_extra">That being as it may, I read on somebody's blog that casting to geography can really
slowthings down so my plan is to add a new column like this:<br /><div class="gmail_extra"><font color="#0b5394"
face="monospace,monospace">alter table tpostalcoordinate  add column geography_position geography(POINT,4326)
;</font></div><divclass="gmail_extra"><font face="arial, helvetica,               sans-serif">then I will populate it
likethis:</font></div><div class="gmail_extra"><font color="#0b5394" face="monospace, monospace">UPDATE
tpostalcoordinateset  geography_position = ST_SetSRID(ST_Point( longitude,  latitude), 4326);</font><br /></div><div
class="gmail_extra"><fontface="arial, helvetica,               sans-serif">and build an index like:</font><br /><font
color="#0b5394"face="monospace, monospace"> 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
knowhow it goes.</div><div class="gmail_extra"><font color="#0b5394" face="monospace, monospace"><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>