Subject: Re: Geometry vs Geography (what to use)
To: Michael Moore <michaeljmoore@gmail.com>
References: 
 <CACpWLjMnn1Bpcq0r6W7ZebF1yp7TCMuWXqVQubERsuKYgh_ogA@mail.gmail.com>
 <CAJexoSJGX20rA3foxHvhkL8zZqYqpE=_1hEzY9eUxtq3U-KJQw@mail.gmail.com>
 <CACpWLjOik+06NRiKDz10xJHqcz0rZC2g6BFr_7Onr6ziJxR1xQ@mail.gmail.com>
 <CANnCtnJAF2Ux3esB=w=3D-Lbg8xfbs=Viby5Uy84YY8mQZ9RRA@mail.gmail.com>
 <CACpWLjMTtzOUj38++Jhkdj6C=UFKw6pAep7oa1-Zdqt1ROTDGw@mail.gmail.com>
Cc: postgres list <pgsql-sql@postgresql.org>
From: Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com>
Message-ID: <57047181.3060106@gmail.com>
Date: Tue, 5 Apr 2016 22:16:33 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.6.0
MIME-Version: 1.0
In-Reply-To: 
 <CACpWLjMTtzOUj38++Jhkdj6C=UFKw6pAep7oa1-Zdqt1ROTDGw@mail.gmail.com>
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

<html>
  <head>
    <meta content="text/html; charset=utf-8" http-equiv="Content-Type">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    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 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).<br>
    <br>
    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()?<br>
    <br>
    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.<br>
    <br>
    As an aside, 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>
    <blockquote
cite="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 casting to geography, but I get
          this:<br>
          <div class="gmail_extra"><font color="#cc0000">ERROR:
               GetProj4StringSPI: Cannot find SRID (8307) in
              spatial_ref_sys</font></div>
          <div class="gmail_extra"><font color="#cc0000">**********
              Error **********</font></div>
          <div class="gmail_extra">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. </div>
          <div class="gmail_extra"><br>
          </div>
          <div class="gmail_extra">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:<br>
            <div class="gmail_extra"><font face="monospace, monospace"
                color="#0b5394">alter table tpostalcoordinate  add
                column geography_position geography(POINT,4326) ;</font></div>
            <div class="gmail_extra"><font face="arial, helvetica,
                sans-serif">then I will populate it like this:</font></div>
            <div class="gmail_extra"><font face="monospace, monospace"
                color="#0b5394">UPDATE tpostalcoordinate set
                 geography_position = ST_SetSRID(ST_Point( longitude,
                 latitude), 4326);</font><br>
            </div>
            <div class="gmail_extra"><font face="arial, helvetica,
                sans-serif">and build an index like:</font><br>
              <font face="monospace, monospace" color="#0b5394"> 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 know how it goes.</div>
            <div class="gmail_extra"><font face="monospace, monospace"
                color="#0b5394"><br>
              </font></div>
          </div>
        </div>
      </div>
    </blockquote>
    <br>
    <pre class="moz-signature" cols="72">-- 
Lee Hachadoorian
Assistant Professor of Instruction, Geography &amp; 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>
  </body>
</html>