[postgis-users] Indices for ST_distance_sphere

[Gregory Williamson]

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

Greg Williamson
Senior DBA
DigitalGlobe

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.

(My corporate masters made me say this.)



-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net on behalf of Paul Ramsey
Sent: Tue 10/14/2008 6:53 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Indices for ST_distance_sphere
 
A UTM zone is too narrow for all of Australia, so that's a bad choice.
But those other projections are parameterized for the whole country,
so unless you accuracy requirements are extremely high, they should
fit the bill.

You'll need to build an index on the geometry to get indexed behavior:

CREATE INDEX mygeoidx ON mytable USING GIST (mygeocolumn);

Paul

On Tue, Oct 14, 2008 at 6:43 PM, Stephen Baillie
<steve at alliancesoftware.com.au> wrote:
> Paul Ramsey wrote:
>
>> If you're in a planar projection, then
>>
>> select * from footable where st_dwithin(the_geom, 'POINT(foo bar)',
>> DISTANCE)
>>
>> will return based on an indexed search for arbitrary foo and bar. You
>> need to be in a planar projection so the spatial index works in the
>> same cartesian domain as the distance calculation.
>>
>
> Ooh, that looks to be just what I was looking for.  Thanks!
>
> Now that I know what function to look for, the documentation says that it
> will use indexes "if available".  Do I need to do anything other than
> AddGeometryColumn() to ensure that indexes are available?
>
>> Every planar projection introduces distance/area distortion, the
>> question is "do you care". Is it big enough to be concerned about,
>> given your use case.  Variance of +/- 1cm in questions that are need
>> 10m accuracy aren't important.
>>
>
> I'm just going on the advice I've received from this list previously, which
> was that using UTM zone 53 projection for my target area (all of Australia)
> would give "EXTREME distortion at the edges".  I haven't tried to quantify
> that; I shall find some numbers using UTM 53 and the other projections you
> suggested.
>
> Thanks again,
>
> Steve.
>
> --
> Stephen Baillie
> Developer
> Alliance Software
>
> 1/234 Whitehorse Road
> Nunawading, VIC 3131
> Australia
>
> Ph:  03 9877 9921
> Fax: 03 9894 2106
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20081014/271f9674/attachment.html>