[postgis-users] Help: Performance of spatial query

[Paul Ramsey]

Check out the expand function, for a handy shortcut for creating a 
"slightly larger" bbox to use for this problem.

Chris is right, the slowness is caused by using an expensive function 
(buffer) in the midst of a high volume query.

On Monday, March 15, 2004, at 09:59 AM, chodgson at refractions.net wrote:

> I expect that using the geos function to buffer your point is slowing 
> things
> down because it has to convert into a geos object and back toa postgis 
> object.
> It would probably be better to just use the distance function, and 
> then in
> order to index it, build a bounding box which is effectively your 
> buffered
> point by using the minx() and miny() functions and string 
> concatenation ie.
> build the wkt of a box object by concatenating some strings with the 
> minx() and
> miny() function. Your where clause should look something like:
>
> WHERE NOD2.shape && (concatenated string using minx/y( NOD1.shape) )
> AND distance( NOD1.shape, NOD2.shape ) <= 0.2
>
> I don't know the exact syntax for the string stuff otherwise I'd give 
> it to
> you...
>
> HTH.
> Chris
>
>
> Quoting Jeremy Palmer <jpalmer at paradise.net.nz>:
>
>> Hi,
>>
>> I have been trying to complete a query that tries to locate duplicate 
>> points
>> i.e. points that have been accidentally created twice or more – These 
>> points
>> may not exactly be in the same geometric position. My basic 
>> methodology is
>> to complete a 0.2m proximity check for each geometry point in the 
>> database.
>> The datum of the geometries is in NZGD2000 (basically the same 
>> parameters as
>> WGS84).
>> To do this I was using the buffer function with the overlaps operator 
>> to
>> find points within 0.000002 of a degree (~ 0.2m) of each other. I was 
>> also
>> trying to stop the point reporting on itself by checking that NOD1.id 
>> <
>> NOD2.id. However as explain query shows below my method produces a 
>> Cartesian
>> product between the 2 aliases of the node table.
>>
>> Can anyone help me with constructing a more efficient query? I have 
>> provided
>> the query explain and table details if that helps.
>>
>>
>> SELECT NOD1.id as nod_id1,
>>        NOD2.id as nod_id2,
>>        NOD1.shape as shape1,
>>        NOD2.shape as shape2
>> FROM node NOD1,
>>           node NOD2
>> WHERE NOD2.shape && buffer(NOD1.shape, 0.000002)
>> AND   NOD1.id < NOD2.id;
>>
>> Explain Query was …
>>
>>   Nested Loop  (cost=590312.88..5062636139085.67 rows=3509048593179
>> width=288)
>>    Join Filter: (("inner".shape && buffer("outer".shape, 2e-06::double
>> precision)) AND ("outer".id < "inner".id))
>>    ->  Seq Scan on crs_node nod1  (cost=0.00..379897.88 rows=10260188
>> width=144)
>>    ->  Materialize  (cost=590312.88..903329.76 rows=10260188 
>> width=144)
>>          ->  Seq Scan on crs_node nod2  (cost=0.00..379897.88 
>> rows=10260188
>> width=144)
>>
>> Table "public. node"
>>      Column      |         Type         | Modifiers
>> -----------------+----------------------+-----------
>>  id              | integer              | not null
>>  cos_id_official | integer              | not null
>>  type            | character varying(4) | not null
>>  status          | character varying(4) | not null
>>  order_group_off | integer              | not null
>>  sit_id          | integer              |
>>  alt_id          | integer              |
>>  wrk_id_created  | integer              |
>>  se_row_id       | integer              |
>>  audit_id        | integer              | not null
>>  shape           | geometry             |
>> Indexes:
>>     "pkey_node" primary key, btree (id)
>>     "nod_aud_id" unique, btree (audit_id)
>>     "fk_nod_alt" btree (alt_id)
>>     "fk_nod_cos" btree (cos_id_official)
>>     "fk_nod_sit" btree (sit_id)
>>     "fk_nod_wrk" btree (wrk_id_created)
>>     "nod_shape_index" gist (shape)
>> Check constraints:
>>     "$1" CHECK (srid(shape) = 104108)
>>     "$2" CHECK (geometrytype(shape) = 'POINT'::text OR shape IS NULL)
>>
>> Thank you very much
>>
>> Jeremy Palmer
>>
>>
>>
>> _______________________________________________
>> 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
>
      Paul Ramsey
      Refractions Research
      Email: pramsey at refractions.net
      Phone: (250) 885-0632