[postgis-users] Fastest test for point (nearly) in polygon

[Stephen Woodbridge]

On 12/13/2011 9:38 PM, Ben Madin wrote:
> G'day all,
>
> I'm sure I've seen a similar discussion to this question, but I can't
> find it now. During a bulk data submission process we have a large
> number of point locations and the code of the province in which they
> are 'reputed' to be occurring. Having put some basic checks through
> the system we are inclined to check this at upload time, but have to
> realistically anticipate that points within a certain limit will be
> adequately accurate (at the moment about 2km, but probably doesn't
> have to be strictly 2km)
>
> There are any number of ways of doing this - all uploaded data (and
> geometries) are currently in EPSG 4283 (Australian Lat Long).
>
> Options would include using :
>
> st_distance_sphere(province polygon, st_setsrid(st_makepoint(long,
> lat), 4283))<  2000

this has to compute all distances regardless

>
> st_dwithin (province polygon, st_setsrid(st_makepoint(long, lat),
> 4283), 0.02)

st_dwithin() is fastest and if you need it more accurately, then:

st_dwithin(...) and st_distance_sphere(province polygon, 
st_setsrid(st_makepoint(long, lat), 4283))<  2000

> st_within (st_buffer(province polygon, 0.02),
> st_setsrid(st_makepoint(long, lat), 4283))

don't buffer the polygon, always buffer the point and even faster than 
that use st_dwithin

> st_intersects(province polygon,
> st_buffer(st_setsrid(st_makepoint(long, lat), 4283)))
>
> or any number of similar variants.
>
> Our problem is that there are many rows, and even prior to these
> checks, other checks on other aspects of the data were inclined to
> take longer than we would like.
>
> I can accept that trying to avoid to much transforming is probably
> better (and accepting some slightly inconsistent accuracy) from a
> speed perspective.
>
> Can anyone suggest which of these checks (or others) might be
> fastest?
>
> I'm leaning towards the latter two as they are mainly functions
> offered by GEOS, which I understand to be faster than PL functions.
>
> cheers
>
> Ben
>
> _______________________________________________ postgis-users mailing
> list postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users