[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Issue with BOX3D and distance
From: nicklas.aven () jordogskog ! no
Date: 2009-07-29 13:05:37
Message-ID: 200907291305.n6TD5b3L030874 () mail-core ! space2u ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
I think you should look at st_dwithin instead of distance. st_dwithin will will first \
compare boundingboxes wich st_distance or distance have no use of.st_dwithin uses the \
boundingboxes for each geometriy. Then the performance is depending on how many \
vertexes there is in your geometries. Where the boundingboxes is not used, postgis \
will have to calculate and compare the distances between every vertex and your point. \
But st_dwithin should help a lot. then you won't need your box-calculation. /Nicklas \
2009-07-29 Mark Cave-Ayland wrote:
Mulone wrote:
>
> > I'm trying to get all the geometries within a certain radius from a point.
> > Given that distance doesn't use the index, I added the && BBox operator \
> > to narrow down the problem to a smaller dataset, but the problem is that the
> > radius is expressed in meters and the BBOX in degrees, but I'd like to
> > define them in a consistent way. Besides, working on a very small dataset
> > (about 2000 geometries), I was shocked to see that it takes up to 500ms to
> > perform the query... That's why I started messing with the index.
> >
> > Mulone
>
> Okay, this is a fairly standard scenario. What normally happens in a
> query similar to yours is that the && operator pulls matches directly
> from the index, the results of which are then run through the distance()
> function. (Incidentally, I notice you are still using the non ST_ prefix
> functions in your queries - you probably want to change this given that
> these are deprecated)
>
> So I suspect a large amount of it comes down to how do you calculate
> your BOX3D here:
>
> way && setsrid(box3d('BOX3D(-6.216 53.300,-6.220 53.304)'), 4326)
>
> If you can reduce the size of the BOX3D so that it exactly matches your
> area of interest, then that should give you the best overall performance.
>
>
> HTH,
>
> Mark.
>
> --
> Mark Cave-Ayland - Senior Technical Architect
> PostgreSQL - PostGIS
> Sirius Corporation plc - control through freedom
> http://www.siriusit.co.uk
> t: +44 870 608 0063
> _______________________________________________
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
[Attachment #5 (text/html)]
<html>
<head>
<title></title>
<meta name="GENERATOR" content="MSHTML 8.00.6001.18783"></meta>
</head>
<body>
<div align="left">I think you should look at st_dwithin instead of distance. \
st_dwithin will will first compare boundingboxes wich st_distance or \
distance have no use of.st_dwithin uses the boundingboxes for each geometriy. </div> \
<div align="left">Then the performance is depending on how many vertexes there is in \
your geometries. Where the boundingboxes is not used, postgis will have to calculate \
and compare the distances between every vertex and your point. But st_dwithin should \
help a lot. then you won't need your box-calculation.</div>
<div align="left"> </div>
<div align="left">/Nicklas</div>
<div align="left"> </div>
<div align="left"> </div>
<div align="left">2009-07-29 Mark Cave-Ayland wrote:<br />
<br />
Mulone wrote:<br />
><br />
>> I'm trying to get all the geometries within a certain radius from a point.<br />
>> Given that distance doesn't use the index, I added the && BBox operator \
to<br /> >> narrow down the problem to a smaller dataset, but the problem is that \
the<br /> >> radius is expressed in meters and the BBOX in degrees, but I'd like \
to<br /> >> define them in a consistent way. Besides, working on a very small \
dataset<br /> >> (about 2000 geometries), I was shocked to see that it takes up to \
500ms to<br /> >> perform the query... That's why I started messing with the \
index.<br /> >> <br />
>> Mulone<br />
><br />
>Okay, this is a fairly standard scenario. What normally happens in a <br />
>query similar to yours is that the && operator pulls matches directly <br \
/> >from the index, the results of which are then run through the distance() <br />
>function. (Incidentally, I notice you are still using the non ST_ prefix <br />
>functions in your queries - you probably want to change this given that <br />
>these are deprecated)<br />
><br />
>So I suspect a large amount of it comes down to how do you calculate <br />
>your BOX3D here:<br />
><br />
>way && setsrid(box3d('BOX3D(-6.216 53.300,-6.220 53.304)'), 4326)<br />
><br />
>If you can reduce the size of the BOX3D so that it exactly matches your <br />
>area of interest, then that should give you the best overall performance.<br />
><br />
><br />
>HTH,<br />
><br />
>Mark.<br />
><br />
>-- <br />
>Mark Cave-Ayland - Senior Technical Architect<br />
>PostgreSQL - PostGIS<br />
>Sirius Corporation plc - control through freedom<br />
>http://www.siriusit.co.uk<br />
>t: +44 870 608 0063<br />
>_______________________________________________<br />
>postgis-users mailing list<br />
>postgis-users@postgis.refractions.net<br />
>http://postgis.refractions.net/mailman/listinfo/postgis-users<br />
><br />
></div>
</body>
</html>
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic