<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7652.24">
<TITLE>RE: [postgis-users] Indices for ST_distance_sphere</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>Nitpicking -- don't forget to run "ANALYZE" on the table after creating the index (or doing lots of changes to indexed columns) so the planner has more accurate statistics on the table).<BR>
<BR>
Greg Williamson<BR>
Senior DBA<BR>
DigitalGlobe<BR>
<BR>
Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.<BR>
<BR>
(My corporate masters made me say this.)<BR>
<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net on behalf of Paul Ramsey<BR>
Sent: Tue 10/14/2008 6:53 PM<BR>
To: PostGIS Users Discussion<BR>
Subject: Re: [postgis-users] Indices for ST_distance_sphere<BR>
<BR>
A UTM zone is too narrow for all of Australia, so that's a bad choice.<BR>
But those other projections are parameterized for the whole country,<BR>
so unless you accuracy requirements are extremely high, they should<BR>
fit the bill.<BR>
<BR>
You'll need to build an index on the geometry to get indexed behavior:<BR>
<BR>
CREATE INDEX mygeoidx ON mytable USING GIST (mygeocolumn);<BR>
<BR>
Paul<BR>
<BR>
On Tue, Oct 14, 2008 at 6:43 PM, Stephen Baillie<BR>
<steve@alliancesoftware.com.au> wrote:<BR>
> Paul Ramsey wrote:<BR>
><BR>
>> If you're in a planar projection, then<BR>
>><BR>
>> select * from footable where st_dwithin(the_geom, 'POINT(foo bar)',<BR>
>> DISTANCE)<BR>
>><BR>
>> will return based on an indexed search for arbitrary foo and bar. You<BR>
>> need to be in a planar projection so the spatial index works in the<BR>
>> same cartesian domain as the distance calculation.<BR>
>><BR>
><BR>
> Ooh, that looks to be just what I was looking for. Thanks!<BR>
><BR>
> Now that I know what function to look for, the documentation says that it<BR>
> will use indexes "if available". Do I need to do anything other than<BR>
> AddGeometryColumn() to ensure that indexes are available?<BR>
><BR>
>> Every planar projection introduces distance/area distortion, the<BR>
>> question is "do you care". Is it big enough to be concerned about,<BR>
>> given your use case. Variance of +/- 1cm in questions that are need<BR>
>> 10m accuracy aren't important.<BR>
>><BR>
><BR>
> I'm just going on the advice I've received from this list previously, which<BR>
> was that using UTM zone 53 projection for my target area (all of Australia)<BR>
> would give "EXTREME distortion at the edges". I haven't tried to quantify<BR>
> that; I shall find some numbers using UTM 53 and the other projections you<BR>
> suggested.<BR>
><BR>
> Thanks again,<BR>
><BR>
> Steve.<BR>
><BR>
> --<BR>
> Stephen Baillie<BR>
> Developer<BR>
> Alliance Software<BR>
><BR>
> 1/234 Whitehorse Road<BR>
> Nunawading, VIC 3131<BR>
> Australia<BR>
><BR>
> Ph: 03 9877 9921<BR>
> Fax: 03 9894 2106<BR>
><BR>
> _______________________________________________<BR>
> postgis-users mailing list<BR>
> postgis-users@postgis.refractions.net<BR>
> <A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
><BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>