[postgis-devel] Two Multipoint and Range Intersection

[nicklas.aven at jordogskog.no]

You should look at the function st_DWithin. 

But I think you have some decisions to make. What output do you want. Do you want both the points that isn't exactly at the same place or just one of them.
Do you want the result as multipoint or points (I didn't get the problem with one point per row. Is the problem to put them into the rows, then postgis makes that very good with st_dump, or is the problem, handling them afterwards)


select ?? what do you want to get ???
from
(select (st_dump(geom)).geom from test1) a,
(select (st_dump(geom)).geom from test2) b
where st_dwithin(a.geom,b.geom,1)

you can rebuild those parts of the multipoints that meets your demand of being in a range. Then you should keep som id from the original multipoints and use st_collect and group the data with that id. If you want your multipoints from test1 like that it should work with:

select gid, st_collect(a.geom) from 
(select gid, (st_dump(geom)).geom from test1) a,
test2
where st_dwithin(a.geom,test2.geom,1)
group by gid;

note that you don't have to do st_dump on test2 in this case because we can check for the distance to the whole multipoint.

hope I understood you right.

By the way, the postgis-users list is better for this type of questions.

Hope that helps
Nicklas

2009-07-19 kevinridge  wrote:


>Hello,
>I have two multipoint fields (lets call them test1 and test2). I am
>wondering how to compute an intersection between them but taking a range in
>the middle.
>
>For example, lets say test1 holds ('0 0, 1 0, 0 1, 1 1') and test2 holds ('0
>0, 5 5')
>
>Now when i do select intersection(test2.geom, test1.geom) it would give me
>('0 0'). Is there a way i can specify the distance apart also for each
>point? 
>
>So something like select intersection(test2.geom, test1.geom, 1) and it
>would take each point in test1, extend its geometry by 1 and see if it
>matches anything in test2. So it would return ('0 0, 1 0, 0 1')
>
>I am basically trying to find everything that is similar in two multipoints
>but at a distance apart (so its not right on top of each other). I tried
>doing this with point but since point means i have to insert a row for each
>new one, its really slow when adding like 5 million points. 
>
>Is there a better way than what i am doing? I am really new to postgis and
>any help would be GREATLY appreciated :)
>-- 
>View this message in context: http://www.nabble.com/Two-Multipoint-and-Range-Intersection-tp24558603p24558603.html
>Sent from the PostGIS - Dev mailing list archive at Nabble.com.
>
>_______________________________________________
>postgis-devel mailing list
>postgis-devel at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-devel
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20090719/09ee848e/attachment.html>