Re: [postgis-users] Fastest test for point (nearly) in polygon
Thanks Stephen, that's very helpful. cheers Ben On 14/12/2011, at 10:47 AM, Stephen Woodbridge wrote: > 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@postgis.refractions.net >> http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Fastest test for point (nearly) in polygon
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@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Fastest test for point (nearly) in polygon
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 st_dwithin (province polygon, st_setsrid(st_makepoint(long, lat), 4283), 0.02) st_within (st_buffer(province polygon, 0.02), st_setsrid(st_makepoint(long, lat), 4283)) 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@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
