Subscribe to this thread
Home - General / All posts - struggling with a SQL update query
vincent

1,716 post(s)
#11-Jul-18 22:47

Hi,

I'm really having hard time with this one. I'm trying to update a field [F] based on the intersection of the geometries of a drawing [A] and a drawing [B]. The [F] field belongs to the [A] drawing. [F] can be boolean or an integer 8 bits.

I would like to update all objects that are selected by the following select statement :

Select * from [A] as [lines][B] as [pts] where Intersects(

buffer(

([pts].[Geom (I)])

,1,"m")

[lines].[Geom (I)] 

[A] contains lines and [B] contains points.

The update should just turn [F] to True / False or 1 / 0 if the object from [A] intersects the buffered object in [B].

Thank you !

* working in M8

vincent

1,716 post(s)
#11-Jul-18 23:00

auto-solved... finally

Update 

(Select [lines].[F] as [F1] from [A] as [lines][B] as [pts] where Intersects(

buffer(

([pts].[Geom (I)])

,1,"m")

[lines].[Geom (I)] 

)  ) 

Set [F1] =  1 

tjhb

8,227 post(s)
online
#12-Jul-18 05:47

Can't resist a few tweaks.

UPDATE

    (

    SELECT [lines].[F][pts].[ID] 

    FROM

        [A] AS [lines]

        LEFT JOIN

        [B] AS [pts]

        ON Intersects([lines].[ID], Buffer([pts].[ID], 1, "m"))

    )

SET [F] = [ID] IS NOT NULL

;

The main advantage is that this also (re)sets [F] to FALSE where there is no intersection.

(By the way, be careful with Intersects()--very often we really mean Touches(). You might want to check.)

tjhb

8,227 post(s)
online
#12-Jul-18 08:53

I should have noticed before, but I wonder if what you mean is not Intersects(line, point buffer) or Touches(line, point buffer), but Distance(line, point) <= 1. That would be faster too.

vincent

1,716 post(s)
#12-Jul-18 13:35

Always good to have another view ! I learned something, again. Thank you.

You right about Distance. It's enough for my purposes.

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.