agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com>
To: Michael Moore <michaeljmoore@gmail.com>
Cc: postgres list <pgsql-sql@postgresql.org>
Subject: Re: Geometry vs Geography (what to use)
Date: Tue, 5 Apr 2016 22:16:33 -0400
Message-ID: <57047181.3060106@gmail.com> (raw)
In-Reply-To: <CACpWLjMTtzOUj38++Jhkdj6C=UFKw6pAep7oa1-Zdqt1ROTDGw@mail.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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
<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 & 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>
view thread (8+ messages) latest in thread
Message-ID: <57047181.3060106@gmail.com>
Permalink: ../57047181.3060106@gmail.com/
Also on: postgresql.org/message-id/57047181.3060106@gmail.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Lee.Hachadoorian+L@gmail.com, michaeljmoore@gmail.com
Subject: Re: Geometry vs Geography (what to use)
In-Reply-To: <57047181.3060106@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox