[postgis-users] Indices for ST_distance_sphere

Paul Ramsey pramsey at cleverelephant.ca
Tue Oct 14 18:53:04 PDT 2008


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
>



More information about the postgis-users mailing list